SAS Macro Works Standalone, But Not When Looped

Question

I have a large dataset where I am storing macro parameters. The macro is itself used to call a number of other macros, each of which runs a number of operations.

Ideally, I'd like to use another macro to loop over each row of the dataset, construct (using PROC SQL) a macro call, store it in a macro variable :CALL, and call the variable at every iteration of the loop (with a PUT &CALL.;) That is:

 %macro OUTER_LOOP(DS);
    %let K = ;
    %COUNT_ROWS(DS, K); /* This stores the number of rows in DS in K. */
    %do i = 1 %to &K.;
        proc sql noprint; ...; quit; /* Create the macro call, and store it in :CALL. */
        %put &CALL.;
    %end;
    %mend;
    %OUTER_LOOP;

This doesn't work as expected: some of the internal checks that exist in my macro indicate several datasets created by the macro are missing. Curiously, when I don't run this in a macro loop (i.e. I manually create a macro call, row-by-row, and execute it), no error occurs.

Has anyone experienced this issue? If so, is anyone familiar with a solution that would still allow me to loop over macro calls? I know that CALL EXECUTE(); (in the data step) runs different parts of the macro at different times--is that what is occurring in this case, as well?

Answer 1

I would add %put Loop iterating: i=&i k=&k ; inside the DO loop. That will let you see how many times the loop iterates. One possibility is the loop is exiting earlier than you intend it to. If that is the case, the cause could be a collision between the macro variable i you use for the looping in %Outer_Loop and another macro variable i you use in one of the inner macros you call. As a general rule, it's a good idea to define macro variables as %LOCAL to the macro they are defined in. Doing that will prevent such macro variable collisions. But without seeing the inner macros, that's just one possibility.

You could also add %put %superq(Call) ; inside the do loop. That will show you the macro calls that are being generated, so you can check you are getting the expected parameter values in each call.

Answer 2

Most likely a scoping issue. Your sub-macros are likely overwriting the values of your macro variables in your calling-macros.

You can fix this by declaring all your variables as local variables using the %local statement. If there are macro variables that you need to access after the macros have run, explicitly declare them as %global.

So for the macro you have listed above you will need the below line:

%local k i;

Don't forget you need to do this for any sub-macros that are called, and so on...

Answer 3

You can avoid a lot of these types of problems by generating the code yourself. For your example you could move the logic that generates the code from SQL to a data step and then instead of a macro you just need a data step. You don't even need know the number of observations in the dataset in advance.

filename code temp ;
data _null_;
    set DS ;
    file code ;
    put '.... generated code based on values in current data ... ;
run;
%include code / source2 ;