0% found this document useful (0 votes)

632 views247 pages

Sas Macros

Uploaded by

schinnam12

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

632 views247 pages

Sas Macros

Uploaded by

schinnam12

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

You are on page 1/ 247

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

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
5.1

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

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

iii

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

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

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

1.1 Purpose of the Macro Facility

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.

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
Data Set";
3
2
1
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.

1-3

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
Data
3
2 Set";
1
footnote1 "Created 10:24 Wednesday, 25AUG2004";
footnote2 "on the WIN System Using Release 9.1";
5
4
run;

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;

run;

Daily report

Is it
Friday?

Yes

proc means data=perm.all;

run;

Macro programs can conditionally execute selected

portions of a SAS program based on user-defined
conditions.
14

1.1 Purpose of the Macro Facility

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.

1-5

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.

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.

1.1 Purpose of the Macro Facility

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

1-7

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.

Program Flow
Input
InputStack
Stack

SUBMIT
Command
data
data new;
new;
set
set perm.mast;
perm.mast;
bonus=wage*0.1;
bonus=wage*0.1;
run;
run;
proc
proc print;
print;
run;
run;

Stored
Process

Batch or
Noninteractive
Submission

%STPBEGIN;
%STPBEGIN;
proc
proc print
print data=new;
data=new;
run;
run;
proc
means
data=new;
proc means data=new;
run;
run;
%STPEND;
%STPEND;

//SYSIN
//SYSIN DD
DD **
options
options nodate;
nodate;
proc
proc sql;
sql;
select
select **
from
from perm.mast;
perm.mast;
quit;
quit;

1.2 Program Flow

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.
Word
Scanner

Input
Stack

data
data
new
new
;;

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

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

Word Scanner

Input Stack

data
data new;
new;
set
set
perm
perm
..
mast
mast
;;
bonus=wage*0.1;
bonus=wage*0.1;
run;
run;
proc
proc print;
print;
run;
run;

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.

1-9

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.

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.

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
01jan2002d
5E8
7.2E-4

1.2 Program Flow

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.

Special Tokens
Special tokens can be any character, or combination of
characters, other than a letter, numeral, or underscore.
Examples: * / + - ** ; $ ( ) . & % @ # = ||

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.

1-11

1-12

Module 1 Introduction to the SAS Macro Facility

Example
Input Stack

var x1-x10

Tokens

1.
2.
3.
4.
5.
6.

var
x1
x10
z
;

Example
Input Stack

title 'Report for May';

Tokens

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

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;

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.

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.

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;

1.2 Program Flow

The %PUT Statement

Example: Use a %PUT statement to write text to the
SAS log.
Partial SAS Log
12 %put Hi Mom!;
Hi Mom!

Program Flow
The %PUT statement is submitted.

Compiler

Macro Processor

Word
Scanner

Input
Stack

%put
%put Hi
Hi Mom!;
Mom!;

Program Flow
The statement is tokenized.

Compiler
Word
Scanner

Input
Stack

Macro Processor
%%
put
put
Hi
Hi
Mom
Mom
!!
;;

1-15

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
Word
Scanner

Macro Processor
%put
%put
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
Scanner

%put
%put Hi
Hi Mom!;
Mom!;

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
Stack

%put "Hi Mom";

1.2 Program Flow

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"

1-17

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

observation per course.

schedule

contains information about each course with one

observation per course at a particular location and
date.

students

contains information about students with one

observation per student.

207

contains information about students registered for

a specific course with one observation per student
for a particular course.

434

all

joins all data files with one observation per

student per course.

434

These data sets are stored in a SAS data library with a libref of perm.

1.3 Course Data

The COURSES Data Set

The CONTENTS Procedure
Data Set Name
Member Type
Engine
Created

PERM.COURSES
DATA
V9
Tuesday, May 30,
2000 04:21:30 PM
Monday, June 12,
2000 10:39:41 AM

Last Modified
Protection
Data Set Type
Label
Data Representation
Encoding

Observations
Variables
Indexes
Observation Length

6
4
0
48

Deleted Observations

Compressed
Sorted

NO
NO

WINDOWS_32
Default

Engine/Host Dependent Information

Data Set Page Size
Number of Data Set Pages
First Data Page
Max Obs per Page
Obs in First Data Page
Number of Data Set Repairs
File Name
Release Created
Host Created

4096
2
1
84
6
0
C:\workshop\winsas\macr\courses.sas7bdat
8.0000M0
WIN_NT

Alphabetic List of Variables and Attributes

Variable

Type

Len

1
2
3
4

Course_Code
Course_Title
Days
Fee

Char
Char
Num
Num

4
25
8
8

Format

1.
DOLLAR5.

Informat

Label

1.
DOLLAR5.

Course Code
Description
Course Length
Course Fee

Listing of PERM.COURSES
Obs
1
2
3
4
5
6

Course_
Code
C001
C002
C003
C004
C005
C006

Course_Title
Basic Telecommunications
Structured Query Language
Local Area Networks
Database Design
Artificial Intelligence
Computer Aided Design

Days
3
4
3
2
2
5

Fee
$795
$1150
$650
$375
$400
$1600

1-19

1-20

Module 1 Introduction to the SAS Macro Facility

The SCHEDULE Data Set

The CONTENTS Procedure
Data Set Name
Member Type
Engine
Created

PERM.SCHEDULE
DATA
V9
Monday, July 12,
2004 04:29:52 PM
Monday, July 12,
2004 04:29:52 PM

Last Modified
Protection
Data Set Type
Label
Data Representation
Encoding

Observations
Variables
Indexes
Observation Length

18
5
0
56

Deleted Observations

Compressed
Sorted

NO
NO

WINDOWS_32
wlatin1 Western
(Windows)

Engine/Host Dependent Information

Data Set Page Size
Number of Data Set Pages
First Data Page
Max Obs per Page
Obs in First Data Page
Number of Data Set Repairs
File Name
Release Created
Host Created

8192
1
1
145
18
0
C:\workshop\winsas\macr\schedule.sas7bdat
9.0101B3
XP_PRO

Alphabetic List of Variables and Attributes

Variable

Type

Len

Format

Informat

Label

4
2
1
3
5

Begin_Date
Course_Code
Course_Number
Location
Teacher

Num
Char
Num
Char
Char

8
4
8
15
20

DATE9.

DATE7.

Begin
Course Code
Course Number
Location
Instructor

Partial Listing of PERM.SCHEDULE

Obs
1
2
3
4
5
6
7

Course_
Number
1
2
3
4
5
6
7

Course_
Code
C001
C002
C003
C004
C005
C006
C001

Location
Seattle
Dallas
Boston
Seattle
Dallas
Boston
Dallas

Begin_
Date
26OCT2004
07DEC2004
11JAN2005
25JAN2005
01MAR2005
05APR2005
24MAY2005

Teacher
Hallis, Dr. George
Wickam, Dr. Alice
Forest, Mr. Peter
Tally, Ms. Julia
Hallis, Dr. George
Berthan, Ms. Judy
Hallis, Dr. George

1.3 Course Data

The STUDENTS Data Set

The CONTENTS Procedure
Data Set Name
Member Type
Engine
Created

PERM.STUDENTS
DATA
V9
Tuesday, May 30,
2000 04:21:31 PM
Monday, June 12,
2000 10:39:11 AM

Last Modified
Protection
Data Set Type
Label
Data Representation
Encoding

Observations
Variables
Indexes
Observation Length

207
3
0
85

Deleted Observations

Compressed
Sorted

NO
NO

WINDOWS_32
Default

Engine/Host Dependent Information

Data Set Page Size
Number of Data Set Pages
First Data Page
Max Obs per Page
Obs in First Data Page
Number of Data Set Repairs
File Name
Release Created
Host Created

8192
4
1
95
80
0
C:\workshop\winsas\macr\students.sas7bdat
8.0000M0
WIN_NT

Alphabetic List of Variables and Attributes

Variable

Type

Len

3
2
1

City_State
Student_Company
Student_Name

Char
Char
Char

20
40
25

Label
City,State
Company
Student Name

Partial Listing of PERM.STUDENTS

Obs
1
2
3
4
5
6
7
8
9
10
11
12

Student_Name

Student_Company

City_State

Abramson, Ms. Andrea

Alamutu, Ms. Julie
Albritton, Mr. Bryan
Allen, Ms. Denise
Amigo, Mr. Bill
Avakian, Mr. Don
Babbitt, Mr. Bill
Baker, Mr. Vincent
Bates, Ms. Ellen
Belles, Ms. Vicki
Benincasa, Ms. Elizabeth
Bills, Ms. Paulette

Eastman Developers
Reston Railway
Special Services
Department of Defense
Assoc. of Realtors
Reston Railway
National Credit Corp.
Snowing Petroleum
Reston Railway
Jost Hardware Inc.
Hospital Nurses Association
Reston Railway

Deerfield, IL
Chicago, IL
Oak Brook, IL
Bethesda, MD
Chicago, IL
Chicago, IL
Chicago, IL
New Orleans, LA
Chicago, IL
Toledo, OH
Naperville, IL
Chicago, IL

1-21

1-22

Module 1 Introduction to the SAS Macro Facility

The REGISTER Data Set

The CONTENTS Procedure
Data Set Name
Member Type
Engine
Created

PERM.REGISTER
DATA
V9
Tuesday, May 30,
2000 04:21:31 PM
Monday, June 12,
2000 10:39:54 AM

Last Modified
Protection
Data Set Type
Label
Data Representation
Encoding

Observations
Variables
Indexes
Observation Length

434
3
0
40

Deleted Observations

Compressed
Sorted

NO
NO

WINDOWS_32
Default

Engine/Host Dependent Information

Data Set Page Size
Number of Data Set Pages
First Data Page
Max Obs per Page
Obs in First Data Page
Number of Data Set Repairs
File Name
Release Created
Host Created

4096
6
1
101
68
0
C:\workshop\winsas\macr\register.sas7bdat
8.0000M0
WIN_NT

Alphabetic List of Variables and Attributes

Variable

Type

Len

2
3
1

Course_Number
Paid
Student_Name

Num
Char
Char

8
1
25

Obs
1
2
3
4
5
6
7
8
9
10
11
12

Format

Informat

Label

Course Number
Paid Status
Student Name

Partial Listing of PERM.REGISTER

