SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Incorrect order of mixed format sas dates

Reply
Occasional Learner
Posts: 1

Incorrect order of mixed format sas dates

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_catFrequency
20098177
201019655
201165243
201292328
2013Q128020
2013Q230888
2013Q334284
2013Q435988
2014AUG9624
2014DEC7243
2014JUL10668
2014NOV8115
2014OCT9194
2014Q130324
2014Q232268
2014SEP10142
2015APR3015
2015AUG644
2015FEB5711
2015JAN6419
2015JUL1766
2015JUN3178
2015MAR3516
2015MAY2488

 

Super User
Posts: 10,500

Re: Incorrect order of mixed format sas dates

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.

Super User
Posts: 5,083

Re: Incorrect order of mixed format sas dates

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.

Ask a Question
Discussion stats
  • 2 replies
  • 255 views
  • 0 likes
  • 3 in conversation