®

SAS Macro Language

Course Notes
SAS® Macro Language Course Notes was developed by Davetta Dunlap and Susan Farmer. Additional
contributions were made by Jim Simon, Warren Repole, Hunter McGhee, Michelle Buchecker,
Michele Ensor, Cynthia Johnson, Johnny Johnson, Linda Jolley, Marjorie Lampton, and Cynthia Zender.
Editing and production support was provided by the Curriculum Development and Support Department.
SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of
SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Other brand and product names are trademarks of their respective companies.

SAS® Macro Language Course Notes

Copyright © 2005 by SAS Institute Inc., Cary, NC 27513, USA. All rights reserved. Printed in the
United States of America. No part of this publication may be reproduced, stored in a retrieval system,
or transmitted, in any form or by any means, electronic, mechanical, photocopying, or otherwise, without
the prior written permission of the publisher, SAS Institute Inc.

Book code E70039, course code LWMACR, prepared date 07Sep05.

 For Your Information iii

Table of Contents

Course Description ...................................................................................................................... vi

Prerequisites ...............................................................................................................................vii

Module 1 Introduction to the SAS Macro Facility .............................................. 1-1

1.1 Purpose of the Macro Facility..........................................................................................1-2

1.2 Program Flow ..................................................................................................................1-8

1.3 Course Data....................................................................................................................1-18

Module 2 Creating and Resolving Macro Variables Prior to Compilation ....... 2-1

2.1 Introduction to Macro Variables ......................................................................................2-2

2.2 Automatic Macro Variables .............................................................................................2-4

2.3 Macro Variable References ..............................................................................................2-7

2.4 User-Defined Macro Variables.......................................................................................2-18

2.5 Delimiting Macro Variable Names.................................................................................2-25

Module 3 Using Macro Functions........................................................................ 3-1

3.1 Basic Macro Functions ....................................................................................................3-2

3.2 Special Macro Functions................................................................................................ 3-11

Module 4 Defining and Executing Macro Programs .......................................... 4-1

4.1 Defining and Calling a Macro..........................................................................................4-2

4.2 Macro Parameters ..........................................................................................................4-15

Module 5 Creating and Resolving Macro Variables During Execution ............ 5-1

5.1 Creating Macro Variables in the DATA Step ...................................................................5-2

iv For Your Information

5.2 Indirect References to Macro Variables .........................................................................5-23

5.3 Creating Macro Variables in SQL..................................................................................5-37

Module 6 Utilizing Macro Language Statements................................................ 6-1

6.1 Iterative Processing..........................................................................................................6-2

6.2 Conditional Processing ..................................................................................................6-13

6.3 Global and Local Symbol Table.....................................................................................6-26

Appendix A Exercises and Solutions ..................................................................... A-1

Session 1................................................................................................................................... A-2

Module 2 Exercises............................................................................................................... A-2
After-Class Exercises............................................................................................................ A-3
Module 2 Solutions to Exercises .......................................................................................... A-5
After-Class Solutions to Exercises........................................................................................ A-9

Session 2................................................................................................................................. A-11

Module 3 Exercises............................................................................................................. A-11
Module 4 Exercises............................................................................................................. A-12
After-Class Exercises.......................................................................................................... A-13
Module 3 Solutions to Exercises ........................................................................................ A-14
Module 4 Solutions to Exercises ........................................................................................ A-17
After-Class Solutions to Exercises ..................................................................................... A-21

Session 3................................................................................................................................. A-23

Module 5 Exercises............................................................................................................. A-23
After-Class Exercises.......................................................................................................... A-24
Module 5 Solutions to Exercises ........................................................................................ A-26
After-Class Solutions to Exercises...................................................................................... A-32

Session 4................................................................................................................................. A-39

Module 6 Exercises............................................................................................................. A-39
 For Your Information v

Module 6 Solutions to Exercises ........................................................................................ A-41

Appendix B SAS Programming Flow and Macro Variable Retrieval in the DATA Step
B-1

B.1 Program Flow ................................................................................................................. B-2

B.2 Retrieving Macro Variables in the DATA Step ............................................................... B-3

vi For Your Information

Course Description
This Live Web course is for experienced SAS programmers who want to build complete macro-based
systems using the SAS macro facility.
This course focuses on the components of the SAS macro facility and how to design, write, and debug
macro systems. Emphasis is placed on understanding how programs with and without macro code are
processed.

To learn more…

A full curriculum of general and statistical instructor-based training is available

at any of the Institute’s training facilities. Institute instructors can also provide
on-site training.
For information on other courses in the curriculum, contact the SAS Education
Division at 1-800-333-7660, or send e-mail to training@sas.com. You can also
find this information on the Web at support.sas.com/training/ as well as in the
Training Course Catalog.

For a list of other SAS books that relate to the topics covered in this
Course Notes, USA customers can contact our SAS Publishing Department at
1-800-727-3228 or send e-mail to sasbook@sas.com. Customers outside the
USA, please contact your local SAS office.
Also, see the Publications Catalog on the Web at www.sas.com/pubs for a
complete list of books and a convenient order form.
 For Your Information vii

Prerequisites
Before attending this course, students should have completed the SAS® Programming II course or have
equivalent knowledge. Specifically, you should be able to
ƒ write and submit SAS programs on your operating system
ƒ use LIBNAME, FILENAME, TITLE, and OPTIONS statements
ƒ use a DATA step to read from or write to a SAS data set or external data file
ƒ use DATA step programming statements such as IF-THEN/ELSE, DO WHILE, DO UNTIL, and
iterative DO
ƒ use character functions such as SUBSTR, SCAN, INDEX, and UPCASE
ƒ use the LENGTH and RETAIN statements
ƒ use SAS data set options such as DROP=, KEEP=, and OBS=
ƒ form subsets of data using the WHERE clause
ƒ create and use SAS date values, including SAS date constants
ƒ execute Base SAS procedures such as SORT, PRINT, CONTENTS, MEANS, FREQ, TABULATE,
and CHART.
Module 1 Introduction to the
SAS Macro Facility

1.1 Purpose of the Macro Facility .......................................................................................1-2

1.2 Program Flow..................................................................................................................1-8

1.3 Course Data ..................................................................................................................1-18

1-2 Module 1 Introduction to the SAS Macro Facility

1.1 Purpose of the Macro Facility

Objectives
„ State the purpose of the macro facility.
„ View examples of macro applications.
„ Identify the tokens in a SAS program.
„ Describe how a SAS program is tokenized, compiled,
and executed.
„ Describe the data used in the course examples and
workshops.

Purpose of the Macro Facility

The macro facility is a text processing facility for
automating and customizing flexible SAS code.

The macro facility supports

„ symbolic substitution within SAS code

„ automated production of SAS code

„ dynamic generation of SAS code

„ conditional construction of SAS code.

9
 1.1 Purpose of the Macro Facility 1-3

Purpose of the Macro Facility

The macro facility enables you to
„ create and resolve macro variables anywhere within
a SAS program
„ write and call macro programs (macros) that
generate custom SAS code.

10

The macro facility is a tool for customizing SAS and for minimizing the amount of program code you
must enter to perform common tasks.

Substituting System Information

Example: Include system information within SAS footnotes.
proc print data=perm.all;
title "Listing of PERM.ALL
1 Data Set";
2 3
footnote1 "Created &systime &sysday, &sysdate9.";
footnote2 "on the &sysscp System Using Release &sysver";
run; 4 5

Automatic macro variables, which store system information,

can be used to avoid hardcoding these values.

11
1-4 Module 1 Introduction to the SAS Macro Facility

Substituting System Information

The automatic macro variables substitute the system
information within SAS footnotes.
proc print data=perm.all;
title "Listing of PERM.ALL
1 Data
2 Set"; 3
footnote1 "Created 10:24 Wednesday, 25AUG2004";
footnote2 "on the WIN System Using Release 9.1";
run; 4 5

12

Substituting User-Defined Information

Example: Include the same value repeatedly throughout
a program.
proc print data=perm.schedule;
where year(begin_date)=2004;
title "Scheduled Classes for 2004";
run;
proc means data=perm.all sum;
where year(begin_date)=2004;
class location;
var fee;
title "Total Fees for 2004 Classes";
title2 "by Training Center";
run;

User-defined macro variables enable you to define a

value once, then substitute that value as often as
necessary within a program.
13

Conditional Processing
Example: Generate a detailed report on a daily basis.
Generate an additional report every Friday,
summarizing data on a weekly basis.

proc print data=perm.all;

Daily report run;

Is it proc means data=perm.all;

Friday? run;
Yes

Macro programs can conditionally execute selected

portions of a SAS program based on user-defined
conditions.
14
 1.1 Purpose of the Macro Facility 1-5

Repetitive Processing
Example: Generate a similar report each year from
2003 to 2005.

proc print data=perm.year2003;

run;

proc print data=perm.year2004;

run;

proc print data=perm.year2005;

run;

The macro facility can generate SAS code repetitively,

substituting different values with each iteration.

15

Data-Driven Applications
Example: Create a separate subset of a data set for each
unique value of a selected variable.
data Boston Dallas Seattle;
set perm.schedule;
select(location);
when("Boston") output Boston;
when("Dallas") output Dallas;
when("Seattle") output Seattle;
otherwise;
end;
run;

The macro facility can generate data-driven code.

16
1-6 Module 1 Introduction to the SAS Macro Facility

Developing Macro-Based Applications

If a macro-based application generates SAS code, use a
four-step approach.

Step 1:
„ write and debug the desired SAS program without any
macro coding
„ make sure the SAS program runs with hardcoded
programming constants on a fixed set of data.

Steps 2-4 will be presented later.

17

Beginning the development process in this manner enables rapid development and debugging because
syntax and logic at the SAS code level is isolated from syntax and logic at the macro level.

Efficiency of Macro-Based Applications

The macro facility can reduce program
„ development time

„ maintenance time.

SAS code generated by macro techniques

„ does not compile or execute faster than any other SAS
code
„ depends on the efficiency of the underlying SAS code,
regardless of how the SAS code was generated.

18
 1.1 Purpose of the Macro Facility 1-7

Student Activity
Substitute &sysdate9 for 25AUG2004 and submit the
following program:

libname perm '.';

options nodate;
proc print data=perm.all;
title "Listing of PERM.ALL Data Set";
footnote1 "Created 25AUG2004";
run;

sa-sysdate

19
1-8 Module 1 Introduction to the SAS Macro Facility

1.2 Program Flow

Program Flow
A SAS program can be any combination of
„ DATA steps and PROC steps

„ global statements

„ SAS Component Language (SCL)

„ Structured Query Language (SQL)

„ SAS macro language.

When you submit a program, it is copied to a location in

memory called the input stack.

22

Program Flow

Input
InputStack
Stack

Batch or
SUBMIT Stored Noninteractive
Command Process Submission
data
data new;
new; %STPBEGIN;
%STPBEGIN; //SYSIN
//SYSIN DD
DD **
set
set perm.mast;
perm.mast; proc
proc print
print data=new;
data=new; options
options nodate;
nodate;
bonus=wage*0.1;
bonus=wage*0.1; run;
run; proc
proc sql;
sql;
run;
run; proc means data=new;
proc means data=new; select
select **
proc
proc print;
print; run;
run; from
from perm.mast;
perm.mast;
run;
run; %STPEND;
%STPEND; quit;
quit;

23
 1.2 Program Flow 1-9

Program Flow
After SAS code is in the input stack, a component of SAS
called the word scanner
„ reads the text in the input stack, character by
character, left-to-right, top-to-bottom
„ breaks the text into fundamental units called tokens.

data
data
Word new
new
Scanner ;;

set
set perm.mast;
perm.mast;
bonus=wage*0.1;
bonus=wage*0.1;
Input run;
run;
proc
proc print;
print;
Stack run;
run;

24

Program Flow
The word scanner passes the tokens, one at a time, to
the appropriate compiler, as the compiler demands.

Compiler data
data new;
new;

set
set
Word Scanner perm
perm
..
mast
mast
;;

Input Stack bonus=wage*0.1;

bonus=wage*0.1;
run;
run;
proc
proc print;
print;
run;
run;

25

Program Flow
The compiler
„ requests tokens until it receives a semicolon
„ performs a syntax check on the statement
„ repeats this process for each statement.

SAS
„ suspends the compiler when a step boundary is
encountered
„ executes the compiled code if there are no
compilation errors
„ repeats this process for each step.

26
1-10 Module 1 Introduction to the SAS Macro Facility

Tokenization
The word scanner recognizes four classes of tokens:
„ literal tokens

„ number tokens

„ name tokens

„ special tokens.

27

Literal Tokens
A literal token is a string of characters enclosed in single
or double quotes.

Examples: 'Any text'

"Any text"

 The string is treated as a unit by the compiler.

28

Number Tokens
Number tokens can be
„ integer numbers, including SAS date constants
„ floating point numbers, containing a decimal point
and/or exponent.

Examples: 3
3.
3.5
-3.5
’01jan2002’d
5E8
7.2E-4

29
 1.2 Program Flow 1-11

Name Tokens
Name tokens contain one or more characters beginning
with a letter or underscore and continuing with
underscores, letters, or numerals.

Examples: infile
_n_
item3
univariate
dollar10.2

 Format and informat names contain a period.

30

Special Tokens
Special tokens can be any character, or combination of
characters, other than a letter, numeral, or underscore.

Examples: * / + - ** ; $ ( ) . & % @ # = ||

31

Tokenization
A token ends when the word scanner detects
„ the beginning of another token

„ a blank after a token.

Blanks
„ are not tokens

„ delimit tokens.

The maximum length of a token is 32,767 characters.

33
1-12 Module 1 Introduction to the SAS Macro Facility

Example

Input Stack var x1-x10 z ;

Tokens 1. var
2. x1
3. -
4. x10
5. z
6. ;

34

Example

Input Stack title 'Report for May';

Tokens 1. title
2. 'Report for May'
3. ;

35

Question
How many tokens are present in each of these
statements?

input @10 ssn comma11. name $30-50;

bonus=3.2*(wage-2000);

plot date*revenue='$'/vref='30jun2001'd;

36
 1.2 Program Flow 1-13

Processing Tokens
flow1.sas
By executing the program below, one token at a time in the Program Editor, you can observe in the SAS
log which tokens trigger SAS to compile and execute code.
proc
options
;
proc
print
;
run
;
1. Which token triggers execution of the PROC OPTIONS step, displaying the current settings of
system options in the SAS log?

2. Which token triggers an error message in the log window indicating that no data set is available to be
printed?

3. Which token triggers a note indicating that SAS stopped processing the step?
1-14 Module 1 Introduction to the SAS Macro Facility

Macro Triggers
During word scanning, two token sequences are
recognized as macro triggers:
„ %name-token a macro statement, function, or call
„ &name-token a macro variable reference.

The word scanner passes macro triggers to the

macro processor, which
„ requests additional tokens as necessary

„ performs the action indicated.

42

Macro Statements
Macro statements
„ begin with a percent sign (%) followed by a name token

„ end with a semicolon

„ represent macro triggers

„ are executed by the macro processor.

43

The %PUT Statement

The %PUT statement
„ writes text to the SAS log

„ writes to column one of the next line

„ writes a blank line if no text is specified

„ does not require quotes around text

„ is valid in open code (anywhere in a SAS program).

General form of the %PUT statement:

%PUT
%PUT text;
text;

44
 1.2 Program Flow 1-15

The %PUT Statement

Example: Use a %PUT statement to write text to the
SAS log.

Partial SAS Log

12 %put Hi Mom!;
Hi Mom!

45

Program Flow
The %PUT statement is submitted.

Compiler
Macro Processor

Word
Scanner

Input %put
%put Hi
Hi Mom!;
Mom!;
Stack

46

Program Flow
The statement is tokenized.

Compiler
Macro Processor

Word %%
Scanner put
put
Hi
Hi
Mom
Mom
!!
;;
Input
Stack

47
1-16 Module 1 Introduction to the SAS Macro Facility

Program Flow
When a macro trigger is encountered, it is passed to the
macro processor for evaluation.

Compiler
Macro Processor

Word %put
%put
Scanner
Hi
Hi
Mom
Mom
!!
;;

Input
Stack
48

Program Flow
The macro processor requests tokens until a semicolon
is encountered, and then it executes the macro statement.

Compiler
Macro Processor

Word %put
%put Hi
Hi Mom!;
Mom!;
Scanner

Input
Stack
49

Quick Quiz
What will be printed in the log if you add quotes around the
text in the %PUT statement?

Compiler
Macro Processor

Word
Scanner

Input %put "Hi Mom";

Stack

50
 1.2 Program Flow 1-17

Quick Quiz - Answer

What will be printed in the log if you add quotes around
the text in the %PUT statement?

Partial SAS Log

1 %put "Hi Mom";
"Hi Mom"

51
1-18 Module 1 Introduction to the SAS Macro Facility

1.3 Course Data

To demonstrate features of the macro facility, this course uses course registration data from a company
that specializes in computer training.
The company presents its courses in cities (Boston, Dallas, and Seattle) around the United States.
The company is developing a registration and reporting system.
Data for October 2004 through March 2006 are documented in the following data sets:

SAS Data Set Description Number of Observations

courses contains information about courses with one 6
observation per course.
schedule contains information about each course with one 18
observation per course at a particular location and
date.
students contains information about students with one 207
observation per student.
register contains information about students registered for 434
a specific course with one observation per student
for a particular course.
all joins all data files with one observation per 434
student per course.

These data sets are stored in a SAS data library with a libref of perm.
 1.3 Course Data 1-19

The COURSES Data Set

The CONTENTS Procedure

Data Set Name PERM.COURSES Observations 6

Member Type DATA Variables 4
Engine V9 Indexes 0
Created Tuesday, May 30, Observation Length 48
2000 04:21:30 PM
Last Modified Monday, June 12, Deleted Observations 0
2000 10:39:41 AM
Protection Compressed NO
Data Set Type Sorted NO
Label
Data Representation WINDOWS_32
Encoding Default

Engine/Host Dependent Information

Data Set Page Size 4096

Number of Data Set Pages 2
First Data Page 1
Max Obs per Page 84
Obs in First Data Page 6
Number of Data Set Repairs 0
File Name C:\workshop\winsas\macr\courses.sas7bdat
Release Created 8.0000M0
Host Created WIN_NT

Alphabetic List of Variables and Attributes

# Variable Type Len Format Informat Label

1 Course_Code Char 4 Course Code

2 Course_Title Char 25 Description
3 Days Num 8 1. 1. Course Length
4 Fee Num 8 DOLLAR5. DOLLAR5. Course Fee

Listing of PERM.COURSES
Course_
Obs Code Course_Title Days Fee

1 C001 Basic Telecommunications 3 $795

2 C002 Structured Query Language 4 $1150
3 C003 Local Area Networks 3 $650
4 C004 Database Design 2 $375
5 C005 Artificial Intelligence 2 $400
6 C006 Computer Aided Design 5 $1600
1-20 Module 1 Introduction to the SAS Macro Facility

The SCHEDULE Data Set

The CONTENTS Procedure

Data Set Name PERM.SCHEDULE Observations 18

Member Type DATA Variables 5
Engine V9 Indexes 0
Created Monday, July 12, Observation Length 56
2004 04:29:52 PM
Last Modified Monday, July 12, Deleted Observations 0
2004 04:29:52 PM
Protection Compressed NO
Data Set Type Sorted NO
Label
Data Representation WINDOWS_32
Encoding wlatin1 Western
(Windows)

Engine/Host Dependent Information

Data Set Page Size 8192

Number of Data Set Pages 1
First Data Page 1
Max Obs per Page 145
Obs in First Data Page 18
Number of Data Set Repairs 0
File Name C:\workshop\winsas\macr\schedule.sas7bdat
Release Created 9.0101B3
Host Created XP_PRO

Alphabetic List of Variables and Attributes

# Variable Type Len Format Informat Label

4 Begin_Date Num 8 DATE9. DATE7. Begin

2 Course_Code Char 4 Course Code
1 Course_Number Num 8 2. 2. Course Number
3 Location Char 15 Location
5 Teacher Char 20 Instructor

Partial Listing of PERM.SCHEDULE

Course_ Course_ Begin_

Obs Number Code Location Date Teacher

1 1 C001 Seattle 26OCT2004 Hallis, Dr. George

2 2 C002 Dallas 07DEC2004 Wickam, Dr. Alice
3 3 C003 Boston 11JAN2005 Forest, Mr. Peter
4 4 C004 Seattle 25JAN2005 Tally, Ms. Julia
5 5 C005 Dallas 01MAR2005 Hallis, Dr. George
6 6 C006 Boston 05APR2005 Berthan, Ms. Judy
7 7 C001 Dallas 24MAY2005 Hallis, Dr. George
 1.3 Course Data 1-21

The STUDENTS Data Set

The CONTENTS Procedure

Data Set Name PERM.STUDENTS Observations 207

Member Type DATA Variables 3
Engine V9 Indexes 0
Created Tuesday, May 30, Observation Length 85
2000 04:21:31 PM
Last Modified Monday, June 12, Deleted Observations 0
2000 10:39:11 AM
Protection Compressed NO
Data Set Type Sorted NO
Label
Data Representation WINDOWS_32
Encoding Default

Engine/Host Dependent Information

Data Set Page Size 8192

Number of Data Set Pages 4
First Data Page 1
Max Obs per Page 95
Obs in First Data Page 80
Number of Data Set Repairs 0
File Name C:\workshop\winsas\macr\students.sas7bdat
Release Created 8.0000M0
Host Created WIN_NT

Alphabetic List of Variables and Attributes

# Variable Type Len Label

3 City_State Char 20 City,State

2 Student_Company Char 40 Company
1 Student_Name Char 25 Student Name

Partial Listing of PERM.STUDENTS

Obs Student_Name Student_Company City_State

1 Abramson, Ms. Andrea Eastman Developers Deerfield, IL

2 Alamutu, Ms. Julie Reston Railway Chicago, IL
3 Albritton, Mr. Bryan Special Services Oak Brook, IL
4 Allen, Ms. Denise Department of Defense Bethesda, MD
5 Amigo, Mr. Bill Assoc. of Realtors Chicago, IL
6 Avakian, Mr. Don Reston Railway Chicago, IL
7 Babbitt, Mr. Bill National Credit Corp. Chicago, IL
8 Baker, Mr. Vincent Snowing Petroleum New Orleans, LA
9 Bates, Ms. Ellen Reston Railway Chicago, IL
10 Belles, Ms. Vicki Jost Hardware Inc. Toledo, OH
11 Benincasa, Ms. Elizabeth Hospital Nurses Association Naperville, IL
12 Bills, Ms. Paulette Reston Railway Chicago, IL
1-22 Module 1 Introduction to the SAS Macro Facility

The REGISTER Data Set

The CONTENTS Procedure

Data Set Name PERM.REGISTER Observations 434

Member Type DATA Variables 3
Engine V9 Indexes 0
Created Tuesday, May 30, Observation Length 40
2000 04:21:31 PM
Last Modified Monday, June 12, Deleted Observations 0
2000 10:39:54 AM
Protection Compressed NO
Data Set Type Sorted NO
Label
Data Representation WINDOWS_32
Encoding Default

Engine/Host Dependent Information

Data Set Page Size 4096

Number of Data Set Pages 6
First Data Page 1
Max Obs per Page 101
Obs in First Data Page 68
Number of Data Set Repairs 0
File Name C:\workshop\winsas\macr\register.sas7bdat
Release Created 8.0000M0
Host Created WIN_NT

Alphabetic List of Variables and Attributes

# Variable Type Len Format Informat Label

2 Course_Number Num 8 2. 2. Course Number

3 Paid Char 1 Paid Status
1 Student_Name Char 25 Student Name

Partial Listing of PERM.REGISTER

Course_
Obs Student_Name Number Paid

1 Albritton, Mr. Bryan 1 Y

2 Amigo, Mr. Bill 1 N
3 Chodnoff, Mr. Norman 1 Y
4 Clark, Mr. Rich 1 Y
5 Crace, Mr. Ron 1 Y
6 Dellmonache, Ms. Susan 1 Y
7 Dixon, Mr. Matt 1 Y
8 Edwards, Mr. Charles 1 N
9 Edwards, Ms. Sonia 1 Y
10 Elsins, Ms. Marisa F. 1 Y
11 Griffin, Mr. Lantz 1 Y
12 Hall, Ms. Sharon 1 Y
 1.3 Course Data 1-23

The ALL Data Set

The program used to create the perm.all data set is shown below.
proc sql;
create table perm.all as
select students.student_name,
schedule.course_number,
paid, courses.course_code,
location, begin_date,
teacher, course_title, days, fee,
student_company, city_state
from perm.schedule, perm.students,
perm.register, perm.courses
where schedule.course_code =
courses.course_code and
schedule.course_number =
register.course_number and
students.student_name =
register.student_name
order by students.student_name,
courses.course_code;
quit;
1-24 Module 1 Introduction to the SAS Macro Facility

The ALL Data Set

The CONTENTS Procedure

Data Set Name PERM.ALL Observations 434

Member Type DATA Variables 12
Engine V9 Indexes 0
Created Friday, July 23, Observation Length 184
2004 02:53:26 PM
Last Modified Friday, July 23, Deleted Observations 0
2004 02:53:26 PM
Protection Compressed NO
Data Set Type Sorted YES
Label
Data Representation WINDOWS_32
Encoding wlatin1 Western (Windows)

Engine/Host Dependent Information

Data Set Page Size 16384

Number of Data Set Pages 6
First Data Page 1
Max Obs per Page 88
Obs in First Data Page 76
Number of Data Set Repairs 0
File Name C:\workshop\winsas\macr\all.sas7bdat
Release Created 9.0101B3
Host Created XP_PRO

Alphabetic List of Variables and Attributes

# Variable Type Len Format Informat Label

6 Begin_Date Num 8 DATE9. DATE7. Begin

12 City_State Char 20 City,State
4 Course_Code Char 4 Course Code
2 Course_Number Num 8 2. 2. Course Number
8 Course_Title Char 25 Description
9 Days Num 8 1. 1. Course Length
10 Fee Num 8 DOLLAR5. DOLLAR5. Course Fee
5 Location Char 15 Location
3 Paid Char 1 Paid Status
11 Student_Company Char 40 Company
1 Student_Name Char 25 Student Name
7 Teacher Char 20 Instructor

Sort Information

Sortedby Student_Name Course_Code

Validated YES
Character Set ANSI
 1.3 Course Data 1-25

The ALL Data Set

Partial Listing of PERM.ALL

Course_ Course_
Obs Student_Name Number Paid Code Location

1 Abramson, Ms. Andrea 10 Y C004 Dallas

2 Abramson, Ms. Andrea 6 N C006 Boston
3 Alamutu, Ms. Julie 14 N C002 Seattle
4 Albritton, Mr. Bryan 1 Y C001 Seattle
5 Albritton, Mr. Bryan 5 Y C005 Dallas

Begin_
Obs Date Teacher Course_Title

1 16AUG2005 Tally, Ms. Julia Database Design

2 05APR2005 Berthan, Ms. Judy Computer Aided Design
3 06DEC2005 Wickam, Dr. Alice Structured Query Language
4 26OCT2004 Hallis, Dr. George Basic Telecommunications
5 01MAR2005 Hallis, Dr. George Artificial Intelligence

Obs Days Fee Student_Company City_State

1 2 $375 Eastman Developers Deerfield, IL

2 5 $1600 Eastman Developers Deerfield, IL
3 4 $1150 Reston Railway Chicago, IL
4 3 $795 Special Services Oak Brook, IL
5 2 $400 Special Services Oak Brook, IL

Student Activity
Examine the contents of the perm library.
Submit the program:

libname perm '.';

proc contents data=perm._all_;
run;

sa-contents

60
1-26 Module 1 Introduction to the SAS Macro Facility

Module 1 Summary
„ State the purpose of the macro facility.
„ View examples of macro applications.
„ Identify the tokens in a SAS program.
„ Describe how a SAS program is tokenized, compiled,
and executed.
„ Describe the data used in the course examples and
workshops.

61
Module 2 Creating and Resolving
Macro Variables Prior to Compilation

2.1 Introduction to Macro Variables....................................................................................2-2

2.2 Automatic Macro Variables............................................................................................2-4

2.3 Macro Variable References............................................................................................2-7

2.4 User-Defined Macro Variables.....................................................................................2-18

2.5 Delimiting Macro Variable Names ...............................................................................2-25

2-2 Module 2 Creating and Resolving Macro Variables Prior to Compilation

2.1 Introduction to Macro Variables

Objectives
„ Understand macro variables.
„ Describe where macro variables are stored.
„ Identify the two types of macro variables.
„ Identify selected automatic macro variables.
„ Display automatic macro variables in the SAS log.
„ Understand how macro variable references are
handled by the word scanner and macro processor.
„ Create user-defined macro variables.
„ Display values of user-defined macro variables in the
SAS log.
„ Place a macro variable reference adjacent to text or
another macro variable reference.

Macro Variables
Macro variables store text, including
„ complete or partial SAS steps

„ complete or partial SAS statements.

Macro variables are referred to as symbolic variables

because SAS programs can reference macro variables
as symbols for additional program text.

4
 2.1 Introduction to Macro Variables 2-3

Global Symbol Table

Macro variables are stored in an area of memory called
the global symbol table. When SAS is invoked, the
global symbol table is created and initialized with
automatic macro variables.

Global Symbol Table

. .
. .
Automatic SYSTIME 09:47
Variables SYSVER 9.1
. .
. .

Global Symbol Table

User-defined macro variables can be added to the
global symbol table.

Global Symbol Table

. .
. .
Automatic SYSTIME 09:47
Variables SYSVER 9.1
. .
. .
User-Defined CITY Dallas
Variables DATE 05JAN2004
AMOUNT 975

Macro Variables
Macro variables in the global symbol table
„ are global in scope (available any time)

„ have a minimum length of 0 characters (null value)

„ have a maximum length of 65,534 (64K) characters

„ store numeric tokens as character strings.

7
2-4 Module 2 Creating and Resolving Macro Variables Prior to Compilation

2.2 Automatic Macro Variables

Automatic Macro Variables

Automatic macro variables
„ are system-defined

„ are created at SAS invocation

„ are global (always available)

„ are assigned values by SAS

„ can be assigned values by the user, in some cases.

System-Defined Automatic Macro Variables

Some automatic macro variables have fixed values that
are set at SAS invocation:
Name Description
SYSDATE date of SAS invocation (DATE7.)
SYSDATE9 date of SAS invocation (DATE9.)
SYSDAY day of the week of SAS invocation
SYSTIME time of SAS invocation
SYSSCP abbreviation for the operating system: OpenVMS,
WIN, HP 300, and so on
SYSVER release of SAS software being used.

10
 2.2 Automatic Macro Variables 2-5

System-Defined Automatic Macro Variables

Some automatic macro variables have values that change
automatically based on submitted SAS statements:

Name Description
SYSLAST name of most recently created SAS data set in
the form libref.name. If no data set has been
created, the value is _NULL_.

SYSPARM text specified at program invocation.

11

Automatic Macro Variables

Example: Write the names and values of all automatic
macro variables to the SAS log using the
_AUTOMATIC_ argument of the %PUT
statement.

%put _automatic_;

12

Automatic Macro Variables

Partial SAS Log
12 %put _automatic_;
AUTOMATIC AFDSID 0
AUTOMATIC AFDSNAME
AUTOMATIC AFLIB
AUTOMATIC AFSTR1
AUTOMATIC AFSTR2
AUTOMATIC FSPBDV
AUTOMATIC SYSBUFFR
AUTOMATIC SYSCC 3000
AUTOMATIC SYSCHARWIDTH 1
AUTOMATIC SYSCMD
AUTOMATIC SYSDATE 05FEB04
AUTOMATIC SYSDATE9 05FEB2004

The macro variables SYSDATE, SYSDATE9, and

SYSTIME store character strings, not SAS date or time
values.

13
2-6 Module 2 Creating and Resolving Macro Variables Prior to Compilation

Student Activity
„ Display in the SAS log the name of every automatic
macro variable.
„ Find the value of SYSSCP.

%put _automatic_;

14
 2.3 Macro Variable References 2-7

2.3 Macro Variable References

Macro Variable Reference

Macro variable references
„ begin with an ampersand (&) followed by a macro
variable name
„ represent macro triggers
„ are also called symbolic references
„ can appear anywhere in your program
„ are passed to the macro processor.

When the macro processor receives a macro variable

reference, it
„ searches the symbol table for the macro variable
„ places the macro variable's value on the input stack
„ issues a warning to the SAS log if the macro variable is
not found in the symbol table.
17

Macro Variable Reference

Example: Write the day of the week to the SAS log.

Partial SAS Log

12 %put Today is &sysday;
Today is Tuesday

18
2-8 Module 2 Creating and Resolving Macro Variables Prior to Compilation

Substitution within a Macro Statement

Compiler
Macro Processor
Word
Scanner

Symbol Table
%put
%put Today
Today is
is &sysday;
Input &sysday;
Stack SYSDAY Tuesday
SYSLAST _NULL_

19

Substitution within a Macro Statement

When a macro trigger is encountered, it is passed to the
macro processor for evaluation.

Compiler
Macro Processor
Word %put
%put
Scanner

Symbol Table
Today
Today is
is &sysday;
Input &sysday;
Stack SYSDAY Tuesday
SYSLAST _NULL_

20

Substitution within a Macro Statement

The macro processor requests tokens until a semicolon
is encountered.

Compiler
Macro Processor
Word %put
%put Today
Today is
is &sysday;
&sysday;
Scanner

Symbol Table
Input
Stack SYSDAY Tuesday
SYSLAST _NULL_

21
 2.3 Macro Variable References 2-9

Substitution within a Macro Statement

The macro variable reference triggers the macro processor
to search the symbol table for the reference.

Compiler
Macro Processor
Word %put
%put Today
Today is
is &sysday;
&sysday;
Scanner

Symbol Table
Input
Stack SYSDAY Tuesday
SYSLAST _NULL_

22

Substitution within a Macro Statement

The macro processor resolves the macro variable reference,
substituting its value.

Compiler
Macro Processor
Word %put
%put Today
Today is
is Tuesday;
Tuesday;
Scanner

Symbol Table
Input
Stack SYSDAY Tuesday
SYSLAST _NULL_

23

Substitution within a Macro Statement

The macro processor executes the %PUT statement,
writing the resolved text to the SAS log.

Compiler
Macro Processor
Word %put
%put Today
Today is
is Tuesday;
Tuesday;
Scanner

Symbol Table
Input
Stack SYSDAY Tuesday
SYSLAST _NULL_

24
2-10 Module 2 Creating and Resolving Macro Variables Prior to Compilation

Substitution within a SAS Literal

If you need to reference a Global Symbol Table
macro variable within a
CITY Dallas
literal, enclose the literal in
DATE 05JAN2000
double quotes.
AMOUNT 975
The word scanner continues to tokenize literals enclosed in
double quotes, permitting macro variables to resolve.
where cityst CONTAINS "&city"; generates
WHERE CITYST CONTAINS "Dallas";