Course_
Student_Name
Number
Albritton, Mr. Bryan
Amigo, Mr. Bill
Chodnoff, Mr. Norman
Clark, Mr. Rich
Crace, Mr. Ron
Dellmonache, Ms. Susan
Dixon, Mr. Matt
Edwards, Mr. Charles
Edwards, Ms. Sonia
Elsins, Ms. Marisa F.
Griffin, Mr. Lantz
Hall, Ms. Sharon

1
1
1
1
1
1
1
1
1
1
1
1

Paid
Y
N
Y
Y
Y
Y
Y
N
Y
Y
Y
Y

1.3 Course Data

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-23

1-24

Module 1 Introduction to the SAS Macro Facility

The ALL Data Set

The CONTENTS Procedure
Data Set Name
Member Type
Engine
Created

PERM.ALL
DATA
V9
Friday, July 23,
2004 02:53:26 PM
Friday, July 23,
2004 02:53:26 PM

Last Modified
Protection
Data Set Type
Label
Data Representation
Encoding

Observations
Variables
Indexes
Observation Length

434
12
0
184

Deleted Observations

Compressed
Sorted

NO
YES

WINDOWS_32
wlatin1 Western (Windows)

Engine/Host Dependent Information

Data Set Page Size
Number of Data Set Pages
First Data Page
Max Obs per Page
Obs in First Data Page
Number of Data Set Repairs
File Name
Release Created
Host Created

16384
6
1
88
76
0
C:\workshop\winsas\macr\all.sas7bdat
9.0101B3
XP_PRO

Alphabetic List of Variables and Attributes

#
6
12
4
2
8
9
10
5
3
11
1
7

Variable

Type

Len

Format

Informat

Label

Begin_Date
City_State
Course_Code
Course_Number
Course_Title
Days
Fee
Location
Paid
Student_Company
Student_Name
Teacher

Num
Char
Char
Num
Char
Num
Num
Char
Char
Char
Char
Char

8
20
4
8
25
8
8
15
1
40
25
20

DATE9.

DATE7.

1.
DOLLAR5.

Begin
City,State
Course Code
Course Number
Description
Course Length
Course Fee
Location
Paid Status
Company
Student Name
Instructor

Sort Information
Sortedby
Validated
Character Set

Student_Name Course_Code
YES
ANSI

1.3 Course Data

The ALL Data Set

Partial Listing of PERM.ALL

Obs
1
2
3
4
5

Abramson, Ms. Andrea

Abramson, Ms. Andrea
Alamutu, Ms. Julie
Albritton, Mr. Bryan
Albritton, Mr. Bryan

Obs

Begin_
Date

1
2
3
4
5

16AUG2005
05APR2005
06DEC2005
26OCT2004
01MAR2005

Obs
1
2
3
4
5

Course_
Number

Student_Name

Days
2
5
4
3
2

10
6
14
1
5

Teacher

$375
$1600
$1150
$795
$400

Y
N
N
Y
Y

C004
C006
C002
C001
C005

Location
Dallas
Boston
Seattle
Seattle
Dallas

Database Design
Computer Aided Design
Structured Query Language
Basic Telecommunications
Artificial Intelligence

Student_Company
Eastman Developers
Eastman Developers
Reston Railway
Special Services
Special Services

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

libname perm '.';

proc contents data=perm._all_;
run;
sa-contents

Course_
Code

Course_Title

Tally, Ms. Julia

Berthan, Ms. Judy
Wickam, Dr. Alice
Hallis, Dr. George
Hallis, Dr. George

Fee

Paid

City_State
Deerfield, IL
Deerfield, IL
Chicago, IL
Oak Brook, IL
Oak Brook, IL

1-25

1-26

Module 1 Introduction to the SAS Macro Facility

Module 1 Summary

State the purpose of the macro facility.

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.

2.1 Introduction to Macro Variables

Global Symbol Table

Description

SYSDATE
SYSDATE9
SYSDAY
SYSTIME
SYSSCP

date of SAS invocation (DATE7.)

date of SAS invocation (DATE9.)
day of the week of SAS invocation
time of SAS invocation
abbreviation for the operating system: OpenVMS,
WIN, HP 300, and so on
release of SAS software being used.

SYSVER

2.2 Automatic Macro Variables

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.

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_;

Automatic Macro Variables

Partial SAS Log
12
%put
AUTOMATIC
AUTOMATIC
AUTOMATIC
AUTOMATIC
AUTOMATIC
AUTOMATIC
AUTOMATIC
AUTOMATIC
AUTOMATIC
AUTOMATIC
AUTOMATIC
AUTOMATIC

_automatic_;
AFDSID 0
AFDSNAME
AFLIB
AFSTR1
AFSTR2
FSPBDV
SYSBUFFR
SYSCC 3000
SYSCHARWIDTH 1
SYSCMD
SYSDATE 05FEB04
SYSDATE9 05FEB2004

The macro variables SYSDATE, SYSDATE9, and

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

2-5

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_;

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

Symbol Table
SYSDAY
SYSLAST

Tuesday
_NULL_

Substitution within a Macro Statement

The macro processor executes the %PUT statement,
writing the resolved text to the SAS log.
Compiler
Macro Processor
Word
Scanner

Input
Stack

%put
%put Today
Today is
is Tuesday;
Tuesday;

Symbol Table
SYSDAY
SYSLAST

Tuesday
_NULL_

2-9

2-10

Module 2 Creating and Resolving Macro Variables Prior to Compilation

Substitution within a SAS Literal

If you need to reference a
macro variable within a
literal, enclose the literal in
double quotes.

Global Symbol Table

CITY
DATE
AMOUNT

Dallas
05JAN2000
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

Input
Stack

proc
proc print
print data=perm.all;
data=perm.all;
title
title "Today
"Today is
is &sysday";
&sysday";
run;
run;

Symbol Table
SYSDAY
SYSLAST

Tuesday
_NULL_

Substitution within a SAS Literal

SAS statements are passed to the compiler.

Compiler
Word
Scanner

Input
Stack

proc
proc print
print data=perm.all;
data=perm.all;
title
title "Today
"Today is
is Tuesday";
Tuesday";

Macro Processor

Word
Scanner
run;
run;

Input
Stack

Symbol Table
SYSDAY
SYSLAST

Tuesday
_NULL_

2.3 Macro Variable References

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

Compiler

Macro Processor

Word
Scanner

Input
Stack

proc
proc print
print data=perm.all;
data=perm.all;
title
title 'Today
'Today is
is &sysday';
&sysday';
run;
run;

Symbol Table
SYSDAY
SYSLAST

Tuesday
_NULL_

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

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-13

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

Input
Stack

proc
proc print
print data=perm.exp;
data=perm.exp;
title
title "Expenses
"Expenses for
for R&D";
R&D";
run;
run;

Symbol Table
SYSDAY
SYSLAST

Tuesday
_NULL_

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

proc
proc print
print data=perm.exp;
data=perm.exp;
title
title

Word
Scanner

""
Expenses
Expenses
for
for
RR

Input
Stack

run;
run;

Macro Processor
&D
&D
Symbol Table

";
";

SYSDAY
SYSLAST

Tuesday
_NULL_

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

proc
proc print
print data=perm.exp;
data=perm.exp;
title
title

Word
Scanner

""
Expenses
Expenses
for
for
RR

Input
Stack

run;
run;

WARNING: Apparent symbolic

reference D not resolved.

Macro Processor
&D
&D

";
";

Symbol Table
SYSDAY
SYSLAST

Tuesday
_NULL_

2.3 Macro Variable References

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

proc
proc print
print data=perm.exp;
data=perm.exp;
title
title "Expenses
"Expenses for
for R&D"
R&D"

Word
Scanner

run;
run;

;;

Macro Processor

Symbol Table

Input
Stack

SYSDAY
SYSLAST

Tuesday
_NULL_

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

Input
Stack

proc
proc print
print data=&syslast;
data=&syslast;
title
title "Listing
"Listing of
of &syslast";
&syslast";
run;
run;

Symbol Table
SYSDAY
SYSLAST

Tuesday
PERM.ALL

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

proc
proc print
print data=
data=

Macro Processor

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.

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.

2.4 User-Defined Macro Variables

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

%let
%let
%let
%let
%let
%let
%let
%let
%let

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

Value
Ed Norton
' Ed Norton '
"Joan's Report"
3+4
0+3+4
varlist

Quick Quiz
What is the name of the macro variable created with this
%LET statement?
Value
Ed Norton
%let name= Ed Norton ;
' Ed Norton '
%let name2= Ed Norton ;
%let title="Joans Report"; "Joan's Report"
%let start=;
3+4
%let sum=3+4;
%let total=0;
0+3+4
%let total=&total+∑
varlist
%let x=varlist;
%let &x=name age height;
Submit your answer as a text question.
62

2.4 User-Defined Macro Variables

%LET Statement Examples

Global Symbol Table
CITY
DATE
AMOUNT

Dallas
05JAN2004
975

Partial SAS Log

OPTIONS SYMBOLGEN;
where fee>&amount;
SYMBOLGEN: Macro variable
where city_state contains
SYMBOLGEN: Macro variable
where city_state contains

AMOUNT resolves to 975

"&city";
CITY resolves to Dallas
'&city';

Quick Quiz
Global Symbol Table
CITY
DATE
AMOUNT

Dallas
05JAN2004
975

Partial SAS Log

OPTIONS SYMBOLGEN;
where fee>&amount;
SYMBOLGEN: Macro variable
where city_state contains
SYMBOLGEN: Macro variable
where city_state contains

AMOUNT resolves to 975

"&city";
CITY resolves to Dallas
'&city';

Why is no message displayed in the log for the final

example?

Deleting User-Defined Macro Variables

The %SYMDEL statement deletes one or more userdefined 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-23

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

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

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.

2-25

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;

2.5 Delimiting Macro Variable Names

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
HBAR
RUN;
PROC
PLOT
RUN;

CHART DATA=PERM.Y2000JAN;
WEEK / SUMVAR=SALE;
PLOT DATA=PERM.Y2000JAN;
SALE*DAY;

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;

2-27

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 variables name as
GRAPHICSCHART because no delimiter separates the
macro variable reference from the trailing text.
Partial Log
1
2
3
4
5

%let
%let
%let
%let
proc

graphics=g;
year=2000;
month=jan;
var=sale;
&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.

Macro Variable Name Delimiter

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.

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;

2.5 Delimiting Macro Variable Names

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

