Nested loop in SAS macro

Question

I got error when I was trying to use macro nested loop in SAS:

%Let t1=30;
%Let t2=40;
%Let t3=50;
%Let t4=60;
%Let t5=70;

%macro Age(time);
%Do I = 1 %to &time.; 
data Milk&I;
set Milk;
/*If Age is less than 30, MilkF and MilkA after 30 should be 0, same for 40-70*/

where (age<&&t&I. and (%Do I = &I. %to 5;
                              MilkF&&t&I. ne 0 or MilkA&&t&I. ne 0 ; 
                        %end;) ) ;

run;
%end;
%mend Age;
%Age(5)

The error shows behind the last "ne 0;" Syntax Error. What's the problem? Thanks for your help!

UPDATE: The output from macro I want is (takeing t1=30 as an example):

where (age<30 and (
               MilkF30 ne 0 or MilkA30 ne 0 or 
               MilkF40 ne 0 or MilkA40 ne 0 or
               MilkF50 ne 0 or MilkA50 ne 0 or
               MilkF60 ne 0 or MilkA60 ne 0 or
               MilkF70 ne 0 or MilkA70 ne 0 
               ) ) ;

So I have changed my code to

where (age<&&t&I. and 
(%Do I = &I. %to 5;
 MilkFreq&&t&I. ne 0 or MilkAmnt&&t&I. ne 0 or
%end;
 ) ) ;

Error:

   ) ) ;  run;
  -
  22
  76

"ERROR: Syntax error while parsing WHERE clause." So what happened now?

No, I am trying to select the wrong data entry. Please see the comments in code. — Yukun
Your line of code between %do/%end doesn't make sense syntax wise. mikf30 ne 0 or milka30 ne 0; There's no if or variable assigned..and if you're trying to list the conditions then you may have too many semicolons. — Reeza
There is no if is because it is part of the where condition. The do loop in the bracket as well as age<&&t&I. are the where condition. — Yukun
You're reusing the counter I twice in the macro which can cause issues. I still think your last do is the issue, but I don't have data to test with, can you post some fake data. — Reeza

Quentin Quentin · Accepted Answer · 2016-01-15T00:05:06

I see a few different things wrong. Consider:

where (age<&&t&I. and (%Do I = &I. %to 5;
                              MilkF&&t&I. ne 0 or MilkA&&t&I. ne 0 ; 
                        %end;) ) ;

This would generate:

where (age<30 and (
                   MilkF30 ne 0 or MilkA30 ne 0 ;
                   MilkF40 ne 0 or MilkA40 ne 0 ;
                   MilkF50 ne 0 or MilkA50 ne 0 ;
                   MilkF60 ne 0 or MilkA60 ne 0 ;
                   MilkF70 ne 0 or MilkA70 ne 0 ;
                   ) ) ;

If you look at the generated WHERE statement, it's invalid due to the extra semicolons. This causes the error message. To fix it, remove the semicolon in your macro at the end of MilkA&&t&I. ne 0 ;
The logic of those OR clauses doesn't look to me like what you describe in the comment, so you might want to check.
As @Reeza points out, you are using the counter &i inside a loop that is using a counter &i. This typically won't cause a syntax error, but will cause either the outer loop to exit prematurely, or continue infinite looping. So you could use &j as the iterator for the inner loop. Also a good idea to declare &i and &j as %LOCAL to the macro, to avoid collisions with any similarly named macro variables in outer scopes.

UPDATE: Suggest you turn on OPTIONS MPRINT, and look at the code generated by the macro. With your edit:

where (age<&&t&I. and 
        (%do I = &I. %to 5;
           MilkFreq&&t&I. ne 0 or MilkAmnt&&t&I. ne 0 or
         %end;
         ) ) ;

you now have one too many ORs, because it will generate an extra OR at the end, i.e. MilkFreq70 ne 0 or MilkAmnt70 ne 0 or ) )

You could try something like:

where (age < &&t&i and 
        (%do j = &i %to 5;
           MilkFreq&&t&j ne 0 or MilkAmnt&&t&j ne 0 
           %if &j ne 5 %then or;
         %end;
         ) ) ;

Nested loop in SAS macro

1 Answers