Looping with SAS Macros

SAS macros can be used to loop through any list stored in a string of text and do a repetitive process on each element of the list. You can also create a list dynamically from an existing SAS dataset.

Step 1: Read in a list of states of abbreviations from an existing dataset

First, you need to create your list of elements. It can be as simple as listing them in a macro variable:

%let list = apple banana grape;

Or you can read them from an existing SAS dataset using "separated by." In this example, I am reading two lists: states and state abbreviations. Notice that I'm separating my state list by slashes, so that "Rhode" and "Island" won't look like two different elements when it comes time to loop. 

proc sql noprint;
select state, abbr into : full_list separated by "/", : abbr_list separated by " " from greg.states;
quit;

Step 2: Count the elements in the list

Now, I'm going to invoke a macro to count the number of elements in the list. Because both of my lists are the same number of elements, I only need to do this once. I start at 0, then increment by 1 each time I scan through the list and see another string of text separated by my delimiter (here, a slash).

%macro varcount;
%global var_num;
%let var_num=0;
%do %while(%qscan(&full_LIST,&var_num.+1,"/") ne %str());
%let var_num = %eval(&var_num+1);
%end;
%mend varcount;
%varcount

Step 3: Assign each element in to a numbered macro variable

Now, I am going to use the count I got above and loop through s times, from 1 to the maximum value. Each time I go through the loop, I'm going to define a numbered macro variable. The first full name will be "Alabama", stored in a macro variable called "full1". The first abbreviation will be "AL", stored in a macro variable called "abbr1". The repeats all the way through "full51" and "abbr51".

%macro numvars;
%do s =1 %to &var_num;
%global full&s. abbr&s.;
%let full&s. = %qscan(&full_LIST.,&s,"/");
%let abbr&s. = %qscan(&abbr_LIST.,&s," ");
%end;
%mend numvars;
%numvars

Step 4: Loop through each element

Here is where the magic starts to happen. I am again looping through s, from 1 to the number of elements that I have. Each time I'm in the loop and want to refer to a state, I should use &&full&s..

As SAS revolves macros, it turns two &'s into one, and turns single & into the macro variable name it finds up until a period.

Original: &&full&s..
First pass (when s = 1): &full1
Second pass (look for value of &full1): Alabama

Within the loop, you can put ANY TEXT, and it will be repeated for each element in the list. Here, I will get a case statement with 51 when clauses, one for each state. Notice that the macro is invoked below, in the proc sql step. You can do pieces of a step like I've done here, or you can have a full step, or 50 full steps. I could start with a step where I am making a data set using only the Alabama rows, and then I can do a ton of transformations on it, then repeat for each new step. The possibilities are endless!

%macro states;
%do s = 1 % to &var_num;
when "&&full&s.." then "&&abbr&s.."
%end;
%mend states;

Proc sql;
select case %states end as state_abbr from full_states;
Quit;