Looping through passing parameters

[robbane28]

Hi,

I'm very new to SAS. I've tried reading through similar topics and it seems as though what I'm trying to do can't be done, but it seems like such an essential of programming that I'm struggling to get round it.

Is it possible to execute a macro, passing incrementing variables? This and varaints of it throw errors. I want to iteratively create a series of tables (I realise I've taken out the changing table names as I'm trying to simplfy my code to see where the errors are).

It looks like using Call Execute might work, but it seems a bit clunky...

%Let CLV_Months = 36;

Option MPRINT;

%Macro CreateChurn(ChurnRate);

Data ChurnCurve;

PC_Live = 1;

Retain PC_Live;

do i = 0 to &CLV_Months;

          PC_Live = PC_Live * (1-&&ChurnRate);

          Output;

End;

Rename i = Month;

Run;

%Mend CreateChurn;

Data CCM;

Do MN = 1 to 10;

          M1 = MN/100;

          %CreateChurn(&M1);

End;

Run;

Any assitance greatly appreciated.

Regards,

Rob

[DF · Accepted solution]

Hi Rob,

I think you've mixed up how the SAS macro processor works.

In your code you're running your macro inside a datastep.  I can understand why you'd have wanted to do this in order to run CreateChurn() with the values required.  However, SAS's macro's aren't like a function or procedure in a conventional programming language.  It's perhaps closer to the #define directive in C/C++ (although more complex) in that the macro pre-processor, which runs before your data steps are run, executes your macro code and returns the results as text which are then processed like any other code.

This ends up meaning your code tries to run a data step inside another data step, which will not work or do as you expect.  (I tried to write up the equivalent output in code, but even a simple example ended up being quite long.)

The two simplest solutions are to either use Call Execute as you mentioned, or simply move the "loop" function to the macro itself and run the macro once.

As a simple example of the macro version, please see below:

%macro runme();

%do i=1 %to 10;

     data test_&i.;

        var = i;

    run;

%end;

%mend;

%runme();

This uses the macro processer to run the loop from 1 to 10, and creates 10 datasets with one row named Test_1 to Test_10.

I hope that's enough to help you make some progress!

[robbane28]

Thanks ever so much. Great answer, I'm just trying to teach myself various techniques and doing such a thing with a macro felt obvious but clearly it doesn't work that way, internalising the loop or using execute seems to be perfect, thanks.

[DLing]

Think of the macro processor as a thing that just spits out code to the main SAS processor.  Thus, if there's structure to your code, then a macro may allow you to represent it more compactly rather than having to write all of it out long-hand, and is especially useful when you need to execute different code depending on certain conditions, or execute code a variable number of times.

In your example, if you want all the curvival curves in one dataset, then you don't need a macro loop.  Since you do want them in different datasets, then a macro would be useful.  Just beware that the macro do loop can only deal with integers, unlike data step do loops.  This will cause a bit of awkwardness.

All curves in one dataset:

%let clv_months=36;

data curves(keep=ChurnRate time PC_live);

    do ChurnRate = 0.01 to 0.1 by 0.01;

        pc_live = 1;

        SurviveRate = 1 - ChurnRate;

       do time = 0 to &clv_months;

           pc_live = pc_live * SurviveRate;

          output;

       end;

    end;

    format pc_live 7.5;

run;

A very literal translation of what was attempted:

%macro curves( clv_months= );

%do i = 1 %to 10;

    data churn&i(keep=ChurnRate time pc_live);

        pc_live = 1;

        ChurnRate = &i / 100;

        SurviveRate = 1 - ChurnRate;

        do time = 0 to &clv_months;

            pc_live = pc_live * SurviveRate;

            output;

        end;

        format pc_live 7.5;

    run;

%end;

%mend;

%curves( clv_months=36 )

Of course this provides no control over the values of ChurnRate to iterate over, and should be changed to macro arguments:

%macro curves( ChurnRate=, clv_months= );

%let i=1;

%let rate=%scan( &ChurnRate, &i, %str( ) );

%do %while( &rate~= );

    data churn&i(keep=ChurnRate time pc_live);

        pc_live = 1;

        ChurnRate = &rate;

        SurviveRate = 1 - ChurnRate;

        do time = 0 to &clv_months;

            pc_live = pc_live * SurviveRate;

            output;

        end;

        format pc_live 7.5;

    run;

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

    %let rate=%scan( &ChurnRate, &i, %str( ) );

%end;

%mend;

%curves( ChurnRate=0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 0.1, clv_months=36 )

[robbane28]

Thanks so much, that really helps my thinking and I think the code should also help me.

Thanks both again.