BookmarkSubscribeRSS Feed
SSchneider
Calcite | Level 5

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

 

2 REPLIES 2
ballardw
Super User

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.

Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 927 views
  • 0 likes
  • 3 in conversation