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 |
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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.