Excel just looks at me like I’m an idiot. And it sorts alphabetically(!).
I cracked this one on SAS, but not without false starts. CATX(SUBSTR... gave results I still do not know how to explain: I used 3 SUBSTR statements in the correct order, so the output should be 03MAR10, but it kept coming out as MAR0310. Does CATX arrange statements automatically?
Sunday-frustration-lengthed-story later, I ended up with this:
sale_date=input((substr(sale_date,5,2)||substr(sale_date,1,3)||substr(sale_date,8)),date7.);
I first included ||20|| before the 2-digit-year, to use date9., but it added a bunch of spaces after the 20? Trim didn’t help. Putting the 20 in quotes did help. Then I realized I could just use date7.
There was much cheering in my apartment.
Hm. I swear the first time I ran this it formatted those variables to date9., but now they are showing up as character. So my revised code uses rename= and drop= to sidestep that problem.
ReplyDeleteSale_Date=input((substr(sale_date2,5,2)||substr(sale_date2,1,3)||substr(sale_date2,8)),date7.);
Does CATX arrange statements automatically?
ReplyDeleteNo. I realized that I was making a mistake -- The first element in the catx function is the delimiter, so it was inserting the day in between the month and year.