Looping over each item of a macro variable

[yabwon]

A bit off-topic but:

1) Why not do the data read only one time (see macro getdata_short)?  [I fixed your `"all"` error in the code]

data datain;
 do year = 2018 to 2023;
    do indicator = "typea","typeb","typec";
      x = ranuni(42);
      output;
    end;
 end;
run;
proc print;
run;


%macro getdata(datain,year,type);
/*get data*/
%if &year = all %then %do;
data data_out; set &datain; run;
%end;
%else %do;
data data_out; set &datain;
where year = &year;
run;
%end;

%if &type = all %then %do;
data data_out_&type; set data_out; run;
%end;
%else %do;
data data_out_&type; set data_out;
where indicator = "&type";
run;
%end;
%mend;



%macro getdata_short(datain,year,type);
data data2_out_&type; 
  set &datain;
  where 1
  %if &year NE all %then 
    %do;
      and year = &year 
    %end;
  %if &type NE all %then 
    %do;
      and indicator = "&type" 
    %end;
  ;
run;
%mend;

options mprint;
%getdata(datain,2020,typec);
%getdata_short(datain,2020,typec);

proc compare base=data_out_typec
              compare=data2_out_typec
              ;
run;

options mprint;
%getdata(datain,all,typec);
%getdata_short(datain,all,typec);

proc compare base=data_out_typec
              compare=data2_out_typec
              ;
run;

options mprint;
%getdata(datain,all,all);
%getdata_short(datain,all,all);

proc compare base=data_out_typec
              compare=data2_out_typec
              ;
run;

2) Other thing, if you execute:

%getdata(datain,2020,typec);

%getdata(datain,2021,typec);

the result of the second execution overwrites result of the first one, maybe adding the "year" value to the data set name?

3) Since you are reading datain dataset several times to create several outputs, why not to read it only one time and use dedicated "output" statement?

%macro getdata_shorter(datain,years,types);
%local i ni j nj year type;

%let ni = %sysfunc(countw(%superq(years)));
%let nj = %sysfunc(countw(%superq(types)));

data
%do i = 1 %to &ni.;
  %do j = 1 %to &nj.;
    %let year = %scan(%superq(years),&i.);
    %let type = %scan(%superq(types),&j.);
    data_out_&year._&type.
  %end;
%end;
;
 
set &datain;

select;
  %do i = 1 %to &ni.;
    %do j = 1 %to &nj.;
      %let year = %scan(%superq(years),&i.);
      %let type = %scan(%superq(types),&j.);
        when (1=1
        %if &year NE all %then 
          %do;
            and year = &year 
          %end;
        %if &type NE all %then 
          %do;
            and indicator = "&type" 
          %end;
        ) output data_out_&year._&type.;
      %end;
  %end;

  otherwise;
end;

run;
%mend;


options mprint;
%getdata_shorter(datain,2020 2021 all,typec typeb all);

Log:

MPRINT(GETDATA_SHORTER):   data data_out_2020_typec data_out_2020_typeb data_out_2020_all data_out_2021_typec data_out_2021_typeb
data_out_2021_all data_out_all_typec data_out_all_typeb data_out_all_all ;
MPRINT(GETDATA_SHORTER):   set datain;
MPRINT(GETDATA_SHORTER):   select;
MPRINT(GETDATA_SHORTER):   when (1=1 and year = 2020 and indicator = "typec" ) output data_out_2020_typec;
MPRINT(GETDATA_SHORTER):   when (1=1 and year = 2020 and indicator = "typeb" ) output data_out_2020_typeb;
MPRINT(GETDATA_SHORTER):   when (1=1 and year = 2020 ) output data_out_2020_all;
MPRINT(GETDATA_SHORTER):   when (1=1 and year = 2021 and indicator = "typec" ) output data_out_2021_typec;
MPRINT(GETDATA_SHORTER):   when (1=1 and year = 2021 and indicator = "typeb" ) output data_out_2021_typeb;
MPRINT(GETDATA_SHORTER):   when (1=1 and year = 2021 ) output data_out_2021_all;
MPRINT(GETDATA_SHORTER):   when (1=1 and indicator = "typec" ) output data_out_all_typec;
MPRINT(GETDATA_SHORTER):   when (1=1 and indicator = "typeb" ) output data_out_all_typeb;
MPRINT(GETDATA_SHORTER):   when (1=1 ) output data_out_all_all;
MPRINT(GETDATA_SHORTER):   otherwise;
MPRINT(GETDATA_SHORTER):   end;
MPRINT(GETDATA_SHORTER):   run;

