SAS Looping through macro variables on an if statement and through multiple datasets

Question

Depending on the value of a macro variable I am looking to output multiple datasets. I am looking to loop this through and I am also looking to do this on multiple input datasets.

%macro data_split;

%do i=1 %to %loop;

data work.test_&&mac_var&loop;
set work.input_dataset;
if col = "&&mac_var&loop" then output test_&&mac_var&loop;
run;
%end;
%mend;

This outputs a number of tables depending on how many times it loops through and gives the correct output depending on the value of mac_var.

However I'm looking at looping it through a number of input_datasets but when trying to add the second loop I'm having issues.

Another solution I thought would be to simply merge these datasets I want into one dataset.

Thanks for any help

Answer 1

You are messing up

• macro's %name and macro variables &name
• your index &i and your final value &loop

Supposing the macro variables &mac_var1, &mac_var2, ... and &loop are well defined,

%let mac_var1 = alpha;
%let mac_var2 = beta;
%let mac_var3 = gamma;
%let loop = 3;

this should do the job (I think, because your question is not that clear).

%macro data_split;
  data
    %do i=1 %to &loop;
      work.test_&&mac_var&i
    %end;
    ;
  set work.input_dataset;
  %do i=1 %to &loop;
    if col = "&&mac_var&i" then output test_&&mac_var&i;
  %end;
  run;
%mend;
%data_split;

But actually, I would rather specify the values of these macro variables as a parameter

%macro data_split(mac_var_list);
  %let loop = %sysfunc(countw(&mac_var_list)); *(1);
  data
    %do i=1 %to &loop;
      %let mac_var = %scan(&mac_var_list, &i); *(1);
      work.test_&mac_var
    %end;
    ; *(2);
  set work.input_dataset;
    %do i=1 %to &loop;
      %let mac_var = %scan(&mac_var_list, &i); *(1);
      if col = "&mac_var" then output test_&mac_var;
    %end;
  run;
%mend;
%data_split(alfa beta gamma);

(1) the scan function exists both as data step function and as macro function, but countw only exists as data step function, so you need %sysfunc to call it here.

(2) All ; between data and here are macro code, so this ; is the one closing the data statement.

PS, next time, give much more details in your question. It is not normal we have to guess what &&mac_var&i might mean.