Looping macro through list of subjects

[Leo9]

Hi All,

I need to generate separate output for each subject. I programmed the report part now I have to filter the data for each subject which will then feed into report.

As a first step I created a list of subjects separated by "'," and also the unique subject count.

proc sql ;
select distinct(strip(subject)) into:sb separated by '-'
from all1 ;

select count(distinct subject) into:cnt
from all1 ;

quit;

Now I am trying to loop this using do loop in a macro but the "if" condition is not filtering the data as desired.

%do i = 1 %to &cnt ;
data all2 ;
set all1;
if subject  =  %scan(&sb,&i,"-");
run;
%end;

[Hao_Luo]

Maybe you need:
if subject = "%scan(&sb,&i,-)";

[Tom · Accepted solution]

Can you explain the larger problem that led you to thinking you needed to code this loopy stuff?

Can you explain the code you did write? 

Why are you running the query twice to get the count? PROC SQL already wrote the count into SQLOBS macro variable.

The DISTINCT keyword is not a function call.

Why are you creating the same dataset over and over in the LOOP?  If you don't do anything with it then only the last one will still exist when the looping is finished.

Why are you using the STRIP() function?  Is subject numeric or character?  The STRIP() function is for character variables. But if you have a character variable then the way you wrote the IF statement cannot work as you are not comparing SUBJECT to a character string since you did not add quotes around the value.  Also if SUBJECT is character you do not want to use STRIP() because it will remove the LEADING spaces and then the resulting values will not match.

Why are you telling the %SCAN() function to use both - and " as the delimiters when you built the macro variable using only - as the delimiter?

Assuming SUBJECT is character you probably want something like:

proc sql noprint;
select distinct quote(trim(subject))
   into :sb separated by '-'
from all1 
;
%let cnt=&sqlobs;
quit;

To make a list like

"xyz"-"abc"-"def"

Then you can use a %DO loop like this to make a separate dataset for each subject.

%do i = 1 %to &cnt ;
data subset&i;
  set all1;
  where subject  =  %scan(&sb,&i,-);
run;
%end;

So you get something like:

data subset1;
  set all1;
  where subject  =  "xyz";
run;
data subset2;
  set all1;
  where subject  =  "abc";
run;
data subset3;
  set all1;
  where subject  =  "def";
run;

And if you don't want to make separate datasets then remove the looping.

proc sql noprint;
select distinct quote(trim(subject))
   into :sb separated by ' '
from all1 
;
quit;
data want;
  set some_other_dataset;
  where subject in (&sb);
run;

[Kurt_Bremser]

Keep in mind that all reporting procedures support BY, so you most probably don't need macro looping at all.