Refer to Exercise 4 for Module 2 in Appendix A.

Module 2 Summary

100

Understand macro variables.

2-31

2-32

Module 2 Creating and Resolving Macro Variables Prior to Compilation

Session 1 Summary

101

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.

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.

3.1 Basic Macro Functions

Macro Functions
Selected character string manipulation functions:
%UPCASE
%SUBSTR
%SCAN
%INDEX
%LENGTH

translates letters from lowercase to uppercase.

extracts a substring from a character string.
extracts a word from a character string.
searches a character string for specified text.
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

Quick Quiz
Partial Log
539
540
541
542
543
544
545

%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;

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.

3-3

3-4

Module 3 Using Macro Functions

Case Sensitivity
Partial Log
539
540
541
542
543
544
545

%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;

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";

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.

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

3.1 Basic Macro Functions

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

11130

Computer Aided Design

20800

Database Design

6375

Local Area Networks

12350

Structured Query Language

20
23000

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-5

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

3.1 Basic Macro Functions

Quick Quiz
Question: Use todays 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.

The %SUBSTR Function

text

macro triggers

text

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

&sysdate9 resolves:

%substr(30OCT2004,3)

%substr executes:

OCT2004

final substitution:

"01OCT2004"d

The %SUBSTR Function

3-7

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.

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.

3.1 Basic Macro Functions

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

The %SCAN Function

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

%let libref=%scan(work.current,1);

%scan
executes:

%let libref=work;

3-9

3-10

Module 3 Using Macro Functions

The %SCAN Function

Partial Output
Contents of Data Set WORK.CURRENT
The DATASETS Procedure
Data Set Name
Member Type
Engine
Created
Last Modified
Protection
Data Set Type
Label
Data Representation
Encoding

WORK.CURRENT
DATA
V9
Thu, Feb 05, 2004 02:04:21 PM
Thu, Feb 05, 2004 02:04:21 PM

Observations
Variables
Indexes
Observation Length
Deleted Observations
Compressed
Sorted

0
5
0
56
0
NO
NO

WINDOWS_32
wlatin1 Western (Windows)
Engine/Host Dependent Information

Data Set Page Size

Number of Data Set Pages
First Data Page
Max Obs per Page
Obs in First Data Page
Number of Data Set Repairs
File Name

Release Created
Host Created

8192
1
1
145
0
0
C:\temp\SAS Temporary
Files\_TD2140\CURRENT.sas7bdat
9.0101M0
WIN_PRO

Refer to Exercise 1 for Module 3 in Appendix A.

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.

What Is the Problem?

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

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-11

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.

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.

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.

3.2 Special Macro Functions

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

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

3-13

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

%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

The %BQUOTE Function

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

3.2 Special Macro Functions

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

The %EVAL Function

Example:

Use the %EVAL function to compute the final

year of a range.
All Courses Scheduled
2004 through 2005

Obs

1
2
3
4
5
6
7
8
9
10
11
12
13
14

Course_
Number
1
2
3
4
5
6
7
8
9
10
11
12
13
14

Course_
Code
C001
C002
C003
C004
C005
C006
C001
C002
C003
C004
C005
C006
C001
C002

Location
Seattle
Dallas
Boston
Seattle
Dallas
Boston
Dallas
Boston
Seattle
Dallas
Boston
Seattle
Boston
Seattle

Begin_
Date
26OCT2004
07DEC2004
11JAN2005
25JAN2005
01MAR2005
05APR2005
24MAY2005
14JUN2005
19JUL2005
16AUG2005
20SEP2005
04OCT2005
15NOV2005
06DEC2005

Teacher
Hallis, Dr. George
Wickam, Dr. Alice
Forest, Mr. Peter
Tally, Ms. Julia
Hallis, Dr. George
Berthan, Ms. Judy
Hallis, Dr. George
Wickam, Dr. Alice
Forest, Mr. Peter
Tally, Ms. Julia
Tally, Ms. Julia
Berthan, Ms. Judy
Hallis, Dr. George
Wickam, Dr. Alice

3-15

3-16

Module 3 Using Macro Functions

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

The %SYSFUNC Function

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

Obs
1
2

1
2

Course_
Code
C001
C002

Location
Seattle
Dallas

Begin_
Date
26OCT2004
07DEC2004

Teacher
Hallis, Dr. George
Wickam, Dr. Alice

3-17

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.

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

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

4-3

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

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
9

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.

4-5

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.

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.

4.1 Defining and Calling a Macro

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

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;
%let
%let vars=days
vars=days fee;
fee;
%printdsn
%printdsn

##
11
22

Name
Name
PRINTDSN
PRINTDSN
TIME
TIME

Type
Type
MACRO
MACRO
MACRO
MACRO

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

Symbol Table
DSN
perm.courses
DSN
perm.courses
VARS
days
VARS
days fee
fee

Word Scanner

Macro Processor

Input Stack

work.sasmacr

%printdsn
%printdsn

##
11
22

Name
Name
PRINTDSN
PRINTDSN
TIME
TIME

Type
Type
MACRO
MACRO
MACRO
MACRO

4-7

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
perm.courses
DSN
perm.courses
VARS
days
VARS
days fee
fee

Word Scanner

Macro Processor
%printdsn
%printdsn

Input Stack

work.sasmacr
##
11
22

Name
Name
PRINTDSN
PRINTDSN
TIME
TIME

Type
Type
MACRO
MACRO
MACRO
MACRO

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

Symbol Table
DSN
perm.courses
DSN
perm.courses
VARS
days
VARS
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;

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

Symbol Table
DSN
perm.courses
DSN
perm.courses
VARS
days
VARS
days fee
fee

Word Scanner

Macro Processor

Input Stack

PRINTDSN.MACRO

proc
proc print
print data=&dsn;
data=&dsn;
var
var &vars;
&vars;
run;
run;

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

4.1 Defining and Calling a Macro

Program Flow
Macro activity pauses while the word scanner tokenizes
text placed on the input stack by the macro processor.
Compiler
proc
proc print
print data=
data=

Symbol Table
DSN
perm.courses
DSN
perm.courses
VARS
days
VARS
days fee
fee

Word Scanner

Macro Processor

Input Stack
var
var &vars;
&vars;
run;
run;

PRINTDSN.MACRO
&dsn;
&dsn;

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

Program Flow
Macro variable references are passed to the macro
processor.
Compiler
proc
proc print
print data=
data=

Symbol Table
DSN
perm.courses
DSN
perm.courses
VARS
days
VARS
days fee
fee

Word Scanner

Macro Processor
&dsn
&dsn

Input Stack
var
var &vars;
&vars;
run;
run;

PRINTDSN.MACRO
;;

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

Program Flow
Symbolic substitution is performed. Word scanning
continues.
Compiler
proc
proc print
print data=
data=

Symbol Table
DSN
perm.courses
DSN
perm.courses
VARS
days
VARS
days fee
fee

Word Scanner

Macro Processor

Input Stack
perm.courses;
perm.courses;
var
var &vars;
&vars;
run;
run;
24

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

4-9

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
proc
proc print
print data=perm.courses;
data=perm.courses;
var
var days
days fee;
fee;

Word Scanner
run;
run;
Input Stack

Symbol Table
DSN
perm.courses
DSN
perm.courses
VARS
days
VARS
days fee
fee
Macro Processor
PRINTDSN.MACRO
%macro
%macro printdsn;
printdsn;
proc
proc print
print data=&dsn;
data=&dsn;
var
var &vars;
&vars;
run;
run;
%mend;
%mend;

Macro Execution
The SAS log reflects that a PROC PRINT step executed.
Partial SAS Log
243
244
245

%let dsn=perm.courses;
%let vars=days fee;
%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.

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

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.

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.

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
32

macro3

4.1 Defining and Calling a Macro

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

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 SAS9

4-13

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;

Refer to Exercise 1 for Module 4 in Appendix A.

4.2 Macro Parameters

Introduction
Example: Note macro variable references within the
PRINTDSN macro.
%macro printdsn;
proc print data=&dsn;
var &vars;
run;
%mend;

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-15

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;

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)

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.

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.

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
DSN
VARS

perm.courses
days fee

Global Table
SYSDAY
Tuesday
SYSLAST _NULL_
CITY
Dallas
AMOUNT
975

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.

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

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)

4.2 Macro Parameters

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

4-19

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

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()
56

param2

4-21

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

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-23

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.

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)
param3
%attend()
63

4.2 Macro Parameters

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-25

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.

4.2 Macro Parameters

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.

4-27

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.

The DATA Step Interface

Example: Automate production of the report below, with an
appropriate footnote.
Paid Status for Course 3
Obs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Student_Name

Student_Company

Bills, Ms. Paulette

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

Reston Railway
Motor Communications
Reston Railway
Von Crump Seafood
Semi;Conductor
SSS Inc.
Paralegal Assoc.
QA Information Systems Center
Reston Railway
So. Cal. Medical Center
Alforone Chemical
Reston Railway
Physicians IPA
SII
Federated Bank
Amberly Corp.
Lomax Services
Reston Railway
Sailbest Ships
Snowing Petroleum

Paid
Y
N
N
Y
Y
N
Y
Y
Y
Y
N
Y
Y
Y
Y
N
Y
N
Y
Y

Some Fees Due

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

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

The DATA Step Interface

Why is the footnote incorrect?
Paid Status for Course 3
Obs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Student_Name

Student_Company

Bills, Ms. Paulette

Paid
Y
N
N
Y
Y
N
Y
Y
Y
Y
N
Y
Y
Y
Y
N
Y
N
Y
Y

All Students Paid

The DATA Step Interface

Word scanning begins. Macro trigger encountered.
%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;
15

Symbol
Table
Symbol Table
crsnum

5-3

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;
set perm.all end=final;

crsnum

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:
%let crsnum=3;
%let crsnum=8;

Symbol Table
crsnum
crsnum

3
8

Please answer using your seat indicator.

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;
%let crsnum=8;

Symbol Table
crsnum

5.1 Creating Macro Variables in the DATA Step

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:
%let crsnum=3;
%let crsnum=8;

Symbol Table
crsnum

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;
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;
20

Symbol Table
crsnum
foot

3
Some Fees Due

5-5

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;
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;

Symbol Table
crsnum
foot

3
All Students Paid

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

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
Macro
if paid='Y'
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;
run;

Fixed Macro
Variable Value

