topic Re: Running Macro in Do-Loop in SAS Programming

Running Macro in Do-Loop

maroulator — Sat, 13 Oct 2012 05:20:46 GMT

I am trying to run a macro named 'collateral' within a do-loop, such that everytime the do-loop is ran, it a) increments the variable 'increment' and b) the macro 'collateral' and the steps captured in it are also ran. Below is the code that I have so far. The variable 'increment' feeds into the macro 'collateral'; the macro 'collateral' runs fine on its own, but things go awry once I put it into the do-loop below.

Any suggestions?

-Maroulator

%let increment=0;

data test;

do until (increment=50);

&increment=&increment+1;

%collateral;

end;

run;

Re: Running Macro in Do-Loop

Tom — Sat, 13 Oct 2012 12:14:12 GMT

You have mixed up macro code and data step code. The best way to do this depends on what the macro COLLATERAL is actually doing.

If it is just generating statements to be included within a single data step then you might be able to do something like:

data test;

do increment=1 to 50 ;

%collateral;

end;

run;

In that case the macro should be referencing the datastep varaiable INCREMENT rather than using &INCREMENT to expand the value of the macro variable INCREMENT.

If it is generating one or more complete DATA or PROC steps then you could wrap it in another macro so that you can use %DO loop instead.

%macro outer(limit);

%do increment=1 %to &limit;

%collateral;

%end;

%mend outer;

%outer(50);

Re: Running Macro in Do-Loop

maroulator — Sat, 13 Oct 2012 15:20:50 GMT

Tom,

Thanks; this is very helpful. Just two more questions referring to the code that I have thus far. This is an abridged version of my collateral macro; the whole thing contains 5 datasteps.

1) At the end of the day, I need the variable INCREMENT in the OUTER macro to stop increasing when the variable NEWASSESSED_FEES in the very last datastep of the COLLATERAL macro reaches 0 (or a value very close to 0).

2) The variable INCREMENT will be taking values anywhere from 0 to 35,000,000; is there a way to have the code not iterate 35 million times?

-Christos

%let wvr=150;

%macro outer(limit);

%do increment=0 %to &limit;

%collateral;

%end;
%mend outer;

%outer(1000);

%macro collateral();

proc sql;

create table all_daily_fees4 as

select aba, sum(daily_cr_net) as newrmp_cr_net

from all_daily_fees3

group by aba;

quit;

data rmp_fees;

set all_daily_fees4;

newassessed_fees=newrmp_cr_net-&wvr;

run;

%mend collateral;

Re: Running Macro in Do-Loop

Tom — Sat, 13 Oct 2012 17:03:51 GMT

You need to set a macro variable that you can test to stop the loop. Because you are doing it in a nested macro call then make sure the target macro variable exists before the inner macro is called so that the value can persist after the inner macro has terminated.

The macro %DO statement is not as flexible as the data step DO statement so you will probably want to change to using a %DO %WHILE and manually incrementing the counter. (It is not clear what the increment variable is intended to do? Is it just used as an upper bound on the number of iterations? or is its value involved in the logic of the complete version of COLLATERAL macro?)

Something like below might work. Note that it will set the FINISH variable to 1 (true) when any of the ABA groups reaches a value less than &FUZZ. If you intended it to stop when all are close to zero then you need to change the logic.

%let wvr=150;

%let fuzz=0.0001;

%macro outer(limit);

%let finish=0;

%let increment=0;

%do %while ( (&increment < &limit) and not &finish );

%let increment = %eval(&increment + 1);

%collateral;

%end;

%put Increment=&increment Finish=&finish;
%mend outer;

%macro collateral;

proc sql;

create table all_daily_fees4 as

select aba

, sum(daily_cr_net) as newrmp_cr_net

from all_daily_fees3

group by aba

;

quit;

data rmp_fees;

set all_daily_fees4;

newassessed_fees=newrmp_cr_net-&wvr;

if abs(newassessed_fees) < &fuzz then call symputx('finish','1');

run;

%mend collateral;

%outer(1000);

Re: Running Macro in Do-Loop

maroulator — Sun, 14 Oct 2012 01:11:51 GMT

Tom, my apologies; I should've sent you my entire code such that the INCREMENT variable was included. Here it is below; all variables partaking in calculations are numeric. The INCREMENT variable increases the value of the numeric variable COLLATERAL2; the goal is to capture the value of INCREMENT (with INCREMENT starting at zero), such that variable NEWASSESSED_FEES goes to zero. My questions (1) and (2) above still stand; would that change the code that you propose above?

%macro collateral();

data all_daily_fees;

set all_daily_fees;

collateral2=collateral+&increment;

run;

data all_daily_fees2;

set all_daily_fees;

if newbal<0 then do;

if abs(newbal)>collateral2 then do;

newcollod2=collateral2;

