Loop a macro through 2 different sets of indices, one of which is staggered

[PurpleNinja]

I'm running SAS 9.4 TS Level 1M3.

I have a macro called FILTER_DATA.  I use it to create smaller data sets by filtering SASHELP.CLASS.  

%macro filter_data(sex, index, age1, age2, age3);

data class_&sex._&index;
     set sashelp.class;

     if sex = "&sex";
    
     if age = &age1 or age= &age2 or age = &age3;
run;

%mend;

My challenge is to loop through many applications of FILTER_DATA.  Instead of explaining how I want to loop them, it's easier just to show you.

%FILTER_DATA(M, 1, 11, 12, 13);
%FILTER_DATA(M, 2, 12, 13, 14);
%FILTER_DATA(M, 3, 13, 14, 15);


%FILTER_DATA(F, 1, 11, 12, 13);
%FILTER_DATA(F, 2, 12, 13, 14);
%FILTER_DATA(F, 3, 13, 14, 15);

What code can implement all 6 lines in a loop?

[LinusH]

If you only have six, you don't need a loop.
But looping is done via %do within the macro.

[PurpleNinja]

My data set is actually MUCH bigger than this, and I have hundreds of iterations.

I used SASHELP.CLASS just to illustrate my intention.

[Reeza]

CALL EXECUTE. Check Example 2 the documentation for the example.

Create a data step loop and pass the arguments to the macro that way. A data step loop is easier to manage.

 http://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n1q1527d51eivsn1ob5hnz0yd1hx.htm&docset...

[Kurt_Bremser]

Create a dataset with your filter value combinations and use call execute from that to execute your macro repeatedly.

[PurpleNinja]

I'm sorry - I don't know how to do that. Could you please show me with some code?

Thank you.

[s_lassen · Accepted solution]

You can do what you want like this:

%macro runit;
  %local sexes index i j sex;
  %let sexes=MF;
  %do index=1 %to 3;
    %do i=1 %to 2;
      %let sex=%substr(&sexes,&i,1);
      %filter_data(&sex,&index,%eval(&index+10),%eval(&index+11),%eval(&index+12));
      %end;
    %end;
%mend;
%runit;

But you will get more mileage with large data if you use the conditions to split the data in one datastep, e.g.:

%macro split_data(sexes,indexes);
  %local i j;
  data
  %do i=1 %to %sysfunc(countw(&sexes));
    %do j=1 %to %sysfunc(countw(&indexes));
      %let sex=%scan(&sexes,&i);
      %let index=%scan(&indexes,&j);
      class_&sex._&index
      %end;
    %end;
    ;
  set sashelp.class;
  %do i=1 %to %sysfunc(countw(&sexes));
    %do j=1 %to %sysfunc(countw(&indexes));
      %let sex=%scan(&sexes,&i);
      %let index=%scan(&indexes,&j);
      if sex="&sex" and age in(%eval(&index+10),%eval(&index+11),%eval(&index+12)) then
        output class_&sex._&index;
      %end;
    %end;
  run;
%mend;
%split_data(M F,1 2 3);

[PurpleNinja]

That's incredible - thank you, s_lassen!