Macro Date Range

[#1]

Hi,

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*/

%macro freqit(var);

proc freq data=Additional_Dataset;

table &var./out=&var._count missing norow nocol nocum nopercent;

run;

data &var._count(drop=&var.);

set &var._count;

where &var.=1;

percent=percent/100;

run;

%mend;

%freqit(performing);

%freqit(Performing_Arr_Elsewhere);

%freqit(non_performing);

data all_new_asset_type_volume;

set

performing_count

Performing_Arr_Elsewhere_count

non_performing_count;

run;

Filename odata dde

"Excel|G:\Mort\Reporting Challenger\[Rk.xlsx]Dashboard!R6C18:R8C19" notab ;

DATA _NULL_;

SET all_new_asset_type_volume;

FILE odata dlm='09'x;

PUT count percent;

RUN;

/*NEW ASSET TYPE - VALUE and PERCENTAGE*/

%macro outbal(var);

proc means data=Additional_Dataset sum;

var balance_outstanding;

where &var.=1;

output out=&var._balance(drop=_type_ _freq_) sum=;

run;

data &var._balance;

set &var._balance;

percent=balance_outstanding/&total_bal.;

balance_outstanding=balance_outstanding/1000000;

run;

%mend (var);

%outbal(performing);

%outbal(Performing_Arr_Elsewhere);

%outbal(non_performing);

data all_new_asset_type_balance;

set

performing_balance

Performing_Arr_Elsewhere_balance

non_performing_balance;

run;

filename odata dde

"Excel|G:\Mort\Reporting Challenger\[Rk.xlsx]Dashboard!R6C20:R8C21" notab ;

DATA _NULL_;

SET all_new_asset_type_balance;

FILE odata dlm='09'x;

PUT balance_outstanding percent;

RUN;

[jakarman]

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.

[Astounding]

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):

data _null_;

   back1 = intnx('month', "&sysdate9"d, -1);

   back2 = intnx('month', "&sysdate9"d, -2);

   back13 = intnx('month', "&sysdate9"d, -13);

run;

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.

Good luck.