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.