5.1 Creating Macro Variables in the DATA Step

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-9

5-10

Module 5 Creating and Resolving Macro Variables During Execution

SAS Output
Paid Status for Course 3
Obs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Student_Name

Student_Company

Bills, Ms. Paulette

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.

Paid
Y
N
N
Y
Y
N
Y
Y
Y
Y
N
Y
Y
Y
Y
N
Y
N
Y
Y

5.1 Creating Macro Variables in the DATA Step

Program Flow
The statements are tokenized.

Compiler
Word
Scanner

Input
Stack

Macro Processor
data
data
_null_
_null_
;;

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

Program Flow
The %LET statement is submitted.
Compiler

Word
Scanner

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

%%
let
let
foot
foot
==
All
All
Students
Students
Paid
Paid
;;

Macro Processor

run;
run;

Input
Stack
31

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

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

Macro Processor
Word
Scanner

Input
Stack
32

foot
foot
==
All
All
Students
Students
Paid
Paid
;;

run;
run;

%let
%let

5-11

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

data
data _null_;
_null_;
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
Input
Stack
33

All Students Paid

run;
run;

Program Flow
Tokenization resumes.

Compiler

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

Macro Processor
Word
Scanner

run;
run;

Symbol Table
foot

All Students Paid

Input
Stack
34

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

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

Macro Processor
Word
Scanner
Symbol Table
foot
35

Input
Stack

Some Fees Due

5.1 Creating Macro Variables in the DATA Step

The SYMPUT Routine

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

Student_Company

Bills, Ms. Paulette

Paid
Y
N
N
Y
Y
N
Y
Y
Y
Y
N
Y
Y
Y
Y
N
Y
N
Y
Y

Note: 14 out of 20 paid

Partial Contents of PERM.ALL

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
variables value are stored in the macro variable.
Values of numeric variables are converted automatically
to character using the BEST12. format.

5-13

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;

%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

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

PAID
Y
N
N
Y
Y
N
Y
Y
Y
Y
N
Y
Y
Y
Y
N
Y
N
Y
Y

Student Activity

Open sa-symputds.sas.

Given the first row of perm.all, what is the value

for the macro variable &first ?

Obs
1

Course_
Code
C004

Course_Title
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:
Obs
1

Course_
Code
C004

Course_Title
Database Design

Symbol Table
first
second

course_title
Database Design

5.1 Creating Macro Variables in the DATA Step

The SYMPUT Routine