The word scanner does not tokenize literals enclosed in

single quotes, so macro variables do not resolve.
where cityst contains '&city'; generates
WHERE CITYST CONTAINS '&city';
26

Substitution within a SAS Literal

Example: Substitute the day of the week in a title.

Compiler
Macro Processor
Word
Scanner

Symbol Table
Input proc
proc print
print data=perm.all;
data=perm.all;
title
title "Today
"Today is
is &sysday";
&sysday";
Stack run;
SYSDAY Tuesday
run; SYSLAST _NULL_

27

Substitution within a SAS Literal

SAS statements are passed to the compiler.

proc
proc print
print data=perm.all;
data=perm.all;
Compiler title
title
Macro Processor
Word ""
Today
Today
Scanner is
is

Symbol Table
Input
&sysday";
&sysday";
Stack run;
SYSDAY Tuesday
run; SYSLAST _NULL_

28
 2.3 Macro Variable References 2-11

Substitution within a SAS Literal

The macro trigger is passed to the macro processor.

proc
proc print
print data=perm.all;
data=perm.all;
Compiler title
title
Macro Processor
Word "" &sysday
&sysday
Today
Today
Scanner is
is

Symbol Table
Input ";
";
Stack run;
run;
SYSDAY Tuesday
SYSLAST _NULL_

29

Substitution within a SAS Literal

The macro processor searches the symbol table.

proc
proc print
print data=perm.all;
data=perm.all;
Compiler title
title
Macro Processor
Word "" &sysday
&sysday
Today
Today
Scanner is
is

Symbol Table
Input
Stack ";
"; SYSDAY Tuesday
run;
run; SYSLAST _NULL_

30

Substitution within a SAS Literal

The resolved reference is passed back to the input stack.

proc
proc print
print data=perm.all;
data=perm.all;
Compiler title
title
Macro Processor
Word ""
Today
Scanner Today
is
is

Symbol Table
Input
Stack Tuesday";
Tuesday"; SYSDAY Tuesday
run;
run; SYSLAST _NULL_

31
2-12 Module 2 Creating and Resolving Macro Variables Prior to Compilation

Substitution within a SAS Literal

Word scanning continues.

proc
proc print
print data=perm.all;
data=perm.all;
Compiler title
title
Macro Processor
Word ""
Today
Today
Scanner is
is
Tuesday
Tuesday
""
Symbol Table
Input
Stack ;; SYSDAY Tuesday
run;
run; SYSLAST _NULL_

32

Substitution within a SAS Literal

The double-quoted string is passed to the compiler as a
unit.

proc
proc print
print data=perm.all;
data=perm.all;
Compiler title
title "Today
"Today is
is Tuesday"
Tuesday"
Macro Processor
Word
Scanner

Symbol Table
Input
Stack ;; SYSDAY Tuesday
run;
run; SYSLAST _NULL_

33

Substitution within a SAS Literal

When a step boundary is encountered, compilation ends and
execution begins.

proc
proc print
print data=perm.all;
data=perm.all;
Compiler title
title "Today
"Today is
is Tuesday";
Tuesday";
Macro Processor
Word
Scanner
run;
run;
Symbol Table
Input
Stack SYSDAY Tuesday
SYSLAST _NULL_

34
 2.3 Macro Variable References 2-13

Quick Quiz
What is the title if you submit the following program?

Compiler
Macro Processor

Word
Scanner

Symbol Table
Input proc
proc print
print data=perm.all;
data=perm.all;
title
title 'Today
'Today is
is &sysday';
Stack run;
&sysday'; SYSDAY Tuesday
run;
SYSLAST _NULL_

35

Substitution within a SAS Literal

Example: Substitute system information in footnotes.
footnote1 "Created &systime &sysday, &sysdate9";
footnote2
"on the &sysscp system using Release &sysver";
title "REVENUES FOR DALLAS TRAINING CENTER";
proc tabulate data=perm.all;
where upcase(location)="DALLAS";
class course_title;
var fee;
table course_title=" " all="TOTALS",
fee=" "*(n*f=3. sum*f=dollar10.)
/ rts=30 box="COURSE";
run;
automatic

40

Substitution within a SAS Literal

REVENUES FOR DALLAS TRAINING CENTER

„ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ…ƒƒƒ…ƒƒƒƒƒƒƒƒƒƒ†
‚COURSE ‚ N ‚ Sum ‚
‡ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒˆƒƒƒƒƒƒƒƒƒƒ‰
‚Artificial Intelligence ‚ 25‚ $10,000‚
‡ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒˆƒƒƒƒƒƒƒƒƒƒ‰
‚Basic Telecommunications ‚ 18‚ $14,310‚
‡ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒˆƒƒƒƒƒƒƒƒƒƒ‰
‚Computer Aided Design ‚ 19‚ $30,400‚
‡ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒˆƒƒƒƒƒƒƒƒƒƒ‰
‚Database Design ‚ 23‚ $8,625‚
‡ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒˆƒƒƒƒƒƒƒƒƒƒ‰
‚Local Area Networks ‚ 24‚ $15,600‚
‡ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒˆƒƒƒƒƒƒƒƒƒƒ‰
‚Structured Query Language ‚ 24‚ $27,600‚
‡ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒˆƒƒƒƒƒƒƒƒƒƒ‰
‚TOTALS ‚133‚ $106,535‚
Šƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ‹ƒƒƒ‹ƒƒƒƒƒƒƒƒƒƒŒ

Created 14:56 Friday, 20AUG2004

on the WIN system using Release 9.1
41
2-14 Module 2 Creating and Resolving Macro Variables Prior to Compilation

Unresolved Reference
Example: Reference a non-existent macro variable.

Compiler

Macro Processor
Word
Scanner

Symbol Table
proc
proc print
print data=perm.exp;
Input title
data=perm.exp;
title "Expenses
"Expenses for
for R&D";
R&D";
Stack run;
run;
SYSDAY Tuesday
SYSLAST _NULL_

42

Unresolved Reference
The macro trigger is passed to the macro processor for
evaluation.

proc
proc print
print data=perm.exp;
Compiler data=perm.exp;
title
title
Macro Processor
Word ""
Scanner
Expenses
Expenses &D
&D
for
for
RR

Symbol Table
";
Input run;
";
run;
Stack SYSDAY Tuesday
SYSLAST _NULL_

43

Unresolved Reference
The macro processor writes a warning to the SAS log
when it cannot resolve a reference.

WARNING: Apparent symbolic

Compiler proc
proc print
print data=perm.exp;
data=perm.exp; reference D not resolved.
title
title

Macro Processor
Word ""
Scanner
Expenses
Expenses &D
&D
for
for
RR

Symbol Table
";
Input run;
";
run;
Stack SYSDAY Tuesday
SYSLAST _NULL_

44
 2.3 Macro Variable References 2-15

Unresolved Reference
If the macro processor cannot resolve a reference, it
passes the tokens back to the word scanner and the word
scanner passes them to the compiler.
proc
proc print
print data=perm.exp;
data=perm.exp;
Compiler title
title "Expenses
"Expenses for
for R&D"
R&D"

Macro Processor
Word ;;
run;
run;
Scanner

Symbol Table
Input
Stack SYSDAY Tuesday
SYSLAST _NULL_

45

Substitution within SAS Code

Example: Generalize PROC PRINT to print the last created
data set, using the automatic macro variable
SYSLAST.

Compiler

Macro Processor
Word
Scanner

Symbol Table
proc
proc print
print data=&syslast;
Input title
data=&syslast;
title "Listing
"Listing of
of &syslast";
&syslast";
Stack run;
run;
SYSDAY Tuesday
SYSLAST PERM.ALL

46

Substitution within SAS Code

SAS statements are passed to the compiler. When a macro
trigger is encountered, it is passed to the macro processor
for evaluation.
proc
proc print
print data=
Compiler data=

Macro Processor
Word
Scanner &syslast
&syslast

Symbol Table
Input ;;
title
title "Listing
"Listing of
of &syslast";
&syslast";
Stack run;
run;
SYSDAY Tuesday
SYSLAST PERM.ALL

47
2-16 Module 2 Creating and Resolving Macro Variables Prior to Compilation

Substitution within SAS Code

The macro variable reference triggers the macro processor
to search the symbol table for the reference.

proc
proc print
print data=
Compiler data=

Macro Processor
Word
&syslast
Scanner

Symbol Table
;;
Input title
title "Listing
"Listing of
of &syslast";
&syslast";
Stack run;
run;
SYSDAY Tuesday
SYSLAST PERM.ALL

48

Substitution within SAS Code

The macro processor resolves the macro variable reference,
passing its resolved value back to the input stack.

proc
proc print
print data=
Compiler data=

Macro Processor
Word
Scanner

Symbol Table
PERM.ALL;
Input title
PERM.ALL;
title "Listing
"Listing of
of &syslast";
&syslast";
Stack run;
run;
SYSDAY Tuesday
SYSLAST PERM.ALL

49

Substitution within SAS Code

Word scanning continues.

Compiler proc
proc print
print data=PERM.ALL;
data=PERM.ALL;

Macro Processor
Word
Scanner

Symbol Table
Input title
title "Listing
"Listing of
of &syslast";
&syslast";
Stack run;
run;
SYSDAY Tuesday
SYSLAST PERM.ALL

50
 2.3 Macro Variable References 2-17

Substitution within SAS Code

A step boundary is encountered. Compilation ends.
Execution begins.

proc
proc print
print data=PERM.ALL;
data=PERM.ALL;
Compiler title
title "Listing
"Listing of
of PERM.ALL";
PERM.ALL";

Macro Processor
Word run;
run;
Scanner

Symbol Table
Input
Stack SYSDAY Tuesday
SYSLAST PERM.ALL

51

Refer to Exercises 1 and 2 for Module 2 in Appendix A.

2-18 Module 2 Creating and Resolving Macro Variables Prior to Compilation

2.4 User-Defined Macro Variables

The %LET Statement

The %LET statement creates a macro variable and
assigns it a value.

General form of the %LET statement:

%LET
%LET variable=value;
variable=value;

„ variable follows SAS naming conventions.

„ If variable already exists, its value is overwritten.
„ If variable or value contain macro triggers, the triggers
are evaluated before the assignment is made.

55

The %LET Statement

Value can be any string:
„ The maximum length is 65,534 (64K) characters.

„ The minimum length is 0 characters (null value).

„ The numeric tokens are stored as character strings.

„ The mathematical expressions are not evaluated.

„ The case of value is preserved.

„ Quotes bounding literals are stored as part of value.

„ Leading and trailing blanks are removed from value

before the assignment is made.

56
 2.4 User-Defined Macro Variables 2-19

The %LET Statement

Value can be any string:
„ The maximum length is 65,534 (64K) characters.

„ The minimum length is 0 characters (null value).

„ The numeric tokens are stored as character strings.

„ The mathematical expressions are not evaluated.

„ The case of value is preserved.

„ Quotes bounding literals are stored as part of value.

„ Leading and trailing blanks are removed from value

before the assignment is made.

56

%LET Statement Examples

Determine the value assigned to each macro variable by
these %LET statements.

Value
%let name= Ed Norton ;
%let name2=' Ed Norton ';
%let title="Joan's Report";
%let start=;
%let sum=3+4;
%let total=0;
%let total=&total+∑
%let x=varlist;
%let &x=name age height;

57

%LET Statement Examples

The %LET statement
„ truncates leading and trailing blanks

„ stores quotation marks as part of the value

„ stores a null value. Value

Ed Norton
%let name= Ed Norton ; ' Ed Norton '
%let name2=' Ed Norton '; "Joan's Report"
%let title="Joan's Report";
%let start=;
%let sum=3+4;
%let total=0;
%let total=&total+∑
%let x=varlist;
%let &x=name age height;
58
2-20 Module 2 Creating and Resolving Macro Variables Prior to Compilation

%LET Statement Examples

The %LET statement
• does not evaluate mathematical expressions

• stores numeric tokens as character strings.

Value
%let name= Ed Norton ; Ed Norton
%let name2=' Ed Norton '; ' Ed Norton '
%let title="Joan's Report"; "Joan's Report"
%let start=;
%let sum=3+4; 3+4
%let total=0; 0
%let total=&total+∑
%let x=varlist;
%let &x=name age height;

59

%LET Statement Examples

The macro trigger is evaluated before assignment is
made. The previous value of total is replaced.

Value
%let name= Ed Norton ; Ed Norton
%let name2=' Ed Norton '; ' Ed Norton '
%let title="Joan's Report"; "Joan's Report"
%let start=;
%let sum=3+4; 3+4
%let total=0;
%let total=&total+∑ 0+3+4
%let x=varlist; varlist
%let &x=name age height;

61

Quick Quiz
What is the name of the macro variable created with this
%LET statement?
Value
%let name= Ed Norton ; Ed Norton
%let name2=’ Ed Norton ’; ' Ed Norton '
%let title="Joan’s Report"; "Joan's Report"
%let start=;
%let sum=3+4; 3+4
%let total=0;
%let total=&total+∑ 0+3+4
%let x=varlist; varlist
%let &x=name age height;

Submit your answer as a text question.

62
 2.4 User-Defined Macro Variables 2-21

%LET Statement Examples

The macro variable's name resolves to varlist.

Value
%let name= Ed Norton ; Ed Norton
%let name2=' Ed Norton '; ' Ed Norton '
%let title="Joan's Report"; "Joan's Report"
%let start=;
%let sum=3+4; 3+4
%let total=0;
%let total=&total+∑ 0+3+4
%let x=varlist; varlist
%let &x=name age height; name age height
macvarname=varlist

63

%LET Statement Examples

Example: Assign the value DALLAS to the macro
variable SITE. Reference the macro variable
within the program.

%let site=DALLAS;
title "REVENUES FOR &site TRAINING CENTER";
proc tabulate data=perm.all(keep=location
course_title fee);
where upcase(location)="&site";
class course_title;
var fee;
table course_title=’ ’ all=’TOTALS’,
fee=’ ’*(n*f=3. sum*f=dollar10.)
/ rts=30 box=’COURSE’;
run;
let1

64

%LET Statement Examples

PROC TABULATE Output

65
2-22 Module 2 Creating and Resolving Macro Variables Prior to Compilation

%LET Statement Examples

Example: Create three macro variables.

%let city=Dallas; Global Symbol Table

%let date=05JAN2004;
%let amount=975; CITY Dallas
DATE 05JAN2004
AMOUNT 975

Macro variables store numbers as character strings, not

as numeric values.

66

Displaying Macro Variables

Example: Display all user-defined macro variables in the
SAS log.

%put _user_;
Partial SAS Log
4 %put _user_;
GLOBAL DATE 05JAN2004
GLOBAL AMOUNT 975
GLOBAL CITY Dallas

Example: Display all user-defined and automatic macro

variables in the SAS log.
%put _all_;

67

Displaying Macro Variables

The SYMBOLGEN system option writes macro variable
values to the SAS log as they are resolved.
General form of the SYMBOLGEN system option:

OPTIONS
OPTIONSSYMBOLGEN;
SYMBOLGEN;

 The default option is NOSYMBOLGEN.

68
 2.4 User-Defined Macro Variables 2-23

Displaying Macro Variables

Global Symbol Table
CITY Dallas
DATE 05JAN2004
AMOUNT 975

Partial SAS Log

OPTIONS SYMBOLGEN;
where fee>&amount;
SYMBOLGEN: Macro variable AMOUNT resolves to 975
where city_state contains "&city";
SYMBOLGEN: Macro variable CITY resolves to Dallas
where city_state contains '&city';

69

Quick Quiz
Global Symbol Table
CITY Dallas
DATE 05JAN2004
AMOUNT 975

Partial SAS Log

OPTIONS SYMBOLGEN;
where fee>&amount;
SYMBOLGEN: Macro variable AMOUNT resolves to 975
where city_state contains "&city";
SYMBOLGEN: Macro variable CITY resolves to Dallas
where city_state contains '&city';

Why is no message displayed in the log for the final

70
example?

Deleting User-Defined Macro Variables

The %SYMDEL statement deletes one or more user-
defined macro variables from the global symbol table.

Because symbol tables are stored in memory, delete

macro variables when they are no longer needed.

General form of the %SYMDEL statement:

%SYMDEL
%SYMDEL macro-variables;
macro-variables;

Example: Delete the macro variables CITY and DATE.

%symdel city date;

72
2-24 Module 2 Creating and Resolving Macro Variables Prior to Compilation

Developing Macro-Based Applications

If a macro-based application generates SAS code, use a
four-step approach.
Step 1:
„ Write and debug the desired SAS program without any
macro coding.
Step 2:
„ Generalize the program by removing hardcoded
programming constants and substituting macro variable
references.
„ Initialize the macro variables with %LET statements.
„ Use the SYMBOLGEN system option for debugging.

Steps 3-4 will be presented later.

73

Refer to Exercise 3 for Module 2 in Appendix A.

 2.5 Delimiting Macro Variable Names 2-25

2.5 Delimiting Macro Variable Names

Referencing Macro Variables

You can reference macro variables anywhere in your
program, including these special situations:
Macro variable references adjacent to leading and/or
trailing text:
text&variable
&variabletext
text&variabletext
Adjacent macro variable references:
&variable&variable

77

Combining Macro Variables with Text

You can place text immediately before a macro variable
reference to build a new token.
Example: Data sets are stored in a SAS data library with
a naming convention of Yyyyymon.
yyyy can be 2000 2001 2002 and so on.
mon can be JAN FEB MAR and so on.
Write an application that uses macro variables to build
SAS data set names and other tokens.

78
2-26 Module 2 Creating and Resolving Macro Variables Prior to Compilation

Combining Macro Variables with Text

%let month=jan;
proc chart data=perm.y2000&month;
hbar week / sumvar=sale;
run;
proc plot data=perm.y2000&month;
plot sale*day;
run;
generates
PROC CHART DATA=PERM.Y2000JAN;
HBAR WEEK / SUMVAR=SALE;
RUN;
PROC PLOT DATA=PERM.Y2000JAN;
PLOT SALE*DAY;
RUN;

79

Combining Macro Variables with Text

This example illustrates adjacent macro variables
references.
Example: Modify the previous program to allow
both the month and the year to be
substituted.

%let year=2000;
%let month=jan;
proc chart data=perm.y&year&month;
hbar week / sumvar=sale;
run;
proc plot data=perm.y&year&month;
plot sale*day;
run;

80

Combining Macro Variables with Text

The generated program is identical to the program in the
previous example.

PROC CHART DATA=PERM.Y2000JAN;

HBAR WEEK / SUMVAR=SALE;
RUN;
PROC PLOT DATA=PERM.Y2000JAN;
PLOT SALE*DAY;
RUN;

81
 2.5 Delimiting Macro Variable Names 2-27

Combining Macro Variables with Text

You can place text immediately after a macro variable
reference if it does not change the reference.
Example: Modify the previous program to
substitute the name of an analysis
variable.
%let year=2000;
%let month=jan;
%let var=sale;
proc chart data=perm.y&year&month;
hbar week / sumvar=&var;
run;
proc plot data=perm.y&year&month;
plot &var*day;
run;

82

Combining Macro Variables with Text

The generated program is identical to the program in the
previous example.

PROC CHART DATA=PERM.Y2000JAN;

HBAR WEEK / SUMVAR=SALE;
RUN;
PROC PLOT DATA=PERM.Y2000JAN;
PLOT SALE*DAY;
RUN;

83

Combining Macro Variables with Text

Example: Modify the previous program to allow a Base
SAS or SAS/GRAPH procedure.

/* GRAPHICS should be null or G */

%let graphics=g;
%let year=2000;
%let month=jan;
%let var=sale;
proc &graphicschart data=perm.y&year&month;
hbar week / sumvar=&var;
run;
proc &graphicsplot data=perm.y&year&month;
What plot
is wrong&var*day;
with this program?
run;

84
2-28 Module 2 Creating and Resolving Macro Variables Prior to Compilation

Quick Quiz
Given the following code, which macro variable reference
gets passed to the macro processor?
„ &graphics

„ &graphicschart
/* GRAPHICS should be null or G */
%let graphics=g;
%let year=2000;
%let month=jan;
%let var=sale;
proc &graphicschart data=perm.y&year&month;
hbar week / sumvar=&var;
run;
proc &graphicsplot data=perm.y&year&month;
plot &var*day;
run;
85
Submit your answer as a text question.

Combining Macro Variables with Text

SAS interprets the macro variable’s name as
GRAPHICSCHART because no delimiter separates the
macro variable reference from the trailing text.
Partial Log
1 %let graphics=g;
2 %let year=2000;
3 %let month=jan;
4 %let var=sale;
5 proc &graphicschart data=perm.y&year&month;
-
10
WARNING: Apparent symbolic reference GRAPHICSCHART not resolved.

ERROR 10-205: Expecting the name of the procedure to be executed.

87

Macro Variable Name Delimiter

The word scanner recognizes the end of a macro variable
reference when it encounters a character that cannot be
part of the reference.
A period (.) is a special delimiter that ends a macro
variable reference and does not appear as text when the
macro variable is resolved.

88
 2.5 Delimiting Macro Variable Names 2-29

Macro Variable Name Delimiter

Example: Correct the problem from the previous
example.

%let graphics=g;
%let year=2000;
%let month=jan;
%let var=sale;
proc &graphics.chart data=perm.y&year&month;
hbar week / sumvar=&var;
run;
proc &graphics.plot data=perm.y&year&month;
plot &var*day;
run;

89

Quick Quiz
What is the token that gets created when &graphics.chart
gets resolved?

%let graphics=g;
%let year=90;
%let month=jan;
%let var=sale;
proc &graphics.chart data=perm.y&year&month;
hbar week / sumvar=&var;
run;
proc &graphics.plot data=perm.y&year&month;
plot &var*day;
run;

Submit your answer as a text question.

90

Macro Variable Name Delimiter

The generated code does not include the period.

PROC GCHART DATA=PERM.Y2000JAN;

HBAR WEEK / SUMVAR=SALE;
RUN;
PROC GPLOT DATA=PERM.Y2000JAN;
PLOT SALE*DAY;
RUN;

92
2-30 Module 2 Creating and Resolving Macro Variables Prior to Compilation

Macro Variable Name Delimiter

Example: Modify the previous example to include a
macro variable that defines a libref.
%let lib=perm;
%let graphics=g;
%let year=2000;
%let month=jan;
%let var=sale;
libname &lib ’SAS-data-library’;
proc &graphics.chart data=&lib.y&year&month;
hbar week / sumvar=&var;
run;
proc &graphics.plot data=&lib.y&year&month;
plot &var*day;
run;
What is the problem this time?

93

Macro Variable Name Delimiter

The program
%let lib=perm;
...
libname &lib 'SAS-data-library';
proc &graphics.chart data=&lib.y&year&month;
...
generates
LIBNAME PERM 'SAS-data-library';
PROC GCHART DATA=PERMY2000JAN;
HBAR WEEK / SUMVAR=SALE;
RUN;
PROC GPLOT DATA=PERMY2000JAN;
PLOT SALE*DAY;
RUN;

The period after &lib is interpreted as a delimiter.

95

Macro Variable Name Delimiter

Use another period after the delimiter period to supply the
needed token.

%let lib=perm;
...
libname &lib 'SAS-data-library';
proc &graphics.chart data=&lib..y&year&month;
...
proc &graphics.plot data=&lib..y&year&month;

96
 2.5 Delimiting Macro Variable Names 2-31

Macro Variable Name Delimiter

delimiter text

proc &graphics.chart data=&lib..y&year&month;

The first period is treated as a delimiter, the second as

text.
The compiler receives
...
PROC GCHART DATA=PERM.Y2000JAN;
...

97

Refer to Exercise 4 for Module 2 in Appendix A.

Module 2 Summary
„ Understand macro variables.
„ Describe where macro variables are stored.
„ Identify the two types of macro variables.
„ Identify selected automatic macro variables.
„ Display automatic macro variables in the SAS log.
„ Understand how macro variable references are
handled by the word scanner and macro processor.
„ Create user-defined macro variables.
„ Display values of user-defined macro variables in the
SAS log.
„ Place a macro variable reference adjacent to text or
another macro variable reference.

100
2-32 Module 2 Creating and Resolving Macro Variables Prior to Compilation

Session 1 Summary
„ Discussed the purpose and application of the macro
facility.
„ Described how a SAS program is tokenized, compiled,
and executed.
„ Used system and user-defined macro variables.
„ Displayed user-defined macro variables in the SAS
log.
„ Placed a macro variable reference adjacent to text or
another macro variable reference.

101
Module 3 Using Macro Functions

3.1 Basic Macro Functions ..................................................................................................3-2

3.2 Special Macro Functions ............................................................................................. 3-11

3-2 Module 3 Using Macro Functions

3.1 Basic Macro Functions

Objectives
Use macro functions to
„ manipulate character strings

„ perform arithmetic

„ execute SAS functions.

Macro Functions
Macro functions
„ have similar syntax as corresponding DATA step
character functions
„ yield similar results

„ manipulate macro variables and expressions

„ represent macro triggers

„ are executed by the macro processor.

8
 3.1 Basic Macro Functions 3-3

Macro Functions
Selected character string manipulation functions:
%UPCASE translates letters from lowercase to uppercase.
%SUBSTR extracts a substring from a character string.
%SCAN extracts a word from a character string.
%INDEX searches a character string for specified text.
%LENGTH returns the length of a character string or text
expression.

Other functions:
%SYSFUNC executes SAS functions.
%EVAL performs arithmetic and logical operations.
%BQUOTE protects blanks and other special characters.
9

Case Sensitivity
Character comparisons are case-sensitive.
Example: Create a summary of total fees outstanding for
each course.

%let paidval=n;
proc means data=perm.all sum maxdec=0;
where paid="&paidval";
var fee;
class course_title;
title "Courses with fee status=&paidval";
run;
upcase1

10

Quick Quiz
Partial Log
539 %let paidval=n;
540 proc means data=perm.all sum maxdec=0;
541 where paid="&paidval";
542 var fee;
543 class course_title;
544 title "Courses with fee status=&paidval";
545 run;

NOTE: No observations were selected from data set PERM.ALL.

Why do you get the following note in the log?

NOTE: No observations were selected from data set
perm.all.
Please press *6 to unmute your phone and give us the
answer verbally.

11
3-4 Module 3 Using Macro Functions

Case Sensitivity
Partial Log
539 %let paidval=n;
540 proc means data=perm.all sum maxdec=0;
541 where paid="&paidval";
542 var fee;
543 class course_title;
544 title "Courses with fee status=&paidval";
545 run;

NOTE: No observations were selected from data set PERM.ALL.

When making comparisons to a data set variable value,

remember SAS is case-sensitive. These two statements
are not equivalent:

where paid="N"; = where paid="n";

13

The %UPCASE Function

The %UPCASE function translates characters to
uppercase.
General form of the %UPCASE function:

%UPCASE(argument)
%UPCASE(argument)

argument can be any combination of text and macro

triggers.

14

The %UPCASE Function

Example: For each course, create a summary of total
fees outstanding and account for case.

%let paidval=n;
proc means data=perm.all sum maxdec=0;
where paid="%upcase(&paidval)";
var fee;
class course_title;
title "Courses with fee status=&paidval";
run;
upcase2

15
 3.1 Basic Macro Functions 3-5

The %UPCASE Function

Courses with fee status=n

The MEANS Procedure

Analysis Variable : Fee Course Fee

Description N Obs Sum

ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
Artificial Intelligence 24 9600

Basic Telecommunications 14 11130

Computer Aided Design 13 20800

Database Design 17 6375

Local Area Networks 19 12350

Structured Query Language 20 23000

ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ

16

The %SUBSTR Function

General form of the %SUBSTR function:

%SUBSTR(argument,
%SUBSTR(argument,position
position<,n>)
<,n>)

The %SUBSTR function

„ returns the portion of argument beginning at position
for a length of n characters
„ returns the portion of argument beginning at position
to the end of argument when an n value is not
supplied.

continued...

17
3-6 Module 3 Using Macro Functions

The %SUBSTR Function

General form of the %SUBSTR function:

%SUBSTR(argument,
%SUBSTR(argument,position
position<,n>)
<,n>)

You can specify argument, position, and n values using

„ constant text
„ macro variable references
„ macro functions
„ macro calls.

It is not necessary to place argument in quotes because it

is always handled as a character string by the %SUBSTR
function.

18

 The values of position and n can also be the result of an arithmetic expression that yields an
integer. For example,
%substr(&var,%length(&var)-1)
returns the last two characters of the value of the macro variable VAR.

The %SUBSTR Function

Example: Print courses with a BEGIN_DATE between
the current date and the first day of the current
month. Use the %SUBSTR function and
SYSDATE9 macro variable to construct the
appropriate dates.
proc print data=perm.schedule;
where begin_date between
"01%substr(&sysdate9,3)"d and
"&sysdate9"d;
title "All Courses Held So Far This Month";
title2 "(as of &sysdate9)";
run;
substr1

19
 3.1 Basic Macro Functions 3-7

Quick Quiz
Question: Use today’s date. What is the first SAS date
constant in the WHERE statement?
proc print data=perm.schedule;
where begin_date between
"01%substr(&sysdate9,3)"d and
"&sysdate9"d;
title "All Courses Held So Far This Month";
title2 "(as of &sysdate9)";
run;

Submit your answer as a text message.

20

The %SUBSTR Function

text macro triggers text

"01%substr(&sysdate9,3)"d

&sysdate9 resolves: %substr(30OCT2004,3)

%substr executes: OCT2004

final substitution: "01OCT2004"d

21

The %SUBSTR Function

22
3-8 Module 3 Using Macro Functions

The %SCAN Function

General form of the %SCAN function:

%SCAN(argument,
%SCAN(argument,nn <<,,delimiters>)
delimiters>)

The %SCAN function

„ returns the nth word of argument, where words are
strings of characters separated by delimiters
„ uses a default set of delimiters if none are specified

„ returns a null string if there are fewer than n words in

argument.

23

 Default delimiters for the %SCAN function include blank . ( & ! $ * ) ; - / , %

It is not necessary to place argument and delimiters in quotes because they are always handled as
character strings by the %SCAN function.

The %SCAN Function

General form of the %SCAN function:

%SCAN(argument,
%SCAN(argument,nn <<,,delimiters>)
delimiters>)

You can specify values for argument, n, and delimiters using

„ constant text

„ macro variable references

„ macro functions

„ macro calls.

The value of n can also be an arithmetic expression that

yields an integer.

24
 3.1 Basic Macro Functions 3-9

The %SCAN Function

Example: Use PROC DATASETS to investigate the
structure of the last data set created.

data work.current;
set perm.schedule;
where year(begin_date) =
year("&sysdate9"d);
run;

%let libref=%scan(&syslast,1);
%let dsname=%scan(&syslast,2,.);
proc datasets lib=&libref nolist;
title "Contents of Data Set &syslast";
contents data=&dsname;
run;
quit;
scan1
25

The %SCAN Function

%let libref=%scan(&syslast,1);

&syslast
resolves: %let libref=%scan(work.current,1);

%scan
executes: %let libref=work;

26
3-10 Module 3 Using Macro Functions

The %SCAN Function

Partial Output
Contents of Data Set WORK.CURRENT

The DATASETS Procedure

Data Set Name WORK.CURRENT Observations 0

Member Type DATA Variables 5
Engine V9 Indexes 0
Created Thu, Feb 05, 2004 02:04:21 PM Observation Length 56
Last Modified Thu, Feb 05, 2004 02:04:21 PM Deleted Observations 0
Protection Compressed NO
Data Set Type Sorted NO
Label
Data Representation WINDOWS_32
Encoding wlatin1 Western (Windows)

Engine/Host Dependent Information

Data Set Page Size 8192

Number of Data Set Pages 1
First Data Page 1
Max Obs per Page 145
Obs in First Data Page 0
Number of Data Set Repairs 0
File Name C:\temp\SAS Temporary
Files\_TD2140\CURRENT.sas7bdat
Release Created 9.0101M0
Host Created WIN_PRO
27

Refer to Exercise 1 for Module 3 in Appendix A.

 3.2 Special Macro Functions 3-11

3.2 Special Macro Functions

Need for Special Macro Functions

Consider the following programming code:
options symbolgen;
%let datastep=data test; x=1; run;
%put &datastep;

This code attempts to create a macro variable that

contains a DATA step as value for the macro variable.
However, there is a problem when this code executes.

31

What Is the Problem?

When the %LET statement executes, you get the
following:
1 options symbolgen; 1
2 %let datastep=data test; x=1; run;
2 %let datastep=data test; x=1; run;
-
180
ERROR 180-322: Statement is not valid or it is used out of
proper order.

SYMBOLGEN: Macro variable DATASTEP resolves to data test

3 %put &datastep;
data test

The first semicolon ends the %LET statement and

&datastep resolves to data test. Because there is
no semicolon after the DATA statement, the other
statements are not recognized as part of the DATA step.
32
3-12 Module 3 Using Macro Functions

How Do We Fix This Problem?

We want to mask the meaning of the semicolons within the
DATA step. If we can mask the normal meaning of the
semicolons, SAS will not interpret the semicolons as the
end of the %LET statement.

options symbolgen; 1 2 3
%let datastep=data test; x=1; run;
%put &datastep;

Mask
You can use macro quoting functions to remove the normal
syntactic meaning of tokens.

33

The %BQUOTE Function

The %BQUOTE function removes the normal meaning of
special tokens that appear as constant text.
Special tokens include: + - * / , < > = ;
LT EQ GT AND OR NOT LE GE NE

General form of the %BQUOTE function:

%BQUOTE(argument)
%BQUOTE(argument)

argument can be any combination of text and macro

triggers.

34

The %BQUOTE function is one of several macro quoting functions designed for specialized purposes.

The %BQUOTE Function

The %BQUOTE function
„ protects (quotes) tokens so that the macro processor
does not interpret them as macro-level syntax
„ enables macro triggers to work normally

„ preserves leading and trailing blanks in its argument.

35
 3.2 Special Macro Functions 3-13

Using the %BQUOTE Function

Solution 1: Mask the whole DATA step with the
%BQUOTE function.
%let datastep=%bquote(data test; x=1; run;);
&datastep;

Solution 2: Mask only the tokens that cause the problem.

%let datastep=data test%bquote(;) x=1%bquote(;) run%bquote(;);
&datastep;

Solution 3: Create a macro variable for the token that

causes the problem and reference it when necessary.
%let semicolon=%bquote(;);
%let datastep=data test&semicolon x=1&semicolon run&semicolon;
&datastep;

36
bquote1

Quick Quiz
Given the following code:

%let name=Valdez,Sanita;
%let lname=%scan(&name,1);

Will this code execute correctly using the default

delimiters for the %SCAN function, returning the value of
Valdez for the macro variable &lname?

