3 Easy Ways to Create a Macro Variable in SAS

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.

Create a Macro Variable with the %LET Statement

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

%let my_name = 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.

For example:

%put &my_name.;

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.

%put My name is: &my_name.;

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.

%put &=my_name.;

Create a Macro Variable with Blanks

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.

%let my_name =      David       ;
%let my_name_blanks = %str(   David    );
 
%put My name is: &my_name.!;
%put My name is: &my_name_blanks.!;

Create a Macro Variable with the CALL SYMPUT Routine

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.

The CALL SYMPUT routine is a combination of the CALL keyword and the SYMPUT function. This function has two mandatory arguments, namely:

1. The name of the macro variable, and
2. The value of the macro variable.

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.

data _null_;
    call symput('my_name', 'David');
run;
 
%put My name is: &my_name.;

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.

Create Macro Variables from a SAS Dataset

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.

The goal is to create 3 macro variables (name1, name2, and name3) that contain the values of the column name. That is:

• name1 = David
• name2= Lisa
• name3 = John

In the example below, we convert the values of the column name into three macro variables (name1, name2, and name3).

data _null_;
    set work.my_data;
 
    call symput('my_name'||left(_n_), name);
run;
 
%put My name 1 is: &my_name1.;
%put My name 2 is: &my_name2.;
%put My name 3 is: &my_name3.;

What is the Difference between CALL SYMPUT and CALL SYMPUTX

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.

data _null_;
    call symput('my_name_symput', '    David    ');
    call symputx('my_name_symputx', '    David    ');
run;
 
 
%put My name (symput) is: &my_name_symput.!;
%put My name (symputX) is: &my_name_symputx.!;

Create a Macro Variable with the PROC SQL INTO Clause

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.

Create a Single Macro Variable with the PROC SQL INTO Clause

First, we demonstrate how to create a single macro variable from a SAS dataset.

These are the steps you need:

1. Select the variable you want to save in a macro variable with the SELECT statement.
2. Save the variable with the INTO clause and give the macro variable a name.
3. Specify the input table.

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.

proc sql;
    select name into :my_name
    from work.my_data;
quit;
 
%put My name is: &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:

proc sql noprint;
    select name into :my_name
    from work.my_data;
quit;
 
%put My name is: &my_name.;

Create Multiple Macro Variables with the PROC SQL INTO Clause

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.

proc sql;
    select name into :my_name1 - :my_name3
    from work.my_data;
quit;
 
%put My name 1 is: &my_name1.;
%put My name 2 is: &my_name2.;
%put My name 3 is: &my_name3.;

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.

proc sql;
    select count(*) into :n_rows trimmed
    from work.my_data;
 
    select name into :my_name1 - :my_name&n_rows.
    from work.my_data;
quit;
 
%put My name 1 is: &my_name1.;
%put My name 2 is: &my_name2.;
%put My name 3 is: &my_name3.;

Create a Multiple Macro Variables of Different Columns with the PROC SQL INTO Clause

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.

proc sql noprint;
    select count(*) into :n_rows trimmed
    from work.my_data;
 
    select name, age into :my_name1 - :my_name&n_rows.,
				:my_age1 - :my_age&n_rows.
    from work.my_data;
quit;
 
%put My name and age 1 is: &my_name1., &my_age1.;
%put My name and age 2 is: &my_name2., &my_age2.;
%put My name and age 3 is: &my_name3., &my_age3.;

Create a List of Macro Variables with the PROC SQL INTO Clause

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.

proc sql noprint;
    select name into :my_list_name separated by " "
    from work.my_data;
quit;
 
%put List of name: &my_list_name.;

Alternatively, you could use a comma as the separator.

proc sql noprint;
    select name into :my_list_name separated by ","
    from work.my_data;
quit;
 
%put List of name: &my_list_name.;

As mentioned above, a macro variable with a list of values is extremely useful when you want to filter data with the IN operator. However, to do so the values must be enclosed between quotes in case of character strings.

You can enclose the values of a macro variable between double quotes with the QUOTE function. Additionally, you can add the STRIP function to your code to remove leading and trailing blanks. See the example below.

proc sql noprint;
    select quote(strip(name)) into :my_list_name separated by ","
    from work.my_data;
quit;
 
%put List of name: &my_list_name.;

Comparison of the %LET Macro Statement, the CALL SYMPUT Routine, and the PROC SQL INTO clause

Now that we have discussed three methods to create a macro variable, we show a small table that summarizes the advantages and disadvantages of each method. You can use this table to decide when to use one method or another.