Create a SAS macro variable that contains a list of values

3

Parameters in SAS procedures are specified a list of values that you manually type into the procedure syntax. For example, if you want to specify a list of percentile values in PROC UNIVARIATE, you need to type the values into the PCTLPTS= option as follows:

proc univariate data=sashelp.cars noprint;
   var MPG_City;
   output out=pctl pctlpre=p 
          pctlpts=2.5 5 10 25 50 75 90 95 97.5; /* <== type long list */
run;

Obviously, typing many values is tedious and error prone. Furthermore, you might have parameters values that are contained in a SAS data set because they were computed by a previous program. A few procedures support reading parameters directly from a SAS data set, but most require that you type the values manually. Wouldn't it be convenient if you could get the parameter values out of a data set and insert them into the syntax without having to type them?

This is a common question that SAS programmers ask on discussion forums. This article shows how you can read the values of a variable from a data set into a SAS macro variable. The macro variable will contain a blank-delimited list of values. You can then use the "macro list" to specify the parameters to a SAS procedure.

Read values into a SAS macro variable

As an example, the following DATA step contains the parameter values for the PCTLPTS= option in the previous PROC UNIVARIATE call:

data ParamData;
input x @@;
datalines;
2.5 5 10 25 50 75 90 95 97.5
;

The SQL procedure provides an easy way to concatenate the values of a variable into a macro variable. Use the SEPARATED BY keywords to specify a character to delimit the values. Usually the delimiter is a blank character, as below, but in some situations you might want to use a comma.

/* put all values into macro variable named ParamList */
proc sql noprint;                              
 select x into :ParamList separated by ' '
 from ParamData;
quit;
%put ParamList = &ParamList;   /* display list in SAS log */
ParamList = 2.5 5 10 25 50 75 90 95 97.5

The PROC SQL statement reads the values from the x variable in the ParamData data set. It converts them to character values and concatenates them into a blank-separated string, which is then stored into the macro variable named ParamList. To use the parameter list, simply use an ampersand (&) to reference the value of the macro variable, as follows:

proc univariate data=sashelp.cars noprint;
   var MPG_City;
   output out=pctl pctlpre=p pctlpts=&ParamList; /* long list in macro */
run;

Read values into a "macro list" from a SAS/IML vector

You can use a similar technique to create a macro variable that contains values that are in a SAS/IML vector. To create a macro variable, use ideas from the articles about concatenating a vector of values into to a string and about using the SYMPUTX subroutine. For convenience, the following statement create a SAS/IML module that implements the technique:

proc iml;
/* Concatenate values into a string separated by a delimiter (by default, 
   a blank). Create a macro variable with the specified name. */
start CreateMacro(values, macroName, delimiter=' ');
   if type(values)='N' then          
      y = rowvec( char(values) );   /* convert numeric to character */
   else 
      y = rowvec(values);
   s = rowcat(y + delimiter);       /* delimit and concatenate */
   s = substr(s, 1, nleng(s)-nleng(delimiter)); /* remove delimiter at end */
   call symputx(macroName, s);      /* create macro variable */
finish;
 
use ParamData; read all var {x}; close;
call CreateMacro(x, "ParamList");

When you call the CreateMacro module, you specify a vector of values and the name of a macro variable. By default, the module creates a blank-delimited list, but you can override that default. After the call, you can then use the macro variable in other SAS procedures.

By the way, you don't need a macro variable if you intend to call a SAS procedure directly from your SAS/IML program. You can pass SAS/IML vectors directly to SAS procedures by using the SUBMIT statement. However, when you do need a macro variable, this technique is worth remembering.

Share

About Author

Rick Wicklin

Distinguished Researcher in Computational Statistics

Rick Wicklin, PhD, is a distinguished researcher in computational statistics at SAS and is a principal developer of PROC IML and SAS/IML Studio. His areas of expertise include computational statistics, simulation, statistical graphics, and modern methods in statistical data analysis. Rick is author of the books Statistical Programming with SAS/IML Software and Simulating Data with SAS.

3 Comments

  1. Pingback: The top 10 posts from The DO Loop in 2016 - The DO Loop

  2. David Bruckman on

    Rick,
    In the first example using DATA and PROC SQL steps to generate macro ParamList, is there any functional difference had you simply used a %LET macro statement? e.g. %LET ParamList = 2.5 5 10 25 50 75 90 95 97.5;
    Wouldn't this be read into SAS by the compiler as a blank-delimited string?
    Thanks.

    • Rick Wicklin

      Yes, the result is the same as if you used a %LET statement. The point of the article is to show how to create a space-separated (or comma-separated) list of values that come from a data set. As you said, if you know that the values are always going to be the same, you can use the %LET statement. This is called knowing the values at "compile time." However, sometimes you don't know the values until run time. In that case, you need to read the values from a data set by using the technique in this article.

Leave A Reply

Back to Top