Often there is need to run some analysis for each distinct value in a dataset. Furthermore, since the set of values are often not known at the time that the program is written, it’s necessary to construct a dynamic process that can handle any number of values.
There are a variety of ways that this can be done in SAS, and it is not the purpose here to cover every option available. Instead we will focus on getting the most done with the least number of lines of code.
Compare the following two examples which creates the same output, namely, a 2-way frequency table (cross-tab) of car model by type of car.
Example 1
*distinct makes of car;
proc sql;
create table dist_makes as
select distinct make
from sashelp.cars;
quit;
%Macro LoopMakes;
data _null_;
set dist_makes end= eof;
call symputx(cats(‘make_’,_n_), make);
if eof then call symputx(‘NoMakes’,_ n_);
run,
%do j=l %to &NoMakes;
%* do something for each make;
proc freq data = sashelp.cars;
where make = “&&make_&j”;
table model * type;
run,
%end;
%Mend LoopMakes;
%LoopMakes;
Here, we first create a distinct list of all makes of car from SASHELP.CARS and then in the macro defining each value of make to a macro variable that becomes &make_1 to &make_n. Once the end of the dataset is reached we then assign the number of rows to the variables &NoMakes so it is available to loop through. Finally, we loop through each make and run a 2-way frequency table.
Example 2
%Macro ScanLoopMakes;
%* get a List of all makes to Look at;
proc sql noprint;
select distinct trim(make) into :all_makes separated by ‘ ‘ from sashelp.cars;
quit;
%do j=l %to %sysfunc(countw(&all_makes));
%* do something for each make;
proc freq data= sashelp.cars;
where make= “%SCAN(&all_makes, &j)”;
table model * type;
run;
%end;
%Mend ScanLoopMakes;
%ScanLoopMakes;
This time, instead of creating a dataset of each distinct value, and then creating the macro variables separately, we simply assign each distinct value to one macro variables, &all_makes, and separate these by a space. To set up the loop we just use the COUNTW function to work out how many items are in the &all_makes variable. Finally, to perform the analysis for each value of make we use the %SCAN function to iterate over the &all_makes variable using the jth element for each iteration.
As you can see, Example 2 is a far more elegant solution to Example 1. By simply adding spaces in the construction of the original list, then using the %SCAN function, we removed the step where we assigned each distinct value to separate macro variables before performing the main loop. Thus removing at least 5 lines of redundant code. The result is a technique to make your SAS code more efficient to write, and also much easier to read.
