call execute inside a loop pulling from one table to execute a macro

Question

i have bellow currently

%macro sqlloop (event_id);

...lots of code, mostly proc sql segments ...

%mend;

that generates an output table (named export_table2). I need to be able to run this code dozens of time for every value in another table (named vars). my trial code testing what I want it to do is below (basically manually typing in the first two values of this 68 row table)

data ;

%let empl_nbr_var = '222';
%let fleet = '7ER';
%let position = 'A'; 
%let base = 'BWI';

%sqlloop(event_id = 1);
run;

data summary_pilots;
set work.export_table2;
run;

data;
%let empl_nbr_var = '111';
%let fleet = '320'; 
%let position = 'B'; 
%let base = 'CHS';

%sqlloop(event_id = 2);

run;

data summary_pilots;
set summary_pilots work.export_table2;
run;

This produces the final output of each execution stacked into one table called summary_pilots. How can I do this in a loop, prehaps using call execute to iterate through each row of vars? The columns of vars are exactly what I need for the macro variables, and I want to iterate through every single row to assign those macro variable and run my %sqlloop again. Thanks for the help!

EDIT:

currently figuring out how call execute works and see how its helpful here but still a bit stuck... code below works exactly as youd think, printing out all the variables in the table vars into the log.

data ;
set work.vars;
call execute( '%put='|| strip(empl_nbr_var)  || ';
%put = ' || strip(fleet) ||';
%put = '|| strip(position) ||'; 
%put = ' || strip(base) ||';' );
run;

I am trying to use the below code, but am getting a crazy amount of errors due to the macros being assigned weirdly. The types in the columns of vars match exactly what I want them to be in the macros, but it still looks like that might be the issue here?

data ;
set work.vars;
call execute( '
%let empl_nbr_var =' || strip(empl_nbr_var)  || ';
%let fleet = ' || strip(fleet) ||';
%let position = '|| strip(position) ||'; 
%let base = ' || strip(base) ||';

%sqlloop(event_id = 17);' );
run;

and the event ID doesnt actually matter here so i just left that as a random number for now.

Answer 1

It makes no sense to code %LET statements in the middle of a data step. The macro processor will evaluate them before it passes the text of the data step code to SAS to process. Avoid confusing yourself by moving the %LET statements before the data step.

If the macro needs values of macros variables, like FLEET, as input then make those things parameters to the macro. Don't create a macro that references "magic" macro variables, macro variables that are neither input parameters nor created by the macro. Instead the reference to them just appears in the middle of the macro definition as if their values will appear by magic somehow.

%macro sqlloop(empl_nbr_var,fleet,position,base);
  ... code that uses &fleet. 
  
%mend;

If you have a lot of combinations of parameters you want run through your macro then collect them into a dataset first.

data inputs ;
  input empl_nbr_var fleet $ position $ base $ ;
cards;
222 7ER A BWI
111 320 B CHS
;

Then you can use those dataset variables to generate the calls to the macro. You could try using call execute() to do this, but personally I find it a lot easier to use a data step to write the code to a file. Then you can examine the file and make sure the code generation logic is correct. Plus you can use the power of the PUT statement to make the code generation easier. For example if the variable names match the parameter names you can use named output.

filename code temp;
data _null_;
  set inputs;
  file code ;
  put '%sqlloop(' empl_nbr_var= ',' fleet= ',' position= ',' base= ')';
run;

Which will generate code like:

%sqlloop(empl_nbr_var=222 ,fleet=7ER ,position=A ,base=BWI )
%sqlloop(empl_nbr_var=111 ,fleet=320 ,position=B ,base=CHS )

Once you are confident that it is generating the right code use the %INCLUDE command to run the code it generates.

%include code / source2;

If the macro does not have its own step for aggregating the results you could include that step in the code generation.

filename code temp;
data _null_;
  set inputs;
  file code ;
  put '%sqlloop(' empl_nbr_var= ',' fleet= ',' position= ',' base= ')';
  put 'proc append base=summary_pilots data=export_table force; run;' ;
run;
%include code / source2;