Example: Further enhance the report as below.
Fee Status for Local Area Networks (#3) Held 01/11/2005
Obs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Student_Name

Student_Company

Bills, Ms. Paulette

Note: $3,900 in Unpaid Fees

Paid
Y
N
N
Y
Y
N
Y
Y
Y
Y
N
Y
Y
Y
Y
N
Y
N
Y
Y

5-17

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

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

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

The SYMPUTX Routine

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

PAID
Y
N
N
Y
Y
N
Y
Y
Y
Y
N
Y
Y
Y
Y
N
Y
N
Y
Y

5-19

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

The SYMPUTX Routine

Example: Further enhance the report, as shown below.
Fee Status for Local Area Networks (#3) Held 01/11/2005
Obs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Student_Name

Student_Company

Bills, Ms. Paulette

Paid
Y
N
N
Y
Y
N
Y
Y
Y
Y
N
Y
Y
Y
Y
N
Y
N
Y
Y

Note: $3,900 in Unpaid Fees

The SYMPUTX Routine

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

5.1 Creating Macro Variables in the DATA Step

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;
sa-symputx
%students( )

Listing of STATS data set

Obs
1
2
3
4
5
6
7
8
9
10
11
12

Course_
Code
C001
C001
C002
C002
C003
C003
C004
C004
C005
C005
C006
C006

Location
Boston
Dallas
Boston
Seattle
Boston
Seattle
Dallas
Seattle
Boston
Dallas
Boston
Seattle

ENROLLMENT PERCENT
28
18
20
33
20
30
23
27
28
25
27
20

9.3645
6.0201
6.6890
11.0368
6.6890
10.0334
7.6923
9.0301
9.3645
8.3612
9.0301
6.6890

5-21

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?

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

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

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

N
Y
N
Y
Y

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;

5-23

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?

Table Lookup Application

The perm.schedule data set contains
Course_Number and Teacher variables.
Partial Listing of PERM.SCHEDULE Data Set
Obs
1
2
3
4
5
6
7
8
9
10

Course_
Number
1
2
3
4
5
6
7
8
9
10

Course_
Code
C001
C002
C003
C004
C005
C006
C001
C002
C003
C004

Begin_
Date

Location
Seattle
Dallas
Boston
Seattle
Dallas
Boston
Dallas
Boston
Seattle
Dallas

26OCT2004
07DEC2004
11JAN2005
25JAN2005
01MAR2005
05APR2005
24MAY2005
14JUN2005
19JUL2005
16AUG2005

Teacher
Hallis, Dr. George
Wickam, Dr. Alice
Forest, Mr. Peter
Tally, Ms. Julia
Hallis, Dr. George
Berthan, Ms. Judy
Hallis, Dr. George
Wickam, Dr. Alice
Forest, Mr. Peter
Tally, Ms. Julia

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

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
from Proc
Print
proc
data=perm.register
noobs;
where course_number=&crs;
var student_name paid;
title1 "Roster for Course &crs";
title2 "Taught by &teacher";
indirect1
run;
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
TEACH2
TEACH3

Hallis, Dr. George

Wickam, Dr. Alice
Forest, Mr. Peter

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-25

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

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
Obs

Course_
Code

1
2
3
4
5
6

C001
C002
C003
C004
C005
C006

Course_Title
Basic Telecommunications
Structured Query Language
Local Area Networks
Database Design
Artificial Intelligence
Computer Aided Design

Days
3
4
3
2
2
5

Fee
$795
$1150
$650
$375
$400
$1600

data _null_;
set perm.courses;
call symput(course_code,trim(course_title));
run;
sa-symput
73

5.2 Indirect References to Macro Variables

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

data
data _null_;
_null_;
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
Scanner
Input
Stack

run;
run;

Symbol Table
%put
%put _user_;
_user_;

SYSDAY

Tuesday

Partial Listing of perm.schedule

Course_Number
1
2
3
4

Teacher
Hallis, Dr. George
Wickam, Dr. Alice
Forest, Mr. Peter
Tally, Ms. Julia

data _null_;
set perm.schedule;
call symput('teach'||
left(course_number),
trim(teacher));
run;

Partial PDV
Course_
Number
N
8

Teacher
$
20

Symbol Table
SYSDAY

Tuesday

Partial Listing of perm.schedule

Course_Number
1
2
3
4

Teacher
Hallis, Dr. George
Wickam, Dr. Alice
Forest, Mr. Peter
Tally, Ms. Julia

data _null_;
set perm.schedule;
call symput('teach'||
left(course_number),
trim(teacher));
run;

The SET statement reads

the first observation into
the PDV.

Partial PDV
Course_
Number
N
8

Teacher
$
20

Hallis, Dr. George

Symbol Table
SYSDAY

Tuesday

5-27

5-28

Module 5 Creating and Resolving Macro Variables During Execution

Partial Listing of perm.schedule

Course_Number
1
2
3
4

Teacher
Hallis, Dr. George
Wickam, Dr. Alice
Forest, Mr. Peter
Tally, Ms. Julia

Partial PDV
Course_
Number
N
8

Teacher
$
20

data _null_;
set perm.schedule;
call symput('teach'||
left(course_number),
trim(teacher));
run;

CALL SYMPUT evaluates the

expressions and adds a macro
variable to the symbol table.

Hallis, Dr. George

Symbol Table
SYSDAY
TEACH1

Partial Listing of perm.schedule

Course_Number
1
2
3
4

Teacher
Hallis, Dr. George
Wickam, Dr. Alice
Forest, Mr. Peter
Tally, Ms. Julia

Tuesday
Hallis, Dr. George

Automatic return
data _null_;
set perm.schedule;
call symput('teach'||
left(course_number),
trim(teacher));
run;

Partial PDV
Course_
Number
N
8

Teacher
$
20

Hallis, Dr. George

Symbol Table
SYSDAY
TEACH1

Tuesday
Hallis, Dr. George

Partial Listing of perm.schedule

Course_Number
1
2
3
4

Teacher
Hallis, Dr. George
Wickam, Dr. Alice
Forest, Mr. Peter
Tally, Ms. Julia

data _null_;
set perm.schedule;
call symput('teach'||
left(course_number),
trim(teacher));
run;

The SET statement reads

the next observation into
the PDV.

Partial PDV
Course_
Number
N
8

Teacher
$
20

Wickam, Dr. Alice

Symbol Table

SYSDAY
TEACH1

Tuesday
Hallis, Dr. George

5.2 Indirect References to Macro Variables

Partial Listing of perm.schedule

Course_Number
1
2
3
4

Teacher
Hallis, Dr. George
Wickam, Dr. Alice
Forest, Mr. Peter
Tally, Ms. Julia

CALL SYMPUT evaluates the

expressions and adds a macro
variable to the symbol table.

Partial PDV
Course_
Number
N
8

data _null_;
set perm.schedule;
call symput('teach'||
left(course_number),
trim(teacher));
run;

Teacher
$
20

Wickam, Dr. Alice

Symbol Table
SYSDAY
TEACH1
TEACH2

Tuesday
Hallis, Dr. George
Wickam, Dr. Alice

Partial Listing of perm.schedule

Course_Number
1
2
3
4

Teacher
Hallis, Dr. George
Wickam, Dr. Alice
Forest, Mr. Peter
Tally, Ms. Julia

Partial PDV
Course_
Number
N
8

Teacher
$
20

data _null_;
set perm.schedule;
call symput('teach'||
left(course_number),
trim(teacher));
run;

Processing continues until SAS

has read all observations in the
perm.schedule data set.

Wickam, Dr. Alice

Symbol Table
SYSDAY
TEACH1
TEACH2

Tuesday
Hallis, Dr. George
Wickam, Dr. Alice

Creating a Series of Macro Variables

After the DATA step completes, control returns to the
word scanner.
Compiler
Macro Processor
Word
Scanner

Input
Stack
84

%%
put
put

_user_;
_user_;

Symbol Table
SYSDAY
TEACH1
TEACH2

Tuesday
Hallis, Dr. George
Wickam, Dr. Alice

5-29

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
Scanner

%put
%put _user_;
_user_;

Symbol Table

Input
Stack

SYSDAY
TEACH1
TEACH2

Tuesday
Hallis, Dr. George
Wickam, Dr. Alice

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;
87

indirect3

5.2 Indirect References to Macro Variables

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
Print
proc from
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?

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;

5-31

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.

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
Scannell, Ms. Robin
Seitz, Mr. Adam
Smith, Ms. Jan
Sulzbach, Mr. Bill
Williams, Mr. Gene

Paid
N
Y
N
Y
Y

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

1st scan

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

&&teach&crs

5.2 Indirect References to Macro Variables

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
1st scan

&teach&crs

&&teach&crs

&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
1st scan

&teach&crs
&teach 3

&&teach&crs
& 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
1st scan

&teach&crs
&teach 3

&&teach&crs
& teach3

WARNING

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

Forest, Mr. Peter

5-33

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
Value
Variable
CRS
TEACH1
TEACH2
TEACH3

3
Hallis, Dr. George
Wickam, Dr. Alice
Forest, Mr. Peter

Scan sequence:
&&teach&crs

&teach3

Forest, Mr. Peter

Quick Quiz
Given the following symbol table, what does
&&teach&crs resolve to?
Symbol Table
Value
Variable
CRS
TEACH1
TEACH2
TEACH3

2
Hallis, Dr. George
Wickam, Dr. Alice
Forest, Mr. Peter

5.2 Indirect References to Macro Variables

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
68
var student_name paid;
SYMBOLGEN: Macro variable CRS resolves to
69
title1 "Roster for Course &crs";
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable CRS resolves to
SYMBOLGEN: Macro variable TEACH3 resolves
70
title2 "Taught by &&teach&crs";
71 run;

3
3

3
to Forest, Mr. Peter

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-35

5-36

Module 5 Creating and Resolving Macro Variables During Execution

SAS Output
Roster for Course 3
Taught by Forest, Mr. Peter
Student_Name
Bills, Ms. Paulette
Chevarley, Ms. Arlene
Clough, Ms. Patti
Crace, Mr. Ron
Davis, Mr. Bruce
Elsins, Ms. Marisa F.
Gandy, Dr. David
Gash, Ms. Hedy
Haubold, Ms. Ann
Hudock, Ms. Cathy
Kimble, Mr. John
Kochen, Mr. Dennis
Larocque, Mr. Bret
Licht, Mr. Bryan
McKnight, Ms. Maureen E.
Scannell, Ms. Robin
Seitz, Mr. Adam
Smith, Ms. Jan
Sulzbach, Mr. Bill
Williams, Mr. Gene

Paid
Y
N
N
Y
Y
N
Y
Y
Y
Y
N
Y
Y
Y
Y
N
Y
N
Y
Y

Refer to Exercise 2 for Module 5 in Appendix A.

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-37

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

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-39

5-40

Module 5 Creating and Resolving Macro Variables During Execution

The SQL Procedure INTO Clause

SELECT Statement
Output
SQL result
Course_Code Begin_Date

C003
01/10/2006
C004
01/24/2006
C005
02/28/2006
C006
03/28/2006

Partial SAS Log

53
%put &crsid1, &date1;
C003, 01/10/2006
54
%put &crsid2, &date2;
C004, 01/24/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
SEPARATEDBY
BYdelimiter,
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;
SELECT statement output
SQL result

115

Location

Boston
Dallas
Seattle

sql4

Partial SAS Log

20
%put sites=&sites;
sites=Boston Dallas Seattle

5.3 Creating Macro Variables in SQL

Refer to Exercise 3 for Module 5 in Appendix A.

Module 5 Summary

Create macro variables during DATA step execution.

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

120

Obs

Course_
Code

1
2
3
4
5
6

C001
C002
C003
C004
C005
C006

Course_Title
Basic Telecommunications
Structured Query Language
Local Area Networks
Database Design
Artificial Intelligence
Computer Aided Design

Days
3
4
3
2
2
5

Fee
$795
$1150
$650
$375
$400
$1600

5-41

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
C001
C002
C003
C004
C005
C006

C002
Basic Telecommunications
Structured Query Language
Local Area Networks
Database Design
Artificial Intelligence
Computer Aided Design

Scan sequence:
&&&crsid
123

&c002

Structured Query Language

5.3 Creating Macro Variables in SQL

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

126

&&&crsid
&C006

Computer
Aided
Design

5-43

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.

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;

6.1 Iterative Processing

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-3

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
Obs
1
2
3
4
5

Course_
Number

Course_
Code

Location

1
2
3
4
5

C001
C002
C003
C004
C005

Seattle
Dallas
Boston
Seattle
Dallas

Begin_
Date
23OCT2000
04DEC2000
08JAN2001
22JAN2001
26FEB2001

Teacher
Hallis, Dr. George
Wickam, Dr. Alice
Forest, Mr. Peter
Tally, Ms. Julia
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

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;

year1999
data year&year;
raw1999.dat
infile "raw&year..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;
%mend readraw;
%readraw(first=2000,last=2002)
11

1999

loop2

Generating Complete Steps

Partial SAS Log
MLOGIC(READRAW):
MPRINT(READRAW):
MPRINT(READRAW):
MPRINT(READRAW):
MPRINT(READRAW):

%DO loop index variable YEAR is now 2001; loop will iterate again.
data year2001;
infile "raw2001.dat";
input course_code $4. location $15. begin_date date9. teacher $25.;
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):
MPRINT(READRAW):
MPRINT(READRAW):

proc print data=year2001;

title "Scheduled classes for 2001";
run;

NOTE: There were 12 observations read from the data set WORK.YEAR2001.
MLOGIC(READRAW):
MPRINT(READRAW):
MPRINT(READRAW):
MPRINT(READRAW):
MPRINT(READRAW):

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

6-5

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
3480
3481
3482
3483
3484

libname
PERM
PERM
PERM
PERM
PERM

memname
ALL
COURSES
REGISTER
SCHEDULE
STUDENTS

memtype
DATA
DATA
DATA
DATA
DATA

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
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL

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

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

loop3

6.1 Iterative Processing

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.

Reviewing the Results

The value of &lib..&&dsn&i is PERM.SCHEDULE.
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

&lib..&&dsn&i
PERM.&dsn4
PERM.SCHEDULE

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

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
Obs

Course_
Number

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Course_
Code
C001
C002
C003
C004
C005
C006
C001
C002
C003
C004
C005
C006
C001
C002
C003
C004
C005
C006

Location
Seattle
Dallas
Boston
Seattle
Dallas
Boston
Dallas
Boston
Seattle
Dallas
Boston
Seattle
Boston
Seattle
Dallas
Boston
Seattle
Dallas

Begin_
Date

Teacher

26OCT2004
07DEC2004
11JAN2005
25JAN2005
01MAR2005
05APR2005
24MAY2005
14JUN2005
19JUL2005
16AUG2005
20SEP2005
04OCT2005
15NOV2005
06DEC2005
10JAN2006
24JAN2006
28FEB2006
28MAR2006

Hallis, Dr. George

Wickam, Dr. Alice
Forest, Mr. Peter
Tally, Ms. Julia
Hallis, Dr. George
Berthan, Ms. Judy
Hallis, Dr. George
Wickam, Dr. Alice
Forest, Mr. Peter
Tally, Ms. Julia
Tally, Ms. Julia
Berthan, Ms. Judy
Hallis, Dr. George
Wickam, Dr. Alice
Forest, Mr. Peter
Tally, Ms. Julia
Hallis, Dr. George
Berthan, Ms. Judy

Generating Data-Dependent Steps

SAS Program and Log
MPRINT(SITES):
MPRINT(SITES):
MPRINT(SITES):
MPRINT(SITES):
MPRINT(SITES):
MPRINT(SITES):
MPRINT(SITES):
MPRINT(SITES):
MPRINT(SITES):
NOTE:
NOTE:
NOTE:
NOTE:

data Boston Dallas Seattle

set perm.schedule;
select(location);

when("Boston") output Boston;

when("Dallas") output Dallas;
when("Seattle") output Seattle;
otherwise;
end;
run;

There were 18 observations read from the data set PERM.SCHEDULE.

The data set WORK.BOSTON has 6 observations and 5 variables.
The data set WORK.DALLAS has 6 observations and 5 variables.
The data set WORK.SEATTLE has 6 observations and 5 variables.

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

Generating Data-Dependent Steps

Partial SAS log with result of %put _local_;
SITES
SITES
SITES
SITES
SITES
SITES
SITES

DATA perm.schedule
I
COUNT 3
VAR location
SITE3 Seattle
SITE2 Dallas
SITE1 Boston

The _local_ argument of the %PUT statement lists the

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

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.

6-9

6-10

Module 6 Utilizing Macro Language Statements

Generating Data-Dependent Steps

Partial SAS Log
MPRINT(SITES):
MPRINT(SITES):
MPRINT(SITES):
MPRINT(SITES):
MPRINT(SITES):
MPRINT(SITES):
MPRINT(SITES):
MPRINT(SITES):
MPRINT(SITES):
NOTE:
NOTE:
NOTE:
NOTE:

data Boston Dallas Seattle ;

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

There were 18 observations read from the data set PERM.SCHEDULE.

The data set WORK.BOSTON has 6 observations and 5 variables.
The data set WORK.DALLAS has 6 observations and 5 variables.
The data set WORK.SEATTLE has 6 observations and 5 variables.

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

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
executes at least once.

Self-Study: Conditional Iteration

Review:
573
574
575
576
577

Create a macro variable with a delimited list of

values.

proc sql noprint;

select distinct upcase(location)
into :sitelist separated by '*'
from perm.schedule;
quit;

578 %put sitelist=&sitelist;

sitelist=BOSTON*DALLAS*SEATTLE

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

6-11

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

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

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.

The Need for Macro-Level Programming

Example: Automate the application so that only one
program is required.
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 &sysdate9";
run;

Always Print
the
Daily Report

Is it
Friday?
50

Yes

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;

6-13

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

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

6-15

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.

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.
compiled.
517 %mend reports;

A dummy macro will be

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

6.2 Conditional Processing

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;

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;
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

6-17

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.

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

The %INCLUDE Statement

The contents of the external file are placed on the input
stack. The word scanner then reads the newly inserted
statements.
Input Stack

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;

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;

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

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

cond04

6-19

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

6-21

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 rows * cols ;
tables
%if &rows ne %then &rows *;
&cols
;
tables cols ;
run;
%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.

6.2 Conditional Processing

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

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.

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-23

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.

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

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

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-25

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.

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

6.3 Global and Local Symbol Table

The Global Symbol Table

Global Symbol Table
Variable
SYSDATE
SYSDAY
SYSVER
.
.
.
uservar1
uservar2

Value
23FEB04
Monday
9.1
.
.
.
value1
value2

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.

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.

6-27

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.

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
parameter1
parameter2
.
.
.
uservar1
uservar2
101

Value
value1
value2
.
.
.
value1
value2

6.3 Global and Local Symbol Table

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-29

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

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
macro call:

%LET MACVAR=VALUE;
Macro Processor

Does MACVAR already exist

in the local table?

Yes

Update MACVAR with VALUE

in the local table.

No
Does MACVAR already exist
in the global table?
No

Yes

Update MACVAR with VALUE

in the global table.

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
local table?

Yes

Retrieve its value from the

local table.

No
Does MACVAR exist in the
global table?
No

Yes

Retrieve its value from the

global table.

108

Give the tokens back to the wordscanner.

Issue warning message in SAS log:
Apparent symbolic reference MACVAR not resolved.

6.3 Global and Local Symbol Table

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;
%let x=1;
%inner
%mend outer;
%macro inner;
%local y;
%let y=&x;
%mend inner;

Create a global macro variable X.

%let x=0;
Global Table
X

111

Multiple Local Tables

Call the OUTER macro. When the %LOCAL statement
executes, a local table is created.
Global Table

%outer
%macro outer;
%local x;
%let x=1;
%inner
%mend outer;
%macro inner;
%local y;
%let y=&x;
%mend inner;

112

0
OUTER Local Table
X

What happens if the %LOCAL statement in the OUTER

115

0
OUTER Local Table
X

6.3 Global and Local Symbol Table

Multiple Local Tables

When the OUTER macro finishes execution, its local
table is removed. Only the GLOBAL table remains.
Global Table

%macro outer;
%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

118

%macro numobs(lib,dsn);
Why is NUM
%global num;
declared global in
%let num=0;
the NUMOBS
proc sql noprint;
macro? Is there
select (nobs-delobs) into :num
another solution?
from dictionary.tables
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..;
symbol1
%mend check;

6-33

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

Global Table
check Local Table

comp

Reston Railway
numobs Local Table
lib
dsn

120

work
subset

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;

num

Global Table
check Local Table

comp

121

Reston Railway
numobs Local Table
lib
dsn

work
subset

6.3 Global and Local Symbol Table

Multiple Local Tables

num

NUM is the number

of observations
selected by this
query.
Global Table

14
check Local Table

comp

Reston Railway
numobs Local Table
lib
dsn

122

work
subset

Student Activity

123

%macro numobs(lib,dsn);
Why is NUM
%*global num;
declared global in
%let num=0;
the NUMOBS
proc sql noprint;
macro?
select (nobs-delobs) into :num
from dictionary.tables
Submit the
where libname="%upcase(&lib)"
and memname="%upcase(&dsn)";
program
quit;
sa-symbol1.sas.
%let num=&num;
Notice the
%mend numobs;
%global num;
%macro check(comp);
statement is now
data subset;
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..;
sa-symbol1
%mend check;

125

%macro numobs(lib,dsn);
Omit the * from
%global num;
the %global num;
%let num=0;
statement.
proc sql noprint;
select (nobs-delobs) into :num
Submit the
from dictionary.tables
program
where libname="%upcase(&lib)"
and memname="%upcase(&dsn)";
sa-symbol1.sas
quit;
again.
%let num=&num;
%mend numobs;
What happens?
%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..;
symbol1
%mend check;

Student Activity

6-35

6-36

Module 6 Utilizing Macro Language Statements

%macro check(comp);
data subset;
set perm.students;
NUMOBS has finished
where student_company="&comp";
run;
execution. Therefore, its
%numobs(work,subset)
local symbol table is
%if &num>0 %then %do;
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

because it was placed
into the global table.

num

14
check Local 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)
program.
%if 14>0 %then %do;
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

