sas macro assignment

SAS Example Code

Examples, Tips & Tricks

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:

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.

For example:

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.

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.

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:

• The name of the macro variable, and
• The value of the macro variable.

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.

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

These are the steps to save the values of a SAS variable into a macro variable

Normally, you use the DATA statement to create a SAS Dataset. However, to create macro variables from a SAS dataset you need the special DATA _null_ statement. With the DATA _null_ statement, SAS processes all observations from the input dataset, but it doesn’t create an output dataset.

You use the SET statement to specify the name of the dataset that contains the values you want to save as macro variables.

The first mandatory argument of the SYMPUT function is the name of the macro variable. For example, name . Since SAS processes the observations of the input dataset sequentially, the names of your macro variables must also have some sort of enumeration. For example, name1, name2, name3, etc. If you omit this enumeration, SAS creates only one macro variable with the value of the last row of the dataset. To create a unique macro variable name, you can use the special variable _N_ . This variable is an internal counter that stores the row number SAS is processing. If you concatenate the name of your macro variable with the value of the _N_ variable, then your macro variable will always be unique. You can concatenate the name and the value of the _N_ variable with the double pipe symbol (i.e., ||). In addition, you need the LEFT function to remove (leading) blanks from the _N_ variable. Hence, the code to create unique macro variable names will look like this: ‘my_name’||LEFT(_N_) .

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

You execute the SAS code with the RUN statement.

In the example below, we convert the values of the column name into three macro variables ( name1, name2 , and 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.

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:

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

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.

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:

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 .

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.

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 .

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.

Alternatively, you could use a comma as the separator.

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.

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.

Similar Posts

How to Find the Maximum Value of a Row in SAS

How to Easily Create a Beautiful Title in SAS

One thought on “ 3 easy ways to create a macro variable in sas ”.

• Pingback: How to Check if a SAS Dataset is Empty - SAS Example Code

Comments are closed.

User Preferences

Content preview.

Arcu felis bibendum ut tristique et egestas quis:

• Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris
• Duis aute irure dolor in reprehenderit in voluptate
• Excepteur sint occaecat cupidatat non proident

Keyboard Shortcuts

Lesson 33: sas macro programming for beginners, overview section  .

This lesson introduces the most commonly used features of the SAS macro language. When you write a program that will be run over and over again, you might want seriously to consider using " macros " in your code, because:

• macros allow you to make a change in one location of your program so that SAS can cascade the change throughout your program
• macros allow you to write a section of code once and use it over and over again, in the same program or even different programs
• macros allow you to make programs data-driven, letting SAS decide what to do based on actual data values.

To whet our appetite for SAS macros, we'll read this paper:

SAS Macro Programming for Beginners

written by Susan J. Slaughter and Lora D. Delwiche and presented as a tutorial at the 2004 SAS Users Group International (SUGI) Meeting in Montreal, Canada.

• understand what a SAS macro is
• distinguish between local and global macro variables
• create a macro variable using a %LET assignment statement
• use a macro variable in a SAS program
• write and invoke a basic SAS macro
• write and invoke a SAS macro that uses parameters
• write a macro with conditional macro %IF-%THEN-%ELSE statements
• use and understand automatic macro variables such as &SYSDATE and &SYSDAY
• use CALL SYMPUT to write data-driven programs

Textbook Reference Section  

  SAS Macro Programming for Beginners paper written by Susan J. Slaughter and Lora D. Delwiche.