09-29-2014 06:19 AM
I have some code which runs off a dataset (called - Additional Dataset) which is created monthly. So my dataset is and August file then my summary will display August.
I need my summary to display data 1 month ago, 2 months ago, 1 year ago from current month.
So I would need - August 2014, July 2014, June 2014, & August 2013.
Any idea as to how I go about doing this please? I will need to make changes to the DDE to accomdate. Is it best to replicate this data 4 times and each assign a macro to it?
/*NEW ASSET TYPE - VOLUME and PERCENTAGE*/
proc freq data=Additional_Dataset;
table &var./out=&var._count missing norow nocol nocum nopercent;
Filename odata dde
"Excel|G:\Mort\Reporting Challenger\[Rk.xlsx]Dashboard!R6C18:R8C19" notab ;
FILE odata dlm='09'x;
PUT count percent;
/*NEW ASSET TYPE - VALUE and PERCENTAGE*/
proc means data=Additional_Dataset sum;
output out=&var._balance(drop=_type_ _freq_) sum=;
filename odata dde
"Excel|G:\Mort\Reporting Challenger\[Rk.xlsx]Dashboard!R6C20:R8C21" notab ;
FILE odata dlm='09'x;
PUT balance_outstanding percent;
09-29-2014 07:50 AM
Your time window is shifting when running those monthly job-s
A fundamental solution would be defining thos shifting windows as part of your autoexec (a whole set of them).
That could be based on the system time. Better would be using a macro-var like simdate to be set externally. When that does not exist (left empty) using the system date. By that you can rerun job-s for an other period, using faked data with regression tests etc. It can be made part of your DTAP environment structure.
09-29-2014 09:44 AM
The standard SAS tool for handling this is the INTNX function. It's easier to illustrate in a DATA step (although %sysfunc could be applied to use only macro language):
back1 = intnx('month', "&sysdate9"d, -1);
back2 = intnx('month', "&sysdate9"d, -2);
back13 = intnx('month', "&sysdate9"d, -13);
It returns the first day of a time period. If you were to run this program any day during September 2014, BACK1 would be the SAS date equivalent of August 1, 2014. Similarly, BACK2 would be the SAS date equivalent of July 1, 2014. All you need to do is to run the program during the current month, in order to automatically get the right set of months. Left to do: (1) translate from SAS dates into the character strings that you want, and (2) use CALL SYMPUT to transfer those character strings to macro variables.