Change your seat indicator to Yes or No.

37

Quick Quiz - Answer

Given the following code:

%let name=Valdez,Sanita;
%let lname=%scan(&name,1);

Will this code execute correctly using the default delimiter

for the %SCAN function, returning the value of Valdez
for the macro variable &lname? No

38
3-14 Module 3 Using Macro Functions

What Is the Problem This Time?

When &name resolves you get the following for the
%SCAN function.
1 2 3

%let lname=%scan(Valdez,Sanita,1);

The comma from the resolved value of &name is used as

an argument separator for the %SCAN function. The
second argument in the %SCAN function is the text
Sanita.

The %SCAN function expects the second argument to be

a number, therefore you get the following error message:
ERROR: Argument 2 to macro function %SCAN is not a
number.
39

The %BQUOTE Function

We use the %BQUOTE function to mask the meaning of
the comma in this example, or any other tokens in the first
argument that need masking.

%let name=Valdez,Sanita;
%let lname=%scan(%bquote(&name),1);
%put &lname;

1 2

40

The %BQUOTE Function

Partial SAS log

53 %let name=Valdez,Sanita;
54 %let lname=%scan(%bquote(&name),1);
55 %put &lname;
Valdez

41
 3.2 Special Macro Functions 3-15

The %EVAL Function

General form of the %EVAL function:

%EVAL(expression)
%EVAL(expression)

The %EVAL function

„ performs arithmetic and logical operations

„ truncates noninteger results

„ returns a character result

„ returns 1 (true) or 0 (false) for logical operations

„ returns a null value and issues an error message

when noninteger values are used in arithmetic
operations.

42

The %EVAL Function

Example: Use the %EVAL function to compute the final
year of a range.

%let firstyr=2004;
%let numyears=2;
%let finalyr=%eval(&firstyr+&numyears-1);
proc print data=perm.schedule;
where year(begin_date) between
&firstyr and &finalyr;
title "All Courses Scheduled";
title2 "&firstyr through &finalyr";
run;
eval1

44

The %EVAL Function

Example: Use the %EVAL function to compute the final
year of a range.
All Courses Scheduled
2004 through 2005

Course_ Course_ Begin_

Obs Number Code Location Date Teacher

1 1 C001 Seattle 26OCT2004 Hallis, Dr. George

2 2 C002 Dallas 07DEC2004 Wickam, Dr. Alice
3 3 C003 Boston 11JAN2005 Forest, Mr. Peter
4 4 C004 Seattle 25JAN2005 Tally, Ms. Julia
5 5 C005 Dallas 01MAR2005 Hallis, Dr. George
6 6 C006 Boston 05APR2005 Berthan, Ms. Judy
7 7 C001 Dallas 24MAY2005 Hallis, Dr. George
8 8 C002 Boston 14JUN2005 Wickam, Dr. Alice
9 9 C003 Seattle 19JUL2005 Forest, Mr. Peter
10 10 C004 Dallas 16AUG2005 Tally, Ms. Julia
11 11 C005 Boston 20SEP2005 Tally, Ms. Julia
12 12 C006 Seattle 04OCT2005 Berthan, Ms. Judy
13 13 C001 Boston 15NOV2005 Hallis, Dr. George
14 14 C002 Seattle 06DEC2005 Wickam, Dr. Alice
45
3-16 Module 3 Using Macro Functions

The %SYSFUNC Function

The %SYSFUNC macro function executes SAS functions.

General form of the %SYSFUNC function:

%SYSFUNC(SAS
%SYSFUNC(SASfunction(argument(s))
function(argument(s))<,format>)
<,format>)

„ SAS function(argument(s)) is the name of a SAS

function and its corresponding arguments.
„ The second argument is an optional format for the
value returned by the first argument.

46

The %SYSFUNC Function

The automatic macro variables SYSDATE9 and
SYSTIME can be used in titles:

title "Report Produced on &sysdate9";

title2 "at &systime";

generates
Report Produced on 11JUN2004
at 09:21

SYSDATE9 and SYSTIME represent the date and

time the SAS session started.

47

The %SYSFUNC Function

Example: Generate titles containing the current date and
time. Format the date and time with the
WEEKDATE. and TIME8. formats, respectively.

title "%sysfunc(today(),weekdate.)";
title2 "%sysfunc(time(),time8.)";

generates

Tuesday, August 24, 2004

13:06:08

48
 3.2 Special Macro Functions 3-17

The %SYSFUNC Function

Example: Compute the first year of a range based on
the current date using the TODAY function.

%let thisyr=%sysfunc(today(),year4.);
%let lastyr=%eval(&thisyr-1);
proc print data=perm.schedule;
where year(begin_date) between &lastyr and &thisyr;
title1 "Courses Scheduled &lastyr and &thisyr";
title2 "(as of &sysdate9)";
run;
sysfunc1

49

The %SYSFUNC Function

SAS Output
Courses Scheduled 2003 and 2004
(as of 02AUG2004)

Course_ Course_ Begin_

Obs Number Code Location Date Teacher

1 1 C001 Seattle 26OCT2004 Hallis, Dr. George

2 2 C002 Dallas 07DEC2004 Wickam, Dr. Alice

50
3-18 Module 3 Using Macro Functions

The %SYSFUNC Function

Most SAS functions can be used with the %SYSFUNC
function. Exceptions include:
„ Array processing (DIM, HBOUND, LBOUND)

„ Variable information (VNAME, VLABEL, MISSING)

„ Macro interface (RESOLVE, SYMGET)

„ Data conversion (INPUT, PUT)

„ Other functions (IORC, MSG, LAG, DIF).

 INPUTC and INPUTN can be used in place of INPUT.

PUTC and PUTN can be used in place of PUT.

51

 Variable Information functions include functions such as VNAME and VLABEL. For a complete
list, see “Functions and CALL Routines” in the SAS® Language Reference: Dictionary.
Because %SYSFUNC is a macro function, you do not need to enclose character values in
quotation marks as you do in DATA step functions. Use commas to separate all arguments in
DATA step functions within %SYSFUNC. You cannot use argument lists preceded by the word
OF.

Refer to Exercise 2 for Module 3 in Appendix A.

Module 3 Summary
„ Use macro functions to manipulate character strings.
„ Use macro functions to perform arithmetic.
„ Use macro functions to execute SAS functions.

54
Module 4 Defining and Executing
Macro Programs

4.1 Defining and Calling a Macro ........................................................................................4-2

4.2 Macro Parameters ........................................................................................................4-15

4-2 Module 4 Defining and Executing Macro Programs

4.1 Defining and Calling a Macro

Objectives
„ Define and call a simple macro.
„ Control macro storage.
„ Define and call macros with parameters.
„ Describe the difference between positional and
keyword parameters.

Defining a Macro
A macro or macro definition enables you to write macro
programs.
General form of a macro definition:

%MACRO
%MACRO macro-name;
macro-name;
macro-text
macro-text
%MEND
%MEND<macro-name>;
<macro-name>;

macro-name follows SAS naming conventions.

macro-text can include
„ any text

„ SAS statements or steps

„ macro variables, functions, statements, or calls

„ any combination of the above.

4
 4.1 Defining and Calling a Macro 4-3

Macro Compilation
When a macro definition is submitted,
„ macro language statements are

– checked for syntax errors

– compiled
„ SAS statements and other text are not

– checked for syntax errors

– compiled
„ the macro is stored as an entry in a SAS catalog, the
temporary catalog work.sasmacr by default.

 Do not name a macro with the name of a macro statement or function (LET or SCAN, for
example). Refer to the documentation for a complete list of reserved names.

Macro Compilation
The MCOMPILENOTE=ALL option issues a note to the
SAS log after a macro definition has compiled.

General form of the MCOMPILENOTE= option:

OPTIONS
OPTIONSMCOMPILENOTE=ALL
MCOMPILENOTE=ALL||NONE;
NONE;

The default setting is MCOMPILENOTE=NONE.

 The MCOMPILENOTE= option is new in SAS®9.

6
4-4 Module 4 Defining and Executing Macro Programs

Macro Compilation
Example: Submit a macro definition.

options mcompilenote=all;
%macro time;
%put The current time is %sysfunc
(time(),time11.2).;
%mend time;
macro1

Partial SAS Log

NOTE: The macro TIME completed compilation without errors.
3 instructions 76 bytes.

Macro Storage
Example: Produce a list of compiled macros stored in the
default temporary catalog work.sasmacr.

proc catalog cat=work.sasmacr;

contents;
title "My Temporary Macros";
quit;

PROC CATALOG Output

My Temporary Macros

Contents of Catalog WORK.SASMACR

# Name Type Create Date Modified Date Description

----------------------------------------------------------------
1 TIME MACRO 11JUN2004:15:55:59 11JUN2004:15:55:59

8
 4.1 Defining and Calling a Macro 4-5

Calling a Macro
A macro call
„ causes the macro to execute

„ is specified by placing a percent sign before the name

of the macro
„ can be made anywhere in a program (similar to a
macro variable reference)
„ represents a macro trigger

„ is not a statement (no semicolon required).

General form of a macro call:

%macro-name
%macro-name

 Placing a semicolon after a macro call may insert an inappropriate semicolon into the resulting
program, leading to errors during compilation or execution.

Calling a Macro
Example: Call the TIME macro.

%time

Message generated from the execution of %TIME.

The current time is 15:55:59.05.

10
4-6 Module 4 Defining and Executing Macro Programs

Student Activity
Example: Submit and call the TIME macro.

%time

sa-macro1
11

Program Flow
When the macro processor receives %macro-name, it
1. searches the designated SAS catalog
(WORK.SASMACR by default) for an entry named
macro-name.MACRO
2. executes compiled macro language statements
3. sends any remaining text to the input stack for word
scanning
4. pauses while the word scanner tokenizes the
inserted text and SAS code executes
5. resumes execution of macro language statements
after the SAS code executes.

13

Example
A macro can generate SAS code.

Example: Write a macro that generates a PROC PRINT

step. Reference macro variables within the macro.

%macro printdsn;
proc print data=&dsn;
var &vars;
run;
%mend;
macro2

This macro contains no macro language statements.

14
 4.1 Defining and Calling a Macro 4-7

Example
Example: Call the PRINTDSN macro. Precede the call
with %LET statements that populate macro
variables referenced within the macro.

%let dsn=perm.courses;
%let vars=days fee;
%printdsn

15

Program Flow
Example: Submit the %LET statements and call the
PRINTDSN macro.

Compiler Symbol Table

Word Scanner Macro Processor

Input Stack work.sasmacr

%let
%let dsn=perm.courses;
dsn=perm.courses; ## Name
Name Type
Type
%let
%let vars=days
vars=days fee;
fee; 11 PRINTDSN
PRINTDSN MACRO
MACRO
%printdsn
%printdsn 22 TIME
TIME MACRO
MACRO

17

Program Flow
The macro processor executes the %LET statements and
populates the symbol table.

Compiler Symbol Table

DSN
DSN perm.courses
perm.courses
VARS
VARS days
days fee
fee
Word Scanner Macro Processor

Input Stack work.sasmacr

%printdsn
%printdsn ## Name
Name Type
Type
11 PRINTDSN
PRINTDSN MACRO
MACRO
22 TIME
TIME MACRO
MACRO

18
4-8 Module 4 Defining and Executing Macro Programs

Program Flow
When the macro processor receives %PRINTDSN, it locates
PRINTDSN.MACRO within the work.sasmacr catalog.

Compiler Symbol Table

DSN
DSN perm.courses
perm.courses
VARS
VARS days
days fee
fee
Word Scanner Macro Processor
%printdsn
%printdsn
Input Stack work.sasmacr
## Name
Name Type
Type
11 PRINTDSN
PRINTDSN MACRO
MACRO
22 TIME
TIME MACRO
MACRO

19

Program Flow
The macro processor opens PRINTDSN.MACRO. There
are no macro language statements to execute.

Compiler Symbol Table

DSN
DSN perm.courses
perm.courses
VARS
VARS days
days fee
fee
Word Scanner Macro Processor

Input Stack PRINTDSN.MACRO

%macro
%macro printdsn;
printdsn;
proc
proc print
print data=&dsn;
data=&dsn;
var
var &vars;
&vars;
run;
run;
%mend;
%mend;
20

Program Flow
The macro processor places the macro text on the input
stack.

Compiler Symbol Table

DSN
DSN perm.courses
perm.courses
VARS
VARS days
days fee
fee
Word Scanner Macro Processor

Input Stack PRINTDSN.MACRO

proc
proc print
print data=&dsn;
data=&dsn; %macro
%macro printdsn;
printdsn;
var proc
proc print
print data=&dsn;
var &vars;
&vars; var
data=&dsn;
run;
run; var &vars;
&vars;
run;
run;
%mend;
%mend;
21
 4.1 Defining and Calling a Macro 4-9

Program Flow
Macro activity pauses while the word scanner tokenizes
text placed on the input stack by the macro processor.

Compiler Symbol Table

proc
proc print
print data=
data= DSN
DSN perm.courses
perm.courses
VARS
VARS days
days fee
fee
Word Scanner Macro Processor

Input Stack PRINTDSN.MACRO

&dsn;
&dsn; %macro
%macro printdsn;
printdsn;
var proc
proc print
print data=&dsn;
data=&dsn;
var &vars;
&vars; var
run;
run; var &vars;
&vars;
run;
run;
%mend;
%mend;
22

Program Flow
Macro variable references are passed to the macro
processor.

Compiler Symbol Table

proc
proc print
print data=
data= DSN
DSN perm.courses
perm.courses
VARS
VARS days
days fee
fee
Word Scanner Macro Processor
&dsn
&dsn
Input Stack PRINTDSN.MACRO
;; %macro
%macro printdsn;
printdsn;
var proc
proc print
print data=&dsn;
var &vars;
&vars; var
data=&dsn;
run; var &vars;
&vars;
run; run;
run;
%mend;
%mend;
23

Program Flow
Symbolic substitution is performed. Word scanning
continues.

Compiler Symbol Table

proc
proc print
print data=
data= DSN
DSN perm.courses
perm.courses
VARS
VARS days
days fee
fee
Word Scanner Macro Processor

Input Stack PRINTDSN.MACRO

perm.courses;
perm.courses; %macro
%macro printdsn;
printdsn;
var proc
proc print
print data=&dsn;
var &vars;
&vars; var
data=&dsn;
run; var &vars;
&vars;
run; run;
run;
%mend;
%mend;
24
4-10 Module 4 Defining and Executing Macro Programs

Program Flow
When a step boundary is encountered, SAS executes the
compiled step as macro activity remains paused. Macro
activity stops when the %MEND statement is encountered.

Compiler Symbol Table

proc
proc print
var
print data=perm.courses;
data=perm.courses; DSN
DSN perm.courses
perm.courses
var days
days fee;
fee;
VARS
VARS days
days fee
fee
Word Scanner Macro Processor
run;
run;
Input Stack PRINTDSN.MACRO
%macro
%macro printdsn;
printdsn;
proc
proc print
print data=&dsn;
data=&dsn;
var
var &vars;
&vars;
run;
run;
%mend;
%mend;
25

Macro Execution
The SAS log reflects that a PROC PRINT step executed.

Partial SAS Log

243 %let dsn=perm.courses;
244 %let vars=days fee;
245 %printdsn

NOTE: There were 6 observations read from the data set PERM.COURSES.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Why does PROC PRINT source code not appear in the

SAS log?

26

Macro Execution
The MPRINT option writes to the SAS log the text sent to
the SAS compiler as a result of macro execution.
General form of the MPRINT|NOMPRINT option:

OPTIONS
OPTIONSMPRINT;
MPRINT;
OPTIONS
OPTIONSNOMPRINT;
NOMPRINT;

The default setting is NOMPRINT.

27
 4.1 Defining and Calling a Macro 4-11

Macro Execution
Example: Set the MPRINT option before calling the
macro.

Partial SAS Log

267 options mprint;
268 %printdsn
MPRINT(PRINTDSN): proc print data=perm.courses;
MPRINT(PRINTDSN): var days fee;
MPRINT(PRINTDSN): run;

NOTE: There were 6 observations read from the data set PERM.COURSES.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

28

 Macro-generated code is treated as a series of tokens. The MPRINT option shows each statement
on a new line without indentation.

Macro Storage
Example: Produce a list of compiled macros stored in the
default temporary catalog work.sasmacr.

proc catalog cat=work.sasmacr;

contents;
title "My Temporary Macros";
quit;

PROC CATALOG Output

My Temporary Macros

Contents of Catalog WORK.SASMACR

# Name Type Create Date Modified Date Description

--------------------------------------------------------------------
1 PRINTDSN MACRO 15JUN2004:15:58:21 15JUN2004:15:58:21
2 TIME MACRO 15JUN2004:15:55:59 15JUN2004:15:55:59

29
4-12 Module 4 Defining and Executing Macro Programs

Macro Storage
Macros are stored in the work library, by default.

The MSTORED system option enables storage of compiled

macros in a permanent SAS library.

The SASMSTORE= system option designates a permanent

library to store compiled macros.

OPTIONS
OPTIONSMSTORED
MSTOREDSASMSTORE=libref
SASMSTORE=libref;;

libref points to an allocated SAS data library.

30

Macro Storage
General form of a macro definition for permanent macro
storage:

%MACRO
%MACRO macro-name
macro-name //STORE;
STORE;
macro-text
macro-text
%MEND
%MEND macro-name;
macro-name;

The STORE option stores the compiled macro in the library

indicated by the SASMSTORE= system option.

31

Macro Storage
Example: Store the PRINTDSN macro in a permanent
library.
libname perm '.';
options mstored sasmstore=perm;
%macro printdsn / store;
proc print data=&dsn;
var &vars;
run;
%mend printdsn;

Call the PRINTDSN macro in a new SAS session.

libname perm '.';
options mstored sasmstore=perm;
%let dsn=perm.courses;
%let vars=days fee;
%printdsn macro3
32
 4.1 Defining and Calling a Macro 4-13

Macro Storage
General form of a macro definition for permanent macro
storage and storage of the macro source code:

%MACRO
%MACRO macro-name
macro-name //STORE
STORE <SOURCE>;
<SOURCE>;
macro-text
macro-text
%MEND
%MEND macro-name;
macro-name;

The SOURCE option stores the macro source code along

with the compiled code.

 The SOURCE option is new in SAS®9. In earlier

releases, be sure to save your source code externally.

34

Macro Storage
Example: Store the PRINTDSN macro and the macro
source code in a permanent library.
libname perm '.';
options mstored sasmstore=perm;
%macro printdsn / store source;
proc print data=&dsn;
var &vars;
run;
%mend printdsn;

Call the PRINTDSN macro in a new SAS session.

libname perm '.';
options mstored sasmstore=perm;
%let dsn=perm.courses;
%let vars=days fee;
%printdsn macro4
35

Macro Storage
Use %COPY statement to store macro source code.

%COPY
%COPY macro-name
macro-name //SOURCE
SOURCE
<OUT='external
<OUT='externalfile'
file'>;
>;

If the OUT= option is omitted, the code is written to the

SAS log.

 The %COPY statement is new in SAS®9

36
4-14 Module 4 Defining and Executing Macro Programs

Macro Storage
Example: Copy the source code from the stored
PRINTDSN macro to the SAS log.

%copy printdsn / source;

Partial SAS Log

265 %copy printdsn / source;
%macro printdsn / store source;
proc print data=&dsn;
var &vars;
run;
%mend;

37

Refer to Exercise 1 for Module 4 in Appendix A.

 4.2 Macro Parameters 4-15

4.2 Macro Parameters

Introduction
Example: Note macro variable references within the
PRINTDSN macro.

%macro printdsn;
proc print data=&dsn;
var &vars;
run;
%mend;

41

Introduction
Example: Call the macro twice, each time substituting
different values of the macro variables DSN
and VARS.

%let dsn=perm.courses;
%let vars=days fee;
%printdsn

%let dsn=perm.schedule;
%let vars=location teacher;
%printdsn

The user must submit three lines per macro call. How
can this be simplified?

42
4-16 Module 4 Defining and Executing Macro Programs

Macro Parameters
Macros can be defined with a parameter list of macro
variables referenced within the macro.

%macro printdsn(dsn,vars);
proc print data=&dsn;
var &vars;
run;
%mend;

43

Macro Parameters
Example: Call the PRINTDSN macro and provide
parameter values.

%macro printdsn(dsn,vars);
proc print data=&dsn;
var &vars;
run;
%mend;

%printdsn(perm.courses,days fee)

44

Macro Parameters
General form of a macro definition with a parameter list:

%MACRO
%MACRO macro-name(parameter-1,
macro-name(parameter-1, …
… parameter-n);
parameter-n);
macro
macro text
text
%MEND;
%MEND;

Parameter names are

„ parenthesized

„ comma-delimited.

45
 4.2 Macro Parameters 4-17

Macro Parameters
General form of a macro call with parameters:

%macro-name(value-1,
%macro-name(value-1, …
…value-n)
value-n)

Parameter values are

„ parenthesized

„ comma-delimited.

Parameter values can be any text, null values, macro

variable references, or macro calls.

46

 To assign a null value to one or more positional parameters, use commas as placeholders for the
omitted values.

Local Symbol Tables

When a macro with a parameter list is called, the
parameters are created in a separate symbol table called
a local table.
The macro call
%printdsn(perm.courses, days fee)

initializes a local table:

Local Table Global Table

DSN perm.courses SYSDAY Tuesday
VARS days fee SYSLAST _NULL_
CITY Dallas
AMOUNT 975
47
4-18 Module 4 Defining and Executing Macro Programs

Local Symbol Tables

A local symbol table is
„ created when a macro with a parameter list is called

„ deleted when the macro finishes execution.

Macro variables in the local table are available only during

macro execution and can be referenced only within the
macro.

48

Quick Quiz
Does a %LET statement outside of a macro program
create a macro variable in the global or local symbol
table?

Answer with your seat indicator:

Yes = Global
No = Local

49

Positional Parameters
Positional parameters use a one-to-one correspondence
between
„ parameter names supplied on the macro definition

„ parameter values supplied on the macro call.

%macro printdsn(dsn,vars);
proc print data=&dsn;
var &vars;
run;
%mend;

%printdsn(perm.courses,days fee)

51
 4.2 Macro Parameters 4-19

Positional Parameters
%macro attend(opts, start, stop);
%let start=%upcase(&start);
%let stop=%upcase(&stop);
proc freq data=perm.all;
where begin_date between "&start"d and "&stop"d;
table location / &opts;
title1 "Enrollment from &start to &stop";
run;
%mend;
options mprint;
%attend(nocum,01jan2005,31dec2005)
%attend(,01oct2005,31dec2005)

param1

52
4-20 Module 4 Defining and Executing Macro Programs

Macros with Positional Parameters

param1
Define a macro that creates reports showing enrollment for individual training centers. Use positional
parameters to specify a range of dates and options for the TABLES statement in the FREQ procedure.
%macro attend(opts, start, stop);
%let start=%upcase(&start);
%let stop=%upcase(&stop);
proc freq data=perm.all;
where begin_date between "&start"d and "&stop"d;
table location / &opts;
title1 "Enrollment from &start to &stop";
run;
%mend;

options mprint;
%attend(nocum,01jan2005,31dec2005)
%attend(,01oct2005,31dec2005)

 A null value is passed for OPTS in the second call.

Partial SAS Log for %attend(nocum,01jan2005,31dec2005)
MPRINT(ATTEND): proc freq data=perm.all;
MPRINT(ATTEND): where begin_date between "01JAN2005"d and
"31DEC2005"d;
MPRINT(ATTEND): table location / nocum;
MPRINT(ATTEND): title1 "Enrollment from 01JAN2005 to 31DEC2005";
MPRINT(ATTEND): run;
NOTE: There were 299 observations read from the dataset PERM.ALL.
WHERE ((begin_date>='01JAN2005'D and begin_date<='31DEC2005'D));
NOTE: PROCEDURE FREQ used:
real time 28.40 seconds
cpu time 0.36 seconds

Partial SAS Log for %attend(,01oct2005,31dec2005)

MPRINT(ATTEND): proc freq data=perm.all;
MPRINT(ATTEND): where begin_date between "01OCT2005"d and "31DEC2005"d;
MPRINT(ATTEND): table location / ;
MPRINT(ATTEND): title1 "Enrollment from 01OCT2005 to 31DEC2005";
MPRINT(ATTEND): run;
NOTE: There were 81 observations read from the dataset PERM.ALL.
WHERE ((begin_date>='01OCT2005'D and begin_date<='31DEC2005'D));
NOTE: PROCEDURE FREQ used:
real time 0.10 seconds
cpu time 0.10 seconds
 4.2 Macro Parameters 4-21

Keyword Parameters
General form of a macro call with keyword parameters:

%macro-name(keyword=value,
%macro-name(keyword=value,…,
…,keyword=value)
keyword=value)

keyword=value combinations can be

„ specified in any order

„ omitted from the call without placeholders.

If omitted from the call, a keyword parameter receives

its default value. To omit every keyword parameter from
a macro call, specify %macro-name(). Specifying
%macro-name without the parentheses may not
immediately execute the macro.
55

Keyword Parameters
Example: Assign default parameter values by defining the
macro with keyword parameters.
%macro attend(opts=,start=01jan05,stop=31dec05);
%let start=%upcase(&start);
%let stop=%upcase(&stop);
proc freq data=perm.all;
where begin_date between
"&start"d and "&stop"d;
table location / &opts;
title1 "Enrollment from &start to &stop";
run;
%mend;
options mprint;
%attend(opts=nocum)
%attend(stop=30jun05,opts=nocum nopercent)
%attend()
param2
56
4-22 Module 4 Defining and Executing Macro Programs

Macros with Keyword Parameters

param2
Alter the previous macro by using keyword parameters. Issue various calls to the macro.
%macro attend(opts=,start=01jan2005,stop=31dec2005);
%let start=%upcase(&start);
%let stop=%upcase(&stop);
proc freq data=perm.all;
where begin_date between "&start"d and "&stop"d;
table location / &opts;
title1 "Enrollment from &start to &stop";
run;
%mend;

options mprint;
%attend(opts=nocum)
%attend(stop=30jun2005,opts=nocum nopercent)
%attend()
What are the values of the omitted parameters in each call?
Partial SAS Log for %attend(opts=nocum)
MPRINT(ATTEND): proc freq data=perm.all;
MPRINT(ATTEND): where begin_date between "01JAN2005"d and "31DEC2005"d;
MPRINT(ATTEND): table location / nocum;
MPRINT(ATTEND): title1 "Enrollment from 01JAN2005 to 31DEC2005";
NOTE: There were 299 observations read from the dataset PERM.ALL.
WHERE ((begin_date>='01JAN2005'D and begin_date<='31DEC2005'D));
NOTE: PROCEDURE FREQ used:
real time 0.12 seconds
cpu time 0.10 seconds

Partial SAS Log for %attend(stop=30jun2005,opts=nocum nopercent)

MPRINT(ATTEND): proc freq data=perm.all;
MPRINT(ATTEND): where begin_date between "01JAN2005"d and "30JUN2005"d;
MPRINT(ATTEND): table location / nocum nopercent;
MPRINT(ATTEND): title1 "Enrollment from 01JAN2005 to 30JUN2005";
MPRINT(ATTEND): run;
NOTE: There were 137 observations read from the dataset PERM.ALL.
WHERE ((begin_date>='01JAN2005'D and begin_date<='30JUN2005'D));
NOTE: PROCEDURE FREQ used:
real time 0.11 seconds
cpu time 0.09 seconds
 4.2 Macro Parameters 4-23

Partial SAS Log for %attend()

MPRINT(ATTEND): proc freq data=perm.all;
MPRINT(ATTEND): where begin_date between "01JAN2005"d and "31DEC2005"d;
MPRINT(ATTEND): table location / ;
MPRINT(ATTEND): title1 "Enrollment from 01JAN2005 to 31DEC2005";
MPRINT(ATTEND): run;
NOTE: There were 299 observations read from the dataset PERM.ALL.
WHERE ((begin_date>='01JAN2005'D and begin_date<='31DEC2005'D));
NOTE: PROCEDURE FREQ used:
real time 0.09 seconds
cpu time 0.09 seconds
4-24 Module 4 Defining and Executing Macro Programs

Mixed Parameter Lists

You can use a combination of positional and keyword
parameters. In a mixed parameter list, positional
parameters must be listed before keyword parameters on
both the macro definition and the macro call.

62

Mixed Parameter Lists

Example: Use a combination of positional and keyword
parameters.
%macro attend(opts,start=01jan05,stop=31dec05);
%let start=%upcase(&start);
%let stop=%upcase(&stop);
proc freq data=perm.all;
where begin_date between
"&start"d and "&stop"d;
table location / &opts;
title1 "Enrollment from &start to &stop";
run;
%mend;
options mprint;
%attend(nocum)
%attend(stop=30jun05,start=01apr05)
%attend(nocum nopercent,stop=30jun05)
%attend() param3
63
 4.2 Macro Parameters 4-25

Macros with Mixed Parameter Lists

param3
Alter the previous macro by using a mixed parameter list. Issue various calls to the macro.
82 %macro attend(opts,start=01jan05,stop=31dec05);
83 %let start=%upcase(&start);
84 %let stop=%upcase(&stop);
85 proc freq data=perm.all;
86 where begin_date between
87 "&start"d and "&stop"d;
88 table location / &opts;
89 title1 "Enrollment from &start to &stop";
90 run;
91 %mend;
92 options mprint;
93 %attend(nocum)
MPRINT(ATTEND): proc freq data=perm.all;
MPRINT(ATTEND): where begin_date between "01JAN05"d and "31DEC05"d;
MPRINT(ATTEND): table location / nocum;
MPRINT(ATTEND): title1 "Enrollment from 01JAN05 to 31DEC05";
MPRINT(ATTEND): run;

NOTE: There were 299 observations read from the data set PERM.ALL.
WHERE (begin_date>='01JAN2005'D and begin_date<='31DEC2005'D);

94 %attend(stop=30jun05,start=01apr05)
MPRINT(ATTEND): proc freq data=perm.all;
MPRINT(ATTEND): where begin_date between "01APR05"d and "30JUN05"d;
MPRINT(ATTEND): table location / ;
MPRINT(ATTEND): title1 "Enrollment from 01APR05 to 30JUN05";
MPRINT(ATTEND): run;

NOTE: There were 65 observations read from the data set PERM.ALL.
WHERE (begin_date>='01APR2005'D and begin_date<='30JUN2005'D);

95 %attend(nocum nopercent,stop=30jun05)
MPRINT(ATTEND): proc freq data=perm.all;
MPRINT(ATTEND): where begin_date between "01JAN05"d and "30JUN05"d;
MPRINT(ATTEND): table location / nocum nopercent;
MPRINT(ATTEND): title1 "Enrollment from 01JAN05 to 30JUN05";
MPRINT(ATTEND): run;

NOTE: There were 137 observations read from the data set PERM.ALL.
WHERE (begin_date>='01JAN2005'D and begin_date<='30JUN2005'D);
96 %attend()
MPRINT(ATTEND): proc freq data=perm.all;
MPRINT(ATTEND): where begin_date between "01JAN05"d and "31DEC05"d;
MPRINT(ATTEND): table location / ;
MPRINT(ATTEND): title1 "Enrollment from 01JAN05 to 31DEC05";
MPRINT(ATTEND): run;

NOTE: There were 299 observations read from the data set PERM.ALL.
WHERE (begin_date>='01JAN2005'D and begin_date<='31DEC2005'D);
4-26 Module 4 Defining and Executing Macro Programs

Developing Macro-Based Applications

If a macro-based application generates SAS code, use a
four-step approach.

1. Write and debug the SAS program without any macro

coding.
2. Generalize the program by replacing hardcoded
constants with macro variable references and initialize
the macro variables with %LET statements.
3. Create a macro definition by placing %MACRO and
%MEND statements around your program. Convert
%LET statements to macro parameters as appropriate.

Step 4 is presented later.

66

 These steps permit rapid development and debugging because they isolate syntax and logic at the
SAS code level from the syntax and logic at the macro level.

Refer to Exercise 2 for Module 4 in Appendix A.

Module 4 Summary
„ Define and call a simple macro.
„ Control macro storage.
„ Define and call macros with parameters.
„ Describe the difference between positional and
keyword parameters.

69
 4.2 Macro Parameters 4-27

Session 2 Summary
„ Used macro functions to manipulate macro variables
and expressions.
„ Defined and called simple, positional, and keyword
macro definitions.
„ Stored macro definition and source code permanently.

70
Module 5 Creating and Resolving
Macro Variables During Execution

5.1 Creating Macro Variables in the DATA Step .................................................................5-2

5.2 Indirect References to Macro Variables .....................................................................5-23

5.3 Creating Macro Variables in SQL ................................................................................5-37

5-2 Module 5 Creating and Resolving Macro Variables During Execution

5.1 Creating Macro Variables in the DATA Step

Objectives
„ Create macro variables during DATA step execution.
„ Describe the difference between the SYMPUT routine
and the %LET statement.
„ Reference macro variables indirectly.
„ Create a series of macro variables using the SYMPUT
routine.
„ Create macro variables during PROC SQL execution.
„ Store several values in one macro variable using the
SQL procedure.

10

The DATA Step Interface

Example: Automate production of the report below, with an
appropriate footnote.
Paid Status for Course 3

Obs Student_Name Student_Company Paid

1 Bills, Ms. Paulette Reston Railway Y

2 Chevarley, Ms. Arlene Motor Communications N
3 Clough, Ms. Patti Reston Railway N
4 Crace, Mr. Ron Von Crump Seafood Y
5 Davis, Mr. Bruce Semi;Conductor Y
6 Elsins, Ms. Marisa F. SSS Inc. N
7 Gandy, Dr. David Paralegal Assoc. Y
8 Gash, Ms. Hedy QA Information Systems Center Y
9 Haubold, Ms. Ann Reston Railway Y
10 Hudock, Ms. Cathy So. Cal. Medical Center Y
11 Kimble, Mr. John Alforone Chemical N
12 Kochen, Mr. Dennis Reston Railway Y
13 Larocque, Mr. Bret Physicians IPA Y
14 Licht, Mr. Bryan SII Y
15 McKnight, Ms. Maureen E. Federated Bank Y
16 Scannell, Ms. Robin Amberly Corp. N
17 Seitz, Mr. Adam Lomax Services Y
18 Smith, Ms. Jan Reston Railway N
19 Sulzbach, Mr. Bill Sailbest Ships Y
20 Williams, Mr. Gene Snowing Petroleum Y

Some Fees Due

11

Many applications require macro variables to have values based on data values, programming logic, or
expressions.
 5.1 Creating Macro Variables in the DATA Step 5-3

The DATA Step Interface

