Macro Help (urgent)

[Violet_09]

Hello SAS users, I need help to modify the below macro. For the cbalance, it has to be &name - 1 (for example if &name is January and &datayear is 2012 then it has to say cbalance_December_2011). I am struggling to modify this. The code needs to be flexible to account for the year movement from December to January. Below is my code. Could someone help take a look? Many thanks!!

%macro bal;

%let edate = %sysfunc(inputn(&ending_year&ending_month,yymmn6.),date.);                                                              

%let datayear=&beginning_year;                                                                                                                                        

data dataset.new;                                                                                                                

set dataset.new;      

%do j=0 %to 3;                                                                                                                        

%let sdate = %sysfunc(inputn(&datayear&beginning_month,yymmn6.));                                                                    

%do i=0 %to 11;   %do h=-1 %to 10;   %put sdate=&sdate;                                                                                                                    

%let nextsdate=%sysfunc(intnx(month,&sdate,&i),date.);                                                                                

%let lastday=%sysfunc(day(%sysfunc(intnx(month,&sdate,&i,end))));                                                                    

%let datamonth=%sysfunc(month(%sysfunc(inputn(&nextsdate,date.))));                                                                  

%let datayear=%sysfunc(year(%sysfunc(inputn(&nextsdate,date.))));                                                                    

%let name=%sysfunc(intnx(month,&sdate,&i),monname.); 

%let prevdate=%sysfunc(intnx(month,&sdate,&h),date.); 

%let prevmonth=%sysfunc(putn(%sysfunc(intnx(month,&sdate,&h),monname.))); 

%let prevyear=%sysfunc(year(%sysfunc(inputn(&prevdate,date.))));    

%if &nextsdate=&edate %then %do;                                                                                                      

%let i=11;                                                                                                                            

%let j=3;   

%let h=10; 

%end;                                                                                                                                      

Rt1_&name.&datayear = (balance1_&name.&datayear / cbalance_&prevmonth.&prevyear)*100;

Rt2_&name.&datayear  = (balance2_&name.&datayear / cbalance_&prevmonth.&prevyear)*100;

%end;                                                                                                                                    

%end;                                                                                                                                

%end;                                                                                                                                

run;

%mend;

%bal

[art297]

It would help if you provide some example data .. preferably in the form of a datastep.

[Violet_09]

I do not have a sample data that I can send. But below is the log - outputs are not what I need. My feeling is I put the code in the wrong loop. The first one is correct then it is wrong all the way.

MPRINT(ANALYSIS7): Rt1_June2009 = (balance1_June2009 / cbalance_May2009)*100;

MPRINT(ANALYSIS7): Rt2_June2009 = (balance2_June2009 / cbalance_May2009)*100;

sdate=18049

MPRINT(ANALYSIS7): Rt1_June2009 = (balance1_June2009 / cbalance_June2009)*100;

MPRINT(ANALYSIS7): Rt2_June2009 = (balance2_June2009 / cbalance_June2009)*100;

sdate=18049

MPRINT(ANALYSIS7): Rt1_June2009 = (balance1_June2009 / cbalance_July2009)*100;

MPRINT(ANALYSIS7): Rt2_June2009 = (balance2_June2009 / cbalance_July2009)*100;

sdate=18049

MPRINT(ANALYSIS7): Rt1_June2009 = (balance1_June2009 / cbalance_August2009)*100;

MPRINT(ANALYSIS7): Rt2_June2009 = (balance2_June2009 / cbalance_August2009)*100;

sdate=18049

MPRINT(ANALYSIS7): Rt1_June2009 = (balance1_June2009 / cbalance_September2009)*100;

MPRINT(ANALYSIS7): Rt2_June2009 = (balance2_June2009 / cbalance_September2009)*100;

sdate=18049

[Astounding]

I think you would find this relatively easy to do if you broke the code into two DATA steps.  In the first one, use DATA step functions to calculate the values you will need, and finally CALL SYMPUT to transfer them to macro variables.  Then use those macro variables in the second DATA step.

Good luck.

[Patrick]

I'd probably generate an array statement and then would do all the rest on data step level.

I believe in your code the command generation would need to be inside of the first loops.

It's really hard to say without test data and an example of the expected result.

[Astounding]

Violet_09,

First, let's worry about generating the proper names.  Then we can worry about how to incorporate them into your program.

Please correct any of this if it is wrong.

It looks like you are starting with two macro variables:  &ENDING_YEAR (which contains 4 digits) and &ENDING_MONTH (which contains two digits).  Based on that, you need to generate a name that represents the prior month.  (Perhaps you need to generate a set of names representing a range of months, but your post doesn't make that clear.)

That's relatively easy for a DATA step to do.  For example:

data test;

   current_month = mdy(&ending_month, 1, &ending_year);

   name1 = cats('cbalance_', put(current_month,monname.), '_', put(current_month,year4.));

   prior_month = current_month - 1;

   name2 = cats('cbalance_', put(prior_month,monname.), '_', put(prior_month,year4.));

run;

Do NAME1 and NAME2 contain exactly what you are looking for?  Do you need additional names (since it does look like the original macro contained a few loops)?  If NAME1 and NAME2 are correct, how should they be used in your program?

Apologies if there are any errors in the code, but I'm not in a position to test this right now.

Good luck.