LET statement can be used to create a few macro variables which work extremely well. Data step with
CALL SYMPUT or
INTO statement in
PROC SQL provides a better method to automate the creation of macro variables.
However, if you have a large number of variables in a data step that needs to be converted into macro variables, these solutions may require a great deal of typing.
SAS provides an elegant solution to overcome these problems with the
CALL SET routine. You can convert data step variables into macro variables with a few lines of code using the
%SYSCALL SET routine.
This routine also allows you to convert and process one observation of data step variables at a time thus saving memory usage.
Ways to create Macro Variables in SAS
SAS provides a number of different options for creating macro variables.
1. %LET statement for creating a single macro variable
The %LET statement is one of the easiest methods to create a macro variable and is the equivalent of the DATA step’s assignment statement.
%LET statement is followed by the macro variable name, an equal sign (=), and then the text value to be assigned to the macro variable.
%LET <macro-variable-name> = <macro-variable-value>
%LET Name = Smith;
Note that, quotation marks are not used while creating macro variables with the LET statement. Macro variables are neither character nor numeric, they always just stored as a plain text.
When the macro variables &name is created with the %let statement, the value of ‘Smith’, you can use this macro variable across anywhere in your program.
However, if you have more than a few macro variables to create, typing the %let statements can be tedious and error-prone.
2. CALL SYMPUT – To create many macro variables
If the variables that you need to store in macro variables are contained in a SAS dataset, then %LET statements cannot be used to assign those values into a macro variable.
In the below, example the Fname macro variable is supposed to contain the values of the Name variable from the SASHELP.CLASS dataset.
data new_class; set sashelp.class; %let FName = Name; run;
Since %LET statements are executed before the data step is even compiled, the macro variable &name will resolve to ‘Name’ as the value and not the actual value in the data set.
To overcome this problem we will need to use the data step to place information onto the macro variable symbol tables.
We can do this with SYMPUT which is a DATA step call routine, and not a macro language statement.
CALL SYMPUT(macro-variable, value);
data new_class; set sashelp.class; call symput('FName',Name); run;
In the above example, the CALL SYMPUT statement creates the macro variable &Fname containing the variable name. However, only the last value of the variable name is assigned to the macro variable.
To place all of the values of the name into a macro variable, we can include a suffix as in the below example.
data new_class; set sashelp.class; suffix=put(_n_,5.); call symput(cats('FName',suffix),Name); run;
The suffix variable counts the observation(_n_) and appends count onto the macro variable Fname so that unique Macro variables are created for each name.
The resultant macro variables &Fname1 will store the first name value, the second value is stored in the macro variable &Fname2 and so on.
Now, in order to retrieve any value of the Name variable, you can use the below statement and replace the number with the observation number.
The statement below resolves to ‘Carol‘ which is the 4th Observation in the dataset.
3. PROC SQL
You can automate the creation of a macro variable using PROC SQL.
Placing a single value in a macro variable
There are situations where you may require to count the number of observations in a table. The below code is an example of how you can use PROC SQL to count the number of observations and store that count in a macro variable.
proc sql noprint; select count(*) into :nobs from sashelp.class; quit;
INTO clause is used to create the new macro variable NOBS.
The colon(:) informs the
SELECT statement that the result of the
COUNT function is to be written into a macro variable.
We can also create macro variables for each of the values as we have created in the data step earlier.
proc sql noprint; select count(*) into :nobs from sashelp.class; select Name into :Name1-:Name%left(&nobs) from sashelp.class; quit;
Unlike, the data step method, you must know there are 19 suffixes for the macro Name or you could first calculate the number of Observations and store in a separate macro variable.
In the above example, COUNT function is used to determine the number of observation which are stored into the macro &nobs.
Creating lists of values
It is also possible to create more than one macro variable in the SELECT statement.
In the example below two macro variables are created (&name and &age) each contains a comma-separated list of values.
proc sql outobs=5 noprint; select name, age into :names separated by ',', :age separated by '-' from sashelp.class; quit; %put names are &names; %put ages are &age; %put number of obs &sqlobs;
OUTOBS=5 option limits the number of observations for processing to 5.
The values of the variables Name and Age will be written
INTO macro variables.
The list of values of Name is written into &names with the values separated by a comma. Without the
SEPARATED BY clause the individual values will replace previous values rather than be appended onto the list. The last comma is needed to separate the two macro variables (: Names and: age).
The list of the age will be written to &age.
&SQLOBS is an automated macro variable and contains the number number of rows that are processed to create the macro variable list or range. In this case, it contains the number of rows as 5.
73 %put names are &names; names are Alfred,Alice,Barbara,Carol,Henry 74 %put ages are &age; ages are 14,13,13,14,14 75 %put number of obs &sqlobs; number of obs 5
Creating many macro variables using do loop and array
There are situations where you need to convert all of the variables of the dataset into a macro variable.
data _null_; set sashelp.class; suffix=put(_n_, 5.); array num _NUMERIC_; do i=1 to dim(num); call symputx(cats(vname(num[i]), suffix), num[i]); end; array char _CHARACTER_; do i=1 to dim(char); call symputx(cats(vname(char[i]), suffix), char[i]); end; run; %put &name2; %put &age2;
As in the above example, we are appending the suffix created from the automatic variable
_n_ onto the variable name supplied by the function
VNAME. The SAS system requires separate array statements for characters and numeric.
By using the
_CHARCATER_, we can assign all of the variables in the dataset to these arrays. Then, we can use the “*” in the array definition and the
DIM function in the loop to the number of variables.
The data step will convert all data set variables to macro variables with the same name and observation suffix.
However, if the dataset has hundreds of observations and dozens of variables, a lot of system resources may be used up in storing the macro variables.
4. CALL SET
You can use the CALL SET statement to replace the SET statement in a SAS data step.
%macro createMacro(); %let dsid=%sysfunc(open(sashelp.class)); %let nobs=%sysfunc(attrn(&dsid, nobs)); %syscall set(dsid); %do i=1 %to &nobs; %let rc=%sysfunc(fetchobs(&dsid, &i)); %put &name &age &weight; %end; %let dsid=%sysfunc(close(&dsid)); %mend; %createMacro();
%SYSCALL SET” is the macro version of “
CALL SET” which creates a macro variable corresponding to each of the variables in the data set.
When an observation using
FETCHOBS, each of these macro variables is populated with the value of the corresponding data set variable.
Now, you have all the dataset variables replaced with the macro variables without any need for PROC SQL or CALL SYMPUT.
Additionally, the macro variables need no suffix which means you do not need a macro variable for each observation. This can significantly reduce the number of system resources.
Four techniques for creating macro variables have been discussed. The choice of creating macro variables depends upon the programmer preferences. For creating a single macro variable, %LET is most adequate. if you want to create many macro variables you can use CALL SYMPUT or PROC SQL.
However, if the requirement is to create many macro variables to process each observation in the data step then the use of CALL SET will lead to concise and efficient coding.