Step 1: Read in a list of states of abbreviations from an existing datasetFirst, 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;
Step 2: Count the elements in the listNow, 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).
%do %while(%qscan(&full_LIST,&var_num.+1,"/") ne %str());
%let var_num = %eval(&var_num+1);
Step 3: Assign each element in to a numbered macro variableNow, 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".
%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," ");
Step 4: Loop through each elementHere 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.
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!
%do s = 1 % to &var_num;
when "&&full&s.." then "&&abbr&s.."
select case %states end as state_abbr from full_states;