This week’s author tip is from Robert Virgile and his book “SAS Macro Language Magic: Discovering Advanced Techniques”. Virgile chose this tip because discovering and developing this technique will help you make the most of MACROS.
We hope you find this tip useful. You can also read an excerpt from Virgile’s book.
Macros Tip #1: A “Most Commonly Asked” Question
One macro language question arises again and again. How can I get macro language to perform a %DO loop over a list of values? For example, this syntax is illegal in macro language:
%do name=Amy, Bob, Fred;
But what if that is exactly what you need: execute a %DO loop for each name on the list? Macro language lets you work around this limitation, as long as you program the logic yourself. Here are the steps you’ll need to take.
First, create a macro variable holding the list of names. A %LET statement might look like this:
%let name_list = Amy Bob Fred;
Of course, there are many ways to generate a list of values. You could pass it as a parameter:
%call_my_macro (name_list=Amy Bob Fred)
Or, if a data set stores the names, SQL can retrieve them into a macro variable:
proc sql noprint; select distinct name into : name_list separated by ‘ ‘ from my.dataset; quit;
Once the list of names is ready, execute the %DO loop for each name. A few considerations come into play here:
- While a variety of approaches exist, none of them take very long to execute. It might be advisable to select a simple method, rather than a faster-running method.
- All of these statements must appear within a macro definition. %DO loops and %LOCAL statements are not permitted elsewhere.
Begin with a more complex approach:
%local i next_name; %let i=1; %do %while (%scan(&name_list, &i) ne ); %let next_name = %scan(&name_list, &i); %** DO whatever needs to be done for &NEXT_NAME; %let i = %eval(&i + 1); %end;
When &i is 1, %SCAN assigns the first word in &NAME_LIST as the value of &NEXT_NAME. When it is 2, %SCAN assigns the second word. After %SCAN reads all the words in &NAME_LIST, it returns a null value and the %DO loop ends. Is the code difficult to read? As always, beauty is in the eye of the beholder.
An alternative approach has macro language count the number of names in the list. While entire macros have been written for this purpose, advances in macro language make counting words a straightforward task:
%local i next_name; %do i=1 %to %sysfunc(countw(&name_list)); %let next_name = %scan(&name_list, &i); %** DO whatever needs to be done for &NEXT_NAME; %end;
The DATA step function COUNTW counts the number of words in a list, and %SYSFUNC permits macro language to use most DATA step functions.
Of course, complications arise. Will your logic still work when:
<ul> <li>The list is empty?</li> <li>An item on the list is a significant word in macro language? For example, what if &NEXT_NAME is NE or OR?</li> </ul>
Those issues can arise, but are beyond the scope of this tip. This tip is to understand the steps needed to instruct macro language to process each item on a list of values.
For more information about the macro language and the magic you can create with it, check out Robert Virgile’s book “SAS Macro Language Magic: Discovering Advanced Techniques”.