Blogs

Blogs

SAS author’s tip: Getting the macro language to perform a %DO loop over a list of values

15
By

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:

  • The list is empty?
  • An item on the list is a significant word in macro language? For example, what if &NEXT_NAME is NE or OR?

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”.

Tags sas author's tip sas tip
Share

About Author

Cindy Puryear

Senior Marketing Specialist, SAS Publications

15 Comments

  1. Anna on

    Hi I have a list of path stored in a column, when I run the script, I actually got a element between "/"
    What I want is
    pathlist=
    c:/users
    c:/users/name/
    c:/users/name/photo
    c:/users/name/music

    but what I got is
    c:
    users
    name
    photo
    music

    %macro allcols;
    /*GET LIBNAME PATH LIST*/
    proc sql;
    select distinct libpath into : libname_list from pa.libname_path;
    quit;

    %local i next_path;
    %do i=1 %to %sysfunc(countw(&libname_list));
    %let next_path = %scan(&libname_list, &i);
    /* %** DO whatever needs to be done for &NEXT_NAME;*/
    %put a="&next_path";
    %end;

    %mend allcols;
    %allcols;

  4. Cindy on

    Jordan, the code featured in the blog doesn't represent the entire program that would be needed. These errors are occurring because the sample statements within the blog cannot appear just anywhere. The program has to define a macro that includes the statements, and then call the macro (this is mentioned in one of the bullet points). Given the shorter blog, Robert explained the context briefly and focused more on the statements themselves.For more context, see: http://blogs.sas.com/content/sastraining/2015/01/30/sas-authors-tip-getting-the-macro-language-to-perform-a-do-loop-over-a-list-of-values/. Thanks. Cindy

  5. Jordan on

    When I use this code, I get errors about using the %local, %do, and %end statements in open code. Any idea why I may be getting these?

    Thanks!

  7. Lisa on

    Very helpful post! However, when I copied and pasted the SELECT...INTO code into SAS EG and ran it, I got an error. Then I realized the apostrophes pasted into EG as accent marks. Once I changed them to apostrophes it worked fine. So word to the wise for others who may try the same thing.

    • Cindy Puryear
      Cindy Puryear on

      Thank you Lisa for letting me know about this error along with how you fixed it. Very helpful. Cindy

  8. Jason Van Slyke on

    Thank you Cindy!

    Every time I've needed to do this in %do things were so rushed I couldn't take time to research how to do it or just think my way through it. I have saved this in my cheat sheet!
    Thx, Jason

  9. Al Barron on

    Hi Cindy.

    Suppose I were to assign the "Amy, Bob and Fred' values a font (say, their first+last names ) using Proc Font. How can I pass the font values through the macro call ?

    thanks,
    Al

    • Cindy on

      Al,

      Robert and I appreciate your question and apologize for the delay. Robert was checking into this for you and wanted to ask if you could be a little more specific on what you are trying to do?

      Thanks. Cindy

Back to Top