09-24-2015 05:38 PM
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?
09-24-2015 05:58 PM
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.
09-24-2015 11:14 PM
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).