NOTE: There were 18 observations read from the data set WORK.DATAIN.
NOTE: The data set WORK.DATA_OUT_2020_TYPEC has 1 observations and 3 variables.
NOTE: The data set WORK.DATA_OUT_2020_TYPEB has 1 observations and 3 variables.
NOTE: The data set WORK.DATA_OUT_2020_ALL has 1 observations and 3 variables.
NOTE: The data set WORK.DATA_OUT_2021_TYPEC has 1 observations and 3 variables.
NOTE: The data set WORK.DATA_OUT_2021_TYPEB has 1 observations and 3 variables.
NOTE: The data set WORK.DATA_OUT_2021_ALL has 1 observations and 3 variables.
NOTE: The data set WORK.DATA_OUT_ALL_TYPEC has 4 observations and 3 variables.
NOTE: The data set WORK.DATA_OUT_ALL_TYPEB has 4 observations and 3 variables.
NOTE: The data set WORK.DATA_OUT_ALL_ALL has 4 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

All the best

Bart

[windy]

Thanks for your suggestion. I like the way you do. I tried the code. The log I got is similar to what you showed here, but the number of obs in datasets when &year = all are not correct yet. I'll recheck to see what's going on.

[Quentin]

Hi,

You've already got some great answers.  Just wanted to add some debugging thoughts.

When you're debugging macros, it often makes sense to debug the SAS code first, then debug the macro.  In the case where one macro calls another macro, you want to debug and test the called macro first, to get it working.  In this case you thought the problem was in your looping macro, but it's actually in the called macro.

If you take your called macro:

%macro getdata(datain,year,type);
  %if &year = "all" %then %do;
    data data_out; set &datain; run;
  %end;
  %else %do;
    data data_out; set &datain;
      where year = &year;
    run;
  %end;

  %if &type = "all" %then %do;
    data datain_out_&type; set data_out; run;
  %end;
  %else %do;
    data data_out_&type; set data_out;
      where indicator = "&type";
    run;
  %end;
%mend;

You can test it with options MPRINT and MLOGIC turned on, to see what it does.

%getdata(datain,2020,typec) works.

%getdata(datain,all,typec) errors.  You can see in the log that the condition &year="all" evaluated to false (because of the quote marks):

MLOGIC(GETDATA):  %IF condition &year = "all" is FALSE
MPRINT(GETDATA):   data data_out;
MPRINT(GETDATA):   set datain;
MPRINT(GETDATA):   where year = all;
ERROR: Variable all is not on file WORK.DATAIN.
MPRINT(GETDATA):   run;

%getdata(datain,"all",typec) works. 

%getdata(datain,"all","all") does not error, but it gives a bad result.  It outputs datain_out_ rather than datain_out_all, because of the quote marks:

MLOGIC(GETDATA):  %IF condition &type = "all" is TRUE
MPRINT(GETDATA):   data datain_out_"all";
MPRINT(GETDATA):   set data_out;
MPRINT(GETDATA):   run;

NOTE: There were 18 observations read from the data set WORK.DATA_OUT.
NOTE: The data set WORK.DATAIN_OUT_ has 18 observations and 3 variables.
NOTE: The data set all has 18 observations and 3 variables.

I actually would have thought that last example, with data datain_out_"all"; would error.  I guess the compiler just ignores  the quoted text.

You've already seen solutions, so I won't go further.  Just wanted to suggest that when you're running macros, you should have MPRINT turned on so that you can see the SAS code that is generated, and when you're debugging it's sometimes helpful to turn on MLOGIC and SYMBOLGEN.  More importantly, in complex macro settings, you want to debug the innermost macro first, and test it thoroughly.