CHECK local table will be

deleted when the CHECK
macro finishes execution.

14
CHECK Local Table

comp

Reston Railway

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

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

132

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.

6-37

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

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
Basic Telecommunications
Artificial Intelligence
Computer Aided Design

Begin
24MAY2005
01MAR2005
28MAR2006

Report Created on 05FEB2004

Location
Dallas
Dallas
Dallas

Instructor
Hallis, Dr. George
Hallis, Dr. George
Berthan, Ms. Judy

A-6

Appendix A Exercises and Solutions

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

Defining and Using Macro Variables (solution program is s-var.sas)

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

Basic Telecommunications 24MAY2005

Artificial Intelligence 01MAR2005
Computer Aided Design
28MAR2006

Dallas
Dallas
Dallas

Instructor
Hallis, Dr. George
Hallis, Dr. George
Berthan, Ms. Judy

0 Session 1

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
Basic Telecommunications
Artificial Intelligence
Computer Aided Design

Begin Location
24MAY2005 Dallas
01MAR2005 Dallas
28MAR2006 Dallas

Instructor
Hallis, Dr. George
Hallis, Dr. George
Berthan, Ms. Judy

-- Student Name=Baker, Mr. Vincent Company=Snowing Petroleum --Description

Begin Location

Structured Query Language 14JUN2005 Boston

Instructor
Wickam, Dr. Alice

----- Student Name=Bates, Ms. Ellen Company=Reston Railway ----Description

Basic Telecommunications
Database Design
Computer Aided Design

Begin Location
24MAY2005 Dallas
25JAN2005 Seattle
28MAR2006 Dallas

Instructor
Hallis, Dr. George
Tally, Ms. Julia
Berthan, Ms. Judy

Student Name=Turner, Ms. Barbara Company=Gravely Finance Center

Description

Begin Location

Structured Query Language 06DEC2005 Seattle

Computer Aided Design
28MAR2006 Dallas

Instructor
Wickam, Dr. Alice
Berthan, Ms. Judy

A-7

A-8

Appendix A Exercises and Solutions

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;

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

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
%let
%let
proc
run;

opt=;
lib=perm;
ds=_all_;
contents data=&lib..&ds &opt;

0 Session 1

A-9

After-Class Solutions to Exercises

Macro Variable References and Macro Definitions (solution program s-delim.sas)

The original program produces this output:

SAS Output
Location
Count

Boston
150
Dallas
133
Seattle
151

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

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

Select File Save As from your pull-down menu. Type the name of the program,
AfterClass1 in the File Name field.

Using the %SYMDEL Statement (solution program s-symdel)

Use the %SYMDEL statement to remove the FREQVAR and JOINVAR macro variables from
the global symbol table.
%symdel freqvar joinvar;

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

Using Macro Quoting Functions

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;

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;

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;

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

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

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
%let
%let
%put

finit=S;
minit=%bquote( );
linit=F;
&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
174
175
176
177
178
179
180
181

%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;
%let num=8;
%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
Student Name

Enrollment for Course 8

Company

Baker, Mr. Vincent

Blayney, Ms. Vivian
Boyd, Ms. Leah
Chevarley, Ms. Arlene
Coley, Mr. John
Crace, Mr. Ron
Garza, Ms. Cheryl
Hamilton, Mr. Paul
Huels, Ms. Mary Frances
Kendig, Ms. Linda
Knight, Ms. Susan
Koleff, Mr. Jim
Leon, Mr. Quinton
Lochbihler Mr. Mark
Nicholson, Ms. Elizabeth
Purvis, Mr. Michael
Ramsey, Ms. Kathleen
Shipman, Ms. Jan
Sulzbach, Mr. Bill
Woods, Mr. Joseph

Snowing Petroleum
Southern Gas Co.
United Shoes Co.
Motor Communications
California Dept. of Insurance
Von Crump Seafood
Admiral Research & Development Co.
Imperial Steel
Basic Home Services
Crossbow of California
K&P Products
Emulate Research
Dept. of Defense
K&P Products
Silver, Sachs & Co.
Roam Publishers
Pacific Solid State Corp.
Southern Edison Co.
Sailbest Ships
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

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

Using Keyword Parameters (solution program s-AfterClass2.sas)

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

Call the macro with the default values supplied in the macro definition.
%freq_report( )

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)

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;

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_
Number

Student_Name
Chavez, Ms. Louise
Edwards, Ms. Kathy
Garza, Ms. Cheryl
Gemelos, Mr. Jerry
Green, Mr. Pat
Hipps, Mr. Rich
Kiraly, Mr. Bill
Knight, Ms. Susan
Leon, Mr. Quinton
Lewanwowski, Mr. Dale R.
McCoy, Mr. Phil
Mikles, Ms. Wendy
Morgan, Ms. Kathy
Norton, Ms. Suzanne M.
Ray, Ms. Mary Frances
Right, Ms. Tina
Schier, Ms. Joan
Smith, Mr. Anthony
Smith, Ms. Donna
Stebel, Mr. Thomas C.
Voboril, Mr. Jim
Wallace, Mr. Jules
Williams, Mr. Gregory
Ziegler, Mr. David
N = 24

15
15
15
15
15
15
15
15
15
15
15
15
15
15
15
15
15
15
15
15
15
15
15
15

Paid
Y
Y
Y
Y
N
N
Y
Y
N
Y
Y
N
N
Y
Y
N
Y
Y
Y
Y
Y
Y
N
N

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

Student_Name
Chavez, Ms. Louise
Edwards, Ms. Kathy
Garza, Ms. Cheryl
Gemelos, Mr. Jerry
Green, Mr. Pat

Course_
Number
15
15
15
15
15

Paid
Y
Y
Y
Y
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
165
var student_name student_company;
SYMBOLGEN: Macro variable CRS resolves to
166
title1 "Roster for Course &crs";
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable CRS resolves to
SYMBOLGEN: Macro variable START4 resolves
167
title2 "Beginning on &&start&crs";
168 run;

4
4

4
to 01/25/2005

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

Boyd, Ms. Leah
Chan, Mr. John
Chevarley, Ms. Arlene
Chow, Ms. Sylvia
Crace, Mr. Ron
Edwards, Mr. Charles
Garza, Ms. Cheryl
Geatz, Mr. Patrick D.
Keever, Ms. Linda
Kelley, Ms. Gail
Kendig, Mr. James
Kimble, Mr. John
Koleff, Mr. Jim
Montgomery, Mr. Jeff
Moore, Mr. John
Page, Mr. Scott
Parker, Mr. Robert