%let crsnum=3;
data revenue;
set perm.all end=final;
where course_number=&crsnum;
total+1;
if paid='Y' then paidup+1;
if final then do;
put total= paidup=;
if paidup<total then do;
%let foot=Some Fees Due;
end;
else do;
%let foot=All Students Paid;
end;
end;
run;
proc print data=revenue;
var student_name student_company paid;
title "Paid Status for Course &crsnum";
footnote "&foot";
run; symput1
12

The DATA Step Interface

Why is the footnote incorrect?
Paid Status for Course 3

Obs Student_Name Student_Company Paid

1 Bills, Ms. Paulette Reston Railway Y

2 Chevarley, Ms. Arlene Motor Communications N
3 Clough, Ms. Patti Reston Railway N
4 Crace, Mr. Ron Von Crump Seafood Y
5 Davis, Mr. Bruce Semi;Conductor Y
6 Elsins, Ms. Marisa F. SSS Inc. N
7 Gandy, Dr. David Paralegal Assoc. Y
8 Gash, Ms. Hedy QA Information Systems Center Y
9 Haubold, Ms. Ann Reston Railway Y
10 Hudock, Ms. Cathy So. Cal. Medical Center Y
11 Kimble, Mr. John Alforone Chemical N
12 Kochen, Mr. Dennis Reston Railway Y
13 Larocque, Mr. Bret Physicians IPA Y
14 Licht, Mr. Bryan SII Y
15 McKnight, Ms. Maureen E. Federated Bank Y
16 Scannell, Ms. Robin Amberly Corp. N
17 Seitz, Mr. Adam Lomax Services Y
18 Smith, Ms. Jan Reston Railway N
19 Sulzbach, Mr. Bill Sailbest Ships Y
20 Williams, Mr. Gene Snowing Petroleum Y

All Students Paid

13

The DATA Step Interface

Word scanning begins. Macro trigger encountered.

%let crsnum=3; Symbol Table

Symbol Table
crsnum 3
data revenue;
set perm.all end=final;
where course_number=&crsnum;
total+1;
if paid='Y' then paidup+1;
if final then do;
put total= paidup=;
if paidup<total then do;
%let foot=Some Fees Due;
end;
else do;
%let foot=All Students Paid;
end;
end;
run;
15
5-4 Module 5 Creating and Resolving Macro Variables During Execution

The DATA Step Interface

Compiling begins. The macro variable reference is resolved.

Symbol Table
Symbol Table
data revenue; crsnum 3
set perm.all end=final;
where course_number=3;
total+1;
if paid='Y' then paidup+1;
if final then do;
put total= paidup=;
if paidup<total then do;
%let foot=Some Fees Due;
end;
else do;
%let foot=All Students Paid;
end;
end;
run;
16

Quick Quiz
Can a macro variable have more than one value assigned
to it at one time?

Example:
Symbol Table
%let crsnum=3;
crsnum 3
%let crsnum=8;
crsnum 8

Please answer using your seat indicator.

17

Using %LET Statements to Assign Macro

Variable Values (Review)
Remember, a macro variable cannot have more than one
value assigned to it at one time.

Example:
%let crsnum=3; Symbol Table
%let crsnum=8; crsnum 3

18
 5.1 Creating Macro Variables in the DATA Step 5-5

Updating a Macro Variable Value (Review)

If you have multiple %LET statements for the same macro
variable, the value in the symbol table is overwritten with
the updated macro variable value.

Example: Symbol Table

%let crsnum=3;
crsnum 8
%let crsnum=8;

19

The DATA Step Interface

The macro trigger is passed to the macro processor. The
%LET statement compiles and executes when passed to the
macro processor.
data revenue; Symbol Table
set perm.all end=final;
where course_number=3; crsnum 3
total+1; foot Some Fees Due
if paid='Y' then paidup+1;
if final then do;
put total= paidup=;
if paidup<total then do;
%let foot=Some Fees Due;
end;
else do;
%let foot=All Students Paid;
end;
end;
run;
20
5-6 Module 5 Creating and Resolving Macro Variables During Execution

The DATA Step Interface

When the next macro trigger is encountered, it overwrites
the previous value.
data revenue; Symbol Table
set perm.all end=final;
where course_number=3; crsnum 3
total+1; foot All Students Paid
if paid='Y' then paidup+1;
if final then do;
put total= paidup=;
if paidup<total then do;

end;
else do;
%let foot=All Students Paid;
end;
end;
run;

21

 %LET statements execute at word scanning time, while SAS statements other than macro
statements are sent to the compiler.

The DATA Step Interface

Compile phase complete. Ready for execution.

data revenue; Symbol Table

set perm.all end=final;
where course_number=3; crsnum 3
total+1; foot All Students Paid
if paid='Y' then paidup+1;
if final then do;
put total= paidup=;
if paidup<total then do;

end;
else do; Nothing in this DATA step
affects the value of FOOT.
end;
It remains
end;
run; All Students Paid.

22
 5.1 Creating Macro Variables in the DATA Step 5-7

The SYMPUT Routine

The SYMPUT routine
„ is an executable DATA step statement

„ assigns to a macro variable any value available to the

DATA step during execution time
„ can create macro variables with

– static values
– dynamic (data dependent) values
– dynamic (data dependent) names.
Symbol Table

DATA step variables

SYMPUT
DATA step expressions
character literals
23

The SYMPUT Routine

The SYMPUT routine creates a macro variable and
assigns it a value.

General form of the SYMPUT routine:

CALL
CALL SYMPUT(macro-variable,
SYMPUT(macro-variable, text);
text);

macro-variable is assigned the character value of text.

If macro-variable already exists, its value is replaced.

If either argument represents a literal value, it must be

quoted.

24

The SYMPUT Routine

Example: The SYMPUT routine can be controlled with
DATA step execution time logic.
%let crsnum=3;
data revenue;
set perm.all end=final; No macro
where course_number=&crsnum; triggers within
total+1; DO groups
if paid='Y' then paidup+1;
if final then do;
put total= paidup=;
if paidup<total then do;
call symput('foot','Some Fees Due');
end;
else do;
call symput('foot','All Students Paid');
end;
end; symput2
run;
25
5-8 Module 5 Creating and Resolving Macro Variables During Execution

The SYMPUT Routine

Example: The SYMPUT routine can be controlled with
DATA step execution time logic.
%let crsnum=3;
data revenue;
set perm.all end=final;
where course_number=&crsnum;
total+1;
Fixed
if paid='Y' Macro
then paidup+1;
if final Variable
then do;Name
put total= paidup=;
if paidup<total then do;
call symput('foot','Some Fees Due');
end;
else do;
call symput('foot','All Students Paid');
end;
end; Fixed Macro
run; Variable Value
26
 5.1 Creating Macro Variables in the DATA Step 5-9

The SYMPUT Routine

symput2
Conditionally assign a text value to a macro variable FOOT based on DATA step values. Reference this
macro variable later in the program.
options symbolgen;
%let crsnum=3;
data revenue;
set perm.all end=final;
where course_number=&crsnum;
total+1;
if paid=‘Y’ then paidup+1;
if final then do;
if paidup<total then do;
call symput('foot','Some Fees Due');
end;
else do;
call symput('foot','All Students Paid');
end;
end;
run;

proc print data=revenue;

var student_name student_company paid;
title "Paid Status for Course &crsnum";
footnote "&foot";
run;
The value assigned to the macro variable FOOT is set dynamically to either Some Fees Due or
All Students Paid, based on DATA step execution time logic.
5-10 Module 5 Creating and Resolving Macro Variables During Execution

SAS Output
Paid Status for Course 3

Obs Student_Name Student_Company Paid

1 Bills, Ms. Paulette Reston Railway Y

2 Chevarley, Ms. Arlene Motor Communications N
3 Clough, Ms. Patti Reston Railway N
4 Crace, Mr. Ron Von Crump Seafood Y
5 Davis, Mr. Bruce Semi;Conductor Y
6 Elsins, Ms. Marisa F. SSS Inc. N
7 Gandy, Dr. David Paralegal Assoc. Y
8 Gash, Ms. Hedy QA Information Systems Center Y
9 Haubold, Ms. Ann Reston Railway Y
10 Hudock, Ms. Cathy So. Cal. Medical Center Y
11 Kimble, Mr. John Alforone Chemical N
12 Kochen, Mr. Dennis Reston Railway Y
13 Larocque, Mr. Bret Physicians IPA Y
14 Licht, Mr. Bryan SII Y
15 McKnight, Ms. Maureen E. Federated Bank Y
16 Scannell, Ms. Robin Amberly Corp. N
17 Seitz, Mr. Adam Lomax Services Y
18 Smith, Ms. Jan Reston Railway N
19 Sulzbach, Mr. Bill Sailbest Ships Y
20 Williams, Mr. Gene Snowing Petroleum Y

Some Fees Due

Quick Quiz
What is the value of &foot after execution of this DATA
step?

data _null_;
call symput('foot','Some Fees Due');
%let foot=All Students Paid;
run;

Submit your answer as a text message to the moderator.

29
 5.1 Creating Macro Variables in the DATA Step 5-11

Program Flow
The statements are tokenized.

Compiler
Macro Processor

Word data
data
Scanner _null_
_null_
;;

