SAS macro loop aggregate output tables

Question

I have a macro function defined as below.

%macro sqlloop(event_id, empl_nbr_var, fleet, base, position);
...lots of code...
%mend;

this macro creates a table called export_table2 that i want to stack on top of each other for all of the times I run this code. I tried below

data executed;
set work.vars;
call execute('%sqlloop(17,'||strip(empl_nbr_var)||','||strip(fleet)||','||strip(base)||','||strip(position)||');');
run;

data summary_pilots;
set work.export_table2 summary_pilots;
run;

but this gives an error since summary_pilots doesnt exist as I'm trying to reference it. I also tried a weird work around where I took out the first row of vars into a new table and deleted it from the old, then tried executing below code (where row1 is the first row and vars is everything except the first row).

data _null_;
    set Work.row1;
    call execute('%sqlloop(17,'||strip(empl_nbr_var)||','||strip(fleet)||','||strip(base)||','||strip(position)||');');
run;

data summary_pilots;
set work.export_table2;
run;


data executed;
set work.vars;
call execute('%sqlloop(17,'||strip(empl_nbr_var)||','||strip(fleet)||','||strip(base)||','||strip(position)||');');
run;

data summary_pilots;
set work.export_table2 summary_pilots;
run;

and it still doesnt function quite as I want. This only includes the first table (from row1) and the very last table generated by the macro. I can see why it does this, since call execute is running them all at once and writing over any old tables, but I still dont know how to fix it. Any attempt to put it inside the earlier data step has failed as well. any suggestions?

Joe Joe · Accepted Answer · 2021-06-02T18:06:42

First, the code to append needs to go inside %sqlloop, if possible. If not, make a wrapper macro around %sqlloop, something like

%macro write_loop(parameters...);
  %sqlloop (parameters)
  
  data summary_pilots... ;
  run;
%mend write_loop;

Then call execute that. You could also add a second call execute line, but that's unnecessarily complex. There's also ways to do the %sqlloop that could possibly give you a single dataset instead of row-wise datasets, which would be best most likely in many situations. There's also the possibility of giving a unique dataset for each %sqlloop execution. I like the inclusion into the macro for most cases where a single dataset doesn't work, but there are reasons you might prefer unique datasets.

Second, you have a bunch of options, depending on details, for how to make the whole thing work. PROC APPEND is a good start, as Tom noted in comments; that works if the dataset you're appending is always columnwise identical to what's been there before.

proc append base=summary_pilots data=export_table2;
quit;

That will create summary_pilots the first time it's run. This only really works if the columns always match - sounds like that's likely? FORCE option is also possible but it mostly isn't useful for this sort of thing unless you guarantee the first dataset is the complete dataset variable-wise.

If they don't match, then you are left with running, before anything else:

data summary_pilot;
  stop;
run;

Don't try to open it of course - it won't open, it has no rows and no columns - but it exists, and can be your "base". You could also define the columns in it if you have a good "template"; I do this for some projects.

SAS macro loop aggregate output tables

1 Answers