Reston Railway
United Shoes Co.
California Lawyers Assn.
Motor Communications
Bostic Amplifier Inc.
Von Crump Seafood
Gorman Tire Corp.
Admiral Research & Development Co.
San Juan Gas and Electric
Crossbow of California
Crossbow of California
Rocks International
Alforone Chemical
Emulate Research
Bonstell Electronics
California Dept. of Insurance
Applied Technologies
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

Partial Output
Roster for Course 4 Beginning on 01/25/2005
Student_Name

Student_Company

Bates, Ms. Ellen

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-31

A-32

Appendix A Exercises and Solutions

After-Class Solutions to Exercises

Generating Multiple Steps with Macro Loops (solution program s-AfterClass3.sas)

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

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):
MPRINT(PRINTALL):
MPRINT(PRINTALL):
MPRINT(PRINTALL):
MPRINT(PRINTALL):

proc print data=PERM.SCHEDULE noobs;

where COURSE_CODE="C005";
title "Listing of PERM.SCHEDULE Data Set";
title2 "for COURSE_CODE=C005";
run;

A-33

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

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
real time
cpu time

2.
3.

A-35

0.00 seconds
0.01 seconds

Using Macro Variables in the DATA Step (Optional after-class exercise)

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
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL
GLOBAL

STARTDATE17 02/28/2006
STARTDATE16 01/24/2006
DSN perm.courses
VARS days fee
STARTDATE8 06/14/2005
STARTDATE18 03/28/2006
STARTDATE9 07/19/2005
CRSNUM 3
DATE 01/11/2005
STARTDATE4 01/25/2005
STARTDATE5 03/01/2005
STARTDATE6 04/05/2005
NUMPAID 14
STARTDATE7 05/24/2005
STARTDATE11 09/20/2005
NUMSTU 20
CRSNAME Local Area Networks
DUE $3,900
STARTDATE10 08/16/2005
NUM 8
STARTDATE1 10/26/2004
STARTDATE13 11/15/2005
STARTDATE2 12/07/2004
STARTDATE12 10/04/2005
STARTDATE3 01/11/2005
STARTDATE15 01/10/2006
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

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

Obs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Student_Name
Amigo, Mr. Bill
Edwards, Mr. Charles
Haubold, Ms. Ann
Hodge, Ms. Rita
McGillivray, Ms. Kathy
Pancoast, Ms. Jane
Divjak, Ms. Theresa
Gandy, Dr. David
Harrell, Mr. Ken
Hill, Mr. Paul
Lewanwowski, Mr. Dale R.
Nandy, Ms. Brenda
Ng, Mr. John
Williams, Mr. Gene
Chevarley, Ms. Arlene

Course_
Number
1
1
1
1
1
1
2
2
2
2
2
2
2
2
3

begin
26OCT2004
26OCT2004
26OCT2004
26OCT2004
26OCT2004
26OCT2004
07DEC2004
07DEC2004
07DEC2004
07DEC2004
07DEC2004
07DEC2004
07DEC2004
07DEC2004
11JAN2005

0 Session 3

A-37

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.

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.

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.

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.

The reference &VAR3 is a text string during execution, so this is also a hardcoded
value.

Value obtained from the symbol table.

Value obtained from the symbol tables does not fill allotment of 200 characters;
there are 199 trailing blanks.

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

Char

cat

Num

Char

&var3

Char

cat

Char

200

Num

Char

dog

Char

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

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

Validating Macro Parameters (solution program s-cond1.sas)

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

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)

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

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
MLOGIC(PAID): %IF condition &crsnum >= 1 and &crsnum
MPRINT(PAID):
proc print data=perm.register label n
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
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.

is TRUE
<= 18 is TRUE
noobs;

PERM.REGISTER.

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

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
Compiler

PROCEDURE
Parser

WORD SCANNER

GLOBAL STATEMENT
Parser

SQL
Compiler

SYMBOL TABLE
MACRO
PROCESSOR

MACRO LIBRARY

INPUT STACK

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

B-3

B-4

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

Review
create macro
variables

retrieve macro
variables

%LET

&macvar

create macro
variables

retrieve macro
variables

%LET

&macvar

create macro
variables

retrieve macro
variables

%LET

&macvar

Review

word
scanning
time

Review

word
scanning
time

execution
time

CALL SYMPUT

B.2 Retrieving Macro Variables in the DATA Step

The SYMGET Function

word
scanning
time

execution
time

create macro
variables

retrieve macro
variables

%LET

&macvar

CALL SYMPUT SYMGET(macvar)

The SYMGET Function

Retrieve a macro variables value during DATA step
execution with the SYMGET function.
Symbol Table
Program Data Vector
DATA Step
Variables

SYMGET

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.

Question: When does SYMGET retrieve the value of the macro variable?

B-5

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));
symget1
run;
Symbol Table
teach1
teach2
teach3

Hallis, Dr. George

Wickam, Dr. Alice
Forest, Mr. Peter

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

B.2 Retrieving Macro Variables in the DATA Step

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

data
data teachers;
teachers;
set
set perm.register;
perm.register;
length
length teacher
teacher $$ 20;
20;
teacher=symget(teach||left(course_number));
teacher=symget(teach||left(course_number));

Word
Scanner

run;
run;

Macro Processor

Symbol Table

Input
Stack

teach1
teach2
teach3

Hallis, Dr. George

Wickam, Dr. Alice
Forest, Mr. Peter

Partial Listing of perm.register

Course_
Number

Student_Name
Albritton, Mr. Bryan
Amigo, Mr. Bill
Chodnoff, Mr. Norman

1
2
1

Partial PDV
Course_
Number
N
8

Paid

Teacher
$
20

Y
Y
Y

data teachers;
set perm.register;
length teacher $ 20;
teacher=symget(teach||
left(course_number));
run;

Initialize PDV
to missing.

.
Symbol Table
teach1
teach2
teach3

Partial Listing of perm.register

Course_
Number

Student_Name
Albritton, Mr. Bryan
Amigo, Mr. Bill
Chodnoff, Mr. Norman

1
2
1

Partial PDV
Course_
Number
N
8

Paid
Y
Y
Y

Hallis, Dr. George

Wickam, Dr. Alice
Forest, Mr. Peter

data teachers;
set perm.register;
length teacher $ 20;
teacher=symget(teach||
left(course_number));
run;

The SET statement reads the

first observation into the PDV.

Teacher
$
20

1
Symbol Table

teach1
teach2
teach3

Hallis, Dr. George

Wickam, Dr. Alice
Forest, Mr. Peter

B-7

B-8

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

Partial Listing of perm.register

Course_
Number

Student_Name
Albritton, Mr. Bryan
Amigo, Mr. Bill
Chodnoff, Mr. Norman

Paid

1
2
1

Y
Y
Y

Partial PDV
Course_
Number
N
8

data teachers;
set perm.register;
length teacher $ 20;
teacher=symget(teach||
left(course_number));
run;

The SYMGET function

retrieves the macro variable
value from the symbol table.

Teacher
$
20

teacher=symget(teach1);

Hallis, Dr. George

Symbol Table
teach1
teach2
teach3

Partial Listing of perm.register

Course_
Number

Student_Name
Albritton, Mr. Bryan
Amigo, Mr. Bill
Chodnoff, Mr. Norman

Paid

1
2
1

Y
Y
Y

data teachers;
set perm.register;
length teacher $ 20;
teacher=symget(teach||
left(course_number));
run;

Automatic output

Partial PDV
Course_
Number
N
8

Hallis, Dr. George

Wickam, Dr. Alice
Forest, Mr. Peter

At the bottom of the step, SAS

automatically outputs the
observation to the new data set
work.teachers.

Teacher
$
20

Hallis, Dr. George

Symbol Table
teach1
teach2
teach3

Automatic return

Partial Listing of perm.register

Course_
Number

Student_Name
Albritton, Mr. Bryan
Amigo, Mr. Bill
Chodnoff, Mr. Norman

1
2
1

Partial PDV
Course_
Number
N
8

Teacher
$
20

Hallis, Dr. George

Wickam, Dr. Alice
Forest, Mr. Peter

Paid
Y
Y
Y

data teachers;
set perm.register;
length teacher $ 20;
teacher=symget(teach||
left(course_number));
run;

At the bottom of the step,

SAS automatically returns to
the top of the step. The PDV
is reinitialized.

1
Symbol Table

teach1
teach2
teach3

Hallis, Dr. George

Wickam, Dr. Alice
Forest, Mr. Peter

B.2 Retrieving Macro Variables in the DATA Step

Partial Listing of perm.register

Course_
Number

Student_Name
Albritton, Mr. Bryan
Amigo, Mr. Bill
Chodnoff, Mr. Norman

Paid

1
2
1

Y
Y
Y

Partial PDV
Course_
Number
N
8

data teachers;
set perm.register;
length teacher $ 20;
teacher=symget(teach||
left(course_number));
run;

The SET statement reads the

second observation into the
PDV.

Teacher
$
20

2
Symbol Table
teach1
teach2
teach3

Partial Listing of perm.register

Course_
Number

Student_Name
Albritton, Mr. Bryan
Amigo, Mr. Bill
Chodnoff, Mr. Norman

Paid

1
2
1

Y
Y
Y

Partial PDV
Course_
Number
N
8

Hallis, Dr. George

Wickam, Dr. Alice
Forest, Mr. Peter

data teachers;
set perm.register;
length teacher $ 20;
teacher=symget(teach||
left(course_number));
run;

The SYMGET function

retrieves the macro variable
value from the symbol table.

Teacher
$
20

teacher=symget(teach2);

Wickam, Dr. Alice

Symbol Table
teach1
teach2
teach3

Partial Listing of perm.register

Course_
Number

Student_Name
Albritton, Mr. Bryan
Amigo, Mr. Bill
Chodnoff, Mr. Norman

1
2
1

Partial PDV
Course_
Number
N
8

Teacher
$
20

Paid
Y
Y
Y

Hallis, Dr. George

Wickam, Dr. Alice
Forest, Mr. Peter

data teachers;
set perm.register;
length teacher $ 20;
teacher=symget(teach||
left(course_number));
run;

