How to automate a code whose execution part varies every month

[nikunjgattani]

Hi,

I am running a code each month. In which I have to calculate monthly growth by taking base value as of last quarter.
Here is the dummy code -

/*April 2015*/

if xyz_Date >= '01april2015:00:00:00'dt then do;
net_growth = sum(bal_04_2015,-bal_03_2015);

end;
else do;
net_growth = sum(bal_04_2015,-bal_03_2015);
end;

/*May 2015*/

if xyz_Date >= '01may2015:00:00:00'dt then do;
net_growth = sum(bal_05_2015,-bal_04_2015);
end;

else if xyz_Date >= '01apr2015:00:00:00'dt then do;
net_growth = sum(bal_05_2015,-bal_03_2015);
end;
else do;
net_growth = sum(bal_05_2015,-bal_03_2015);
end;

/*June 2015*/

if xyz_Date >= '01jun2015:00:00:00'dt then do;
net_growth = sum(bal_06_2015,-bal_05_2015);
end;
else if xyz_Date >= '01may2015:00:00:00'dt then do;
net_growth = sum(bal_06_2015,-bal_04_2015);
end;
else if xyz_Date >= '01apr2015:00:00:00'dt then do;
net_growth = sum(bal_06_2015,-bal_03_2015);
end;
else do;
net_growth = sum(bal_06_2015,-bal_03_2015);
end;

In the above code, if the current month is April, then April section will run only, if May then May section will run only, if June then June section will run only
and if July then again code will be same as April section with different variables (variables for July and June month).

How can I automate this code?

Any Idea...Looping or something else

[Haikuo]

This will generally to be a Macro job: branching SAS code based conditions. Something along the line of the following code may get you going:

%macro Apr;
/*April 2015*/
if xyz_Date >= '01april2015:00:00:00'dt then do;
 net_growth = sum(bal_04_2015,-bal_03_2015); 

end;
else do;
 net_growth = sum(bal_04_2015,-bal_03_2015);
end;
%mend;

%macro May;
/*May 2015*/

if xyz_Date >= '01may2015:00:00:00'dt then do;
 net_growth = sum(bal_05_2015,-bal_04_2015); 
end;

else if xyz_Date >= '01apr2015:00:00:00'dt then do;
 net_growth = sum(bal_05_2015,-bal_03_2015); 
end;
else do;
 net_growth = sum(bal_05_2015,-bal_03_2015);
end;
%mend;

%macro Jun;
if xyz_Date >= '01jun2015:00:00:00'dt then do;
 net_growth = sum(bal_06_2015,-bal_05_2015); 
end;
else if xyz_Date >= '01may2015:00:00:00'dt then do;
 net_growth = sum(bal_06_2015,-bal_04_2015); 
end;
else if xyz_Date >= '01apr2015:00:00:00'dt then do;
 net_growth = sum(bal_06_2015,-bal_03_2015); 
end;
else do;
 net_growth = sum(bal_06_2015,-bal_03_2015);
end;
%mend;

%let month=%sysfunc(today(),monname3.);

%macro test;
 %&month
%mend;


data steps;
blah...;
%test
run;

Please note, main Macro 'test' is just to give you some flexibilities if you want to do more than just calling another nested macro. In your case, you can simply call the monthly Macros directly. 

[ballardw]

You should provide at least a description of how your data is structured. I suspect this might be possible with arrays to define the  period variables and use of appropriate date or datetime functions to extract the month from the XYZ_date variable. The TODAY function generally works to get the date code is run so that might be useable for branches.

I would suggest not using a variable with DATE in the name when it is a datetime variable as someone could get confused.

You really need to provide some example input data and desired output;

Also, what happens in January, Februrary and March? It looks like you might be looking across year boundaries. If this data is coming from a summary result, it may be easier to implement with the rawer data then a summary.

[Reeza · Accepted solution]

I'd also bet that if you changed for data format to a long format instead of wide you'll make your life simpler.