61
Paper 028-2010
When Best to Use the %LET Statement, the SYMPUT Routine, or the INTO
Clause to Create Macro Variables
Arthur X. Li, City of Hope Comprehensive Cancer Center, Duarte, CA
ABSTRACT
Macro variables are the most essential part of the SAS® macro facility. One can create macro variables by
using the %LET statement, the SYMPUT routine, or the INTO clause in the SELECT statement from the
SQL procedure. Sometimes a SAS programmer is often unsure when best to use which method due to a
lack in understanding each step of macro language processing. This lack in understanding includes how
SAS statements are transferred from the input stack to the macro processor and DATA step compiler, what
role the macro processor plays during this process, and when best to utilize the interface to interact with the
macro facility during the DATA or SQL execution. Once one grasps the intricacies of macro language
processing, one will best know how to accurately create a macro variable.
INTRODUCTION
One of the most important components of Base SAS is the SAS macro facility, which includes macro
variables and macro programs. One can use SAS macro variables and macro programming statements in a
SAS macro program to generate SAS codes. In order to utilize the SAS macro facility, one needs to learn
SAS macro language. Although the convention of the macro language is similar to SAS language, macro
language is indeed a separate language that is used in the SAS macro program. The prerequisites of
learning macro programs begins with macro variables. In this paper, we will only focus on macro variables.
To truly understand macro variables, one must grasp the mechanisms of macro processing. One excellent
source for an introduction to macro processing is from SAS
®
Macro Programming Made Easy by Michele M.
Burlew. In this book, Burlew cleverly uses figures to illustrate the mechanics of macro processing. The
figures in this paper are adapted from the figures in her book.
CREATING AND REFERENCING MACRO VARIABLES
Macro variables are either automatic, which is provided by SAS, or user-defined, which is created by SAS
users. We will only cover the user-defined macro variables in this paper.
CREATING USER-DEFINED VARIABLES USING THE %LET STATEMENT
One way to create a macro variable is to use the %LET statement, which has the following form.
%LET macro-variable = value;
The value is stored as character strings that can be ranged from 0 to 65,534 characters. Mathematical
expressions that are stored as value are not evaluated. The case of the value is also preserved. If the
value contains quotation marks that include literals, the quotation marks will be part of the value. Before
assignment is made, any of the leading and trailing blanks will be removed from the value.
If macro- variable and/or value contain references to another macro variable, the reference will be evaluated first
before the assignment. Also, if the macro-variable has already been defined previously in the program, value will
replace the most current value. Here are some examples of defining macro variables by using the %LET statement.
%let var1 = 4 + 3;
%let var2 = hello;
%let var3 = leading blank;
%let var4 = " quotations ";
The values of the macro variables just defined are summarized in the following table:
Macro variable name
Value
Notes
var1
4 + 3
Mathematical expressions are not evaluated
var2
hello
var3
leading blank Leading blanks are removed
var4
“ quotations “ Quotation marks are part of the values
1
Beyond the Basics
SAS Global Forum 2010
010
0
70
REFERENCING MACRO VARIABLES
Once a macro variable is defined, the value of the macro variable is stored in the global symbol table
1
. In
order to substitute a macro variable in the SAS program, you must reference the macro variable by
preceding the macro variable name with an ampersand (&). The reference causes the macro processor to
search the macro variable in the symbol table. Once the macro variable is found, the value that corresponds
to the macro variable will be substituted into the SAS program. If the reference of a macro variable is within
quotations, the double quotation marks must be used.
Consider the following dataset, Height.sas7bdat, which contains information for SEX and HEIGHT of each
student. Suppose we would like to create an indicator variable, TALL, which is defined as either 1 for
students who are taller than 63 inches or otherwise 0. The number 63 could possibly be used multiple times
in the program. Furthermore, the definition of TALL might change in the future; for instance, instead of using
63 inches, we might use 65. Thus, we can define a macro variable that contains the threshold value and
reference this macro variable in the program. Since we use a macro variable to store this threshold value, if
we are going to make changes for this threshold, we will only need to change it once in the macro variable
definition. Program 1 below contains the partial code of the entire program:
Height.sas7bdat
name
sex
height
1
John
m
65
2
Tom
m
60
3
Mary
f
62
4
Helen
f
64
Program 1:
%let ht = 63;
data ex1;
set height;
tall = height > &ht;
run;
In Program 1 above, to reference the macro variable HT, an ampersand (&) is placed in front of the macro
variable HT. After the substitution, the code will be as follows:
data ex1;
set height;
tall = height > 63;
run;
SAS AND MACRO PROCESSING
SAS PROCESSING
In order to understand how macro variables are processed and stored, one needs to understand how SAS
processing works. Once a sequence of SAS code is submitted, it is processed in two-phase sequences: the
compilation and execution phases. The compilation phase is performed by the compiler.
Before the SAS code is transferred to the compiler, the codes are placed in a memory area, which is called
the input stack. Next, the word scanner takes the SAS code from the input stack and breaks that code
into words or symbols, which are called tokens, and directs the tokens to the correct destination, such as
the compiler or the macro processor. When the compiler receives the semicolon following the RUN
statement, it stops accepting tokens from the word scanner. The compiler then compiles the received
tokens, checking for syntax errors. If there are no syntax errors, the execution phase begins (see Figures
1a-c for illustrations). The types of tokens that the compiler recognizes are illustrated in following table:
1
In addition to the global symbol table, there are also local symbol tables. The local symbol table is used within the
macro definition. Since we are not covering macro programs, we will only use global symbol table in this paper. For
simplicity, the symbol table in this paper will be referred to as the global symbol table.
2
Beyond the Basics
SAS Global Forum 2010
010
0
84
Types of Token Contains…
Examples
Literal
Characters enclosed in quotation marks
“John”
‘John’
Number
Numerals including decimals, E-notation, date, time, and
datetime constants, and hexadecimal constants
555
‘01mar2010’d
30e4
2.5
Name
Characters that begin with a letter or underscore and that
continues with underscores, letters, or numbers. A period
can sometimes be part of a name
_n_
means
dollar9.2
descending
Special character Characters other than a letter, number, or underscore that
have a special meaning to the SAS system
*
/
+
%
& .
;
Figure 1a.
The input stack receives submitted SAS code.
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
WORD SCANNER
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
COMPILER
COMPILER
INPUT STACK
data ex1; set
height; tall =
height > 63;
run;
INPUT STACK
INPUT STACK
data ex1; set
height; tall =
height > 63;
run;
Figure 1b.
The word scanner tokenizes the SAS code from the input stack and directs the tokens to the compiler.
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
WORD SCANNER
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
COMPILER
COMPILER
INPUT STACK
; tall =
height > 63;
run;
INPUT STACK
INPUT STACK
; tall =
height > 63;
run;
height
set
;
data ex1
3
Beyond the Basics
SAS Global Forum 2010
010
0
51
Figure 1c. When the compiler receives the semicolon following the RUN statement, it stops accepting tokens from
the word scanner. The compilation begins.
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
WORD SCANNER
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
COMPILER
COMPILER
INPUT STACK
INPUT STACK
INPUT STACK
data ex1; set
height; tall =
Height > 63;
run;
MACRO PROCESSING
The macro processor is responsible for processing all the macro languages, such as the %LET statement
and the macro variable references. So that the macro processor can process the macro language, the word
scanner has to be able to recognize the macro language and direct the macro language to the macro
processor. The tokens that prompt the word scanner that the subsequent codes are macro languages are
called macro triggers, such as %LET followed by a name token and & followed by a name token. Once the
macro triggers are detected by the word scanner, the word scanner passes the tokens to the macro
processor. Then the macro processor performs its actions.
For macro variables, the macro processors will either create a new macro variable or modify an existing
macro variable in a symbol table. The macro processor also retrieves the value from the existing macro
variable and returns it to the input stack where it originally contains the macro reference.
We can use Program 1 as an example to illustrate how macro processing works. First, Program 1 is pushed
into the input stack (Figure 2a). The word scanner recognizes that %LET followed by HT (a name token) is
a macro trigger; it directs the %LET statement to the macro processor (Figure 2b). The macro processor will
keep requesting tokens until it reaches the semicolon. The macro processor creates macro variable HT and
assigns the value 63 in the symbol table (Figure 2c). After the macro processor receives the semicolon, the
word scanner begins to transfer the subsequent statements to the compiler until it reaches the next macro
trigger, which is ampersand (&) followed by a name token, HT. Then word scanner directs &HT to the
macro processor (Figure 2d). The macro processor retrieves the value that corresponds to HT in the symbol
table, which is 63, and returns it to the input stack (Figure 2e). The word scanner continues scanning.
Since there are no more macro triggers, the remaining tokens are passed-on to the compiler. When the
compiler receives the semicolon following the RUN statement, it stops accepting tokens from the word
scanner. The compilation phase begins.
Remember that the word scanner does not recognize macro triggers that are enclosed in single quotation
marks. Thus, you need to use double quotations if you want to include a macro reference inside the
quotation.
4
Beyond the Basics
SAS Global Forum 2010
010
0
121
Figure 2a. Program 1 is pushed into the input stack.
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
COMPILER
INPUT STACK
%let ht = 63;
data ex1; set
height; tall =
height > &ht;
run;
MACRO PROCESSOR
GLOBAL SYMBOL TABLE
SAS Automatic Macro Variables
SYSDATE 19JUN06
SYSDAT Monday
SYSTIME 10:34
.
.
.
User-Defined Macro Variables
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
WORD SCANNER
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
COMPILER
COMPILER
INPUT STACK
%let ht = 63;
data ex1; set
height; tall =
height > &ht;
run;
INPUT STACK
INPUT STACK
%let ht = 63;
data ex1; set
height; tall =
height > &ht;
run;
MACRO PROCESSOR
MACRO PROCESSOR
GLOBAL SYMBOL TABLE
SAS Automatic Macro Variables
SYSDATE 19JUN06
SYSDAT Monday
SYSTIME 10:34
.
.
.
User-Defined Macro Variables
GLOBAL SYMBOL TABLE
SAS Automatic Macro Variables
SYSDATE 19JUN06
SYSDAT Monday
SYSTIME 10:34
.
.
.
User-Defined Macro Variables
Figure 2b. The word scanner recognizes that %LET followed by “ht” (a name token) is a
macro trigger, directing %LET to the macro processor. The macro processor requests
additional tokens until it receives a semicolon.
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
WORD SCANNER
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
COMPILER
COMPILER
INPUT STACK
= 63;
data ex1; set
height; tall =
height > &ht;
run;
INPUT STACK
INPUT STACK
= 63;
data ex1; set
height; tall =
height > &ht;
run;
ht
let
MACRO PROCESSOR
MACRO PROCESSOR
GLOBAL SYMBOL TABLE
SAS Automatic Macro Variables
SYSDATE 19JUN06
SYSDAT Monday
SYSTIME 10:34
.
.
.
User-Defined Macro Variables
GLOBAL SYMBOL TABLE
SAS Automatic Macro Variables
SYSDATE 19JUN06
SYSDAT Monday
SYSTIME 10:34
.
.
.
User-Defined Macro Variables
%
5
Beyond the Basics
SAS Global Forum 2010
010
0
107
Figure 2c. The macro processor creates the macro variable HT and assigns the value 63 in
the global symbol table.
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
WORD SCANNER
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
COMPILER
COMPILER
INPUT STACK
set height;
tall = height
> &ht;
run;
INPUT STACK
INPUT STACK
set height;
tall = height
> &ht;
run;
;
ex1
data
MACRO PROCESSOR
MACRO PROCESSOR
GLOBAL SYMBOL TABLE
SAS Automatic Macro Variables
SYSDATE 19JUN06
SYSDAT Monday
SYSTIME 10:34
.
.
.
User-Defined Macro Variables
Ht
63
GLOBAL SYMBOL TABLE
SAS Automatic Macro Variables
SYSDATE 19JUN06
SYSDAT Monday
SYSTIME 10:34
.
.
.
User-Defined Macro Variables
Ht
63
Figure 2d. After the macro processor receives the semicolon, the word scanner begins to
transfer the subsequent statements to the compiler until it reaches the next macro trigger,
which is ampersand (&) followed by a name token (HT). The word scanner directs &HT to the
macro processor.
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
WORD SCANNER
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
COMPILER
COMPILER
INPUT STACK
;
run;
INPUT STACK
INPUT STACK
;
run;
ht
MACRO PROCESSOR
MACRO PROCESSOR
GLOBAL SYMBOL TABLE
SAS Automatic Macro Variables
SYSDATE 19JUN06
SYSDAT Monday
SYSTIME 10:34
.
.
.
User-Defined Macro Variables
Ht
63
GLOBAL SYMBOL TABLE
SAS Automatic Macro Variables
SYSDATE 19JUN06
SYSDAT Monday
SYSTIME 10:34
.
.
.
User-Defined Macro Variables
Ht
63
&
data ex1; set
Height; tall =
Height >
6
Beyond the Basics
SAS Global Forum 2010
010
0
78
Figure 2e. The macro processor retrieves the value that corresponds to HT in the symbol
table, which is 63, returning it to the input stack.
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
WORD SCANNER
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
COMPILER
COMPILER
INPUT STACK
63;
run;
INPUT STACK
INPUT STACK
63;
run;
MACRO PROCESSOR
MACRO PROCESSOR
GLOBAL SYMBOL TABLE
SAS Automatic Macro Variables
SYSDATE 19JUN06
SYSDAT Monday
SYSTIME 10:34
.
.
.
User-Defined Macro Variables
Ht
63
GLOBAL SYMBOL TABLE
SAS Automatic Macro Variables
SYSDATE 19JUN06
SYSDAT Monday
SYSTIME 10:34
.
.
.
User-Defined Macro Variables
Ht
63
data ex1; set
Height; tall =
Height >
PROCESSING MACRO VARIABLES DURING DATA STEP EXECUTION
In some situations, creating a macro variable during the DATA step execution is necessary. For example,
you might want to create a macro variable based on the data value in the SAS dataset, such as a computed
value or based on programming logic. Using the %LET statement will not work in this situation since the
macro variable created by the %LET statement occurs before the execution starts.
Suppose you would like to create macro variable TITL, which served as a title for printing the dataset ex1
(that was created in Program 1). If there are some students taller than 63 inches, the TITL macro variable
will store the value of “Some students are taller than 63 inches,” and if none of the students are talker than
63 inches, TITL will store “None of the students are taller than 63 inches.” Program 2 uses the %LET
statement to create the macro variable.
Program 2:
data _null_;
set ex1 end=last;
if tall then count_tall +1;
if last then do;
if count_tall then do;
%let titl = "Some students are taller than 63 inches";
end;
else do;
%let titl = "None of the students are taller than 63 inches";
end;
end;
run;
proc print data=ex1;
title &titl;
run;
7
Beyond the Basics
SAS Global Forum 2010
010
0
51
Output:
Notice that Program 2 did not create the macro variable correctly. There are two people that are taller than
63 inches in the dataset ex1, but the title in the output above shows “None of the students are taller than 63
inches.”
None of the students are taller than 63 inches
Obs name sex height tall
1 John m 65 1
2 Tom m 60 0
3 Mary f 62 0
4 Helen f 64 1
Let’s exam this program in more detail. At first, Program 2 is pushed into the input stack (Figure 3a). The
word scanner directs the SAS code to the compiler until it reaches the macro trigger (%LET). The word
scanner directs the %LET statement to the macro processor (Figure 3b). The macro processor creates
macro variable TITL with the value of “Some students are taller than 63 inches” in the symbol table (Figure
3c). After directing the %LET statement to the macro processor, the word scanner directs subsequent
tokens to the compiler until it reads the second %LET statement. The word scanner directs the second
%LET statement to the macro processor (Figure 3d). The macro processor reassigns “None of the students
are taller than 63 inches” to the macro variable TITL (Figure 3e). The word scanner continues to send the
remaining tokens to the compiler. When the compiler receives the semicolon following the RUN statement,
it stops accepting tokens from the word scanner. When the compilation begins, there will be no more %LET
statements in the DATA step. Here is what the program will look like when it is processed by the compiler:
data _null_;
set ex1 end=last;
if tall then count_tall +1;
if last then do;
if count_tall then do;
end;
else do;
end;
end;
run;
THE SYMPUT ROUTINE
To fix the problem in Program 2, we need to be able to create a macro variable during the DATA step
execution, which can be accomplished by using the SYMPUT routine. Since the macro-variable is assigned
with the value during the DATA step execution, you can only reference the macro-variable after the DATA
step in which it is created. The SYMPUT routine has the following form:
CALL SYMPUT (Macro-variable, value);
In the SYMPUT routine, both macro-variable and value can be specified as literal (text in quotations), a
DATA step variable, or a DATA step expression. We will examine each of these cases.
THE SYMPUT ROUTINE: BOTH ARGUMENTS ARE LITERALS
CALL SYMPUT (‘Macro-variable’, ‘value’);
When both macro-variable and value are literal, they are enclosed in quotation marks. The text enclosed in
the quotation marks for the first argument is the exact macro variable name. The second argument
enclosed in the quotation marks is the exact value that is assigned to the macro variable. In Program 2, the
macro variable we attempted to create is based on a calculated value in the DATA step. This is a perfect
situation to utilize the SYMPUT routine. Program 3 is a correct modification of Program 2.
8
Beyond the Basics
SAS Global Forum 2010
010
0
125
Figure 3a. Program 2 is pushed into the input stack (Figure 3a).
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
WORD SCANNER
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
COMPILER
COMPILER
INPUT STACK
data _null_; set
ex1 end=last; if
tall then
count_tall +1;
if last then do;
if count_tall then
do; %let titl =
"Some students are
taller than 63
inches"; end;
else do; %let titl
= "None of the
students are taller
than 63 inches";
end; end; run;
INPUT STACK
data _null_; set
ex1 end=last; if
tall then
count_tall +1;
if last then do;
if count_tall then
do; %let titl =
"Some students are
taller than 63
inches"; end;
else do; %let titl
= "None of the
students are taller
than 63 inches";
end; end; run;
MACRO PROCESSOR
MACRO PROCESSOR
GLOBAL SYMBOL TABLE
SAS Automatic Macro Variables
SYSDATE 19JUN06
SYSDAT Monday
SYSTIME 10:34
.
.
User-Defined Macro Variables
GLOBAL SYMBOL TABLE
SAS Automatic Macro Variables
SYSDATE 19JUN06
SYSDAT Monday
SYSTIME 10:34
.
.
User-Defined Macro Variables
Figure 3b. The word scanner directs the SAS code to the compiler until it reaches the macro
trigger (%LET). The word scanner direct the %LET statement to the macro processor.
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
WORD SCANNER
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
COMPILER
COMPILER
INPUT STACK
= "Some students
are taller than 63
inches"; end;
else do; %let titl
= "None of the
students are taller
than 63 inches";
end; end; run;
INPUT STACK
= "Some students
are taller than 63
inches"; end;
else do; %let titl
= "None of the
students are taller
than 63 inches";
end; end; run;
titl
let
MACRO PROCESSOR
MACRO PROCESSOR
GLOBAL SYMBOL TABLE
SAS Automatic Macro Variables
SYSDATE 19JUN06
SYSDAT Monday
SYSTIME 10:34
.
.
User-Defined Macro Variables
GLOBAL SYMBOL TABLE
SAS Automatic Macro Variables
SYSDATE 19JUN06
SYSDAT Monday
SYSTIME 10:34
.
.
User-Defined Macro Variables
%
data _null_; set ex1
end=last; if tall
then count_tall +1;
if last then do;
if count_tall then
do;
9
Beyond the Basics
SAS Global Forum 2010
010
0
120
Figure 3c. The macro processor creates macro variable TITL with the value of “Some students
are taller than 63 inches” in the symbol table.
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
WORD SCANNER
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
COMPILER
COMPILER
INPUT STACK
%let titl = "None
of the students are
taller than 63
inches"; end; end;
run;
INPUT STACK
%let titl = "None
of the students are
taller than 63
inches"; end; end;
run;
;
do
else
MACRO PROCESSOR
MACRO PROCESSOR
GLOBAL SYMBOL TABLE
SAS Automatic Macro Variables
SYSDATE 19JUN06
SYSDAT Monday
SYSTIME 10:34
.
.
User-Defined Macro Variables
Titl
Some students are
taller than 63 inches
GLOBAL SYMBOL TABLE
SAS Automatic Macro Variables
SYSDATE 19JUN06
SYSDAT Monday
SYSTIME 10:34
.
.
User-Defined Macro Variables
Titl
Some students are
taller than 63 inches
data _null_; set ex1
end=last; if tall
then count_tall +1;
if last then do;
if count_tall then
do; end;
Figure 3d. The word scanner directs subsequent tokens to the compiler until it reads the
second %LET statement. The word scanner directs the %LET statement to the macro
processor.
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
WORD SCANNER
WORD SCANNER
TOKENNIZER
DETERMINE
DESTINATION
COMPILER
COMPILER
INPUT STACK
= "None of the
students are taller
than 63 inches";
end; end; run;
INPUT STACK
= "None of the
students are taller
than 63 inches";
end; end; run;
titl
let
MACRO PROCESSOR
MACRO PROCESSOR
GLOBAL SYMBOL TABLE
SAS Automatic Macro Variables
SYSDATE 19JUN06
SYSDAT Monday
SYSTIME 10:34
.
.
User-Defined Macro Variables
Titl
Some students are
taller than 63 inches
GLOBAL SYMBOL TABLE
SAS Automatic Macro Variables
SYSDATE 19JUN06
SYSDAT Monday
SYSTIME 10:34
.
.
User-Defined Macro Variables
Titl
Some students are
taller than 63 inches
%
data _null_; set ex1
end=last; if tall
then count_tall +1;
if last then do;
if count_tall then
do; end; else do;
10
Beyond the Basics
SAS Global Forum 2010
010
0
Documents you may be interested
Documents you may be interested