Processing continues until

SAS has read all rows in the
perm.register data
set.

Symbol Table

teach1
teach2
teach3

Hallis, Dr. George

Wickam, Dr. Alice
Forest, Mr. Peter

B-9

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
Obs
1
2
3

Student_Name
Albritton, Mr. Bryan
Amigo, Mr. Bill
Chodnoff, Mr. Norman

Course_
Number

teacher

1
2
1

Hallis, Dr. George

Wickam, Dr. Alice
Hallis, Dr. George

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

SAS Macro Basics
No ratings yet
SAS Macro Basics
10 pages
SAS Macro Language Reference PDF
No ratings yet
SAS Macro Language Reference PDF
385 pages
LWMC1V2 001
100% (2)
LWMC1V2 001
296 pages
Advanced SAS 9 ExamPrep SritejGunta
100% (4)
Advanced SAS 9 ExamPrep SritejGunta
60 pages
Sas Adv 130 PDF
No ratings yet
Sas Adv 130 PDF
60 pages
SaS Notes
No ratings yet
SaS Notes
8 pages
Sas Macros Final
No ratings yet
Sas Macros Final
75 pages
SAS Data Merging Techniques Guide
100% (1)
SAS Data Merging Techniques Guide
1 page
Base Sas Advance SAS Interview Questions by Keli Technologies
No ratings yet
Base Sas Advance SAS Interview Questions by Keli Technologies
12 pages
SAS DI Developer - Fast Track PDF
100% (8)
SAS DI Developer - Fast Track PDF
982 pages
SAS Certification A00-212
63% (8)
SAS Certification A00-212
81 pages
SAS DI Developer - Fast Track PDF
No ratings yet
SAS DI Developer - Fast Track PDF
982 pages
(Pronounced ": History of Sas SAS
No ratings yet
(Pronounced ": History of Sas SAS
141 pages
SAS Environment Basics Guide
No ratings yet
SAS Environment Basics Guide
417 pages
SAS Enterprise Guide Querying and Reporting - Course Notes PDF
100% (1)
SAS Enterprise Guide Querying and Reporting - Course Notes PDF
292 pages
Practice Exam. SAS Pre-Advanced
50% (2)
Practice Exam. SAS Pre-Advanced
50 pages
SAS® Programming 3 Advanced Techniques PDF
100% (3)
SAS® Programming 3 Advanced Techniques PDF
244 pages
SAS Procedures
100% (1)
SAS Procedures
44 pages
1 Base SAS Training 12th May 2015
100% (2)
1 Base SAS Training 12th May 2015
494 pages
SAS DO Loops and Arrays Guide
100% (1)
SAS DO Loops and Arrays Guide
118 pages
Very Good Examples SAS Before Interview
No ratings yet
Very Good Examples SAS Before Interview
22 pages
(Statistical Analysis System) : By: Kirtikrushna
No ratings yet
(Statistical Analysis System) : By: Kirtikrushna
129 pages
Base SAS Certification Questions Series - Part 1
100% (1)
Base SAS Certification Questions Series - Part 1
3 pages
Sample Sas Questions
100% (1)
Sample Sas Questions
4 pages
An Introduction To SAS Programming
No ratings yet
An Introduction To SAS Programming
52 pages
SAS Cheat Sheet
No ratings yet
SAS Cheat Sheet
2 pages
Introduction to PROC SQL
No ratings yet
Introduction to PROC SQL
10 pages
SAS Base Programming Exam A00-211
100% (3)
SAS Base Programming Exam A00-211
57 pages
Sas Notes
100% (2)
Sas Notes
146 pages
Advanced SAS Interview Questions and Answers
No ratings yet
Advanced SAS Interview Questions and Answers
11 pages
PROC REPORT for Clinical Data
No ratings yet
PROC REPORT for Clinical Data
43 pages
Sas Data Management
100% (1)
Sas Data Management
908 pages
Base SAS Certification Exam Solution
No ratings yet
Base SAS Certification Exam Solution
16 pages
SAS SQL - Course Notes PDF
No ratings yet
SAS SQL - Course Notes PDF
366 pages
Basic SAS Interview Questions
No ratings yet
Basic SAS Interview Questions
71 pages
SAS Macro for FTP File Listing
No ratings yet
SAS Macro for FTP File Listing
9 pages
SAS Programming 3 Advanced Techniques and Efficiencies
100% (1)
SAS Programming 3 Advanced Techniques and Efficiencies
888 pages
Base Sas Certification Exercise
No ratings yet
Base Sas Certification Exercise
47 pages
Complete Sas
100% (1)
Complete Sas
284 pages
SAS Manipulate Datasets
No ratings yet
SAS Manipulate Datasets
32 pages
Advanced SAS Programming Techniques
100% (2)
Advanced SAS Programming Techniques
81 pages
Sascheatsheet 170401221255
100% (1)
Sascheatsheet 170401221255
29 pages
SAS Tutorial: Presented By: Shashi Kumar
No ratings yet
SAS Tutorial: Presented By: Shashi Kumar
78 pages
Sas Library Dataset Creation
No ratings yet
Sas Library Dataset Creation
44 pages
SAS Exam
100% (1)
SAS Exam
32 pages
SAS Basics
100% (1)
SAS Basics
29 pages
SAS BASE CERTIFICATION QUESTIONS AND ANSWERS - PART 3 OF 4 - Listen Data PDF
No ratings yet
SAS BASE CERTIFICATION QUESTIONS AND ANSWERS - PART 3 OF 4 - Listen Data PDF
5 pages
The Ins and Outs of SAS® Data Integration Studio
No ratings yet
The Ins and Outs of SAS® Data Integration Studio
16 pages
Sas Workbook With Examples
100% (2)
Sas Workbook With Examples
100 pages
Sas Advanced Qs
No ratings yet
Sas Advanced Qs
56 pages
SAS Macro and Data Step Questions
0% (3)
SAS Macro and Data Step Questions
114 pages
Proc SQL Subquery
No ratings yet
Proc SQL Subquery
10 pages
SAS Macros
No ratings yet
SAS Macros
247 pages
Intro To SAS Macro Language Programming Seminar Description Aug 2012
No ratings yet
Intro To SAS Macro Language Programming Seminar Description Aug 2012
1 page
SAS 9 2 Macro Language Reference 1st Edition Sas Publishing Online Version
No ratings yet
SAS 9 2 Macro Language Reference 1st Edition Sas Publishing Online Version
134 pages
SAS Training and Certification 2.0 - Curriculum PDF
0% (1)
SAS Training and Certification 2.0 - Curriculum PDF
6 pages
SAS Programming 1: Essentials: Course Notes
No ratings yet
SAS Programming 1: Essentials: Course Notes
10 pages
Sas Certification Course Contenst
No ratings yet
Sas Certification Course Contenst
2 pages
SAS Macros: Introduction and Advanced Techniques
No ratings yet
SAS Macros: Introduction and Advanced Techniques
22 pages
Sas Base and Advanced OnlineTtraining Institutes in Bangalore
No ratings yet
Sas Base and Advanced OnlineTtraining Institutes in Bangalore
10 pages
Ricoh Fi8170
No ratings yet
Ricoh Fi8170
2 pages
FRP File
No ratings yet
FRP File
19 pages
اسرار الحاسب والانترنت
100% (1)
اسرار الحاسب والانترنت
28 pages
Port Security Questions: Answer
No ratings yet
Port Security Questions: Answer
28 pages
Day 5 Slot 3 Mid - SPR 24 25
No ratings yet
Day 5 Slot 3 Mid - SPR 24 25
100 pages
StarCraft Manual
54% (13)
StarCraft Manual
8 pages
Installation Manual DM800 ECDIS G2
100% (1)
Installation Manual DM800 ECDIS G2
67 pages
Employee Training Effectiveness Analyzer
No ratings yet
Employee Training Effectiveness Analyzer
3 pages
Biblatex Documentation
No ratings yet
Biblatex Documentation
277 pages
2015 IT Risk Assessment Template
No ratings yet
2015 IT Risk Assessment Template
12 pages
EPA 2006 Architecture Standard and Guidance
No ratings yet
EPA 2006 Architecture Standard and Guidance
41 pages
Year 9 ICT MID TERM Exam
No ratings yet
Year 9 ICT MID TERM Exam
9 pages
Digital Electronics N5 QP Apr 2021
No ratings yet
Digital Electronics N5 QP Apr 2021
6 pages
Arrays
No ratings yet
Arrays
15 pages
14-Troubleshooting Spanning Tree
No ratings yet
14-Troubleshooting Spanning Tree
13 pages
Fat & SAT Procedure v01
No ratings yet
Fat & SAT Procedure v01
16 pages
Soft Proofing With H&H Color Lab
No ratings yet
Soft Proofing With H&H Color Lab
4 pages
Programmable Logic Design (PLD)
No ratings yet
Programmable Logic Design (PLD)
31 pages
L2TP VPN Setup Guide for ZyWALL/USG
No ratings yet
L2TP VPN Setup Guide for ZyWALL/USG
12 pages
System Calls
No ratings yet
System Calls
31 pages
IAL IT Scheme-of-Work U4 011019
No ratings yet
IAL IT Scheme-of-Work U4 011019
35 pages
Simulated Unix File System Project Evaluation: SOFE 3950U / CSCI 3020U Operating Systems Uoit
No ratings yet
Simulated Unix File System Project Evaluation: SOFE 3950U / CSCI 3020U Operating Systems Uoit
3 pages
Routers Interview Questions and Answers Guide.: Global Guideline
No ratings yet
Routers Interview Questions and Answers Guide.: Global Guideline
5 pages
Opper Time Opper Time Domain Reflectometer Domain Reflectometer
No ratings yet
Opper Time Opper Time Domain Reflectometer Domain Reflectometer
4 pages
User Manual - SunSmart Web 2
No ratings yet
User Manual - SunSmart Web 2
77 pages
NewBlueFX Activation Guide
No ratings yet
NewBlueFX Activation Guide
4 pages
HRIS Guide for HR Professionals
No ratings yet
HRIS Guide for HR Professionals
15 pages
5G RAN Insights for Telecom Experts
100% (3)
5G RAN Insights for Telecom Experts
76 pages
Tugas Citra 2
No ratings yet
Tugas Citra 2
21 pages
2019 Subject Guide PDF
No ratings yet
2019 Subject Guide PDF
190 pages