A SAS macro variable is an extremely powerful tool to make your code more efficient. It helps you to make your code more dynamic and saves you a lot of time. However, how do you create a SAS macro variable?
In SAS, you create a macro variable with the %LET macro statement, the CALL SYMPUT routine, or the PROC SQL INTO clause. The %LET macro statement is appropriate for creating a single macro variable. The CALL SYMPUT routine and the PROC SQL INTO clause are more suited to create macro variables from SAS datasets.
In this article, we compare the three methods and discuss their pros and cons. We also show how to use these methods in real life by providing many examples.
The easiest way to create a macro variable in SAS is by using the %LET Macro Statement. This statement creates a macro variable and assigns it a value. You can also use the %LET statement to overwrite the existing value of a macro variable.
This is the syntax of the %LET statement:
%LET macro_variable_name = value;
The name of the macro variable must comply with the SAS naming convention. In other words, the name should start with an underscore or a letter from the Latin alphabet (A-Z, a-z). Also, the name must be 32 or fewer characters long and cannot contain blanks or special characters (except blanks).
The value of the macro variable is a character string or a number. If you omit the value, SAS creates a null value (i.e., a macro variable of length 0).
In the example below, we create a macro variable (my_name) and assign it a value (David).
Once you have created a macro variable, you can use the %PUT Macro Statement to display the value of the macro variable in the SAS log. You do so with the %PUT macro statement, followed by an ampersand (&), the name of the macro variable, and a semicolon. Additionally, you can add a dot after the name of your macro variable to make your code more readable.
Above we printed only the value of the macro variable to the log. Additionally, you can add normal text to the %PUT macro statement to make your code easier to understand.
A special way of displaying a macro variable is by using the “&=” syntax. For example, &=my_name. By doing so, SAS prints the name of the macro variable and its value to the log.
By default, the %LET macro statement removes leading and trailing blanks when you assign a value to a macro variable. However, in some cases, you might want to keep these blanks.
You create a SAS macro variable with leading and/or trailing blanks with the %STR() function. This function makes the blanks significant while assigning the value to the macro variable.
In the example below, we show the effect of using the %STR() function when you define a macro variable.
Another way to create a macro variable in SAS is by using the CALL SYMPUT routine. This routine assigns a value produced in a Data Step to a macro variable. Therefore, this method is perfect for creating macro variables from a dataset.
CALL SYMPUT(macro_variable_name = value);
The name of the macro variable is either a text string, a variable name of a SAS dataset, or a character expression that creates a text string. As always, the name of the macro variable must comply with the SAS naming convention.
The value of the macro variable is can be a text string, a number, the name of a variable of a SAS dataset, or a SAS expression.
Below we show the easiest way of using the CALL SYMPUT routine where both the macro variable name and value are a text string.
Although the code above works well, we recommend using the %LET macro statement if you just want to create a simple macro variable. It will save you a lot of code.
The advantage of the CALL SYMPUT routine is that you can create macro variables from a SAS dataset. In other words, you can assign the value of a SAS variable to a macro variable without explicitly specifying the value. This makes your program versatile.
We will use the dataset below to demonstrate this.
These are the steps to save the values of a SAS variable into a macro variable
You use the SET statement to specify the name of the dataset that contains the values you want to save as macro variables.
The second mandatory argument of the SYMPUT function is the value of the macro variable. If you want to assign the values of a column into a macro variable, then the second argument is simply the column name (i.e., variable name).
In the example below, we convert the values of the column name into three macro variables (name1, name2, and name3).
Besides the SYMPUT function, SAS has also a built-in function that is called SYMPUTX. But, what is the difference between both of them?
The main difference between the SYMPUT and SYMPUTX functions is that the SYMPUTX function removes leading and trailing blanks while SYMPUT doesn’t. Also, SYMPUT writes a message to the SAS log when it converts a numeric value into a character value. Even though the SYMPUTX function also converts numeric values into characters it doesn’t generate a message.
In the example below, we show the difference between the SYMPUT and SYMPUTX functions with an example.
So far, we’ve demonstrated how to create a macro variable with the %LET macro statement and the CALL SYMPUT routine. But, how do you create a macro variable using PROC SQL in SAS?
To create a SAS macro variable with SQL you use the PROC SQL INTO clause. This clause stores the value of one or more columns in a macro variable. The advantage of the PROC SQL INTO clause is that it creates a single macro variable, multiple macro variables, as well as a list of macro variables.
Similar to the %LET macro statement and the CALL SYMPUT routine, the PROC SQL INTO clause overrides the value of existing macro variables. However, in contrast to the other methods, you can use PROC SQL INTO only to create macro variables from an input dataset.
For the examples in this section, we will use the dataset below.
First, we demonstrate how to create a single macro variable from a SAS dataset.
proc sql; select variable_name into :macro_variable_name from dataset_name; quit;
Note that, by default, the INTO clause preserves leading and trailing blanks. If you want to remove these blanks, you can add the trimmed option to your code.
proc sql; select variable_name into :macro_variable_name trimmed from dataset_name; quit;
In the example below, we use the PROC SQL INTO clause to create the macro variable my_name.
Although the input table contains 3 rows, the PROC SQL INTO clause creates just 1 macro variable. More specifically, it creates a macro variable of the value in the first row.
Besides creating a macro variable, SAS also generates a report with the values of the selected column (in this case name).
This report might be useful, however, you can avoid this report by adding the noprint option to the PROC SQL statement. For example:
noprint;Instead of creating one macro variable, you can use the PROC SQL INTO clause also for creating multiple SAS macro variables. In other words, you can easily convert all the values of a column from a dataset into separate macro variables.
To create multiple macro variables from a SAS dataset, you use the PROC SQL INTO clause followed by a range of SAS macro variable names. The range starts with a colon followed by the name of the first variable, a dash, another colon, and the name of the last variable.
In contrast to creating one macro variable, SAS removes any leading and trailing blanks if you create multiple macro variables. Hence, you don’t need to add the trimmed option. However, if you do want to keep the leading and trailing blanks, you can use the notrim option.
In the example below, we create three macro variables, called name1, name2, and nam3.
In the example above, we exactly knew how many observations the dataset contained. Therefore, we could explicitly specify the number of macro variables that we wanted to create. However, this is not always the case.
In the example below, we first count and save the number of observations in our dataset into a macro variable (n_rows). Then, we use this macro variable to define the range of macro variables we want to create.
In the previous examples, we used just one column to create our macro variables, namely name. However, you can use the PROC SQL INTO clause also to create macro variables from different columns.
You create macro variables from different columns with the SELECT statement. First, you select the columns you want to convert into macro variables (separated by a comma). Then, after the INTO keyword, you define the names of the macro variables.
For example, with the code below, we create macro variables of the values in the columns name and age.
noprint;
So far, we have created a separated macro variable for each value in a column. However, it is also possible to create one macro variable with a list of values.
You create a macro variable with a list of values with the PROC SQL INTO clause and the separated by option. This option reads all the values from a specified column and stores the values in one macro variable as a list. The values in the list are separated by a character you define. For example, a blank or a comma.
When you use the separated by option to store a list of values in a macro variable, SAS removes any leading and trailing blanks. However, if you want to keep these blanks, you can add the notrim option.
Creating a macro variable that contains a list of values is especially useful when you want to filter rows from a dataset with the IN operator. Instead of explicitly defining all the values in the IN operator, you can use the macro variable.
In the example below, we use the separated by option to create a macro variable with all the values from the column name. We use one blank to separate the values.
noprint;
Alternatively, you could use a comma as the separator.
