Many SAS users at some point need to run almost the same block of SAS code repeatedly, with only small changes between each run. Enter the SAS Macro language and the %DO Loop.
The loopreport Macro example below reads a "control table" sashelp.nvst1, and for each row performs a PROC MEANS on the sashelp.orsales table - using the Date and Amount columns from each row of the control table as filters "inside" each PROC MEANS.
%macro loopreport;
data _null_;
set sashelp.nvst1 end=last;
call symputx('date'||left(_n_), Date, 'L');
call symputx('amount'||left(_n_), Amount, 'L');
if last = 1 then call symputx('endloop', _n_, 'L');
run;
%do i=1 %to &endloop;
proc means data=sashelp.orsales sum mean maxdec=1;
var Profit;
where Year = year(&&date&i) and Total_Retail_Price > &&amount&i;
title "Year: %sysfunc(year(&&date&i))";
run;
%end;
%mend loopreport;
%loopreport;
The result is a series of PROC MEANS outputs, one for each year, with each PROC MEANS filtered by the appropriate Date and Amount from the control table.
Output of loopreport macro - PROC MEANS output for each year in the control table
Now to the core topic of this post. Since a few years SAS also provides a few other ways to do similar things – now with the programming languages Lua and Python. PROC LUA and PROC PYTHON allows us to call Lua and Python from SAS code and to call SAS from Lua and Python code.
Below is an example of PROC LUA doing the exact same thing as the macro above.
proc lua;
submit;
local dates_and_amounts = sas.read_ds("sashelp.nvst1")
for i, row in ipairs(dates_and_amounts) do
local date = row.date
local amount = row.amount;
sas.submit[[
proc means data=sashelp.orsales sum mean maxdec=1;
var Profit;
where Year = year(@date@) and Total_Retail_Price > @amount@;
title "Year: %sysfunc(year(@date@))";
run;
]]
end
endsubmit;
run;
Submitting this PROC LUA statement produces the exact same output as the loopreport macro.
(Note that the SAS syntax color highlighting get confused when there is non-SAS code in the editor. This is much better in the new built-in Python editor in SAS Studio on SAS Viya (which also automatically wraps your Python code in a PROC PYTHON call). This is covered in more detail in @JeppeDeigaard's Juletip #7 and @CecilyHoffritz's LinkedIn piece.)
I won't go into a discussion about which variant is "better". My intention here is only to show there are options and it's really not that difficult to switch between. Loop with Macro, Lua or Python… your choice!
... View more