newuncollod2=abs((collateral2+newbal));

end;

else do;

newcollod2=abs(newbal);

newuncollod2=0;

end;

else do;

newcollod2=0;

newuncollod2=0;

end;

run;

data all_daily_fees3;

set all_daily_fees2;

if fee_cd='F' then do;

if newbal<0 then newdaily_cr_net=&ed_uc_rate*(newUnCollOD2)/1291;

else newdaily_cr_net=0;

end;

run;

proc sql;

create table all_daily_fees4 as

select aba, sum(newdaily_cr_net) as newrmp_cr_net

from all_daily_fees3

group by aba;

quit;

data rmp_fees;

set all_daily_fees4;

newassessed_fees=newrmp_cr_net-&wvr;

run;

%mend;

%macro outer(limit);

%do increment=31999000 %to &limit;

%collateral;

%end;

%mend outer;

%outer(32000000);

Re: Running Macro in Do-Loop

maroulator — Sun, 14 Oct 2012 03:53:56 GMT

One more thing; what is the purpose of the 'not &finish' in the do-while part of the macro 'outer'?

Re: Running Macro in Do-Loop

Tom — Sun, 14 Oct 2012 17:53:00 GMT

You wanted a method to stop the looping. So the idea I proposed and demonstrated was to use a macro variable that will be set by the inner macro when the stopping criteria has been met. I decided to call that macro variable FINISH and code it as binary 0/1 values (0 is false and 1 is true or "finished"). So before beginning the loop I set FINISH to 0 (false) and then within the macro it conditionally sets the value to 1 (true). So then the (NOT &finish) condition in the %DO %WHILE loop will become false so it will stop calling the inner macro.

Re: Running Macro in Do-Loop

maroulator — Mon, 15 Oct 2012 00:38:03 GMT

Thanks! Is there a way of taking the value of INCREMENT after the %do-%while stops and putting it into a table?

Re: Running Macro in Do-Loop

maroulator — Wed, 17 Oct 2012 03:10:26 GMT

Hi Tom,

Many thanks for the help thus far. I have what I am hoping to be my last question.

Below is the code that we have been discussing thus far. The difference now is that I have introduced the X macro variable in the OUTER macro and I am re-setting it in the last lines of the COLLATERAL macro depending on the value of DIFFERENCE, such that the variable INCREMENT in the OUTER MACRO gets incremented by X and flows into the COLLATERAL macro. The idea still is to increment the variable INCREMENT such that the DIFFERENCE valiable approaches zero; when DIFFERENCE approaches zero, the outer OUTER should stop calling the COLLATERAL macro. I have not yet tested this approach with the X variable and will not be able to troubleshoot for another day. Could I please have a sanity check on 1) the treatment of variable X, and 2) the 'killing' of the OUTER macro (by way of the binary finish variable) once DIFFERENCE is between -1 and 1. Again below is the code; thanks in advance.

%macro outer();

%let increment=0;

%let x=0;

%let finish=1;

%do %while (&finish);

%let increment=%eval(&increment + &x);

%end;

%mend outer();

%outer();

%macro collateral();

data all_daily_fees2;

set all_daily_fees;

collateral2=collateral+&increment;

if newbal<0 then do;

if abs(newbal)>collateral2 then do;

newcollod2=collateral2;

newuncollod2=abs((collateral2+newbal));

end;

else do;

newcollod2=abs(newbal);

newcollod2=0;

end;
end;

else do;

newcollod2=0;

newuncollod2=0;

end;

if fee_cd='F' then do;

if bal<0 then daily_cr_net=&ed_uc_rate*(UncollOD/1291);

else daily_cr_net=0;

if newbal<0 then newdaily_cr_net=&ed_uc_rate*(newUncollOD2/1291);

else newdaily_cr_net=0;

end;

proc sql;

create table all_daily_fees4 as

select aba, sum(daily_cr_net) as rmp_cr_net, sum(newdaily_cr_net) as newrmp_cr_net

from all_daily_fees3

group by aba;

quit;

data rmp_fees;

set all_daily_fees4;

assessed_fees=max(0, rmp_cr_net-&wvr);

newassessed_fees=max(0,newrmp_cr_net-&wvr);

difference=newassessed_fees-assessed_fees;

if difference between 10000000 and 100 then %let x=1000000; /*If difference is between 10000000 and 100, reset x to 1000000*/

if difference between 100 and 10 then %let x=1000;

if difference between 1 and 10 then %let x=1;

if difference between -1 and 1 then call symputx('finish',0); /*If difference is between -1 and 1, kill the %outer macro*/

run;

%mend collateral();

Re: Running Macro in Do-Loop

maroulator — Wed, 17 Oct 2012 03:20:31 GMT

Hi Tom,

When you get a chance, could you please take a look at the latest comment in the discussion? I accidentally replied to myself.

Christos