topic Re: Incorrect order of mixed format sas dates in SAS Data Management

Incorrect order of mixed format sas dates

SSchneider — Thu, 24 Sep 2015 21:38:15 GMT

I have a dataset with lots of daily data. I need a report that summarizes the data into various date formats on a go forward rolling date basis.

If the record is GE than 3 years old I want it summed by year,

if the record is greater than 14 months but less than 3 years I want it summed by year and quarter

if the record is within 13 months I want it summed by year month.

I have created this code which is giving me the correct value for the DATE_CAT variable

DATE_DELTA = YEAR(a)-YEAR(b);

DATE_DELTA2 = INTCK('MONTH',b,a);

IF DATE_DELTA2 LE 12 THEN date_cat = put(b,yymon7.);

if DATE_DELTA LE 2 AND DATE_DELTA2 GE 13 Then DATE_CAT = put(b,yyq6.);

if DATE_DELTA GE 3 then date_cat = PUT(b,YEAR.);

The problem is when I run a proc.. (freq, tabulate, report, etc.) the order of the DATE_CAT variable is not in the correct date order. It looks like its sorting alpha numeric hence why AUG is before Dec but Dec before Jul.

Is there a way to retain the correct sort order of the date with the modified date catagory variable?

Thanks,

ex:

date_cat	Frequency
2009	8177
2010	19655
2011	65243
2012	92328
2013Q1	28020
2013Q2	30888
2013Q3	34284
2013Q4	35988
2014AUG	9624
2014DEC	7243
2014JUL	10668
2014NOV	8115
2014OCT	9194
2014Q1	30324
2014Q2	32268
2014SEP	10142
2015APR	3015
2015AUG	644
2015FEB	5711
2015JAN	6419
2015JUL	1766
2015JUN	3178
2015MAR	3516
2015MAY	2488

Re: Incorrect order of mixed format sas dates

ballardw — Thu, 24 Sep 2015 21:58:47 GMT

The procedures you mention usually have an ORDER=option but they behave somewhat differently in each procedure.

With Tabulate , Freq, Means and Summary(on a class statement) or Report (in a define statement) you may get what you are looking for by sorting on either variable A or B and then using order=data to present data by the order it appears in the dataset.

Re: Incorrect order of mixed format sas dates

Astounding — Fri, 25 Sep 2015 03:14:21 GMT

It would be easier to use a different scheme, instead of 2015AUG, for example, use 201508. You could obtain this in a few ways, one being:

length datecat $ 6;

if date_delta2 < 12 then datecat = put(b, yymmddn8.);

If you wanted to print 2015AUG instead of 201508, you would need to take two additional steps. First, create a format that translates from "201508" into "2015AUG" (and for all the other possible months as well). And second, specify ORDER=INTERNAL (which is the default for some procedures, but not for all).

Good luck.