call
call symput('foot','Some
symput('foot','Some Fees
Fees Due');
Due');
Input %let
%let foot=All
foot=All Students
Students Paid;
Paid;
run;
run;
Stack
30

Program Flow
The %LET statement is submitted.
data
data _null_;
_null_;
Compiler call
call symput('foot','Some
symput('foot','Some Fees
Fees Due');
Due');

%%
let
let Macro Processor
foot
foot
==
Word All
All
Students
Scanner Students
Paid
Paid
;;

run;
run;
Input
31
Stack

Program Flow
When a macro trigger is encountered, it is passed to the
macro processor for evaluation.
data
data _null_;
_null_;
Compiler call
call symput('foot','Some
symput('foot','Some Fees
Fees Due');
Due');

Macro Processor
foot
foot
==
%let
%let
Word All
All
Students
Scanner Students
Paid
Paid
;;

Input run;
run;
32
Stack
5-12 Module 5 Creating and Resolving Macro Variables During Execution

Program Flow
The macro processor requests tokens until a semicolon
is encountered, then executes the macro statement.
data
data _null_;
_null_;
Compiler call
call symput('foot','Some
symput('foot','Some Fees
Fees Due');
Due');

Macro Processor

%let
%let foot=All
foot=All Students
Students Paid;
Paid;
Word
Scanner
Symbol Table
foot All Students Paid
Input run;
run;
33
Stack

Program Flow
Tokenization resumes.

data
data _null_;
_null_;
Compiler call
call symput('foot','Some
symput('foot','Some Fees
Fees Due');
Due');

Macro Processor

run;
Word run;
Scanner
Symbol Table
foot All Students Paid
Input
34
Stack

Program Flow
The compiler receives a step boundary and executes the
DATA step. The SYMPUT routine updates the value
of &FOOT directly in the symbol table at execution.
data
data _null_;
_null_;
Execute call
call symput('foot','Some
symput('foot','Some Fees
Fees Due');
Due');
run;
run;

Macro Processor

Word
Scanner
Symbol Table
foot Some Fees Due
Input
35
Stack
 5.1 Creating Macro Variables in the DATA Step 5-13

The SYMPUT Routine

Example: Enhance the title and footnote as below.
Fee Status for Local Area Networks (#3)

Student_Name Student_Company Paid

Bills, Ms. Paulette Reston Railway Y

Chevarley, Ms. Arlene Motor Communications N
Clough, Ms. Patti Reston Railway N
Crace, Mr. Ron Von Crump Seafood Y
Davis, Mr. Bruce Semi;Conductor Y
Elsins, Ms. Marisa F. SSS Inc. N
Gandy, Dr. David Paralegal Assoc. Y
Gash, Ms. Hedy QA Information Systems Center Y
Haubold, Ms. Ann Reston Railway Y
Hudock, Ms. Cathy So. Cal. Medical Center Y
Kimble, Mr. John Alforone Chemical N
Kochen, Mr. Dennis Reston Railway Y
Larocque, Mr. Bret Physicians IPA Y
Licht, Mr. Bryan SII Y
McKnight, Ms. Maureen E. Federated Bank Y
Scannell, Ms. Robin Amberly Corp. N
Seitz, Mr. Adam Lomax Services Y
Smith, Ms. Jan Reston Railway N
Sulzbach, Mr. Bill Sailbest Ships Y
Williams, Mr. Gene Snowing Petroleum Y

Note: 14 out of 20 paid

36

Partial Contents of PERM.ALL

37

The SYMPUT Routine

You can copy the current value of a DATA step variable
into a macro variable by using the name of a DATA step
variable as the second argument to the SYMPUT routine.

CALL
CALLSYMPUT('macro-variable',
SYMPUT('macro-variable', DATA-step-variable);
DATA-step-variable);

„ A maximum of 32,767 characters can be assigned to

the receiving macro variable.
„ Any leading or trailing blanks within the DATA step
variable’s value are stored in the macro variable.
„ Values of numeric variables are converted automatically
to character using the BEST12. format.
38
5-14 Module 5 Creating and Resolving Macro Variables During Execution

The SYMPUT Routine

%let crsnum=3;
data revenue;
set perm.all end=final;
where course_number=&crsnum;
total+1;
if paid='Y' then paidup+1;
if final then do;
call symput('numpaid',paidup);
call symput('numstu',total);
call symput('crsname',course_title);
end;
run;
proc print data=revenue noobs;
var student_name student_company paid;
title "Fee Status for &crsname (#&crsnum)";
footnote "Note: &numpaid out of &numstu paid";
run; symput3
39

The SYMPUT Routine

Notice the extra blanks within the title and the footnote.
Fee Status for Local Area Networks (#3)
Student_Name Student_Company Paid
Bills, Ms. Paulette Reston Railway Y
Chevarley, Ms. Arlene Motor Communications N
Clough, Ms. Patti Reston Railway N
Crace, Mr. Ron Von Crump Seafood Y
Davis, Mr. Bruce Semi;Conductor Y
Elsins, Ms. Marisa F. SSS Inc. N
Gandy, Dr. David Paralegal Assoc. Y
Gash, Ms. Hedy QA Information Systems Center Y
Haubold, Ms. Ann Reston Railway Y
Hudock, Ms. Cathy So. Cal. Medical Center Y
Kimble, Mr. John Alforone Chemical N
Kochen, Mr. Dennis Reston Railway Y
Larocque, Mr. Bret Physicians IPA Y
Licht, Mr. Bryan SII Y
McKnight, Ms. Maureen E. Federated Bank Y
Scannell, Ms. Robin Amberly Corp. N
Seitz, Mr. Adam Lomax Services Y
Smith, Ms. Jan Reston Railway N
Sulzbach, Mr. Bill Sailbest Ships Y
Williams, Mr. Gene Snowing Petroleum Y
Note: 14 out of 20 paid
40

 There are extra blanks between the course title and course number, as well as extra blanks before
14 and 20 in the footnote.
 5.1 Creating Macro Variables in the DATA Step 5-15

The SYMPUT Routine

You can use DATA step functions and expressions in the
SYMPUT routine's second argument to
„ left-align character strings created by numeric-to-
character conversion
„ remove trailing blanks

„ format data values

„ perform arithmetic operations on numeric data values.

CALL
CALLSYMPUT('macro-variable',expression);
SYMPUT('macro-variable',expression);

43

The SYMPUT Routine

%let crsnum=3;
data revenue;
set perm.all end=final;
where course_number=&crsnum;
total+1;
if paid='Y' then paidup+1;
if final then do;
call symput('numpaid',trim(left(paidup)));
call symput('numstu',trim(left(total)));
call symput('crsname',trim(course_title));
end;
run;
proc print data=revenue noobs;
var student_name student_company paid;
title "Fee Status for &crsname (#&crsnum)";
footnote "Note: &numpaid out of &numstu paid";
run; symput4
44

 The LEFT function left-justifies the value. The TRIM function removes trailing blanks. Both
functions expect character arguments. Numeric arguments cause automatic numeric-to-character
conversion, with notes written to the SAS log.
5-16 Module 5 Creating and Resolving Macro Variables During Execution

The SYMPUT Routine

The TRIM and LEFT functions together remove the
leading and trailing blanks within the title and the footnote.

Fee Status for Local Area Networks (#3)

NAME COMPANY PAID
Bills, Ms. Paulette Reston Railway Y
Chevarley, Ms. Arlene Motor Communications N
Clough, Ms. Patti Reston Railway N
Crace, Mr. Ron Von Crump Seafood Y
Davis, Mr. Bruce Semi;Conductor Y
Elsins, Ms. Marisa F. SSS Inc. N
Gandy, Dr. David Paralegal Assoc. Y
Gash, Ms. Hedy QA Information Systems Center Y
Haubold, Ms. Ann Reston Railway Y
Hudock, Ms. Cathy So. Cal. Medical Center Y
Kimble, Mr. John Alforone Chemical N
Kochen, Mr. Dennis Reston Railway Y
Larocque, Mr. Bret Physicians IPA Y
Licht, Mr. Bryan SII Y
McKnight, Ms. Maureen E. Federated Bank Y
Scannell, Ms. Robin Amberly Corp. N
Seitz, Mr. Adam Lomax Services Y
Smith, Ms. Jan Reston Railway N
Sulzbach, Mr. Bill Sailbest Ships Y
Williams, Mr. Gene Snowing Petroleum Y
Note: 14 out of 20 paid
45

Student Activity
„ Open sa-symputds.sas.
„ Given the first row of perm.all, what is the value
for the macro variable &first ?

Course_
Obs Code Course_Title
1 C004 Database Design

%let first=course_title;

data _null_;
/* use obs= data set option to read in 1st row only */
set perm.all(obs=1);
call symput('second',trim(course_title));
run; sa-symputds
46

Reviewing the Results

Given the first row of perm.all, &first and
&second will have the following values:
Course_
Obs Code Course_Title
1 C004 Database Design

Symbol Table
first course_title
second Database Design

48
 5.1 Creating Macro Variables in the DATA Step 5-17

The SYMPUT Routine

Example: Further enhance the report as below.
Fee Status for Local Area Networks (#3) Held 01/11/2005

Obs Student_Name Student_Company Paid

1 Bills, Ms. Paulette Reston Railway Y

2 Chevarley, Ms. Arlene Motor Communications N
3 Clough, Ms. Patti Reston Railway N
4 Crace, Mr. Ron Von Crump Seafood Y
5 Davis, Mr. Bruce Semi;Conductor Y
6 Elsins, Ms. Marisa F. SSS Inc. N
7 Gandy, Dr. David Paralegal Assoc. Y
8 Gash, Ms. Hedy QA Information Systems Center Y
9 Haubold, Ms. Ann Reston Railway Y
10 Hudock, Ms. Cathy So. Cal. Medical Center Y
11 Kimble, Mr. John Alforone Chemical N
12 Kochen, Mr. Dennis Reston Railway Y
13 Larocque, Mr. Bret Physicians IPA Y
14 Licht, Mr. Bryan SII Y
15 McKnight, Ms. Maureen E. Federated Bank Y
16 Scannell, Ms. Robin Amberly Corp. N
17 Seitz, Mr. Adam Lomax Services Y
18 Smith, Ms. Jan Reston Railway N
19 Sulzbach, Mr. Bill Sailbest Ships Y
20 Williams, Mr. Gene Snowing Petroleum Y

Note: $3,900 in Unpaid Fees

49
5-18 Module 5 Creating and Resolving Macro Variables During Execution

The SYMPUT Routine

%let crsnum=3;
data revenue;
set perm.all end=final;
where course_number=&crsnum;
total+1;
if paid='Y' then paidup+1;
if final then do;
call symput('crsname',trim(course_title));
call symput('date',put(begin_date,mmddyy10.));
call symput('due',put(fee*(total-paidup),dollar8.));
end;
run;
proc print data=revenue;
var student_name student_company paid;
title "Fee Status for &crsname (#&crsnum) Held &date";
footnote "Note: &due in Unpaid Fees";
run;

symput5
50

The PUT function returns the character string formed by writing a value with a specified format.
You can use the PUT function to
• format the result of a numeric expression
• perform explicit numeric-to-character conversion.
General form of the PUT function:

PUT(source, format)

source is a constant, variable, or expression (numeric or character).

format is any SAS or user-defined format.
format determines
the width of the resulting string
whether the string is right- or left-aligned.

Refer to Exercise 1 for Module 5 in Appendix A.

 5.1 Creating Macro Variables in the DATA Step 5-19

The SYMPUTX Routine

The SYMPUTX routine automatically removes leading
and trailing blanks from both arguments.

General form of the SYMPUTX routine:

CALL
CALL SYMPUTX(macro-variable,
SYMPUTX(macro-variable,expression);
expression);

 The SYMPUTX routine is new in SAS®9.

52

The SYMPUTX Routine

%let crsnum=3;
data revenue;
set perm.all end=final;
where course_number=&crsnum;
total+1;
if paid='Y' then paidup+1;
if final then do;
call symputx('numpaid',paidup);
call symputx('numstu',total);
call symputx('crsname',course_title);
end;
run;
proc print data=revenue noobs;
var student_name student_company paid;
title "Fee Status for &crsname (#&crsnum)";
footnote "Note: &numpaid out of &numstu paid";
run; symput6
53

The SYMPUTX Routine

Fee Status for Local Area Networks (#3)
NAME COMPANY PAID
Bills, Ms. Paulette Reston Railway Y
Chevarley, Ms. Arlene Motor Communications N
Clough, Ms. Patti Reston Railway N
Crace, Mr. Ron Von Crump Seafood Y
Davis, Mr. Bruce Semi;Conductor Y
Elsins, Ms. Marisa F. SSS Inc. N
Gandy, Dr. David Paralegal Assoc. Y
Gash, Ms. Hedy QA Information Systems Center Y
Haubold, Ms. Ann Reston Railway Y
Hudock, Ms. Cathy So. Cal. Medical Center Y
Kimble, Mr. John Alforone Chemical N
Kochen, Mr. Dennis Reston Railway Y
Larocque, Mr. Bret Physicians IPA Y
Licht, Mr. Bryan SII Y
McKnight, Ms. Maureen E. Federated Bank Y
Scannell, Ms. Robin Amberly Corp. N
Seitz, Mr. Adam Lomax Services Y
Smith, Ms. Jan Reston Railway N
Sulzbach, Mr. Bill Sailbest Ships Y
Williams, Mr. Gene Snowing Petroleum Y
Note: 14 out of 20 paid
54
5-20 Module 5 Creating and Resolving Macro Variables During Execution

The SYMPUTX Routine

%let crsnum=3;
data revenue;
set perm.all end=final;
where course_number=&crsnum;
total+1;
if paid='Y' then paidup+1;
if final then do;
call symputx('crsname',course_title);
call symputx('date',put(begin_date,mmddyy10.));
call symputx('due',put(fee*(total-paidup),dollar8.));
end;
run;
proc print data=revenue;
var student_name student_company paid;
title "Fee Status for &crsname (#&crsnum) Held &date";
footnote "Note: &due in Unpaid Fees";
run;

symput7
55

The SYMPUTX Routine

Example: Further enhance the report, as shown below.
Fee Status for Local Area Networks (#3) Held 01/11/2005

Obs Student_Name Student_Company Paid

1 Bills, Ms. Paulette Reston Railway Y

2 Chevarley, Ms. Arlene Motor Communications N
3 Clough, Ms. Patti Reston Railway N
4 Crace, Mr. Ron Von Crump Seafood Y
5 Davis, Mr. Bruce Semi;Conductor Y
6 Elsins, Ms. Marisa F. SSS Inc. N
7 Gandy, Dr. David Paralegal Assoc. Y
8 Gash, Ms. Hedy QA Information Systems Center Y
9 Haubold, Ms. Ann Reston Railway Y
10 Hudock, Ms. Cathy So. Cal. Medical Center Y
11 Kimble, Mr. John Alforone Chemical N
12 Kochen, Mr. Dennis Reston Railway Y
13 Larocque, Mr. Bret Physicians IPA Y
14 Licht, Mr. Bryan SII Y
15 McKnight, Ms. Maureen E. Federated Bank Y
16 Scannell, Ms. Robin Amberly Corp. N
17 Seitz, Mr. Adam Lomax Services Y
18 Smith, Ms. Jan Reston Railway N
19 Sulzbach, Mr. Bill Sailbest Ships Y
20 Williams, Mr. Gene Snowing Petroleum Y

Note: $3,900 in Unpaid Fees

56

The SYMPUTX Routine

Example: Based on user-selected time periods, dynamically
compute statistics for automatic inclusion within
titles, footnotes, and a graphic reference line.

57
 5.1 Creating Macro Variables in the DATA Step 5-21

The SYMPUTX Routine

%macro students(start=01Jan2005 stop=31Dec2005);
proc freq data = perm.all;
where begin_date between "&start"d and "&stop"d;
table course_code*location / noprint
out=stats (rename=(count=ENROLLMENT));
run;
data _null_;
set stats end=last;
classes+1;
students+enrollment;
if last;
call symputx('students',students);
call symputx('average',put(students/classes,4.1));
run;
options nolabel;
proc gchart data=stats;
vbar3d location / patternid=midpoint cframe=w shape=c
sumvar=enrollment type=mean mean ref=&average;
title1 "Report from &start to &stop";
title2 h=2 f=swiss "Students this period: " c=b "&students";
footnote1 h=2 f=swiss "Enrollment average: " c=b "&average";
run;
%mend;
%students( ) sa-symputx
58

Listing of STATS data set

Course_
Obs Code Location ENROLLMENT PERCENT

1 C001 Boston 28 9.3645

2 C001 Dallas 18 6.0201
3 C002 Boston 20 6.6890
4 C002 Seattle 33 11.0368
5 C003 Boston 20 6.6890
6 C003 Seattle 30 10.0334
7 C004 Dallas 23 7.6923
8 C004 Seattle 27 9.0301
9 C005 Boston 28 9.3645
10 C005 Dallas 25 8.3612
11 C006 Boston 27 9.0301
12 C006 Seattle 20 6.6890
5-22 Module 5 Creating and Resolving Macro Variables During Execution

Student Activity
„ Open sa-symputx.sas.
„ Submit the code and review the initial results in the
GRAPH1 window.
„ Issue a macro call that changes the stop value to
31Jul2005.
„ Review the results in the GRAPH1 window.

 If you do not see the results with the changes, scroll

down in the GRAPH1 window.
„ How many students attended courses from 01Jan2005
through 31Jul2005?

59

If you add a %put _user_; statement into the macro definition, you see the values that are currently in
the local symbol table for the macro variables created in this macro definition.
%put _user_;
GLOBAL STUDENTS 299
GLOBAL START 01Jan2005
GLOBAL STOP 31Dec2005
GLOBAL AVERAGE 24.9
 5.2 Indirect References to Macro Variables 5-23

5.2 Indirect References to Macro Variables

Table Lookup Application

Example: Use the perm.register data set to create
a roster for a given course. The report title
should display the instructor for the course.
Roster for Course 3
Taught by Forest, Mr. Peter

Student_Name Paid

Scannell, Ms. Robin N

Seitz, Mr. Adam Y
Smith, Ms. Jan N
Sulzbach, Mr. Bill Y
Williams, Mr. Gene Y

63

Table Lookup Application

Step 1: Hardcode the entire program, including the
course number and instructor's name.

proc print data=perm.register noobs;

where course_number=3;
var student_name paid;
title1 "Roster for Course 3";
title2 "Taught by Forest, Mr. Peter";
run;

64
5-24 Module 5 Creating and Resolving Macro Variables During Execution

Table Lookup Application

Step 2: Use a macro variable to control the subset and
display the course number in the report title.

%let crs=3;
proc print data=perm.register noobs;
where course_number=&crs;
var student_name paid;
title1 "Roster for Course &crs";
run;

How can we add the instructor's name in TITLE2 without

hardcoding it?

65

Table Lookup Application

The perm.schedule data set contains
Course_Number and Teacher variables.
Partial Listing of PERM.SCHEDULE Data Set

Course_ Course_ Begin_

Obs Number Code Location Date Teacher

1 1 C001 Seattle 26OCT2004 Hallis, Dr. George

2 2 C002 Dallas 07DEC2004 Wickam, Dr. Alice
3 3 C003 Boston 11JAN2005 Forest, Mr. Peter
4 4 C004 Seattle 25JAN2005 Tally, Ms. Julia
5 5 C005 Dallas 01MAR2005 Hallis, Dr. George
6 6 C006 Boston 05APR2005 Berthan, Ms. Judy
7 7 C001 Dallas 24MAY2005 Hallis, Dr. George
8 8 C002 Boston 14JUN2005 Wickam, Dr. Alice
9 9 C003 Seattle 19JUL2005 Forest, Mr. Peter
10 10 C004 Dallas 16AUG2005 Tally, Ms. Julia

66

Table Lookup Application

Step 3: Add a DATA step to create a macro variable with
the instructor's name from perm.schedule
and resolve the name in TITLE2.

%let crs=3;
data _null_;
set perm.schedule;
where course_number=&crs;
call symput('teacher',trim(teacher));
Output
run; from Proc Print
proc print data=perm.register noobs;
where course_number=&crs;
var student_name paid;
title1 "Roster for Course &crs";
title2 "Taught by &teacher";
run; indirect1
67
 5.2 Indirect References to Macro Variables 5-25

Table Lookup Application

Each time you select a course number to generate a
different report, you must rerun the DATA step. This is
inefficient.

%let crs=4; Change

data _null_;
set perm.schedule;
where course_number=&crs;
call symput('teacher',trim(teacher));
run;
Outputprint
proc from Proc Print
data=perm.register noobs;
where course_number=&crs;
var student_name paid;
title1 "Roster for Course &crs";
title2 "Taught by &teacher";
run; indirect1

68

Creating a Series of Macro Variables

Solution: Execute the DATA step one time only, creating
a numbered series of macro variables to store instructor
names. Derive unique macro variable names by
appending the Course_Number variable, unique on
every observation (1-18), to the prefix (root) TEACH.

Symbol Table
Variable Value
TEACH1 Hallis, Dr. George
TEACH2 Wickam, Dr. Alice
TEACH3 Forest, Mr. Peter
… …

69

Creating a Series of Macro Variables

To create a series of macro variables, use the SYMPUT
or SYMPUTX routine with a DATA step variable or
expression in argument1.

CALL
CALLSYMPUT(expression1,expression2);
SYMPUT(expression1,expression2);

CALL
CALLSYMPUTX(expression1,expression2);
SYMPUTX(expression1,expression2);

expression1 evaluates to a character value that is a valid

macro variable name, unique to each
execution of the routine.

expression2 value to assign to each macro variable.

70
5-26 Module 5 Creating and Resolving Macro Variables During Execution

Creating a Series of Macro Variables

Step 4: Create a series of macro variables containing the
name of the instructor assigned to a specific
course.
data _null_;
set perm.schedule;
call symput('teach'||left(course_number),
trim(teacher));
run;
%put _user_;
indirect2

71

Creating a Series of Macro Variables

SAS Log
137 %put _user_;
GLOBAL TEACH1 Hallis, Dr. George
GLOBAL TEACH13 Hallis, Dr. George
GLOBAL TEACH12 Berthan, Ms. Judy
GLOBAL TEACH3 Forest, Mr. Peter
GLOBAL TEACH15 Forest, Mr. Peter
GLOBAL TEACH2 Wickam, Dr. Alice
GLOBAL TEACH14 Wickam, Dr. Alice
GLOBAL TEACH17 Hallis, Dr. George
GLOBAL TEACH16 Tally, Ms. Julia
GLOBAL TEACH18 Berthan, Ms. Judy
GLOBAL TEACH9 Forest, Mr. Peter
GLOBAL TEACH8 Wickam, Dr. Alice
GLOBAL TEACH5 Hallis, Dr. George
GLOBAL TEACH4 Tally, Ms. Julia
GLOBAL TEACH7 Hallis, Dr. George
GLOBAL TEACH11 Tally, Ms. Julia
GLOBAL TEACH6 Berthan, Ms. Judy
GLOBAL TEACH10 Tally, Ms. Julia

72

Quick Quiz
How many macro variables are created with the program
named sa-symput?
Listing of PERM.COURSES Data Set

Course_
Obs Code Course_Title Days Fee

1 C001 Basic Telecommunications 3 $795

2 C002 Structured Query Language 4 $1150
3 C003 Local Area Networks 3 $650
4 C004 Database Design 2 $375
5 C005 Artificial Intelligence 2 $400
6 C006 Computer Aided Design 5 $1600

data _null_;
set perm.courses;
call symput(course_code,trim(course_title));
run;
sa-symput
73
 5.2 Indirect References to Macro Variables 5-27

Creating a Series of Macro Variables

Because there are no macro triggers, the entire DATA step
is passed to the compiler. The compiled DATA step
executes after the RUN statement is encountered.
data
data _null_;
_null_;
Compiler set
set perm.schedule;
perm.schedule;
call
call symput('teach'||left(course_number),
symput('teach'||left(course_number),
trim(teacher));
trim(teacher));

Macro Processor
Word run;
run;
Scanner

Symbol Table
Input %put
%put _user_;
_user_;
Stack SYSDAY Tuesday

76

Partial Listing of perm.schedule

Course_Number Teacher data _null_;
set perm.schedule;
1 Hallis, Dr. George call symput('teach'||
2 Wickam, Dr. Alice
left(course_number),
3 Forest, Mr. Peter
4 Tally, Ms. Julia
trim(teacher));
run;

Partial PDV
Course_
Teacher
Number
$
N
20
8

Symbol Table

SYSDAY Tuesday

77

Partial Listing of perm.schedule

Course_Number Teacher data _null_;
set perm.schedule;
1 Hallis, Dr. George call symput('teach'||
2 Wickam, Dr. Alice left(course_number),
3 Forest, Mr. Peter
4 Tally, Ms. Julia
trim(teacher));
run;
The SET statement reads
Partial PDV the first observation into
Course_ the PDV.
Teacher
Number
$
N
20
8
1 Hallis, Dr. George

Symbol Table

SYSDAY Tuesday

78
5-28 Module 5 Creating and Resolving Macro Variables During Execution

Partial Listing of perm.schedule

Course_Number Teacher data _null_;
set perm.schedule;
1 Hallis, Dr. George call symput('teach'||
2 Wickam, Dr. Alice left(course_number),
3 Forest, Mr. Peter
4 Tally, Ms. Julia
trim(teacher));
run;

Partial PDV CALL SYMPUT evaluates the

Course_ expressions and adds a macro
Teacher
Number variable to the symbol table.
$
N
20
8
1 Hallis, Dr. George

Symbol Table

SYSDAY Tuesday
79
TEACH1 Hallis, Dr. George

Partial Listing of perm.schedule

Automatic return
Course_Number Teacher data _null_;
set perm.schedule;
1 Hallis, Dr. George call symput('teach'||
2 Wickam, Dr. Alice
left(course_number),
3 Forest, Mr. Peter
4 Tally, Ms. Julia trim(teacher));
run;

Partial PDV
Course_
Teacher
Number
$
N
20
8
1 Hallis, Dr. George

Symbol Table

SYSDAY Tuesday
80
TEACH1 Hallis, Dr. George

Partial Listing of perm.schedule

Course_Number Teacher data _null_;
set perm.schedule;
1 Hallis, Dr. George call symput('teach'||
2 Wickam, Dr. Alice left(course_number),
3 Forest, Mr. Peter
4 Tally, Ms. Julia
trim(teacher));
run;

Partial PDV The SET statement reads

Course_ the next observation into
Teacher the PDV.
Number
$
N
20
8
2 Wickam, Dr. Alice

Symbol Table

SYSDAY Tuesday
81
TEACH1 Hallis, Dr. George
 5.2 Indirect References to Macro Variables 5-29

Partial Listing of perm.schedule

Course_Number Teacher data _null_;
set perm.schedule;
1 Hallis, Dr. George call symput('teach'||
2 Wickam, Dr. Alice left(course_number),
3 Forest, Mr. Peter
4 Tally, Ms. Julia
trim(teacher));
run;

Partial PDV CALL SYMPUT evaluates the

Course_ expressions and adds a macro
Teacher
Number variable to the symbol table.
$
N
20
8
2 Wickam, Dr. Alice

Symbol Table

SYSDAY Tuesday
TEACH1 Hallis, Dr. George
82
TEACH2 Wickam, Dr. Alice

Partial Listing of perm.schedule

Course_Number Teacher data _null_;
set perm.schedule;
1 Hallis, Dr. George call symput('teach'||
2 Wickam, Dr. Alice left(course_number),
3 Forest, Mr. Peter
4 Tally, Ms. Julia
trim(teacher));
run;

Partial PDV Processing continues until SAS

Course_ has read all observations in the
Teacher
Number
N
$ perm.schedule data set.
20
8
2 Wickam, Dr. Alice

Symbol Table

SYSDAY Tuesday
TEACH1 Hallis, Dr. George
83
TEACH2 Wickam, Dr. Alice

Creating a Series of Macro Variables

After the DATA step completes, control returns to the
word scanner.

Compiler

Macro Processor
Word %%
put
Scanner put

Symbol Table
_user_;
Input _user_;
SYSDAY Tuesday
Stack TEACH1 Hallis, Dr. George
84
TEACH2 Wickam, Dr. Alice
5-30 Module 5 Creating and Resolving Macro Variables During Execution

Creating a Series of Macro Variables

The %PUT statement is passed to the macro processor
for execution.

Compiler

Macro Processor
Word %put
%put _user_;
_user_;
Scanner

Symbol Table
Input
SYSDAY Tuesday
Stack TEACH1 Hallis, Dr. George
85
TEACH2 Wickam, Dr. Alice

Creating a Series of Macro Variables

SAS Log
137 %put _user_;
GLOBAL TEACH1 Hallis, Dr. George
GLOBAL TEACH13 Hallis, Dr. George
GLOBAL TEACH12 Berthan, Ms. Judy
GLOBAL TEACH3 Forest, Mr. Peter
GLOBAL TEACH15 Forest, Mr. Peter
GLOBAL TEACH2 Wickam, Dr. Alice
GLOBAL TEACH14 Wickam, Dr. Alice
GLOBAL TEACH17 Hallis, Dr. George
GLOBAL TEACH16 Tally, Ms. Julia
GLOBAL TEACH18 Berthan, Ms. Judy
GLOBAL TEACH9 Forest, Mr. Peter
GLOBAL TEACH8 Wickam, Dr. Alice
GLOBAL TEACH5 Hallis, Dr. George
GLOBAL TEACH4 Tally, Ms. Julia
GLOBAL TEACH7 Hallis, Dr. George
GLOBAL TEACH11 Tally, Ms. Julia
GLOBAL TEACH6 Berthan, Ms. Judy
GLOBAL TEACH10 Tally, Ms. Julia

86

Creating a Series of Macro Variables

You can now reference the correct name without
rerunning the DATA step.
Symbol Table
Variable Value
CRS 2
TEACH1 Hallis, Dr. George
TEACH2 Wickam, Dr. Alice
TEACH3 Forest, Mr. Peter
… …

%let crs=2;
proc print data=perm.register noobs;
where course_number=&crs;
var student_name paid;
title1 "Roster for Course &crs";
title2 "Taught by &teach2";
run; indirect3

87
 5.2 Indirect References to Macro Variables 5-31

Creating a Series of Macro Variables

But now you must change two lines of code for every new
report. How can this be improved?
Symbol Table
Variable Value
CRS 3
TEACH1 Hallis, Dr. George
TEACH2 Wickam, Dr. Alice
TEACH3 Forest, Mr. Peter
… …

%let crs=3; Change

Output
proc from Proc
print Print
data=perm.register noobs;
where course_number=&crs;
var student_name paid;
title1 "Roster for Course &crs";
title2 "Taught by &teach3"; Change
run;

88

Student Activity
„ Open sa-indirect.sas.
„ Submit the code and review the results.
„ Change the value of crs in the %LET statement to 8.
„ What macro variable reference can we substitute for
&teach3, in order to make one change to &crs
and have the value of teacher populated or referenced
indirectly?

89

Indirect References to Macro Variables

Because the CRS macro variable matches part of the
name of a TEACH macro variable, the CRS macro
variable can indirectly reference a TEACH macro
variable.
Symbol Table
Variable Value
CRS 3
TEACH1 Hallis, Dr. George
TEACH2 Wickam, Dr. Alice
TEACH3 Forest, Mr. Peter
… …
%let crs=3;
proc print data=perm.register noobs;
where course_number=&crs;
var student_name paid;
title1 "Roster for Course &crs";
title2 "Taught by &&teach&crs"; Substitute
run;
91
5-32 Module 5 Creating and Resolving Macro Variables During Execution

Indirect References to Macro Variables

The Forward Rescan Rule:
„ Multiple ampersands preceding a name token denote
an indirect reference that ends when a token is
encountered that cannot be part of a macro variable
reference. This includes a token other than a name, an
ampersand, or a period delimiter.
„ The macro processor rescans an indirect reference,
left to right, from the point where the multiple
ampersands begin.
„ Two ampersands (&&) resolve to one ampersand (&).

„ Scanning continues until no more triggers can be

resolved.

92

Indirect References to Macro Variables

Step 5: Use an indirect reference.
%let crs=3;
proc print data=perm.register noobs;
where course_number=&crs;
var student_name paid;
title1 "Roster for Course &crs";
title2 "Taught by &&teach&crs";
run; indirect4

Roster for Course 3

Taught by Forest, Mr. Peter

Student_Name Paid

Scannell, Ms. Robin N

Seitz, Mr. Adam Y
Smith, Ms. Jan N
Sulzbach, Mr. Bill Y
Williams, Mr. Gene Y
93

Indirect References to Macro Variables

Placing two ampersands at the start of the original token
sequence alters the processing of the tokens and macro
triggers.

reference &teach&crs &&teach&crs

1st scan

2nd scan
(only occurs when
&& is encountered)

94
 5.2 Indirect References to Macro Variables 5-33

Indirect References to Macro Variables

Placing two ampersands at the start of the original token
sequence alters the processing of the tokens and macro
triggers.

reference &teach&crs &&teach&crs

1st scan &teach 3

WARNING

2nd scan
(only occurs when
&& is encountered)

95

Indirect References to Macro Variables

Placing two ampersands at the start of the original token
sequence alters the processing of the tokens and macro
triggers.

reference &teach&crs &&teach&crs

1st scan &teach 3 & teach3

WARNING

2nd scan
(only occurs when
&& is encountered)

96

Indirect References to Macro Variables

Placing two ampersands at the start of the original token
sequence alters the processing of the tokens and macro
triggers.

reference &teach&crs &&teach&crs

1st scan &teach 3 & teach3

WARNING

2nd scan Forest, Mr. Peter

(only occurs when
&& is encountered)

97
5-34 Module 5 Creating and Resolving Macro Variables During Execution

Indirect References to Macro Variables

The CRS macro variable is an indirect reference to a
TEACH macro variable.

Symbol Table
Variable Value
CRS 3
TEACH1 Hallis, Dr. George
TEACH2 Wickam, Dr. Alice
TEACH3 Forest, Mr. Peter
… …

Scan sequence:

&&teach&crs &teach3 Forest, Mr. Peter

98

Quick Quiz
Given the following symbol table, what does
&&teach&crs resolve to?

Symbol Table
Variable Value
CRS 2
TEACH1 Hallis, Dr. George
TEACH2 Wickam, Dr. Alice
TEACH3 Forest, Mr. Peter

99
 5.2 Indirect References to Macro Variables 5-35

Indirect References to Macro Variables

indirect2.sas, indirect4.sas
Create a series of macro variables, TEACH1 to TEACHn, each containing the name of the instructor
assigned to a specific course. Reference one of these variables when a course number is designated.
options symbolgen;
data _null_;
set perm.schedule;
call symput('teach'||left(course_number),trim(teacher));
run;

%let crs=3;
proc print data=perm.register noobs;
where course_number=&crs;
var student_name paid;
title1 "Roster for Course &crs";
title2 "Taught by &&teach&crs";
run;
Partial SAS Log
65 %let crs=3;
66 proc print data=perm.register noobs;
67 where course_number=&crs;
SYMBOLGEN: Macro variable CRS resolves to 3
68 var student_name paid;
SYMBOLGEN: Macro variable CRS resolves to 3
69 title1 "Roster for Course &crs";
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable CRS resolves to 3
SYMBOLGEN: Macro variable TEACH3 resolves to Forest, Mr. Peter
70 title2 "Taught by &&teach&crs";
71 run;

NOTE: There were 20 observations read from the dataset PERM.REGISTER.

WHERE course_number=3;
NOTE: PROCEDURE PRINT used:
real time 2.03 seconds
cpu time 0.03 seconds
5-36 Module 5 Creating and Resolving Macro Variables During Execution

SAS Output
Roster for Course 3
Taught by Forest, Mr. Peter

Student_Name Paid

Bills, Ms. Paulette Y

Chevarley, Ms. Arlene N
Clough, Ms. Patti N
Crace, Mr. Ron Y
Davis, Mr. Bruce Y
Elsins, Ms. Marisa F. N
Gandy, Dr. David Y
Gash, Ms. Hedy Y
Haubold, Ms. Ann Y
Hudock, Ms. Cathy Y
Kimble, Mr. John N
Kochen, Mr. Dennis Y
Larocque, Mr. Bret Y
Licht, Mr. Bryan Y
McKnight, Ms. Maureen E. Y
Scannell, Ms. Robin N
Seitz, Mr. Adam Y
Smith, Ms. Jan N
Sulzbach, Mr. Bill Y
Williams, Mr. Gene Y

Refer to Exercise 2 for Module 5 in Appendix A.

 5.3 Creating Macro Variables in SQL 5-37

5.3 Creating Macro Variables in SQL

The SQL Procedure INTO Clause

The SQL procedure INTO clause can create or update
macro variables.

General form of the SQL procedure INTO clause:

SELECT
SELECTcol1,
col1,col2,
col2, ......INTO
INTO:mvar1,
:mvar1, :mvar2,...
:mvar2,...
FROM
FROMtable-expression
table-expression
WHERE
WHEREwhere-expression
where-expression
other
otherclauses;
clauses;

This form of the INTO clause does not trim leading or

trailing blanks.

107

The SQL Procedure INTO Clause

Example: Create a macro variable that contains the
total of all course fees.
proc sql noprint;
select sum(fee) format=dollar10.
into :totfee
from perm.all;
quit;
sql1
Partial SAS Log
13 %let totfee=&totfee;
14 %put totfee=&totfee;
totfee=$354,380

The %LET statement removes leading and trailing blanks

from TOTFEE.
108
5-38 Module 5 Creating and Resolving Macro Variables During Execution

The SQL Procedure INTO Clause

The INTO clause can create multiple macro variables per
row when multiple rows are selected.

General form of the INTO clause to create multiple macro

variables per row:

SELECT
SELECTcol1,
col1,......INTO
INTO:mvar1
:mvar1--:mvarn,...
:mvarn,...
FROM table-expression
FROM table-expression
WHERE
WHEREwhere-expression
where-expression
other
otherclauses;
clauses;

109

The SQL Procedure INTO Clause

Example: Create macro variables from the course code and
begin date from the first two rows returned by the
SELECT statement from perm.schedule.
title 'SQL result';
proc sql;
select course_code, begin_date format=mmddyy10.
into :crsid1-:crsid2, :date1-:date2
from perm.schedule
where year(begin_date)=2006
order by begin_date;
quit;
%put &crsid1, &date1;
%put &crsid2, &date2;
sql2

110
 5.3 Creating Macro Variables in SQL 5-39

Creating a Varying Number of Variables

sql2.sas
Create macro variables from the course code and begin date from the first two rows returned by the
SELECT statement from perm.schedule.
title 'SQL result';
proc sql;
select course_code, begin_date format=mmddyy10.
into :crsid1-:crsid2,
:date1-:date2
from perm.schedule
where year(begin_date)=2006
order by begin_date;
quit;
%put &crsid1, &date1;
%put &crsid2, &date2;
SAS Log
1 title 'SQL result';
2 proc sql;
3 select course_code, begin_date format=mmddyy10.
4 into :crsid1-:crsid2,
5 :date1-:date2
6 from perm.schedule
7 where year(begin_date)=2006
8 order by begin_date;
9 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 1.41 seconds
cpu time 0.24 seconds

10 %put &crsid1, &date1;

C003, 01/10/2006
11 %put &crsid2, &date2;
C004, 01/24/2006

SAS Output
SQL result

Course
Code Begin
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
C003 01/10/2006
C004 01/24/2006
C005 02/28/2006
C006 03/28/2006
5-40 Module 5 Creating and Resolving Macro Variables During Execution

The SQL Procedure INTO Clause

SELECT Statement
Output
SQL result Partial SAS Log
Course_Code Begin_Date
53 %put &crsid1, &date1;
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
C003, 01/10/2006
C003 01/10/2006
54 %put &crsid2, &date2;
C004 01/24/2006
C004, 01/24/2006
C005 02/28/2006
C006 03/28/2006

113

The SQL Procedure INTO Clause

The INTO clause can store all unique values of a
specified column into a single macro variable.

General form of the INTO clause to create a list of

unique values in one macro variable:

SELECT
SELECTcol1,
col1,......
INTO
INTO:mvar
:mvarSEPARATED
SEPARATEDBYBY’delimiter’,
’delimiter’,......
FROM table-expression
FROM table-expression
WHERE
WHEREwhere-expression
where-expression
other
otherclauses;
clauses;

114

The SQL Procedure INTO Clause

Example: Create a macro variable that concatenates
the names of each location from the
perm.schedule data set. Delimit the
names with blanks.
proc sql noprint;
select distinct location into :sites
separated by ' '
from perm.schedule;
quit; sql4

SELECT statement output Partial SAS Log

SQL result 20 %put sites=&sites;
sites=Boston Dallas Seattle
Location
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
Boston
Dallas
Seattle
115
 5.3 Creating Macro Variables in SQL 5-41

Refer to Exercise 3 for Module 5 in Appendix A.

Module 5 Summary
„ Create macro variables during DATA step execution.
„ Describe the difference between the SYMPUT routine
and the %LET statement.
„ Reference macro variables indirectly.
„ Create a series of macro variables using the SYMPUT
routine.
„ Create macro variables during PROC SQL execution.
„ Store several values in one macro variable using the
SQL procedure.

119

Self-Study: Table Lookup Application

The perm.courses data set contains course names
that can be transferred into macro variables as in the
previous example.
The values of Course_Code are unique and can be
used as macro variable names without alteration.
Listing of PERM.COURSES Data Set

Course_
Obs Code Course_Title Days Fee

1 C001 Basic Telecommunications 3 $795

2 C002 Structured Query Language 4 $1150
3 C003 Local Area Networks 3 $650
4 C004 Database Design 2 $375
5 C005 Artificial Intelligence 2 $400
6 C006 Computer Aided Design 5 $1600

120
5-42 Module 5 Creating and Resolving Macro Variables During Execution

Self-Study: Table Lookup Application

Example: Create a series of macro variables, one for each
course code. Assign the corresponding value of
the variable Course_Title to each macro
variable.

data _null_;
set perm.courses;
call symputx(course_code, course_title);
run;
indirect5

Because the values of Course_Code represent valid

macro variable names, there is no need to precede the
value of Course_Code with a separate prefix (root).

121

Self-Study: Table Lookup Application

Because the value of one macro variable exactly matches
the name of another macro variable, three ampersands
appear together in this indirect macro variable reference.
%let crsid=C002;
proc print data=perm.schedule noobs label;
where course_code="&crsid";
var location begin_date teacher;
title1 "Schedule for &&&crsid";
run;
indirect6

122

Self-Study: Table Lookup Application

Use three ampersands when the value of one macro
variable matches the entire name of a second macro
variable.
Symbol Table
Variable Value
CRSID C002
C001 Basic Telecommunications
C002 Structured Query Language
C003 Local Area Networks
C004 Database Design
C005 Artificial Intelligence
C006 Computer Aided Design

Scan sequence:

&&&crsid &c002 Structured Query Language

123
 5.3 Creating Macro Variables in SQL 5-43

Self-Study: Table Lookup Application

Placing three ampersands at the start of the original
token sequence alters the processing of the tokens and
macro triggers.

reference &&&crsid

1st scan & c002

2nd scan Structured Query Language

(only occurs when
&& is encountered)

124

Quick Quiz
Given the following symbol table, what does &&&CRSID
resolve to?

Symbol Table
Variable Value
CRSID C006
C001 Basic Telecommunications
C002 Structured Query Language
C003 Local Area Networks
C004 Database Design
C005 Artificial Intelligence
C006 Computer Aided Design

125

Quick Quiz - Answer

Given the following symbol table, what does &&&CRSID
resolve to?

Symbol Table &&&crsid

Variable Value
CRSID C006
C001 Basic Telecommunications &C006
C002 Structured Query Language
C003 Local Area Networks
C004 Database Design
Computer
C005 Artificial Intelligence
Aided
C006 Computer Aided Design
Design

126
5-44 Module 5 Creating and Resolving Macro Variables During Execution

Self-Study: The SQL Procedure INTO

Clause
The INTO clause can create macro variables for an
unknown number of rows.

1. Run a query to determine the number of rows and

create a macro variable NUMROWS to store that
number.
2. Run a query using NUMROWS as the suffix of a
numbered series of macro variables.

127
 5.3 Creating Macro Variables in SQL 5-45

Self-Study: The SQL Procedure INTO Clause

sql3
Create ranges of macro variables that contain the course code, location, and starting date of all courses
scheduled in 2006.
proc sql noprint;
select count(*)
into :numrows
from perm.schedule
where year(begin_date)=2006;
%let numrows=&numrows;
%put There are &numrows courses in 2006;
select course_code, location,
begin_date format=mmddyy10.
into :crsid1-:crsid&numrows,
:place1-:place&numrows,
:date1-:date&numrows
from perm.schedule
where year(begin_date)=2006
order by begin_date;
%put _user_;
quit;
5-46 Module 5 Creating and Resolving Macro Variables During Execution

Partial SAS Log

20 proc sql noprint;
21 select count(*)
22 into :numrows
23 from perm.schedule
24 where year(begin_date)=2006;
25 %let numrows=&numrows;
26 %put There are &numrows courses in 2006;
There are 4 courses in 2006
27 select course_code, location,
28 begin_date format=mmddyy10.
29 into :crsid1-:crsid&numrows,
30 :place1-:place&numrows,
31 :date1-:date&numrows
32 from perm.schedule
33 where year(begin_date)=2006
34 order by begin_date;
35 %put _user_;
GLOBAL SQLOBS 4
GLOBAL CRSID2 C004
GLOBAL SQLOOPS 22
GLOBAL CRSID3 C005
GLOBAL DATE4 03/28/2006
GLOBAL PLACE1 Dallas
GLOBAL CRSID1 C003
GLOBAL PLACE2 Boston
GLOBAL PLACE3 Seattle
GLOBAL DATE1 01/10/2006
GLOBAL CRSID4 C006
GLOBAL TOTFEE $354,380
GLOBAL DATE2 01/24/2006
GLOBAL DATE3 02/28/2006
GLOBAL SQLRC 0
GLOBAL NUMROWS 4
GLOBAL PLACE4 Dallas
Module 6 Utilizing Macro Language
Statements

6.1 Iterative Processing .......................................................................................................6-2

6.2 Conditional Processing ...............................................................................................6-13

6.3 Global and Local Symbol Table...................................................................................6-26

6-2 Module 6 Utilizing Macro Language Statements

6.1 Iterative Processing

Objectives
„ Execute macro language statements iteratively.
„ Generate SAS code iteratively.
„ Conditionally process SAS code within a macro
program.
„ Monitor macro execution.
„ Insert entire steps, entire statements, and partial
statements into a SAS program.
„ Explain the difference between global and local symbol
tables.
„ Describe how the macro processor decides which
symbol table to use.
„ Describe the concept of nested macros and the
hierarchy of symbol tables.
2

Simple Loops
Many macro applications require iterative processing.
The iterative %DO statement can repeatedly
„ execute macro language statements

„ generate SAS code.

General form of the iterative %DO statement:

%DO
%DO index-variable=start
index-variable=start %TO
%TOstop
stop<%BY
<%BY increment>;
increment>;
text
text
%END;
%END;

4
 6.1 Iterative Processing 6-3

Simple Loops
„ %DO and %END statements are valid only inside a
macro definition.
„ Index-variable is a macro variable.
„ Index-variable is created in the local symbol table if it
does not already exist in an existing symbol table.
„ Start, stop, and increment values can be any valid
macro expressions that resolve to integers.
„ %BY clause is optional (default increment is 1).

Simple Loops
Text can be
„ constant text

„ macro variables or expressions

„ macro statements

„ macro calls.

6
6-4 Module 6 Utilizing Macro Language Statements

Simple Loops
Example: Create a numbered series of macro variables.
Display each macro variable in the SAS log by
repeatedly executing %PUT within a macro loop.
data _null_;
set perm.schedule end=no_more;
call symputx('teach'||left(_n_),teacher);
if no_more then call symputx('count',_n_);
run;

%macro putloop;
%do i=1 %to &count;
%put TEACH&i is &&teach&i;
%end;
%mend putloop;
loop1

 No code is sent to the compiler when the macro executes. The %PUT statements are executed by
the macro processor.

Quick Quiz
Given the data below, what is the name and the value of
the first macro variable created?

Listing of PERM.SCHEDULE

Course_ Course_ Begin_

Obs Number Code Location Date Teacher

1 1 C001 Seattle 23OCT2000 Hallis, Dr. George

2 2 C002 Dallas 04DEC2000 Wickam, Dr. Alice
3 3 C003 Boston 08JAN2001 Forest, Mr. Peter
4 4 C004 Seattle 22JAN2001 Tally, Ms. Julia
5 5 C005 Dallas 26FEB2001 Hallis, Dr. George

data _null_;
set perm.schedule end=no_more;
call symput('teach'||left(_n_),(trim(teacher)));
if no_more then call symput('count',_n_);
run;

8
 6.1 Iterative Processing 6-5

Simple Loops
Partial SAS Log
12 %putloop
TEACH1 is Hallis, Dr. George
TEACH2 is Wickam, Dr. Alice
TEACH3 is Forest, Mr. Peter
TEACH4 is Tally, Ms. Julia
TEACH5 is Hallis, Dr. George
TEACH6 is Berthan, Ms. Judy
TEACH7 is Hallis, Dr. George
TEACH8 is Wickam, Dr. Alice
TEACH9 is Forest, Mr. Peter
TEACH10 is Tally, Ms. Julia
TEACH11 is Tally, Ms. Julia
TEACH12 is Berthan, Ms. Judy
TEACH13 is Hallis, Dr. George
TEACH14 is Wickam, Dr. Alice
TEACH15 is Forest, Mr. Peter
TEACH16 is Tally, Ms. Julia
TEACH17 is Hallis, Dr. George
TEACH18 is Berthan, Ms. Judy

10

Generating Complete Steps

Example: Iteratively generate complete SAS steps.
%macroreadraw(first=1999,last=2005);
%do year=&first %to &last;
data year&year; year1999
infile "raw&year..dat"; raw1999.dat
input course_code $4.
location $15.
begin_date date9.
teacher $25.;
run;
proc print data=year&year; year1999
title "Scheduled classes for &year";
run;
%end; 1999
%mend readraw;
%readraw(first=2000,last=2002)
loop2
11

Generating Complete Steps

Partial SAS Log
MLOGIC(READRAW): %DO loop index variable YEAR is now 2001; loop will iterate again.
MPRINT(READRAW): data year2001;
MPRINT(READRAW): infile "raw2001.dat";
MPRINT(READRAW): input course_code $4. location $15. begin_date date9. teacher $25.;
MPRINT(READRAW): run;

NOTE: The infile "raw2001.dat" is:

File Name=C:\workshop\winsas\macr\raw2001.dat,
RECFM=V,LRECL=256

NOTE: 12 records were read from the infile "raw2001.dat".

The minimum record length was 53.
The maximum record length was 53.
NOTE: The data set WORK.YEAR2001 has 12 observations and 4 variables.

MPRINT(READRAW): proc print data=year2001;

MPRINT(READRAW): title "Scheduled classes for 2001";
MPRINT(READRAW): run;

NOTE: There were 12 observations read from the data set WORK.YEAR2001.

MLOGIC(READRAW): %DO loop index variable YEAR is now 2002; loop will iterate again.
MPRINT(READRAW): data year2002;
MPRINT(READRAW): infile "raw2002.dat";
MPRINT(READRAW): input course_code $4. location $15. begin_date date9. teacher $25.;
MPRINT(READRAW): run;

13
6-6 Module 6 Utilizing Macro Language Statements

Generating Data-Dependent Steps

Example: Print all data sets in a SAS data library.
Data set information is available in the dynamic view
vstabvw in the sashelp library.
proc print data=sashelp.vstabvw;
where libname="PERM";
title "sashelp.vstabvw";
run;
PROC PRINT Output
sashelp.vstabvw

Obs libname memname memtype

3480 PERM ALL DATA

3481 PERM COURSES DATA
3482 PERM REGISTER DATA
3483 PERM SCHEDULE DATA
3484 PERM STUDENTS DATA
14

Generating Data-Dependent Steps

Store data set names in macro variables.
data _null_;
set sashelp.vstabvw end=final;
where libname="PERM";
call symputx('dsn'||left(_n_),memname);
if final then call symputx('totaldsn',_n_);
run;
%put _user_;

Partial SAS Log

7 %put _user_;
GLOBAL DSN1 ALL
GLOBAL DSN2 COURSES
GLOBAL DSN3 REGISTER
GLOBAL DSN4 SCHEDULE
GLOBAL DSN5 STUDENTS
GLOBAL TOTALDSN 5

15

Generating Data-Dependent Steps

Use a macro loop to print every data set in the library.
%macro printlib(lib=WORK,obs=5);
%let lib=%upcase(&lib);
data _null_;
set sashelp.vstabvw end=final;
where libname="&lib";
call symputx('dsn'||left(_n_),memname);
if final then call symputx('totaldsn',_n_);
run;
%do i=1 %to &totaldsn;
proc print data=&lib..&&dsn&i(obs=&obs);
title "&lib..&&dsn&i Data Set";
run;
%end;
%mend printlib;
%printlib(lib=PERM)
loop3
16
 6.1 Iterative Processing 6-7

Quick Quiz
Given the symbol table below, what is the value of
&lib..&&dsn&i?
7 %put _user_;
GLOBAL DSN1 ALL
GLOBAL DSN2 COURSES
GLOBAL DSN3 REGISTER
GLOBAL DSN4 SCHEDULE
GLOBAL DSN5 STUDENTS
GLOBAL TOTALDSN 5
LOCAL LIB PERM
LOCAL I 4
LOCAL OBS 5

Answer verbally or with a text message.

17

Reviewing the Results

The value of &lib..&&dsn&i is PERM.SCHEDULE.

7 %put _user_;
GLOBAL DSN1 ALL &lib..&&dsn&i
GLOBAL DSN2 COURSES
GLOBAL DSN3 REGISTER
GLOBAL DSN4 SCHEDULE
GLOBAL DSN5 STUDENTS
PERM.&dsn4
GLOBAL TOTALDSN 5
LOCAL LIB PERM
LOCAL I 4 PERM.SCHEDULE
LOCAL OBS 5

18

Generating Data-Dependent Steps

Partial SAS Log
MPRINT(PRINTLIB): proc print data=PERM.ALL(obs=5);
MPRINT(PRINTLIB): title "PERM.ALL Data Set";
MPRINT(PRINTLIB): run;
NOTE: There were 5 observations read from the data set PERM.ALL.

MPRINT(PRINTLIB): proc print data=PERM.COURSES(obs=5);

MPRINT(PRINTLIB): title "PERM.COURSES Data Set";
MPRINT(PRINTLIB): run;
NOTE: There were 5 observations read from the data set PERM.COURSES.

MPRINT(PRINTLIB): proc print data=PERM.REGISTER(obs=5);

MPRINT(PRINTLIB): title "PERM.REGISTER Data Set";
MPRINT(PRINTLIB): run;
NOTE: There were 5 observations read from the data set PERM.REGISTER.

MPRINT(PRINTLIB): proc print data=PERM.SCHEDULE(obs=5);

MPRINT(PRINTLIB): title "PERM.SCHEDULE Data Set";
MPRINT(PRINTLIB): run;
NOTE: There were 5 observations read from the data set PERM.SCHEDULE.

19
6-8 Module 6 Utilizing Macro Language Statements

Generating Data-Dependent Steps

Example: Create a separate data set for each value of a
selected variable in a selected data set. Use the
variable location in perm.schedule.
Listing of PERM.SCHEDULE

Course_ Course_ Begin_

Obs Number Code Location Date Teacher

1 1 C001 Seattle 26OCT2004 Hallis, Dr. George

2 2 C002 Dallas 07DEC2004 Wickam, Dr. Alice
3 3 C003 Boston 11JAN2005 Forest, Mr. Peter
4 4 C004 Seattle 25JAN2005 Tally, Ms. Julia
5 5 C005 Dallas 01MAR2005 Hallis, Dr. George
6 6 C006 Boston 05APR2005 Berthan, Ms. Judy
7 7 C001 Dallas 24MAY2005 Hallis, Dr. George
8 8 C002 Boston 14JUN2005 Wickam, Dr. Alice
9 9 C003 Seattle 19JUL2005 Forest, Mr. Peter
10 10 C004 Dallas 16AUG2005 Tally, Ms. Julia
11 11 C005 Boston 20SEP2005 Tally, Ms. Julia
12 12 C006 Seattle 04OCT2005 Berthan, Ms. Judy
13 13 C001 Boston 15NOV2005 Hallis, Dr. George
14 14 C002 Seattle 06DEC2005 Wickam, Dr. Alice
15 15 C003 Dallas 10JAN2006 Forest, Mr. Peter
16 16 C004 Boston 24JAN2006 Tally, Ms. Julia
17 17 C005 Seattle 28FEB2006 Hallis, Dr. George
18 18 C006 Dallas 28MAR2006 Berthan, Ms. Judy
20

Generating Data-Dependent Steps

SAS Program and Log

MPRINT(SITES): data Boston Dallas Seattle ;

MPRINT(SITES): set perm.schedule;
MPRINT(SITES): select(location);
MPRINT(SITES):
when("Boston") output Boston;
MPRINT(SITES):
when("Dallas") output Dallas;
MPRINT(SITES):
when("Seattle") output Seattle;
MPRINT(SITES): otherwise;
MPRINT(SITES): end;
MPRINT(SITES): run;

NOTE: There were 18 observations read from the data set PERM.SCHEDULE.
NOTE: The data set WORK.BOSTON has 6 observations and 5 variables.
NOTE: The data set WORK.DALLAS has 6 observations and 5 variables.
NOTE: The data set WORK.SEATTLE has 6 observations and 5 variables.

21

Generating Data-Dependent Steps

Store data values in macro variables.
Partial Code
%macro sites (data=, var=);
proc sort data=&data(keep=&var)
out=values nodupkey;
by &var;
run;
data _null_;
set values end=last;
call symputx('site'||left(_n_),location);
if last then call symputx('count',_n_);
run;
%put _local_;
loop4
continued...

22
 6.1 Iterative Processing 6-9

Generating Data-Dependent Steps

Partial SAS log with result of %put _local_;
SITES DATA perm.schedule
SITES I
SITES COUNT 3
SITES VAR location
SITES SITE3 Seattle
SITES SITE2 Dallas
SITES SITE1 Boston

The _local_ argument of the %PUT statement lists the

name and value of macro variables local to the currently
executing macro.

23

Generating Data-Dependent Steps

Generate the DATA step, using macro loops for iterative
substitution. Call the macro.
data
%do i=1 %to &count;
&&site&i
%end;
;
set &data;
select(&var);
%do i=1 %to &count;
when("&&site&i") output &&site&i;
%end;
otherwise;
end;
run;
%mend sites;
%sites(data=perm.schedule, var=location)
24

Quick Quiz
Given the symbol table below, what is the value of
&&site&i?

7 %put _user_;
GLOBAL SITE1 Boston
GLOBAL SITE2 Dallas
GLOBAL SITE3 Seattle
GLOBAL COUNT 3
LOCAL I 1

Submit your answer as a text question.

25
6-10 Module 6 Utilizing Macro Language Statements

Generating Data-Dependent Steps

Partial SAS Log
MPRINT(SITES): data Boston Dallas Seattle ;
MPRINT(SITES): set perm.schedule;
MPRINT(SITES): select(location);
MPRINT(SITES): when("Boston") output Boston;
MPRINT(SITES): when("Dallas") output Dallas;
MPRINT(SITES): when("Seattle") output Seattle;
MPRINT(SITES): otherwise;
MPRINT(SITES): end;
MPRINT(SITES): run;

NOTE: There were 18 observations read from the data set PERM.SCHEDULE.
NOTE: The data set WORK.BOSTON has 6 observations and 5 variables.
NOTE: The data set WORK.DALLAS has 6 observations and 5 variables.
NOTE: The data set WORK.SEATTLE has 6 observations and 5 variables.

27

Refer to Exercise 1 for Module 6 in Appendix A.

Self-Study: Conditional Iteration

You can perform conditional iteration in macros with %DO
%WHILE and %DO %UNTIL statements.

General form of the %DO %WHILE statement:

%DO
%DO %WHILE(expression);
%WHILE(expression);
text
text
%END;
%END;

A %DO %WHILE loop

„ evaluates expression at the top of the loop before the
loop executes
„ executes repetitively while expression is true.

32
 6.1 Iterative Processing 6-11

Self-Study: Conditional Iteration

General form of the %DO %UNTIL statement:

%DO
%DO %UNTIL(expression);
%UNTIL(expression);
text
text
%END;
%END;

expression can be any valid macro expression.

A %DO %UNTIL loop

„ evaluates expression at the bottom of the loop after
the loop executes
„ executes repetitively until expression is true

33
„ executes at least once.

Self-Study: Conditional Iteration

Review: Create a macro variable with a delimited list of
values.
573 proc sql noprint;
574 select distinct upcase(location)
575 into :sitelist separated by '*'
576 from perm.schedule;
577 quit;

578 %put sitelist=&sitelist;

sitelist=BOSTON*DALLAS*SEATTLE

34

Self-Study: Conditional Iteration

Example: Execute macro language statements within a
%DO %WHILE loop.
%macro values(text,delim=*);
%let i=1;
%let value=%scan(&text,&i,&delim);
%if &value= %then %put Text is blank.;
%else %do %while (&value ne );
%put Value &i is: &value;
%let i=%eval(&i+1);
%let value=%scan(&text,&i,&delim);
%end;
%mend values;
%values(&sitelist)
loop5

35
6-12 Module 6 Utilizing Macro Language Statements

Self-Study: Conditional Iteration

Example: Execute macro language statements within a
%DO %UNTIL loop.
%macro values(text,delim=*);
%let i=1;
%let value=%scan(&text,&i,&delim);
%if &value= %then %put Text is blank.;
%else %do %until (&value= );
%put Value &i is: &value;
%let i=%eval(&i+1);
%let value=%scan(&text,&i,&delim);
%end;
%mend values;
%values(&sitelist)
loop6

36

Self-Study: Conditional Iteration

Result of macro call.

Partial SAS Log

572 %values(&sitelist)
Value 1 is: BOSTON
Value 2 is: DALLAS
Value 3 is: SEATTLE

37
 6.2 Conditional Processing 6-13

6.2 Conditional Processing

The Need for Macro-Level Programming

Suppose you submit a program every day to create
registration listings for courses to be held later in the
current month.

Every Friday you also submit a second program to create

a summary of revenue generated so far in the current
month.

49

The Need for Macro-Level Programming

Example: Automate the application so that only one
program is required.

proc print data=perm.all noobs n;

Always Print where put(begin_date,monyy7.)=
the "%substr(&sysdate9,3,7)"
and begin_date ge "&sysdate9"d;
Daily Report var student_name student_company paid;
title "Course Registration as of &sysdate9";
run;

proc means data=perm.all maxdec=0 sum;

where put(begin_date,monyy7.)=
"%substr(&sysdate9,3,7)"
and begin_date le "&sysdate9"d;
Is it Yes class begin_date location course_title;
var fee;
Friday? title "Revenue for Courses as of &sysdate9";
run;
50
6-14 Module 6 Utilizing Macro Language Statements

Conditional Processing
You can perform conditional execution with %IF-%THEN
and %ELSE statements.
General form of %IF-%THEN and %ELSE statements:

%IF
%IF expression
expression %THEN
%THENtext;
text;
%ELSE
%ELSEtext;
text;

expression can be any valid macro expression.

The %ELSE statement is optional.

These macro language statements can only be used

inside a macro definition.
51

Conditional Processing
The text following keywords %THEN and %ELSE can be
„ a macro programming statement

„ constant text

„ an expression

„ a macro variable reference

„ a macro call.

Macro language expressions are similar to DATA step

expressions, except the following, which are not valid in the
macro language:
„ 1 <= &x <= 10

„ special WHERE operators.

52

CAUTION
Compound expressions can be specified using the AND and OR operators. Do not precede
these keywords with %.
 6.2 Conditional Processing 6-15

Monitoring Macro Execution

The MLOGIC system option displays macro execution
messages in the SAS log, including
„ macro initialization

„ parameter values

„ results of arithmetic and logical operations

„ macro termination.

General form of the MLOGIC|NOMLOGIC option:

OPTIONS
OPTIONSMLOGIC;
MLOGIC;
OPTIONS
OPTIONSNOMLOGIC;
NOMLOGIC;

The default setting is NOMLOGIC.

53

Processing Complete Steps

Step 1: Create separate macros for the daily and weekly
programs.
%macro daily;
proc print data=perm.all noobs n;
where put(begin_date,monyy7.)="%substr(&sysdate9,3,7)"
and begin_date ge "&sysdate9"d;
var student_name student_company paid;
title "Course Registration as of &sysdate";
run;
%mend daily;

%macro weekly;
proc means data=perm.all maxdec=0 sum;
where put(begin_date,monyy7.)="%substr(&sysdate9,3,7)"
and begin_date le "&sysdate9"d;
class begin_date location course_title;
var fee;
title "Revenue for Courses as of &sysdate9";
run;
%mend weekly;
54

Processing Complete Steps

Step 2: Write a third macro that always calls the DAILY
macro and conditionally calls the WEEKLY
macro.
%macro reports;
%daily
%if &sysday=Friday %then %weekly;
%mend reports;
cond01

55
6-16 Module 6 Utilizing Macro Language Statements

Monitoring Macro Execution

Example: Use the MLOGIC option to monitor the
REPORTS macro.
Partial SAS Log
494 %macro reports;
495 %daily
496 %if &sysday=Friday %then %weekly;
497 %mend reports;
498
499 options mlogic;
500 %reports
MLOGIC(REPORTS): Beginning execution.
MLOGIC(DAILY): Beginning execution.
MLOGIC(DAILY): Ending execution.
MLOGIC(REPORTS): %IF condition &sysday=Friday is TRUE
MLOGIC(WEEKLY): Beginning execution.
MLOGIC(WEEKLY): Ending execution.
MLOGIC(REPORTS): Ending execution.

56

Macro Syntax Errors

If a macro definition contains macro language syntax
errors, error messages are written to the SAS log and a
non-executable (dummy) macro is created.

Example: Suppose the percent sign is missing from the

%THEN statement.

Partial SAS Log

514 %macro reports;
515 %daily
516 %if &sysday=Friday then %weekly;
ERROR: Expected %THEN statement not found. A dummy macro will be
compiled.
517 %mend reports;

58

Student Activity
Submit the following program. Will this code execute
without errors?
%macro printit(print=Yes);
%if &print=Yes %then
proc print data=perm.schedule;
title "Print the Schedule data set";
run;
%mend printit;
%printit( )

Change your seat indicator to Yes or No.

sa-prob

59
 6.2 Conditional Processing 6-17

Conditional Processing
Use %DO and %END statements following %THEN or
%ELSE to generate text that contains semicolons.

%IF
%IF expression
expression %THEN
%THEN %DO;
%DO;
statement;
statement;statement;
statement;......
%END;
%END;
%ELSE
%ELSE %DO;
%DO;
statement;
statement;statement;
statement;......
%END;
%END;

61

Processing Complete Steps

Example: Use a single macro to generate the daily report
unconditionally and the weekly report on Friday.
%macro reports;
proc print data=perm.all noobs n;
where put(begin_date,monyy7.)="%substr(&sysdate9,3,7)"
and begin_date ge "&sysdate9"d;
var name company paid;
title "Course Registration as of &sysdate";
run;
%if &sysday=Friday %then %do;
proc means data=perm.all maxdec=0 sum;
where put(begin_date,monyy7.)="%substr(&sysdate9,3,7)"
and begin_date le "&sysdate9"d;
class begin_date location course_title;
var fee;
title "Revenue for Courses as of &sysdate9";
run;
%end;
%mend reports;
62 cond02

Processing Complete Steps

Example: Store the production SAS programs in external
files and copy those files to the input stack with
%INCLUDE statements.

%macro reports;
%include 'daily.sas';
%if &sysday=Friday %then %do;
%include 'weekly.sas';
%end;
%mend reports;
cond03

63
6-18 Module 6 Utilizing Macro Language Statements

The %INCLUDE Statement

The %INCLUDE statement retrieves SAS source code
from an external file and places it on the input stack.

General form of the %INCLUDE statement:

%INCLUDE
%INCLUDEfile-specification
file-specification<<//SOURCE2
SOURCE2>;
>;

file-specification physical name or fileref of the file to be

retrieved and placed on the input stack.

SOURCE2 requests inserted SAS statements to

appear in the SAS log.

64

 If SOURCE2 is not specified in the %INCLUDE statement, the setting of the SAS system option
SOURCE2 controls whether the inserted SAS code is displayed.

The %INCLUDE Statement

The %INCLUDE statement
„ copies SAS statements from an external file to the
input stack
„ is a global SAS statement

„ is not a macro language statement

„ can be used only on a statement boundary.

Input Stack
%include 'external-source-file';

External File: external-source-file

proc print data=perm.all noobs n;
where put(begin_date,monyy7.)="%substr(&sysdate9,3,7)"
and begin_date ge "&sysdate9"d;
var name company paid;
title "Course Registration as of &sysdate";
run;
65
 6.2 Conditional Processing 6-19

The %INCLUDE Statement

The contents of the external file are placed on the input
stack. The word scanner then reads the newly inserted
statements.
proc print data=perm.all noobs n;
where put(begin_date,monyy7.)=
"%substr(&sysdate9,3,7)"
Input Stack and begin_date ge "&sysdate9"d;
var name company paid;
title "Course Registration as of &sysdate";
run;

External Source File

proc print data=perm.all noobs n;
where
put(begin_date,monyy7.)="%substr(&sysdate9,3,7)"
and begin_date ge "&sysdate9"d;
var name company paid;
title "Course Registration as of &sysdate";
66 run;

Student Activity
Submit the following program.
%macro attend(crs);
proc print data=perm.all;
%if &crs= %then %do;
title1 "All Courses";
%end;
%else %do;
title1 "Course &crs only";
where course_code="&crs";
%end;
run;
%mend attend;

%attend(C003)
sa-title

67

Processing Complete Statements

Example: Insert individual statements within a PROC step.
%macro attend(crs,start=01jan2005,stop=31dec2005);
proc freq data=perm.all;
where begin_date between "&start"d and "&stop"d;
table location / nocum;
title "Enrollment from &start to &stop";
%if &crs= %then %do;
title2 "For all Courses";
%end;
%else %do;
title2 "For Course &crs only";
where also course_code="&crs";
%end;
run;
%mend;
options mprint mlogic;
%attend(start=01jul2005)
%attend(C003)
cond04
70
6-20 Module 6 Utilizing Macro Language Statements

Processing Complete Statements

SAS Log From Macro Call %attend(start=01jul2005)
71 %attend(start=01jul2005)
MLOGIC(ATTEND): Beginning execution.
MLOGIC(ATTEND): Parameter START has value 01jul2005
MLOGIC(ATTEND): Parameter CRS has value
MLOGIC(ATTEND): Parameter STOP has value 31dec2005
MPRINT(ATTEND): proc freq data=perm.all;
MPRINT(ATTEND): where begin_date between "01jul2005"d and "31dec2005"d;
MPRINT(ATTEND): table location / nocum;
MPRINT(ATTEND): title "Enrollment from 01jul2005 to 31dec2005";
MLOGIC(ATTEND): %IF condition &crs= is TRUE
MPRINT(ATTEND): title2 "For all Courses";
MPRINT(ATTEND): run;

NOTE: There were 162 observations read from the data set PERM.ALL.
WHERE (begin_date>='01JUL2005'D and begin_date<='31DEC2005'D);

MLOGIC(ATTEND): Ending execution.

71

Processing Complete Statements

SAS Log from Macro Call %attend(C003)
72 %attend(C003)
MLOGIC(ATTEND): Beginning execution.
MLOGIC(ATTEND): Parameter CRS has value C003
MLOGIC(ATTEND): Parameter START has value 01jan2005
MLOGIC(ATTEND): Parameter STOP has value 31dec2005
MPRINT(ATTEND): proc freq data=perm.all;
MPRINT(ATTEND): where begin_date between "01jan2005"d and "31dec2005"d;
MPRINT(ATTEND): table location / nocum;
MPRINT(ATTEND): title "Enrollment from 01jan2005 to 31dec2005";
MLOGIC(ATTEND): %IF condition &crs= is FALSE
MPRINT(ATTEND): title2 "For Course C003 only";
MPRINT(ATTEND): where also course_code="C003";
NOTE: Where clause has been augmented.
MPRINT(ATTEND): run;

NOTE: There were 50 observations read from the data set PERM.ALL.
WHERE (begin_date>='01JAN2005'D and begin_date<='31DEC2005'D) and
(course_code='C003');

MLOGIC(ATTEND): Ending execution.

72

Processing Complete Statements

Example: Insert individual statements within a DATA step.
%macro choice(status);
data fees;
set perm.all;
%if %upcase(&status)=PAID %then %do;
where paid = 'Y';
keep student_name course_code
begin_date totalfee;
%end;

%else %do;
where paid = 'N';
keep student_name course_code
begin_date totalfee latechg;
latechg=fee*1.10;
%end;
if location='Boston' then totalfee=fee*1.06;
else if location='Seattle' then totalfee=fee*1.025;
else if location='Dallas' then totalfee=fee*1.05;
run;
%mend choice;
%choice(PAID)
%choice(OWED)
73  Macro comparisons are case-sensitive. cond05
 6.2 Conditional Processing 6-21

Processing Complete Statements

Partial SAS Log
744 %choice(PAID)
MLOGIC(CHOICE): Beginning execution.
MLOGIC(CHOICE): Parameter STATUS has value PAID
MPRINT(CHOICE): data fees;
MPRINT(CHOICE): set perm.all;
MLOGIC(CHOICE): %IF condition %upcase(&status)=PAID is TRUE
MPRINT(CHOICE): where paid = 'Y';
MPRINT(CHOICE): keep student_name course_code begin_date totalfee;
MPRINT(CHOICE): if location='Boston' then totalfee=fee*1.06;
MPRINT(CHOICE): else if location='Seattle' then
totalfee=fee*1.025;
MPRINT(CHOICE): else if location='Dallas' then totalfee=fee*1.05;
MPRINT(CHOICE): run;

NOTE: There were 327 observations read from the data set PERM.ALL.
WHERE paid='Y';
NOTE: The data set WORK.FEES has 327 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.02 seconds

74

Processing Complete Statements

Partial SAS Log
745 %choice(OWED)
MLOGIC(CHOICE): Beginning execution.
MLOGIC(CHOICE): Parameter STATUS has value OWED
MPRINT(CHOICE): data fees;
MPRINT(CHOICE): set perm.all;
MLOGIC(CHOICE): %IF condition %upcase(&status)=PAID is FALSE
MPRINT(CHOICE): where paid = 'N';
MPRINT(CHOICE): keep student_name course_code begin_date totalfee
latechg;
MPRINT(CHOICE): latechg=fee*1.10;
MPRINT(CHOICE): if location='Boston' then totalfee=fee*1.06;
MPRINT(CHOICE): else if location='Seattle' then
totalfee=fee*1.025;
MPRINT(CHOICE): else if location='Dallas' then totalfee=fee*1.05;
MPRINT(CHOICE): run;

NOTE: There were 107 observations read from the data set PERM.ALL.
WHERE paid='N';
NOTE: The data set WORK.FEES has 107 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.02 seconds
75

Refer to Exercise 2 for Module 6 in Appendix A.

6-22 Module 6 Utilizing Macro Language Statements

Processing Partial Statements

Conditionally insert text into the middle of a statement.
Example: Generate either a one-way or two-way
frequency table, depending on a parameter value.

%macro counts (cols=_character_, rows=);

proc freq data=perm.all;
tables tables rows * cols ;
%if &rows ne %then &rows *;
&cols
;
run; tables cols ;
%mend counts;
options mprint mlogic;
%counts(cols=paid)
%counts(cols=paid, rows=course_number)
77 cond06

Processing Partial Statements

Partial SAS Log
633 %counts(cols=paid)
MPRINT(COUNTS): proc freq data=perm.all;
MPRINT(COUNTS): tables paid ;
MPRINT(COUNTS): run;

NOTE: There were 434 observations read from the data set PERM.ALL.
NOTE: PROCEDURE FREQ used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

634 %counts(cols=paid, rows=course_number)

MPRINT(COUNTS): proc freq data=perm.all;
MPRINT(COUNTS): tables course_number * paid ;
MPRINT(COUNTS): run;

NOTE: There were 434 observations read from the data set PERM.ALL.
NOTE: PROCEDURE FREQ used (Total process time):
real time 0.01 seconds
cpu time 0.02 seconds

78

Student Activity
Submit the program below. Will this code run without
errors?

%macro counts (cols=_all_, rows=);

proc freq data=perm.all;
tables
%if &rows ne %then &rows *;;
&cols
;
run;
%mend counts;
%counts(cols=paid, rows=course_number)
sa-partial

Use your seat indicators to answer Yes or No.

79
 6.2 Conditional Processing 6-23

Parameter Validation
Example: Validate a parameter value before generating
SAS code based on that value.

%macro courses(site);
%let site=%upcase(&site);
%if &site=DALLAS or &site=SEATTLE or &site=BOSTON
%then %do;
proc print data=perm.schedule;
where upcase(location)="&site";
title "COURSES OFFERED AT &site";
run;
%end;
%else %put Sorry, no courses taught at &site..;
%mend courses;
cond07

81

Parameter Validation
Partial SAS Log
788 %courses(Dallas)
MPRINT(COURSES): proc print data=perm.schedule;
MPRINT(COURSES): where upcase(location)="DALLAS";
MPRINT(COURSES): title "COURSES OFFERED AT DALLAS";
MPRINT(COURSES): run;
NOTE: There were 6 observations read from the data set
PERM.SCHEDULE.
WHERE UPCASE(location)='DALLAS';
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

789 %courses(LA)
Sorry, no courses taught at LA.

82

Parameter Validation
Use the %INDEX function to check the value of a macro
variable against a list of valid values.
General form of the %INDEX function:

%INDEX(argument1,
%INDEX(argument1,argument2)
argument2)

The %INDEX function

„ searches argument1 for the first occurrence of
argument2
„ returns an integer representing the position in
argument1 of the first character of argument2 if there
is an exact match
„ returns 0 if there is no match.
83
6-24 Module 6 Utilizing Macro Language Statements

Parameter Validation

%INDEX(argument1,
%INDEX(argument1,argument2)
argument2)

argument1 and argument2 can be

„ constant text

„ macro variable references

„ macro functions

„ macro calls.

84

Quick Quiz
What code is placed into the log by the following macro
program?

%macro courses(site);
%if %index(DALLAS SEATTLE BOSTON,&site) > 0
%then %do;
%put Courses taught at this location.;
%end;
%else %do;
%put Sorry, no courses taught at &site..;
%end;
%mend courses;
%courses(LA)

Unmute your phone and answer verbally.

85

Parameter Validation
Example: Parameter validation with the %INDEX function.
%macro courses(site);
%let site=%upcase(&site);
%let sitelist=*DALLAS*SEATTLE*BOSTON*;
%if %index(&sitelist,*&site*) > 0 %then %do;
proc print data=perm.schedule;
where upcase(location)="&site";
title "COURSES OFFERED AT &site";
run;
%end;
%else %do;
%put Sorry, no courses taught at &site..;
%put Valid locations are: &sitelist..;
%end;
%mend courses;
cond09
87
 6.2 Conditional Processing 6-25

Parameter Validation
Partial SAS Log
762 %courses(Dallas)
MPRINT(COURSES): proc print data=perm.schedule;
MPRINT(COURSES): where upcase(location)="DALLAS";
MPRINT(COURSES): title "COURSES OFFERED AT DALLAS";
MPRINT(COURSES): run;
NOTE: There were 6 observations read from the data set
PERM.SCHEDULE.
WHERE UPCASE(location)='DALLAS';
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

763 %courses(LA)
Sorry, no courses taught at LA.
Valid locations are: *DALLAS*SEATTLE*BOSTON*.

88

Parameter Validation
Example: Modify the previous program so that the macro
variable SITELIST is data-driven.
%macro courses(site);
%let site=%upcase(&site);
proc sql noprint;
select distinct upcase(location)
into :sitelist separated by '*'
from perm.schedule;
quit;
%let sitelist=&sitelist;
%if %index(*&sitelist*,*&site*) > 0
%then %do;
. . .
cond10
89

Developing Macro-Based Applications

If a macro-based application generates SAS code, use a
four-step development approach.
1. Write and debug the SAS program without any macro
coding.
2. Generalize the program by replacing hardcoded
constants with macro variable references. Initialize the
macro variables with %LET statements.
3. Create a macro definition by placing %MACRO and
%MEND statements around your program. Convert
%LET statements to macro parameters as appropriate.
4. Add macro-level programming statements such as
%IF-%THEN.

90
6-26 Module 6 Utilizing Macro Language Statements

6.3 Global and Local Symbol Table

The Global Symbol Table

The global symbol table is
„ created during the initialization of a SAS session or
noninteractive execution
„ initialized with automatic or system-defined macro
variables
„ deleted at the end of the session.

93

The Global Symbol Table

Macro variables in the global symbol table
„ are available anytime during the session

„ can be created by your program

„ have values that can be changed during the session

(except some automatic macro variables).

94
 6.3 Global and Local Symbol Table 6-27

The Global Symbol Table

Global Symbol Table

Variable Value
SYSDATE 23FEB04
SYSDAY Monday
SYSVER 9.1
. .
. .
. .
uservar1 value1
uservar2 value2

96

The Global Symbol Table

You can create a global macro variable with a
„ %LET statement (used outside a macro definition)

„ DATA step containing a SYMPUT routine

„ SELECT statement containing an INTO clause in

PROC SQL
„ %GLOBAL statement.

97

The Global Symbol Table

General form of the %GLOBAL statement:

%GLOBAL
%GLOBAL macrovar1
macrovar1macrovar2
macrovar2......;;

The %GLOBAL statement

„ creates one or more macro variables in the global
symbol table and assigns them null values
„ can be used inside or outside a macro definition

„ has no effect on variables already in the global table.

98
6-28 Module 6 Utilizing Macro Language Statements

The Local Symbol Table

A local symbol table is
„ an area of memory

„ created when a macro with a parameter list is

called or a local macro variable is created during
macro execution
„ deleted when the macro finishes execution.

A local table is not created unless and until a request is

made to create a local variable. Macros that do not create
local variables do not have a local table.

99

The Local Symbol Table

Local macro variables can be
„ created and initialized at macro invocation
(macro parameters)
„ created during macro execution

„ updated during macro execution

„ referenced anywhere within the macro.

100

The Local Symbol Table

The memory used by a local table can be reused when
the table is deleted after macro execution. Therefore, use
local variables instead of global variables whenever
possible.
Local Symbol Table

Variable Value
parameter1 value1
parameter2 value2
. .
. .
. .
uservar1 value1
uservar2 value2
101
 6.3 Global and Local Symbol Table 6-29

The Local Symbol Table

In addition to macro parameters, you can create local
macro variables with any of the following methods used
inside a macro definition:
„ %LET statement

„ DATA step containing a SYMPUT routine

„ SELECT statement containing an INTO clause in

PROC SQL
„ %LOCAL statement.

The SYMPUT routine creates local variables only if a local

table already exists.

102

The %LOCAL Statement

General form of %LOCAL statement:

%LOCAL
%LOCAL macrovar1
macrovar1macrovar2
macrovar2......;;

The %LOCAL statement

„ can appear only inside a macro definition

„ creates one or more macro variables in the local

symbol table and assigns them null values
„ has no effect on variables already in the local table.

104

The %LOCAL Statement

Declare the index variable of a macro loop as a local
variable to prevent the accidental contamination of macro
variables of the same name in the global table or other
local tables.
%macro putloop;
%local i;
%do i=1 %to &count;
%put TEACH&i is &&teach&i;
%end;
%mend putloop;

105
6-30 Module 6 Utilizing Macro Language Statements

The SYMPUTX Routine

The optional scope argument of the SYMPUTX routine
specifies where to store the macro variable:

CALL
CALL SYMPUTX(macro-variable,
SYMPUTX(macro-variable,text,
text,<scope>);
<scope>);

„ G specifies the global symbol table.

„ L specifies the most local of existing symbol tables,
which might be the global symbol table if no local
symbol table exists.

 The SYMPUTX routine is new in SAS®9.

106

Rules for Creating and Updating Variables

When the macro processor receives a request to create
or update a macro variable during macro execution, the
macro processor follows these rules:
Request during %LET MACVAR=VALUE;
macro call:
Macro Processor

Does MACVAR already exist Update MACVAR with VALUE

Yes
in the local table? in the local table.
No
Does MACVAR already exist Update MACVAR with VALUE
Yes
in the global table? in the global table.
No
Create MACVAR and assign it VALUE
in the local table.
107

Rules for Resolving Variables

To resolve a macro variable reference during macro
execution, the macro processor follows these rules:

Request during macro call: &MACVAR

Macro Processor

Does MACVAR exist in the Retrieve its value from the

Yes
local table? local table.
No
Does MACVAR exist in the Yes Retrieve its value from the
global table? global table.
No
Give the tokens back to the wordscanner.
Issue warning message in SAS log:
Apparent symbolic reference MACVAR not resolved.
108
 6.3 Global and Local Symbol Table 6-31

Quick Quiz
In the following program, is the macro variable i global or
local?
%let i=5;

%macro quiz;
%do i=1 %to 3;
%put Hi mom.;
%end;
%mend quiz;

%quiz

Please submit your answer as a text message.

109

Multiple Local Tables

Multiple local tables can exist concurrently during macro
execution.

Example: Define two macros. One calls the other.

%macro outer;
%local x; Create a global macro variable X.
%let x=1;
%inner
%mend outer; %let x=0;
%macro inner;
%local y; Global Table
%let y=&x; X 0
%mend inner;

111

Multiple Local Tables

Call the OUTER macro. When the %LOCAL statement
executes, a local table is created.

Global Table
%outer
X 0

%macro outer; OUTER Local Table

%local x;
%let x=1; X 1
%inner
%mend outer;
%macro inner;
%local y;
%let y=&x;
%mend inner;
What happens if the %LOCAL statement in the OUTER
112
macro is omitted?
6-32 Module 6 Utilizing Macro Language Statements

Multiple Local Tables

A nested macro call can create its own local symbol table,
in addition to any other tables that may currently exist.

Global Table
%macro outer; X 0
%local x;
%let x=1;
%inner OUTER Local Table
%mend outer;
%macro inner; X 1
%local y;
%let y=&x; INNER Local Table
%mend inner;
Y

113

Multiple Local Tables

The macro processor resolves a macro variable reference by
searching symbol tables in the reverse order in which they
were created:
1. current local table
2. previously created local tables
3. global table.
Global Table
%macro outer;
%local x; X 0
%let x=1; OUTER Local Table
%inner
%mend outer; X 1
%macro inner;
%local y; INNER Local Table
%let y=&x;
%mend inner;
Y 1

114

The global variable X is not available to the INNER macro.

Multiple Local Tables

When the INNER macro finishes execution, its local table
is deleted. Control passes back to the OUTER macro.

Global Table
%macro outer; X 0
%local x;
%let x=1; OUTER Local Table
%inner
%mend outer; X 1
%macro inner;
%local y;
%let y=&x;
%mend inner;

115
 6.3 Global and Local Symbol Table 6-33

Multiple Local Tables

When the OUTER macro finishes execution, its local
table is removed. Only the GLOBAL table remains.

Global Table
%macro outer; X 0
%local x;
%let x=1;
%inner
%mend outer;
%macro inner;
%local y;
%let y=&x;
%mend inner;

116

Multiple Local Tables

Example: Call the NUMOBS macro within the CHECK
macro to find the number of observations in a
subset of the perm.students data set.
Conditionally execute additional SAS code if the
subset contains any observations.
Call the macro to list students from different
companies.

117

Multiple Local Tables

%macro numobs(lib,dsn); Why is NUM
%global num; declared global in
%let num=0; the NUMOBS
proc sql noprint;
select (nobs-delobs) into :num macro? Is there
from dictionary.tables another solution?
where libname="%upcase(&lib)"
and memname="%upcase(&dsn)";
quit;
%let num=&num;
%mend numobs;
%macro check(comp);
data subset;
set perm.students;
where student_company="&comp";
run;
%numobs(work,subset)
%if &num>0 %then %do;
proc print data=subset noobs;
var student_name city_state;
title "&num Students from &comp";
run;
%end;
%else %put No students from &comp..;
118 %mend check; symbol1
6-34 Module 6 Utilizing Macro Language Statements

%macro check(comp);
data subset;
set perm.students;
where student_company="&comp";
run;
%numobs(work,subset)
%if &num>0 %then %do;
proc print data=subset noobs;
var student_name city_state;
title "&num Students from &comp";
run;
%end;
%else %put No students from &comp..;
%mend check;
%check(Reston Railway) Global Table

check Local Table

comp Reston Railway

119

Multiple Local Tables

%macro check(comp);
data subset;
set perm.students;
where student_company="&comp";
run;
%numobs(work,subset)
%if &num>0 %then %do;
proc print data=subset noobs;
var student_name city_state;
title "&num Students from &comp";
run;
%end; Global Table
%else %put No students from &comp..;
%mend check;
%check(Reston Railway)
check Local Table

comp Reston Railway

numobs Local Table

lib work
dsn subset
120

Multiple Local Tables

%macro numobs(lib,dsn);
%global num;
%let num=0;
proc sql noprint;
select (nobs-delobs) into :num
from dictionary.tables
where libname="%upcase(&lib)"
and memname="%upcase(&dsn)";
quit;
%let num=&num;
%mend numobs;
Global Table

num 0
check Local Table

comp Reston Railway

numobs Local Table
lib work
dsn subset
121
 6.3 Global and Local Symbol Table 6-35

Multiple Local Tables

%macro numobs(lib,dsn);
%global num;
%let num=0;
proc sql noprint;
select (nobs-delobs) into :num NUM is the number
from dictionary.tables
where libname="%upcase(&lib)" of observations
and memname="%upcase(&dsn)"; selected by this
quit;
%let num=&num; query.
%mend numobs;
Global Table

num 14
check Local Table

comp Reston Railway

numobs Local Table
lib work
dsn subset
122

Student Activity
%macro numobs(lib,dsn); Why is NUM
%*global num; declared global in
%let num=0; the NUMOBS
proc sql noprint;
select (nobs-delobs) into :num macro?
from dictionary.tables
where libname="%upcase(&lib)" Submit the
and memname="%upcase(&dsn)"; program
quit; sa-symbol1.sas.
%let num=&num; Notice the
%mend numobs;
%macro check(comp); %global num;
data subset; statement is now
set perm.students; a comment.
where student_company="&comp";
run; What happens?
%numobs(work,subset)
%if &num>0 %then %do;
proc print data=subset noobs;
var student_name city_state;
title "&num Students from &comp";
run;
%end;
%else %put No students from &comp..;
123 %mend check; sa-symbol1

Student Activity
%macro numobs(lib,dsn); Omit the * from
%global num; the %global num;
%let num=0; statement.
proc sql noprint;
select (nobs-delobs) into :num
from dictionary.tables Submit the
where libname="%upcase(&lib)" program
and memname="%upcase(&dsn)"; sa-symbol1.sas
quit; again.
%let num=&num;
%mend numobs;
%macro check(comp); What happens?
data subset;
set perm.students;
where student_company="&comp";
run;
%numobs(work,subset)
%if &num>0 %then %do;
proc print data=subset noobs;
var student_name city_state;
title "&num Students from &comp";
run;
%end;
%else %put No students from &comp..;
125 %mend check; symbol1
6-36 Module 6 Utilizing Macro Language Statements

%macro check(comp);
data subset;
set perm.students;
where student_company="&comp";
NUMOBS has finished
run; execution. Therefore, its
%numobs(work,subset)
%if &num>0 %then %do; local symbol table is
proc print data=subset noobs; deleted.
var student_name city_state;
title "&num Students from &comp";
run;
%end;
%else %put No students from &comp..;
%mend check;
%check(Reston Railway) Global Table

NUM still exists num 14

because it was placed check Local Table
into the global table.
comp Reston Railway

126

%macro check(comp);
data subset; The values of &num
set perm.students;
where student_company="Reston Railway"; and &comp are
run; substituted into the
%numobs(work,subset)
%if 14>0 %then %do; program.
proc print data=subset noobs;
var student_name city_state;
title "14 Students from Reston Railway";
run;
%end;
%else %put No students from Reston Railway.;
%mend check;
%check(Reston Railway) Global Table

num 14
CHECK Local Table

CHECK local table will be comp Reston Railway

deleted when the CHECK
macro finishes execution.
127

Multiple Local Tables

Partial SAS Log
174 %check(Reston Railway)

NOTE: There were 14 observations read from the data set PERM.STUDENTS.
WHERE student_company='Reston Railway';
NOTE: The data set WORK.SUBSET has 14 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds
cpu time 0.01 seconds

NOTE: There were 14 observations read from the data set WORK.SUBSET.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

128
 6.3 Global and Local Symbol Table 6-37

Multiple Local Tables

Partial SAS Log

175 %check(Raston Railway)

NOTE: There were 0 observations read from the data set PERM.STUDENTS.
WHERE student_company='Raston Railway';
NOTE: The data set WORK.SUBSET has 0 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds
cpu time 0.00 seconds

No students from Raston Railway.

129

Refer to Exercise 3 for Module 6 in Appendix A.

Module 6 Summary
„ Conditionally process SAS code within a macro
program.
„ Monitor macro execution.
„ Insert entire steps, entire statements, and partial
statements into a SAS program.
„ Execute macro language statements iteratively.
„ Generate SAS code iteratively.
„ Explain the difference between global and local symbol
tables.
„ Describe how the macro processor decides which
symbol table to use.
„ Describe the concept of nested macros and the
hierarchy of symbol tables.
132
Appendix A Exercises and Solutions

Session 1 ................................................................................................................................. A-2

Module 2 Exercises............................................................................................................................ A-2
After-Class Exercises......................................................................................................................... A-3
Module 2 Solutions to Exercises ....................................................................................................... A-5
After-Class Solutions to Exercises .................................................................................................... A-9

Session 2 ............................................................................................................................... A-11

Module 3 Exercises.......................................................................................................................... A-11
Module 4 Exercises.......................................................................................................................... A-12
After-Class Exercises....................................................................................................................... A-13
Module 3 Solutions to Exercises ..................................................................................................... A-14
Module 4 Solutions to Exercises ..................................................................................................... A-17
After-Class Solutions to Exercises .................................................................................................. A-21

Session 3 ............................................................................................................................... A-23

Module 5 Exercises.......................................................................................................................... A-23
After-Class Exercises....................................................................................................................... A-24
Module 5 Solutions to Exercises ..................................................................................................... A-26
After-Class Solutions to Exercises .................................................................................................. A-32

Session 4 ............................................................................................................................... A-39

Module 6 Exercises.......................................................................................................................... A-39
Module 6 Solutions to Exercises ..................................................................................................... A-41
A-2 Appendix A Exercises and Solutions

Session 1

Module 2 Exercises
1. Using Automatic Macro Variables
Open the babbit program shown below into the Editor window.
options nocenter;
proc print data=perm.all noobs label uniform;
where student_name contains 'Babbit';
by student_name student_company;
var course_title begin_date location teacher;
title 'Courses Taken by Selected Students:';
title2 'Those with Babbit in Their Name';
run;
Add a footnote that displays today's date (use an automatic macro variable) using this text:
Report Created on date
Submit the program and examine the output it creates.
2. Displaying Automatic Macro Variables
Use the %PUT statement to display the values of the SYSDAY and SYSVER macro variables in the
SAS log.
3. Defining and Using Macro Variables
a. Open the babbit program shown below into the Editor window. Submit the program and
examine the output it creates.
options nocenter;
proc print data=perm.all noobs label uniform;
where student_name contains 'Babbit';
by student_name student_company;
var course_title begin_date location teacher;
title 'Courses Taken by Selected Students:';
title2 'Those with Babbit in Their Name';
run;
b. Edit the program to change the search pattern in the WHERE statement and TITLE2 statement
from Babbit to Ba and resubmit. Examine the output.

c. Modify the program so that the two occurrences of Ba are replaced by references to the macro
variable PATTERN. Precede the program with a %LET statement to assign the value Ba to
PATTERN. Submit the program. It produces the same output as before.
d. Submit a %PUT statement to display the value of all user-defined macro variables including
PATTERN.
 0 Session 1 A-3

4. Delimiting Macro Variable Names

a. Open the program contents that uses PROC CONTENTS to display all of the data sets in a
library.
b. Create a new macro variable named DS that enables you to substitute a different name for the data
set in the PROC CONTENTS program.
c. Create another macro variable named LIB that enables you to substitute a different name for the
library reference in the PROC CONTENTS program.
d. Add a macro variable named OPT for the PROC CONTENTS option NODS. The macro variable
should enable you to include or exclude the option from the program.
contents.sas program:
proc contents data=perm._all_ nods;
run;

After-Class Exercises
1. Macro Variable References
Open the program countloc shown below into the Editor window.
title;
proc sql;
select location,n(location) label='Count'
from perm.schedule,perm.register
where schedule.course_number=
register.course_number
group by location;
quit;
a. Submit the program. The SELECT statement creates a listing from two SAS data sets (tables) that
are merged (joined) by the common variable course_number. The GROUP BY clause
reduces the listing to distinct values of location. The N function counts the number of
observations that are within distinct values of the GROUP BY variable.
A-4 Appendix A Exercises and Solutions

b. Modify the program so that it contains references to these macro variables:

TABLE1 second-level name of one input data set

TABLE2 second-level name of the other input data set

JOINVAR name of variable common to both input data sets

FREQVAR name of the GROUP BY variable.

Precede the program with %LET statements that initialize these macro variables to the values
currently in the program. Submit the program and compare the listing with the one created earlier.
It should produce the same output as before.
c. Edit the program to change the values of the macro variables to create a listing from the
perm.students and perm.register data sets that shows the distribution of the
city_state variable. The two data sets have the student_name variable in common (join
variable).
d. Save the program for an after-class exercise tomorrow. Name the program AfterClass1.
2. Using the %SYMDEL Statement
a. Use the %SYMDEL statement to remove the FREQVAR and JOINVAR macro variables from the
global symbol table.
b. Display a list of all the user-defined macro variables that are currently in the global symbol table.
1) Do you see the macro variables you deleted in this list?
2) Do you think it is possible to delete all of the user-defined macro variables from the global
symbol table?
 0 Session 1 A-5

Module 2 Solutions to Exercises

1. Using Automatic Macro Variables (solution program s-refer1.sas)
The automatic macro variable SYSDATE9 contains the date when the current SAS session was
invoked. The footnote text must be enclosed in double quotes for the macro variable reference to be
resolved.
options nocenter;
proc print data=perm.all noobs label uniform;
where student_name contains 'Babbit';
by student_name student_company;
var course_title begin_date location teacher;
title 'Courses Taken by Selected Students';
title2 'Those with Babbit in Their Name';
footnote "Report Created on &sysdate9";
run;

Courses Taken by Selected Students

Those with Babbit in Their Name

Student Name=Babbitt, Mr. Bill Company=National Credit Corp.

Description Begin Location Instructor

Basic Telecommunications 24MAY2005 Dallas Hallis, Dr. George

Artificial Intelligence 01MAR2005 Dallas Hallis, Dr. George
Computer Aided Design 28MAR2006 Dallas Berthan, Ms. Judy

Report Created on 05FEB2004

A-6 Appendix A Exercises and Solutions

2. Displaying Automatic Macro Variables (solution program s-refer2.sas)

Macro variable references are resolved before the text of the %PUT statement is displayed in the log.
%put Today is a &sysday;
%put This is Release &sysver of the SAS System;
Partial SAS Log
61 %put Today is a &sysday;
Today is a Thursday
62 %put This is Release &sysver of the SAS System;
This is Release 9.1 of the SAS System

3. Defining and Using Macro Variables (solution program is s-var.sas)

a. Bill Babbitt is the only student whose name contains the text string Babbit.
proc print data=perm.all noobs label uniform;
where student_name contains 'Babbit';
by student_name student_company;
var course_title begin_date location teacher;
title 'Courses Taken by Selected Students';
title2 'Those with Babbit in Their Name';
run;

Courses Taken by Selected Students

Those with Babbit in Their Name

- Student Name=Babbitt, Mr. Bill Company=National Credit Corp. -

Description Begin Location Instructor

Basic Telecommunications 24MAY2005 Dallas Hallis, Dr. George

Artificial Intelligence 01MAR2005 Dallas Hallis, Dr. George
Computer Aided Design 28MAR2006 Dallas Berthan, Ms. Judy
 0 Session 1 A-7

b. There are four students whose name contains the text string Ba: Bill Babbit, Vincent Baker,
Ellen Bates, and Barbara Turner.
proc print data=perm.all noobs label uniform;
where student_name contains 'Ba';
by student_name student_company;
var course_title begin_date location teacher;
title 'Courses Taken by Selected Students';
title2 'Those with Ba in Their Name';
run;
Partial Output
Courses Taken by Selected Students
Those with Ba in Their Name

- Student Name=Babbitt, Mr. Bill Company=National Credit Corp. -

Description Begin Location Instructor

Basic Telecommunications 24MAY2005 Dallas Hallis, Dr. George

Artificial Intelligence 01MAR2005 Dallas Hallis, Dr. George
Computer Aided Design 28MAR2006 Dallas Berthan, Ms. Judy

-- Student Name=Baker, Mr. Vincent Company=Snowing Petroleum ---

Description Begin Location Instructor

Structured Query Language 14JUN2005 Boston Wickam, Dr. Alice

----- Student Name=Bates, Ms. Ellen Company=Reston Railway -----

Description Begin Location Instructor

Basic Telecommunications 24MAY2005 Dallas Hallis, Dr. George

Database Design 25JAN2005 Seattle Tally, Ms. Julia
Computer Aided Design 28MAR2006 Dallas Berthan, Ms. Judy

Student Name=Turner, Ms. Barbara Company=Gravely Finance Center

Description Begin Location Instructor

Structured Query Language 06DEC2005 Seattle Wickam, Dr. Alice

Computer Aided Design 28MAR2006 Dallas Berthan, Ms. Judy
A-8 Appendix A Exercises and Solutions

c. The macro variable PATTERN should contain the text string Ba without any surrounding quotes.
To resolve the macro variable in the WHERE and TITLE2 statement, change the single quotes to
double quotes.
%let pattern=Ba;
options nocenter;
proc print data=perm.all noobs label uniform;
where student_name contains "&pattern";
by student_name student_company;
var course_title begin_date location teacher;
title 'Courses Taken by Selected Students';
title2 "Those with &pattern in Their Name";
run;
d. A %PUT statement can verify that the macro variable PATTERN contains the text string Ba. The
_USER_ argument displays the values of all user-defined macro variables:
%put _user_;
Partial SAS Log
108 %put _user_;
GLOBAL PATTERN Ba

4. Delimiting Macro Variable Names (solution program is s-contents.sas)

a.
proc contents data=perm._all_ nods;
run;
b.
%let ds=_all_;
proc contents data=perm.&ds nods;
run;
c.
%let lib=perm;
%let ds=_all_;
proc contents data=&lib..&ds;
run;
d.
%let opt=;
%let lib=perm;
%let ds=_all_;
proc contents data=&lib..&ds &opt;
run;
 0 Session 1 A-9

After-Class Solutions to Exercises

1. Macro Variable References and Macro Definitions (solution program s-delim.sas)
a. The original program produces this output:
SAS Output
Location Count
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
Boston 150
Dallas 133
Seattle 151

b. The references to the input data set names in the WHERE clause are followed by two periods,
the first acting as the macro variable name delimiter and the second received by the compiler as
part of the two-level column name.
%let table1=schedule;
%let table2=register;
%let joinvar=course_number;
%let freqvar=location;
title;
proc sql;
select &freqvar,n(&freqvar) label='Count'
from perm.&table1,perm.&table2
where &table1..&joinvar=&table2..&joinvar
group by &freqvar;
quit;
SAS Output
Location Count
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
Boston 150
Dallas 133
Seattle 151

c. The only changes required are new values assigned to the macro variables in the %LET
statements.
%let table1=students;
%let table2=register;
%let joinvar=student_name;
%let freqvar=city_state;
title;
proc sql;
select &freqvar,n(&freqvar) label='Count'
from perm.&table1,perm.&table2
where &table1..&joinvar=&table2..&joinvar
group by &freqvar;
quit;
A-10 Appendix A Exercises and Solutions

Partial Output
City,State Count
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
Akron, OH 5
Albany, NY 2
Allentown, PA 3
Annapolis, MD 7
Atlanta, GA 7
Austin, TX 3
Bethesda, MD 1
Birmingham, AL 2
Bozeman, MT 10
Brea, CA 2
Buena Park, CA 1
Chicago, IL 71
Chicago, IN 2
Cincinnati, OH 1
Cleveland, OH 3
Columbia, MD 4
Columbus, OH 8
Costa Mesa, CA 9
Cupertino, CA 2
Dallas, TX 8

d. Select File Ö Save As from your pull-down menu. Type the name of the program,
AfterClass1 in the File Name field.

2. Using the %SYMDEL Statement (solution program s-symdel)

a. Use the %SYMDEL statement to remove the FREQVAR and JOINVAR macro variables from
the global symbol table.
%symdel freqvar joinvar;
b. Display a list of all the user-defined macro variables that are currently in the global symbol table.
%put _user_;
1) Do you see the macro variables you deleted in this list? No
2) Do you think it is possible to delete all of the user-defined macro variables from the global
symbol table? Yes, it is. An example is given later in the course.
 0 Session 2 A-11

Session 2
If you are starting a new SAS session, remember to submit a LIBNAME statement.
libname perm '.'; *Virtual lab;
libname perm 'C:\SAS_Education\LWMACR'; *working on local PC;

Module 3 Exercises
1. Using Macro Functions
a. Submit the program sortsched shown below to create the work.sorted data set:

proc sort data=perm.schedule out=work.sorted;

by course_number begin_date;
run;

b. Open the program dictcols shown below into the Editor window and submit it. This program
uses a PROC SQL dictionary table to display the variables in a specified data set.
title "Variables in PERM.SCHEDULE";
proc sql;
select name, type, length
from dictionary.columns
where libname="PERM" and
memname="SCHEDULE";
quit;
c. Add a %LET statement to assign the value perm.schedule to a macro variable named DSN.
Use the new macro variable in the TITLE statement. Use one or more macro functions to separate
the value of DSN into the library reference and the data set name for substitution into the
WHERE clause. Submit the modified program. You should get the same report.
d. Change the %LET statement to assign the value perm.courses to the DSN macro variable.
Submit the modified program to see the new report.
e. Change the %LET statement to assign the value of the automatic macro variable SYSLAST to the
DSN macro variable. Submit the modified program to see the new report. What was the value of
SYSLAST? When was that data set created?
A-12 Appendix A Exercises and Solutions

2. The %BQUOTE Function

a. Submit the following statements in the Enhanced Editor window. (nobquote.sas)
%let finit=S;
%let minit= ;
%let linit=F;
%put &finit&minit&linit;
What do you see in the SAS log as a result of the %PUT statement?
______________________________________________________________________________
b. Correct the program using %BQUOTE so that the space is retained between the first and last
initials.

Module 4 Exercises
1. Defining and Calling a Macro
Open the printnum program into the Editor window. The printnum program contains this PROC
PRINT step:
proc print data=perm.all label noobs n;
where course_number=3;
var student_name student_company;
title "Enrollment for Course 3";
run;
a. Change the hardcoded 3 in the WHERE and TITLE statements to reference the macro variable
NUM. Convert this program into a macro. Submit the macro definition to compile the macro.
b. Submit a %LET statement to assign the value 8 to the macro variable NUM. Then, call the macro
defined in the previous step.
c. Activate the appropriate system options to display the source code received by the SAS compiler
2. Defining and Using Macro Parameters
Open the printnum program into the Editor window or modify the program you created in
Exercise 1.
proc print data=perm.all label noobs n;
where course_number=3;
var student_name student_company;
title "Enrollment for Course 3";
run;
a. Change the hardcoded 3 in WHERE and TITLE statements to reference the macro variable NUM.
Convert this program into a macro with a positional parameter. Select a name for the parameter
based on the macro variable references in the program. Submit the macro definition to compile
the macro.
b. Activate the appropriate system option to display the source code received by the SAS compiler.
Call the macro defined in the previous step with a value of 8 for the parameter.
 0 Session 2 A-13

c. Call the macro again, but with a parameter value of 10.

d. Change the positional parameter to a keyword parameter with a default value of 1. Submit the
revised macro definition to compile the macro.
e. Call the macro defined in the previous step with a value of 8 for the keyword parameter.
f. Call the macro again, but allow the macro to use its default parameter value.

After-Class Exercises
1. Using Keyword Parameters
Open the AfterClass1 program saved during a previous session.
a. Convert the program into a macro with keyword parameters. Specify the default values for the
parameters according to the values in the original query from the countloc program. Submit the
macro definition to compile the macro.
b. Call the macro with the default values supplied in the macro definition.
c. Call the macro again. Modify the values in your macro call in order to execute the query for the
values specified below.
Table1 students
Joinvar student_name
Freqvar city_state
2. Using Macro Quoting Functions
a. Retrieve the babbit program shown below into the Editor window. Change the name Babbit in
the WHERE statement and the TITLE2 statement to O'Savio. Remember to change the quotes
to double quotes.
Submit the modified program.
options nocenter;
proc print data=perm.all noobs label uniform;
where student_name contains 'Babbit';
by student_name student_company;
var course_title begin_date location teacher;
title 'Courses Taken by Selected Students:';
title2 'Those with Babbit in Their Name';
run;
b. Modify the program so that the two occurrences of O'Savio are replaced by references to the
macro variable PATTERN. Precede the program with a %LET statement to assign the value
O'Savio to PATTERN. Add a %PUT statement to display the value of the PATTERN macro
variable.
Submit the program. It should produce the same output as before.
A-14 Appendix A Exercises and Solutions

Module 3 Solutions to Exercises

1. Using Macro Functions (solution program s-func.sas)
a. Submit the program sortsched shown below to create the work.sorted data set:

proc sort data=perm.schedule out=work.sorted;

by course_number begin_date;
run;

b. Open the dictcols program shown below into the Editor window and submit it. This program
uses a PROC SQL dictionary table to display the variables in a specified data set.
title "Variables in PERM.SCHEDULE";
proc sql;
select name, type, length
from dictionary.columns
where libname="PERM" and
memname="SCHEDULE";
quit;
c. The %SCAN function can divide the value of the macro variable DSN into parts. The default
delimiter set will work for this example; however, the single applicable delimiter, the period (.),
can be specified as the third argument to %SCAN.

The %UPCASE function may be required, because the values of LIBNAME and MEMNAME in the
DICTIONARY.COLUMNS table are in uppercase.
%let dsn=perm.schedule;
%let libref=%upcase(%scan(&dsn,1,.));
%let dsname=%upcase(%scan(&dsn,2,.));
title "Variables in %upcase(&dsn)";
proc sql;
select name, type, length
from dictionary.columns
where libname="&libref" and
memname="&dsname";
quit;
SAS Output
Variables in PERM.SCHEDULE

Column Column
Column Name Type Length
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
Course_Number num 8
Course_Code char 4
Location char 15
Begin_Date num 8
Teacher char 20
 0 Session 2 A-15

Alternate Solution
%let dsn=perm.schedule;
title "Variables in %upcase(&dsn)";
proc sql;
select name, type, length
from dictionary.columns
where libname="%upcase(%scan(&dsn,1,.))" and
memname="%upcase(%scan(&dsn,2,.))";
quit;
SAS Output
Variables in PERM.SCHEDULE

Column Column
Column Name Type Length
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
Course_Number num 8
Course_Code char 4
Location char 15
Begin_Date num 8
Teacher char 20

d. Changing the value of the macro variable DSN automatically changes which data set is analyzed.
%let dsn=perm.courses;
%let libref=%upcase(%scan(&dsn,1,.));
%let dsname=%upcase(%scan(&dsn,2,.));
title "Variables in %upcase(&dsn)";
proc sql;
select name, type, length
from dictionary.columns
where libname="&libref" and
memname="&dsname";
quit;
SAS Output
Variables in PERM.COURSES

Column Column
Column Name Type Length
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
Course_Code char 4
Course_Title char 25
Days num 8
Fee num 8
A-16 Appendix A Exercises and Solutions

e. The value of the macro variable SYSLAST is assigned as the value of the macro variable DSN,
so the work.sorted data set is analyzed. The work.sorted data set was created in the
PROC SORT step above.
%let dsn=&syslast;
%let libref=%upcase(%scan(&dsn,1,.));
%let dsname=%upcase(%scan(&dsn,2,.));
title "Variables in %upcase(&dsn)";
proc sql;
select name, type, length
from dictionary.columns
where libname="&libref" and
memname="&dsname";
quit;

SAS Output
Variables in WORK.SORTED

Column Column
Column Name Type Length
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
Course_Number num 8
Course_Code char 4
Location char 15
Begin_Date num 8
Teacher char 20

2. The %BQUOTE Function (solution program s-bquote.sas)

a. Submit the following statements in the Enhanced Editor window. (nobquote.sas)
%let finit=S;
%let minit= ;
%let linit=F;
%put &finit&minit&linit;
What do you see in the SAS log as a result of the %PUT statement?
__SF__________________________________________________________________________

b. Correct the program using %BQUOTE so that the space is retained between the first and last
initials.
__S F_________________________________________________________________________

%let finit=S;
%let minit=%bquote( );
%let linit=F;
%put &finit&minit&linit;
 0 Session 2 A-17

Module 4 Solutions to Exercises

1. Defining and Calling a Macro (solution program s-macro.sas)
a. %MACRO and %MEND statements surround the PROC PRINT step to create a macro program.
%macro printnum;
proc print data=perm.all label noobs n;
where course_number=&num;
var student_name student_company;
title "Enrollment for Course &num";
run;
%mend printnum;
b. To execute the macro, use a percent sign followed by the name of the macro. The value of the
macro variable NUM will be resolved during word scanning, after the text of the program is
copied to the input stack.
%let num=8;
%printnum
Partial SAS Log
173 %macro printnum;
174 proc print data=perm.all label noobs n;
175 where course_number=&num;
176 var student_name student_company;
177 title "Enrollment for Course &num";
178 run;
179 %mend printnum;
180 %let num=8;
181 %printnum

NOTE: There were 20 observations read from the dataset PERM.ALL.

WHERE course_number=8;
NOTE: PROCEDURE PRINT used:
real time 11.18 seconds
cpu time 0.12 seconds
A-18 Appendix A Exercises and Solutions

Partial Output
Enrollment for Course 8
Student Name Company

Baker, Mr. Vincent Snowing Petroleum

Blayney, Ms. Vivian Southern Gas Co.
Boyd, Ms. Leah United Shoes Co.
Chevarley, Ms. Arlene Motor Communications
Coley, Mr. John California Dept. of Insurance
Crace, Mr. Ron Von Crump Seafood
Garza, Ms. Cheryl Admiral Research & Development Co.
Hamilton, Mr. Paul Imperial Steel
Huels, Ms. Mary Frances Basic Home Services
Kendig, Ms. Linda Crossbow of California
Knight, Ms. Susan K&P Products
Koleff, Mr. Jim Emulate Research
Leon, Mr. Quinton Dept. of Defense
Lochbihler Mr. Mark K&P Products
Nicholson, Ms. Elizabeth Silver, Sachs & Co.
Purvis, Mr. Michael Roam Publishers
Ramsey, Ms. Kathleen Pacific Solid State Corp.
Shipman, Ms. Jan Southern Edison Co.
Sulzbach, Mr. Bill Sailbest Ships
Woods, Mr. Joseph Federal Landmarks

N = 20

c. To display the code received by the SAS compiler, including all resolved macro variable
references, use the MPRINT system option. To track the resolution of macro variables, use the
SYMBOLGEN system option.
options mprint symbolgen;
%printnum
Partial SAS Log
182 options mprint symbolgen;
183 %printnum
MPRINT(PRINTNUM): proc print data=perm.all label noobs n;
SYMBOLGEN: Macro variable NUM resolves to 8
MPRINT(PRINTNUM): where course_number=8;
MPRINT(PRINTNUM): var student_name student_company;
SYMBOLGEN: Macro variable NUM resolves to 8
MPRINT(PRINTNUM): title "Enrollment for Course 8";
MPRINT(PRINTNUM): run;
NOTE: There were 20 observations read from the dataset PERM.ALL.
WHERE course_number=8;
NOTE: PROCEDURE PRINT used:
real time 11.64 seconds
cpu time 0.14 seconds
 0 Session 2 A-19

2. Defining and Using Macro Parameters (solution program s-param.sas)

a. The macro parameter name should be NUM because the program contains the macro references
&num. When you define positional parameters, enclose the names of the parameter in parentheses
following the macro name.
%macro prtrost(num);
proc print data=perm.all label noobs n;
where course_number=&num;
var student_name student_company;
title "Enrollment for Course &num";
run;
%mend prtrost;
b. To display the code received by the SAS compiler, including all resolved macro variable
references, use the MPRINT system option. To execute the macro, use a percent sign followed by
the name of the macro. To assign a value to a positional parameter, supply the desired value
within parentheses following the macro name.
options mprint;
%prtrost(8)
Partial SAS Log
200 %prtrost(8)
MPRINT(PRTROST): proc print data=perm.all label noobs n;
MPRINT(PRTROST): where course_number=8;
MPRINT(PRTROST): var student_name student_company;
MPRINT(PRTROST): title "Enrollment for Course 8";
MPRINT(PRTROST): run;
NOTE: There were 20 observations read from the dataset PERM.ALL.
WHERE course_number=8;
NOTE: PROCEDURE PRINT used:
real time 11.05 seconds
cpu time 0.16 seconds
A-20 Appendix A Exercises and Solutions

Partial Output
Enrollment for Course 8

Student Name Company

Baker, Mr. Vincent Snowing Petroleum

Blayney, Ms. Vivian Southern Gas Co.
Boyd, Ms. Leah United Shoes Co.
Chevarley, Ms. Arlene Motor Communications
Coley, Mr. John California Dept. of Insurance
Crace, Mr. Ron Von Crump Seafood
Garza, Ms. Cheryl Admiral Research & Development Co.
Hamilton, Mr. Paul Imperial Steel
Huels, Ms. Mary Frances Basic Home Services
Kendig, Ms. Linda Crossbow of California
Knight, Ms. Susan K&P Products
Koleff, Mr. Jim Emulate Research
Leon, Mr. Quinton Dept. of Defense
Lochbihler Mr. Mark K&P Products
Nicholson, Ms. Elizabeth Silver, Sachs & Co.
Purvis, Mr. Michael Roam Publishers
Ramsey, Ms. Kathleen Pacific Solid State Corp.
Shipman, Ms. Jan Southern Edison Co.
Sulzbach, Mr. Bill Sailbest Ships
Woods, Mr. Joseph Federal Landmarks

N = 20

c. The macro definition does not need to be resubmitted with each macro call. The macro call does
not end with a semicolon.
%prtrost(10)
Partial SAS Log
MPRINT(PRTROST): proc print data=perm.all label noobs n;
MPRINT(PRTROST): where course_number=10;
MPRINT(PRTROST): var student_name student_company;
MPRINT(PRTROST): title "Enrollment for Course 10";
MPRINT(PRTROST): run;
NOTE: There were 23 observations read from the dataset PERM.ALL.
WHERE course_number=10;
NOTE: PROCEDURE PRINT used:
real time 11.44 seconds
cpu time 0.17 seconds

d. When you define keyword parameters, an equal sign (=) must follow the name of each parameter.
A default value for each parameter can be specified following the equal sign.
%macro prtrost(num=1);
proc print data=perm.all label noobs n;
where course_number=&num;
var student_name student_company;
title "Enrollment for Course &num";
run;
%mend prtrost;
 0 Session 2 A-21

e. To assign a value to a keyword parameter, specify the name of the parameter followed by an equal
sign (=), followed by the desired value.
%prtrost(num=8)
Partial SAS Log
18 %prtrost(num=8)
MPRINT(PRTROST): proc print data=perm.all label noobs n;
MPRINT(PRTROST): where course_number=8;
MPRINT(PRTROST): var student_name student_company;
MPRINT(PRTROST): title "Enrollment for Course 8";
MPRINT(PRTROST): run;
NOTE: There were 20 observations read from the dataset PERM.ALL.
WHERE course_number=8;
NOTE: PROCEDURE PRINT used:
real time 10.51 seconds
cpu time 0.12 seconds

f. To request that all default parameter values be used, follow the macro call with an empty set of
parentheses.
%prtrost()
Partial SAS Log
19 %prtrost()
MPRINT(PRTROST): proc print data=perm.all label noobs n;
MPRINT(PRTROST): where course_number=1;
MPRINT(PRTROST): var student_name student_company;
MPRINT(PRTROST): title "Enrollment for Course 1";
MPRINT(PRTROST): run;
NOTE: There were 23 observations read from the dataset PERM.ALL.
WHERE course_number=1;
NOTE: PROCEDURE PRINT used:
real time 13.20 seconds
cpu time 0.15 seconds

After-Class Solutions to Exercises

1. Using Keyword Parameters (solution program s-AfterClass2.sas)
a. Convert the program into a macro with keyword parameters. Specify the default values for the
parameters according to the values in the original query from the countloc program. Submit the
macro definition to compile the macro.
title;
%macro freq_report(table1=schedule,table2=register,
joinvar=course_number,freqvar=location);
proc sql;
select &freqvar,n(&freqvar) label='Count'
from perm.&table1,perm.&table2
where &table1..&joinvar=&table2..&joinvar
group by &freqvar;
quit;
%mend freq_report;
A-22 Appendix A Exercises and Solutions

b. Call the macro with the default values supplied in the macro definition.
%freq_report( )
c. Call the macro again. Modify the values in your macro call in order to execute the query for the
values specified below.
Table1 students
Joinvar student_name
Freqvar city_state
%freq_report(table1=students,joinvar=student_name,freqvar=city_state)
2. Using Macro Quoting Functions (solution program s-bquote2.sas)
a. Double quotes are needed to handle the embedded single quotes (apostrophe) in the name
O'Savio when it is used in the literal text.
proc print data=perm.all noobs label uniform;
where student_name contains "O'Savio";
by student_name student_company;
var course_title begin_date location teacher;
title 'Courses Taken by Selected Students:';
title2 "Those with O'Savio in Their Name";
run;
b. The %BQUOTE function is needed to handle the apostrophe in the name O'Savio when
assigning it as the value of a macro variable.
%let pattern=%BQUOTE(O'Savio);

proc print data=perm.all noobs label uniform;

where student_name contains "&pattern";
by student_name student_company;
var course_title begin_date location teacher;
title 'Courses Taken by Selected Students:';
title2 "Those with &pattern in Their Name";
run;

%put The value of PATTERN is &pattern;

 0 Session 3 A-23

Session 3
If you are starting a new SAS session, remember to submit a LIBNAME statement.
libname perm '.'; *Virtual lab;
libname perm 'C:\SAS_Education\LWMACR'; *working on local PC;

Module 5 Exercises
1. Creating Macro Variables with the SYMPUT Routine
a. Reset the system option DATE|NODATE to NODATE using the OPTIONS statement:
options nodate;
You also may want to activate the SYMBOLGEN option.
b. Write a DATA step that creates a macro variable named DATE from the perm.schedule data
set that contains the begin_date variable. This date is the starting date for each course. This
macro variable's value should be the begin_date in MMDDYY10. format, where
course_number=15.

c. Insert the value of the macro variable DATE into a TITLE statement:
title "Roster for Course Offered on &date";
d. Verify that the text of the title resolved correctly by printing the roster for course_number=15
found in the perm.register data set or by opening the TITLES window.

e. Modify the DATA step so that the macro variable DATE has a value that reflects the
WORDDATE20. format (month dd, year).

Verify the text of the title again. Make sure there are no extra blanks in the title.
2. Creating Multiple Macro Variables with the SYMPUT Routine
a. The perm.schedule data set contains the variable begin_date, which contains the starting
date of each course. Use a DATA step to create a series of macro variables named START1
through STARTn, one for each course offered. The value of each START macro variable should
be the starting date of the corresponding class in the MMDDYY10. format.
b. Open the prtrost program shown below into the Editor window. Modify the TITLE statement
so the series of Xs is replaced with an indirect macro variable reference to one of the START
variables based on the current value of CRS. Submit the modified program.
%let crs=4;
proc print data=perm.all noobs n;
where course_number=&crs;
var student_name student_company;
title1 "Roster for Course &crs";
title2 "Beginning on XXXXX";
run;
A-24 Appendix A Exercises and Solutions

3. Creating Multiple Macro Variables Using SQL

a. The perm.schedule data set contains the variable begin_date, which holds the starting
date of each course for 18 classes. Use the SQL procedure to create a set of macro variables
named DATE1 through DATE18. The value of each DATE macro variable should be in
MMDDYY10. format.
b. Open the sqlrost program shown below. Modify the TITLE statement so that the series of Xs
are replaced with the appropriate indirect macro variable references based on the current value of
NUM, which represents the course number (1 through 18). Submit the modified program.
%let num=4;
proc print data=perm.all noobs n;
where course_number=&num;
var student_name student_company;
title "Roster for Course &num Beginning on XXXXXX";
run;
c. (Optional)
Complete parts a and b of this exercise without the explicit knowledge of the number of classes
in the perm.schedule data set. You will need to study and review the code in sql3 program to
complete this exercise.

After-Class Exercises
1. Generating Multiple Steps with Macro Loops
a. Define a macro with positional parameters that can print a series of reports, each report containing
observations having a particular value for a selected variable. For example, because the
perm.schedule data set contains six distinct values for course_code, the macro should
produce six reports, one for each distinct value of course_code.

The macro should generate six separate PROC PRINT steps for each distinct value of
course_code.
proc print data=perm.schedule noobs;
where course_code="C001";
title "Listing of PERM.SCHEDULE Data Set";
title2 "for COURSE_CODE=C001";
run;
Parameters for the macro are
• data set to be printed
• variables used for subsetting.
b. Use the macro to generate a separate report for each value of course_code in the
perm.schedule data set.

c. Use the macro to generate a separate report for each value of location in the
perm.schedule data set.
 0 Session 3 A-25

2. Using Macro Variables in the DATA Step (Optional)

Read Appendix B.2 and then complete Exercises 3 & 4.
3. Resolving Macro Variables with the SYMGET Function (Optional)
Retrieve the starts program shown below and submit it to create a series of macro variables
containing the starting date for each course.

data _null_;
set perm.schedule;
call symput('StartDate'||trim(left(course_number)),
put(begin_date,mmddyy10.));
run;

a. Display the values of the newly created macro variables in the SAS log.
b. Create a temporary data set named outstand containing the students in the perm.register
data set who have not yet paid their registration fee. Create a new variable that indicates the
starting date for the corresponding course number. Print the outstand data set.

 The INPUT function is needed to convert character values of macro variables retrieved
by the SYMGET function into numeric SAS data values.
4. Macro Variable Storage and Resolution (Optional)
Determine the type, length, and value of the DATA step variables in the program below.
%let var1=cat;
%let var2=3;
data test;
length s1 s4 s5 $ 3;
call symput('var3','dog');
r1="&var1";
r2=&var2;
r3="&var3";
s1=symget('var1');
s2=symget('var2');
s3=input(symget('var2'),2.);
s4=symget('var3');
s5=symget('var'||left(r2));
run;
A-26 Appendix A Exercises and Solutions

Name Type Length Value

R1

R2

R3

S1

S2

S3

S4

S5

Hint: Mimic the behavior of SAS by making three passes through the program: word scanning,
compilation, and execution.
Hint: Draw a symbol table, updating it as each macro variable is created and assigned a value.

Module 5 Solutions to Exercises

1. Creating Macro Variables with the SYMPUT Routine (solution program s-symput.sas)
a. Reset the system option DATE|NODATE to NODATE using the OPTIONS statement:
options nodate;
You also may want to activate the SYMBOLGEN option.
 0 Session 3 A-27

b. Write a DATA step that creates a macro variable named DATE from the perm.schedule data
set that contains the begin_date variable. This date is the starting date for each course. This
macro variable's value should be the begin_date in MMDDYY10. format, where
course_number=15.

The PUT function converts the numeric SAS date value returned by BEGIN_DATE into a
character string representing the course start date in mm/dd/yyyy form.
data _null_;
set perm.schedule;
where course_num=15;
call symput('date',put(begin_date,mmddyy10.));
run;
c. Insert the value of the macro variable DATE into a TITLE statement:
title "Roster for Course Offered on &date";
d. This PROC PRINT step should display the desired title:
proc print data=perm.register noobs n;
where course_number=15;
title "Roster for Course Offered on &date";
run;

Roster for Course Offered on 01/10/2006

Course_
Student_Name Number Paid

Chavez, Ms. Louise 15 Y

Edwards, Ms. Kathy 15 Y
Garza, Ms. Cheryl 15 Y
Gemelos, Mr. Jerry 15 Y
Green, Mr. Pat 15 N
Hipps, Mr. Rich 15 N
Kiraly, Mr. Bill 15 Y
Knight, Ms. Susan 15 Y
Leon, Mr. Quinton 15 N
Lewanwowski, Mr. Dale R. 15 Y
McCoy, Mr. Phil 15 Y
Mikles, Ms. Wendy 15 N
Morgan, Ms. Kathy 15 N
Norton, Ms. Suzanne M. 15 Y
Ray, Ms. Mary Frances 15 Y
Right, Ms. Tina 15 N
Schier, Ms. Joan 15 Y
Smith, Mr. Anthony 15 Y
Smith, Ms. Donna 15 Y
Stebel, Mr. Thomas C. 15 Y
Voboril, Mr. Jim 15 Y
Wallace, Mr. Jules 15 Y
Williams, Mr. Gregory 15 N
Ziegler, Mr. David 15 N

N = 24
A-28 Appendix A Exercises and Solutions

e. The WORDDATE20. format typically generates leading blanks. Use the TRIM and LEFT
functions to remove them.
options nodate symbolgen;
data _null_;
set perm.schedule;
where course_num=15;
call symput('date', trim(left(put(today(),
worddate20.))));
run;

title "Roster for Course offered on &date";

proc print data=perm.register noobs n;
where course_number=15;
run;

Partial Output
Roster for Course Offered on January 10, 2006

Course_
Student_Name Number Paid

Chavez, Ms. Louise 15 Y

Edwards, Ms. Kathy 15 Y
Garza, Ms. Cheryl 15 Y
Gemelos, Mr. Jerry 15 Y
Green, Mr. Pat 15 N
 0 Session 3 A-29

2. Creating Multiple Macro Variables with the SYMPUT Routine (solution program s-indir.sas)
a. Concatenating the text START with the value of the CRSNUM variable specifies the name of
each macro variable. Since the CRSNUM variable is numeric, the LEFT function is required to
remove the leading blanks introduced by the automatic numeric-to-character conversion. The
%PUT statement displays the names and values of all user-created macro variables.

data _null_;
set perm.schedule;
call symput('start'||trim(left(course_number)),
put(begin_date,mmddyy10.));
run;

%put _user_;

b. Because each macro variable that contains a course date has a common root at the start of its
name (START) and a suffix that corresponds to the value of the CRS macro variable, two
ampersands are used in front of the complete reference.

options symbolgen;
%let crs=4;
proc print data=perm.all noobs n;
where course_number=&crs;
var student_name student_company;
title1 "Roster for Course &crs";
title2 "Beginning on &&start&crs";
run;

Partial SAS Log

161 options symbolgen;
162 %let crs=4;
163 proc print data=perm.all noobs n;
164 where course_number=&crs;
SYMBOLGEN: Macro variable CRS resolves to 4
165 var student_name student_company;
SYMBOLGEN: Macro variable CRS resolves to 4
166 title1 "Roster for Course &crs";
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable CRS resolves to 4
SYMBOLGEN: Macro variable START4 resolves to 01/25/2005
167 title2 "Beginning on &&start&crs";
168 run;
A-30 Appendix A Exercises and Solutions

Partial Output
Roster for Course 4
Beginning on 1/25/2005

Student_Name Student_Company

Bates, Ms. Ellen Reston Railway

Boyd, Ms. Leah United Shoes Co.
Chan, Mr. John California Lawyers Assn.
Chevarley, Ms. Arlene Motor Communications
Chow, Ms. Sylvia Bostic Amplifier Inc.
Crace, Mr. Ron Von Crump Seafood
Edwards, Mr. Charles Gorman Tire Corp.
Garza, Ms. Cheryl Admiral Research & Development Co.
Geatz, Mr. Patrick D. San Juan Gas and Electric
Keever, Ms. Linda Crossbow of California
Kelley, Ms. Gail Crossbow of California
Kendig, Mr. James Rocks International
Kimble, Mr. John Alforone Chemical
Koleff, Mr. Jim Emulate Research
Montgomery, Mr. Jeff Bonstell Electronics
Moore, Mr. John California Dept. of Insurance
Page, Mr. Scott Applied Technologies
Parker, Mr. Robert SMASH Hardware Inc.

3. Creating Multiple Macro Variables Using SQL (solution program s-sql.sas)

a. A special form of the INTO clause is useful for creating series of macro variables from multiple
rows of an SQL query.

proc sql noprint;

select begin_date format=mmddyy10.
into :date1 - :date18
from perm.schedule;
quit;

b. Because the series of macro variables has a common root (START) and a suffix that corresponds
to the value of the NUM macro variable, two ampersands are used in front of the completed
reference.
%let num=4;

proc print data=perm.all noobs n;

where course_number=&num;
var student_name student_company;
title1 "Roster for Course &num Beginning on &&date&num";
run;
 0 Session 3 A-31

Partial Output
Roster for Course 4 Beginning on 01/25/2005

Student_Name Student_Company

Bates, Ms. Ellen Reston Railway

Boyd, Ms. Leah United Shoes Co.
Chan, Mr. John California Lawyers Assn.
Chevarley, Ms. Arlene Motor Communications
Chow, Ms. Sylvia Bostic Amplifier Inc.
Crace, Mr. Ron Von Crump Seafood
Edwards, Mr. Charles Gorman Tire Corp.
Garza, Ms. Cheryl Admiral Research & Development Co.
Geatz, Mr. Patrick D. San Juan Gas and Electric
Keever, Ms. Linda Crossbow of California
Kelley, Ms. Gail Crossbow of California
Kendig, Mr. James Rocks International
Kimble, Mr. John Alforone Chemical
Koleff, Mr. Jim Emulate Research
Montgomery, Mr. Jeff Bonstell Electronics
Moore, Mr. John California Dept. of Insurance
Page, Mr. Scott Applied Technologies
Parker, Mr. Robert SMASH Hardware Inc.
Pledger, Ms. Terri Candide Corporation
Snell, Dr. William J. US Treasury
Stackhouse, Ms. Loretta Donnelly Corp.
Sulzbach, Mr. Bill Sailbest Ships
Swayze, Mr. Rodney Reston Railway

c. (Optional) The NUMROWS macro variable stores how many records will be returned by the
query. This is the same as the number of macro variables in each series.
proc sql noprint;
select count(*)
into :numrows
from perm.schedule;
%let numrows=&numrows;
select begin_date format=mmddyy10.
into :date1 - :date&numrows
from perm.schedule;
quit;

%let num=4;
proc print data=perm.all noobs n;
where course_number = &num;
var student_name student_company;
title1 "Roster for Course &num";
title2 "Starting on &&date&num";
run;
A-32 Appendix A Exercises and Solutions

After-Class Solutions to Exercises

1. Generating Multiple Steps with Macro Loops (solution program s-AfterClass3.sas)
a.
The SORT procedure can produce a list of distinct values for a given variable. These values can
be placed into a series of macro variables. Using a macro loop, the series of macro variables can
be processed to produce one report for each original data value. The type of variable parameter
controls whether quotes are placed around the data in the WHERE statement.
%macro printall (dsn,var);
%let dsn=%upcase(&dsn);
%let var=%upcase(&var);

proc sort data=&dsn(keep=&var) out=unique nodupkey;

by &var;
run;

data _null_;
set unique end=final;
call symputx('value'||left(_n_),&var);
if final then call symputx('count',_n_);
run;

%do i=1 %to &count;

proc print data=&dsn noobs;
where &var="&&value&i";
title "Listing of &dsn Data Set";
title2 "for &var=&&value&i";
run;
%end;
%mend;
 0 Session 3 A-33

b. The macro call to generate the separate report for each training center location in the
perm.schedule data set is
%printall(perm.schedule,course_code)
Partial SAS Log
MPRINT(PRINTALL): proc print data=PERM.SCHEDULE noobs;
MPRINT(PRINTALL): where COURSE_CODE="C001";
MPRINT(PRINTALL): title "Listing of PERM.SCHEDULE Data Set";
MPRINT(PRINTALL): title2 "for COURSE_CODE=C001";
MPRINT(PRINTALL): run;
NOTE: There were 3 observations read from the data set PERM.SCHEDULE.
WHERE COURSE_CODE='C001';
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds
MPRINT(PRINTALL): proc print data=PERM.SCHEDULE noobs;
MPRINT(PRINTALL): where COURSE_CODE="C002";
MPRINT(PRINTALL): title "Listing of PERM.SCHEDULE Data Set";
MPRINT(PRINTALL): title2 "for COURSE_CODE=C002";
MPRINT(PRINTALL): run;
NOTE: There were 3 observations read from the data set PERM.SCHEDULE.
WHERE COURSE_CODE='C002';
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

MPRINT(PRINTALL): proc print data=PERM.SCHEDULE noobs;

MPRINT(PRINTALL): where COURSE_CODE="C003";
MPRINT(PRINTALL): title "Listing of PERM.SCHEDULE Data Set";
MPRINT(PRINTALL): title2 "for COURSE_CODE=C003";
MPRINT(PRINTALL): run;
NOTE: There were 3 observations read from the data set PERM.SCHEDULE.
WHERE COURSE_CODE='C003';
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

MPRINT(PRINTALL): proc print data=PERM.SCHEDULE noobs;

MPRINT(PRINTALL): where COURSE_CODE="C004";
MPRINT(PRINTALL): title "Listing of PERM.SCHEDULE Data Set";
MPRINT(PRINTALL): title2 "for COURSE_CODE=C004";
MPRINT(PRINTALL): run;
NOTE: There were 3 observations read from the data set PERM.SCHEDULE.
WHERE COURSE_CODE='C004';
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

MPRINT(PRINTALL): proc print data=PERM.SCHEDULE noobs;

MPRINT(PRINTALL): where COURSE_CODE="C005";
MPRINT(PRINTALL): title "Listing of PERM.SCHEDULE Data Set";
MPRINT(PRINTALL): title2 "for COURSE_CODE=C005";
MPRINT(PRINTALL): run;
A-34 Appendix A Exercises and Solutions

NOTE: There were 3 observations read from the data set PERM.SCHEDULE.
WHERE COURSE_CODE='C005';
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

MPRINT(PRINTALL): proc print data=PERM.SCHEDULE noobs;

MPRINT(PRINTALL): where COURSE_CODE="C006";
MPRINT(PRINTALL): title "Listing of PERM.SCHEDULE Data Set";
MPRINT(PRINTALL): title2 "for COURSE_CODE=C006";
MPRINT(PRINTALL): run;
NOTE: There were 3 observations read from the data set PERM.SCHEDULE.
WHERE COURSE_CODE='C006';
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

c. The macro call to generate a separate report for each class duration in the perm.courses data
set.

%printall(perm.schedule,location)

Partial SAS Log

MPRINT(PRINTALL): proc print data=PERM.SCHEDULE noobs;
MPRINT(PRINTALL): where LOCATION="Boston";
MPRINT(PRINTALL): title "Listing of PERM.SCHEDULE Data Set";
MPRINT(PRINTALL): title2 "for LOCATION=Boston";
MPRINT(PRINTALL): run;
NOTE: There were 6 observations read from the data set PERM.SCHEDULE.
WHERE LOCATION='Boston';
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

MLOGIC(PRINTALL): %DO loop index variable I is now 2; loop will iterate again.
MPRINT(PRINTALL): proc print data=PERM.SCHEDULE noobs;
MPRINT(PRINTALL): where LOCATION="Dallas";
MPRINT(PRINTALL): title "Listing of PERM.SCHEDULE Data Set";
MPRINT(PRINTALL): title2 "for LOCATION=Dallas";
MPRINT(PRINTALL): run;
NOTE: There were 6 observations read from the data set PERM.SCHEDULE.
WHERE LOCATION='Dallas';
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

MPRINT(PRINTALL): proc print data=PERM.SCHEDULE noobs;

MPRINT(PRINTALL): where LOCATION="Seattle";
MPRINT(PRINTALL): title "Listing of PERM.SCHEDULE Data Set";
MPRINT(PRINTALL): title2 "for LOCATION=Seattle";
MPRINT(PRINTALL): run;
NOTE: There were 6 observations read from the data set PERM.SCHEDULE.
WHERE LOCATION='Seattle';
NOTE: PROCEDURE PRINT used (Total process time):
 0 Session 3 A-35

real time 0.00 seconds

cpu time 0.01 seconds

2. Using Macro Variables in the DATA Step (Optional after-class exercise)

3. Resolving Macro Variables with the SYMGET Function (Optional after-class exercise) (solution
program s-symget.sas)

a. The _USER_ argument in the %PUT statement displays all user-created macro variables.
%put _user_;
Partial SAS Log
GLOBAL STARTDATE17 02/28/2006
GLOBAL STARTDATE16 01/24/2006
GLOBAL DSN perm.courses
GLOBAL VARS days fee
GLOBAL STARTDATE8 06/14/2005
GLOBAL STARTDATE18 03/28/2006
GLOBAL STARTDATE9 07/19/2005
GLOBAL CRSNUM 3
GLOBAL DATE 01/11/2005
GLOBAL STARTDATE4 01/25/2005
GLOBAL STARTDATE5 03/01/2005
GLOBAL STARTDATE6 04/05/2005
GLOBAL NUMPAID 14
GLOBAL STARTDATE7 05/24/2005
GLOBAL STARTDATE11 09/20/2005
GLOBAL NUMSTU 20
GLOBAL CRSNAME Local Area Networks
GLOBAL DUE $3,900
GLOBAL STARTDATE10 08/16/2005
GLOBAL NUM 8
GLOBAL STARTDATE1 10/26/2004
GLOBAL STARTDATE13 11/15/2005
GLOBAL STARTDATE2 12/07/2004
GLOBAL STARTDATE12 10/04/2005
GLOBAL STARTDATE3 01/11/2005
GLOBAL STARTDATE15 01/10/2006
GLOBAL STARTDATE14 12/06/2005

 The order in which the macro variables are displayed may differ from the order in which
they were created.
A-36 Appendix A Exercises and Solutions

b. The correct date can be obtained by appending the value of the course_number variable as a
suffix to START to identify the corresponding macro variable name. The retrieved (character)
value should be converted to a numeric SAS date value with a permanently assigned format.

data outstand;
set perm.register;
where paid='N';
begin=input(symget('start'||
left(course_number)),mmddyy10.);
format begin date9.;
run;

proc print data=outstand;

var student_name course_number begin;
title1 "Class Dates for Students";
title2 "with Outstanding Fees";
run;

Partial SAS Output

Class Dates for Students
with Outstanding Fees

Course_
Obs Student_Name Number begin

1 Amigo, Mr. Bill 1 26OCT2004

2 Edwards, Mr. Charles 1 26OCT2004
3 Haubold, Ms. Ann 1 26OCT2004
4 Hodge, Ms. Rita 1 26OCT2004
5 McGillivray, Ms. Kathy 1 26OCT2004
6 Pancoast, Ms. Jane 1 26OCT2004
7 Divjak, Ms. Theresa 2 07DEC2004
8 Gandy, Dr. David 2 07DEC2004
9 Harrell, Mr. Ken 2 07DEC2004
10 Hill, Mr. Paul 2 07DEC2004
11 Lewanwowski, Mr. Dale R. 2 07DEC2004
12 Nandy, Ms. Brenda 2 07DEC2004
13 Ng, Mr. John 2 07DEC2004
14 Williams, Mr. Gene 2 07DEC2004
15 Chevarley, Ms. Arlene 3 11JAN2005
 0 Session 3 A-37

4. Macro Variable Storage and Resolution (Optional after-class exercise)

Word Scanning
Substitutions based on macro variable references using & occur during word scanning.
R1 and R2 Macro variables VAR1 and VAR2 exist, so both substitutions occur.
R3 Macro variable VAR3 does not exist until the CALL SYMPUT statement executes, so no
substitution is made.
data test:
length s1 s4 s5 $ 3;
call symput('var3','dog');
r1="cat";
r2=3;
r3="&var3";
s1=symget('var1');
s2=symget('var2');
s3=input(symget('var2'),2.);
s4=symget('var3');
s5=symget('var'||left(r2));
run;
Compilation
The attributes of each variable are determined during compilation of the resulting DATA step
program:
data test:
length s1 s4 s5 $ 3;
call symput('var3','dog');
r1="cat";
r2=3;
r3="&var3";
s1=symget('var1');
s2=symget('var2');
s3=input(symget('var2'),2.);
s4=symget('var3');
s5=symget('var'||left(r2));
run;
S1, S4, S5 Explicit definition as character variables with length 3.
R2 Lack of quotes around the assigned value indicates a numeric variable. Default length
for numeric variables is 8.
R1 & R3 Quotes around the assigned value indicate a character variable. The number of
characters inside the quotes determines the length.
S2 Assignment from the SYMGET function indicates a character variable. No explicitly
assigned length defaults to 200; the compile does not know what value will be in the
symbol table during execution, the 200 bytes is allocated.
S3 Assignment from the INPUT function with a numeric informat indicates a numeric
variable. Default length for numeric variables is 8.
A-38 Appendix A Exercises and Solutions

Execution
The values of each variable are determined during execution of the program. It is at this time that the
CALL SYMPUT statement creates the macro variable VAR3 so that its value is available for retrieval
by the SYMGET function later in the DATA step.
R1 and R2 Hardcoded values are assigned.
R3 The reference &VAR3 is a text string during execution, so this is also a hardcoded
value.
S1 Value obtained from the symbol table.
S2 Value obtained from the symbol tables does not fill allotment of 200 characters;
there are 199 trailing blanks.
S3 The first two characters obtained from the symbol table are converted into a
numeric value using the 2. informat.
S4 and S5 Same value obtained from the symbol table since each SYMGET argument results
in the character string var3. Macro variable VAR3 was created earlier in the
execution of the DATA step.

Name Type Length Value

R1 Char 3 cat
R2 Num 8 3
R3 Char 5 &var3
S1 Char 3 cat
S2 Char 200 3
S3 Num 8 3
S4 Char 3 dog
S5 Char 3 dog
 0 Session 4 A-39

Session 4
If you are starting a new SAS session, remember to submit a LIBNAME statement.
libname perm '.'; *Virtual lab;
libname perm 'C:\SAS_Education\LWMACR'; *working on local PC;

Module 6 Exercises
1. Using Macro Loops
Open the printnum program shown below into the Editor window.
proc print data=perm.all label noobs n;
where course_number=3;
var student_name student_company;
title "Enrollment for Course 3";
run;
Define a macro program that generates a separate listing for each of the courses in the perm.all
data set. The values of course_number range from 1 to 18.

2. Validating Macro Parameters

a. Open the paidstat program shown below into the Editor window and submit it.
%macro paid(crsnum);
proc print data=perm.register label n noobs;
var student_name paid;
where course_number=&crsnum;
title "Fee Status for Course &crsnum";
run;
%mend paid;

%paid(2)
b. Modify the macro so it submits the PROC PRINT step only if the CRSNUM parameter has a
value between 1 and 18. If the CRSNUM value is out of range, the macro should write this
message to the SAS log:
Course Number must be between 1 and 18.
Supplied value was: x

The value of x is the CRSNUM parameter.

 1 <= &CRSNUM <= 18 is not valid in the macro facility.

c. Resubmit the macro definition and call the macro using both valid and invalid parameter values.
A-40 Appendix A Exercises and Solutions

d. Modify the macro to support a second positional parameter named STATUS. Add this statement
after the WHERE statement:
where also paid="&status";
At the beginning of the macro, extract the first character of STATUS and store it in uppercase.
Alter the macro so that the PROC PRINT step can be submitted only when the STATUS
parameter begins with Y or N.
Resubmit the macro definition and call the macro using both valid and invalid values for
STATUS.
3. Creating Multiple Symbol Tables
Open the nested program shown below into the Editor window.
%macro prtrost(num=1);
data _null_;
call symput('today',
trim(left(put(today(),mmddyy10.))));
run;

proc print data=perm.all label noobs n;

where course_number=&num;
var student_name student_company city_state;
title1 "Enrollment for Course &num as of &today";
run;
%mend prtrost;
%prtrost(num=8)
Move the DATA step into a separate macro named DATEMVAR with one parameter corresponding to
the format used in the PUT function. Make DATE9. the default value of this parameter.
Place a call to the new macro before the PROC PRINT step (where the DATA step had been). Use the
value MMDDYY10. instead of the default value for the macro's parameter. Submit the revised
program.
Make certain that the reference to &TODAY in the title resolves to the formatted value of today's
date.
 0 Session 4 A-41

Module 6 Solutions to Exercises

1. Using Macro Loops (solution program s-loop1.sas)
A simple macro loop with an index variable starting at 1 and stopping at 18 will produce the reports.
%macro prtrost;
%do num=1 %to 18;
proc print data=perm.all label noobs n;
where course_number=&num;
var student_name student_company;
title1 "Enrollment for Course &num";
run;
%end;
%mend prtrost;

options mprint nomlogic;

%prtrost
Partial SAS Log
MPRINT(PRTROST): proc print data=perm.all label noobs n;
MPRINT(PRTROST): where course_number=1;
MPRINT(PRTROST): var student_name student_company;
MPRINT(PRTROST): title1 "Enrollment for Course 1";
MPRINT(PRTROST): run;
NOTE: There were 23 observations read from the dataset PERM.ALL.
WHERE course_number=1;
NOTE: PROCEDURE PRINT used:
real time 0.07 seconds
cpu time 0.07 seconds

2. Validating Macro Parameters (solution program s-cond1.sas)

a. Open the program paidstat shown below into the Editor window and submit it.
%macro paid(crsnum);
proc print data=perm.register label n noobs;
var student_name paid;
where course_number=&crsnum;
title "Fee Status for Course &crsnum";
run;
%mend paid;

%paid(2)
A-42 Appendix A Exercises and Solutions

b. To define a valid range, the %IF expression must contain two comparisons connected with the
AND operator. Each message line requires a separate %PUT statement.
%macro paid(crsnum);
%if &crsnum >=1 and &crsnum <= 18 %then %do;
proc print data=perm.register label noobs n;
where course_number=&crsnum;
title "Fee Status for Course &crsnum";
run;
%end;
%else %do;
%put Course Number must be between 1 and 18;
%put Supplied Value was: &crsnum;
%end;
%mend paid;

%paid(2)
%paid(20)
c. Resubmit the macro definition and call the macro.
Partial SAS Log
MLOGIC(PAID): Ending execution.
222 %paid(20)
MLOGIC(PAID): Beginning execution.
MLOGIC(PAID): Parameter CRSNUM has value 20
MLOGIC(PAID): %IF condition &crsnum >=1 and &crsnum <= 18 is FALSE
MLOGIC(PAID): %PUT Course Number must be between 1 and 18
Course Number must be between 1 and 18
MLOGIC(PAID): %PUT Supplied Value was: &crsnum
Supplied Value was: 20
MLOGIC(PAID): Ending execution.
 0 Session 4 A-43

d. The %UPCASE and %SUBSTR functions are used to extract the first character of the parameter
value and translate it to uppercase. The additional condition based on STATUS can be
implemented using the AND operator with the previous CRSNUM validation expression or with
nested %IF-%THEN statements.
%macro paid(crsnum,status);
%let status1=%upcase(%substr(&status,1,1));
%if &status1=Y or &status1=N %then %do;
%if &crsnum >= 1 and &crsnum <= 18 %then %do;
proc print data=perm.register label n noobs;
var student_name paid;
where course_number=&crsnum;
where also paid="&status1";
title "Fee Status for Course &crsnum";
run;
%end;
%else %do;
%put Course Number must be between 1 and 18;
%put Supplied Value was: &crsnum;
%end;
%end;
%else %do;
%put Status must begin with Y or N;
%put Supplied value was: &status;
%end;
%mend paid;

%paid(2,Y)
%paid(2,no)
%paid(2,?)
Partial SAS Log
246 %paid(2,no)
MLOGIC(PAID): Beginning execution.
MLOGIC(PAID): Parameter CRSNUM has value 2
MLOGIC(PAID): Parameter STATUS has value no
MLOGIC(PAID): %LET (variable name is STATUS1)
MLOGIC(PAID): %IF condition &status1=Y or &status1=N is TRUE
MLOGIC(PAID): %IF condition &crsnum >= 1 and &crsnum <= 18 is TRUE
MPRINT(PAID): proc print data=perm.register label n noobs;
MPRINT(PAID): var student_name paid;
MPRINT(PAID): where course_number=2;
MPRINT(PAID): where also paid="N";
NOTE: Where clause has been augmented.
MPRINT(PAID): title "Fee Status for Course 2";
MPRINT(PAID): run;
NOTE: There were 8 observations read from the dataset PERM.REGISTER.
WHERE (course_number=2) and (paid='N');
NOTE: PROCEDURE PRINT used:
real time 2.40 seconds
cpu time 0.03 seconds

MLOGIC(PAID): Ending execution.

A-44 Appendix A Exercises and Solutions

247 %paid(2,?)
MLOGIC(PAID): Beginning execution.
MLOGIC(PAID): Parameter CRSNUM has value 2
MLOGIC(PAID): Parameter STATUS has value ?
MLOGIC(PAID): %LET (variable name is STATUS1)
MLOGIC(PAID): %IF condition &status1=Y or &status1=N is FALSE
MLOGIC(PAID): %PUT Status must begin with Y or N
Status must begin with Y or N
MLOGIC(PAID): %PUT Supplied value was: &status
Supplied value was: ?
MLOGIC(PAID): Ending execution

3. Creating Multiple Symbol Tables (solution program s-symbol.sas)

When the DATA step is moved outside the original macro, and the new macro has parameters, the
macro variable TODAY is placed in the local table for the new macro unless it is explicitly made
available to the original macro.
This can be done by making TODAY
• a global variable, or
• a local variable for the original macro, which can be updated within the new macro as the macro
processor traverses through the separate local tables in the reverse order that they were created.
%macro datemvar(fmt=date9.);
data _null_;
call symput('today',
trim(left(put(today(),&fmt))));
run;
%mend datemvar;

%macro prtrost(num=1);
%local today;
%datemvar(fmt=mmddyy10.);
proc print data=perm.all label noobs n;
where course_number=&num;
var student_name student_company city_state;
title1 "Enrollment for Course &num as of &today";
run;
%mend prtrost;

%prtrost(num=8)
Appendix B SAS Programming Flow
and Macro Variable Retrieval in the
DATA Step
B.1 Program Flow................................................................................................................. B-2

B.2 Retrieving Macro Variables in the DATA Step ............................................................. B-3

B-2 Appendix B SAS Programming Flow and Macro Variable Retrieval in the DATA Step

B.1 Program Flow

DATA STEP PROCEDURE GLOBAL STATEMENT SQL

Compiler Parser Parser Compiler

SYMBOL TABLE
WORD SCANNER
MACRO
PROCESSOR

MACRO LIBRARY

INPUT STACK

3
 B.2 Retrieving Macro Variables in the DATA Step B-3

B.2 Retrieving Macro Variables in the DATA Step

Objectives
„ Obtain the value of a macro variable during DATA step
execution.
„ Describe the difference between the SYMGET
function and macro variable references.

Review

create macro
variables

%LET

6
B-4 Appendix B SAS Programming Flow and Macro Variable Retrieval in the DATA Step

Review

create macro retrieve macro

variables variables

%LET &macvar

Review

create macro retrieve macro

variables variables

word
scanning %LET &macvar
time

Review

create macro retrieve macro

variables variables

word
scanning %LET &macvar
time

execution
time CALL SYMPUT

9
 B.2 Retrieving Macro Variables in the DATA Step B-5

The SYMGET Function

create macro retrieve macro

variables variables

word
scanning %LET &macvar
time

execution
time CALL SYMPUT SYMGET(macvar)

10

The SYMGET Function

Retrieve a macro variable’s value during DATA step
execution with the SYMGET function.

Symbol Table
Program Data Vector

DATA Step
Variables SYMGET

11

The SYMGET Function

General form of the SYMGET function:

SYMGET(macro-variable)
SYMGET(macro-variable)

macro-variable can be specified as a

„ character literal
„ DATA step character expression.

A DATA step variable created by the SYMGET function is

a character variable with a length of 200 bytes unless it
has been previously defined.

12

Question: When does SYMGET retrieve the value of the macro variable?
B-6 Appendix B SAS Programming Flow and Macro Variable Retrieval in the DATA Step

• before DATA step compilation

• during DATA step compilation
• during DATA step execution
• after DATA step execution.

The SYMGET Function

The SYMGET function can be used in table lookup
applications.

Example: Use the SYMPUT routine to create a series of

macro variables.
data _null_;
set perm.schedule;
call symput(’teach’||left(course_number),
trim(teacher));
run; symget1

Symbol Table

teach1 Hallis, Dr. George

teach2 Wickam, Dr. Alice
teach3 Forest, Mr. Peter
14

The SYMGET Function

Example: Look up the teacher's name from the symbol
table by deriving the corresponding macro
variable's name from the data set variable
course_number.

data teachers;
set perm.register;
length teacher $ 20;
teacher=symget(’teach’||left(course_number));
run;
symget1

15
 B.2 Retrieving Macro Variables in the DATA Step B-7

The SYMGET Function

Because there are no macro triggers, the entire DATA
step is passed to the compiler. The DATA step executes
after the RUN statement is encountered.
data
data teachers;
Compiler set
teachers;
set perm.register;
perm.register;
length
length teacher
teacher $$ 20;
20;
teacher=symget(’teach’||left(course_number));
teacher=symget(’teach’||left(course_number));

Word run;
Macro Processor
run;
Scanner

Symbol Table
Input
teach1 Hallis, Dr. George
Stack teach2 Wickam, Dr. Alice
teach3 Forest, Mr. Peter
16

Partial Listing of perm.register

Course_
data teachers;
Student_Name Number Paid set perm.register;
length teacher $ 20;
Albritton, Mr. Bryan 1 Y teacher=symget(’teach’||
Amigo, Mr. Bill 2 Y left(course_number));
Chodnoff, Mr. Norman 1 Y run;

Partial PDV
Initialize PDV
Course_ to missing.
Number Teacher
N $
8 20
.

Symbol Table

teach1 Hallis, Dr. George

teach2 Wickam, Dr. Alice
teach3 Forest, Mr. Peter
17

Partial Listing of perm.register

data teachers;
Course_ set perm.register;
Student_Name Number Paid length teacher $ 20;
teacher=symget(’teach’||
Albritton, Mr. Bryan 1 Y
Amigo, Mr. Bill 2 Y left(course_number));
Chodnoff, Mr. Norman 1 Y run;

Partial PDV The SET statement reads the

Course_
first observation into the PDV.
Number Teacher
N $
8 20
1

Symbol Table

teach1 Hallis, Dr. George

teach2 Wickam, Dr. Alice
teach3 Forest, Mr. Peter
18
B-8 Appendix B SAS Programming Flow and Macro Variable Retrieval in the DATA Step

Partial Listing of perm.register

data teachers;
Course_ set perm.register;
Student_Name Number Paid length teacher $ 20;
teacher=symget(’teach’||
Albritton, Mr. Bryan 1 Y
Amigo, Mr. Bill 2 Y left(course_number));
Chodnoff, Mr. Norman 1 Y run;

Partial PDV The SYMGET function

Course_
retrieves the macro variable
Number Teacher value from the symbol table.
N $ teacher=symget(’teach1’);
8 20
1 Hallis, Dr. George

Symbol Table

teach1 Hallis, Dr. George

teach2 Wickam, Dr. Alice
teach3 Forest, Mr. Peter
19

Partial Listing of perm.register

data teachers;
Course_ set perm.register;
Student_Name Number Paid length teacher $ 20;
teacher=symget(’teach’||
Albritton, Mr. Bryan 1 Y
Amigo, Mr. Bill 2 Y left(course_number));
Chodnoff, Mr. Norman 1 Y run;
Automatic output
Partial PDV
At the bottom of the step, SAS
Course_ automatically outputs the
Number Teacher observation to the new data set
N $ work.teachers.
8 20
1 Hallis, Dr. George

Symbol Table

teach1 Hallis, Dr. George

teach2 Wickam, Dr. Alice
teach3 Forest, Mr. Peter
20

Partial Listing of perm.register

Automatic return
data teachers;
Course_ set perm.register;
Student_Name Number Paid length teacher $ 20;
teacher=symget(’teach’||
Albritton, Mr. Bryan 1 Y
Amigo, Mr. Bill 2 Y left(course_number));
Chodnoff, Mr. Norman 1 Y run;

Partial PDV At the bottom of the step,

Course_
SAS automatically returns to
Number Teacher the top of the step. The PDV
N $ is reinitialized.
8 20
1

Symbol Table

teach1 Hallis, Dr. George

teach2 Wickam, Dr. Alice
teach3 Forest, Mr. Peter
21
 B.2 Retrieving Macro Variables in the DATA Step B-9

Partial Listing of perm.register

data teachers;
Course_ set perm.register;
Student_Name Number Paid length teacher $ 20;
teacher=symget(’teach’||
Albritton, Mr. Bryan 1 Y
Amigo, Mr. Bill 2 Y left(course_number));
Chodnoff, Mr. Norman 1 Y run;

Partial PDV The SET statement reads the

Course_
second observation into the
Number Teacher PDV.
N $
8 20
2

Symbol Table

teach1 Hallis, Dr. George

teach2 Wickam, Dr. Alice
teach3 Forest, Mr. Peter
22

Partial Listing of perm.register

data teachers;
Course_ set perm.register;
Student_Name Number Paid length teacher $ 20;
teacher=symget(’teach’||
Albritton, Mr. Bryan 1 Y
Amigo, Mr. Bill 2 Y left(course_number));
Chodnoff, Mr. Norman 1 Y run;

Partial PDV The SYMGET function

Course_
retrieves the macro variable
Number Teacher value from the symbol table.
N $ teacher=symget(’teach2’);
8 20
2 Wickam, Dr. Alice

Symbol Table

teach1 Hallis, Dr. George

teach2 Wickam, Dr. Alice
teach3 Forest, Mr. Peter
23

Partial Listing of perm.register

data teachers;
Course_ set perm.register;
Student_Name Number Paid length teacher $ 20;
teacher=symget(’teach’||
Albritton, Mr. Bryan 1 Y
Amigo, Mr. Bill 2 Y left(course_number));
Chodnoff, Mr. Norman 1 Y run;

Partial PDV
Processing continues until
Course_ SAS has read all rows in the
Number Teacher perm.register data
N $ set.
8 20

Symbol Table

teach1 Hallis, Dr. George

teach2 Wickam, Dr. Alice
teach3 Forest, Mr. Peter
24
B-10 Appendix B SAS Programming Flow and Macro Variable Retrieval in the DATA Step

The SYMGET Function

title1 "Teacher for Each Registered Student";
proc print data=teachers;
var student_name course_number teacher;
run;
symget1

Partial SAS Output

Teacher for Each Registered Student

Course_
Obs Student_Name Number teacher

1 Albritton, Mr. Bryan 1 Hallis, Dr. George

2 Amigo, Mr. Bill 2 Wickam, Dr. Alice
3 Chodnoff, Mr. Norman 1 Hallis, Dr. George

25

Student Activity
Submit the programs. Would the following pieces of code
have given you the same results?
dataChange
_null_;your seat indicator to Yes or No.
set perm.schedule;
call symput(’teach’||left(course_number),trim(teacher));
run;
data teachers;
set perm.register;
length teacher $ 20;
teacher=symget(’teach’||left(course_number));
run;
sa-append2
data teachers2;
merge perm.register
perm.schedule(keep=course_number teacher);
by course_number;
run;
26