Transcript SAS Macro Language - California State University, East Bay

Authors:
Puja Gautam
Reshmi Vaidhyanathan
Zenitha Shah
OBJECTIVE
Introduction
Macro Variables
Macro Functions
Introduction
Benefits of Macro Language
It is a very versatile and useful tool
Often used to reduce the amount of regular SAS
code
We can use it to write SAS programs that are
“dynamic” and flexible.
SAS Program
(Input Stack)
SAS
Word scanner
Non-Macros
SAS Compiler
% and &
Macro
Processor
Global Symbol Table
Macro variables are stored in a
Memory area called the global
symbol table. When SAS is invoked
the global symbol table is created
and initialized with macro variable.
Note: They store numerical values
as text
Types
Global Symbol Table
Automatic
SYSTIME
SYSDATE
User-defined
Office
Units
Date
10:47
4jun2010
Sydney
4
25may2008
Macro Variables
Are called symbolic variables because they behave
like symbols in SAS code.
Are used to store and manipulate character strings
Follow SAS naming rules
Are NOT same as DATA step variables
Are stored in memory in a macro symbol table
Every name of the macro variable is preceded with
an ampersand (&) sign each time it is used
Macro Statements
Begin with a % and a macro keyword and end with
semicolon (;)
Assign values, substitute values, and change macro
variables
Can branch or generate SAS statements
conditionally.
Types of Macro variables
Automatic
User Defined
Automatic Macro Variables
SAS has many system-defined macro variables. These
macro variables are created automatically when SAS
is started. Some examples of system defined macro
variables are:
Variable
Contains
SYSLAST
Name of most recent SAS data
set in one field
SYSCHARWIDTH
Width value the character
SYSDATE
The character value
representing the date a SAS job
or session began executing
(two-digit year)
SYSDATE9
The character value
representing the date, a SAS job
or session began executing fourdigit year)
Illustration of automatic macro variable with the help
of an example:
title "This report was run on &sysday, &sysdate";
Resolves to:
This report was run on Sunday, 30MAY10
Displaying Macro Variables
%PUT
Displays macro variables to
the log by using
%put x=&x;
Can display text, macro
variables.
Is often the easiest way to
debug macros.
%PUT can display special
variables such as:
_ALL_
_AUTOMATIC_
Displays all current
macro variables
Displays all of the
automatically
generated variables
_GLOBAL_
Displays all of the
global variables
_USER_
Displays all of the
user-created macro
variables
_LOCAL_
Displays all of the
local variables
User Defined Macro Variables
Macro variables defined by macro programmers are called user-defined
macro variables.
Creating a Macro variable:
%LET is used to create a macro variable
Syntax:
%LET macro-variable=value;
The value of the macro variable can be changed and SAS will repeat the
new value throughout the program until unless its changed again
Examples:
%LET variable=value
%LET name = Ed Norton;
%LET name2=' Ed Norton’;
Resolves to
Name = Ed Norton
Name = ‘Ed Norton’
%LET sum=3+4;
Sum=3+4
%LET total=0 ;
Total=0
%LET total=&total + ∑
Total=0+3+4
%LET x=varlist
X=varlist
%LET start=;
Start=
Example (Selecting a character value)
%let office=Sydney;
proc print data=orion.Employee_Addresses;
where City="&office";
var Employee_Name;
title "&office Employees";
run;
Note: 1) When the value of a macro variable is a character, it
has to be enclosed in double quotations
2)Wrong way to assign a character macro variable:
‘&office’ and &office
Example (Selecting a Numeric value)
%let units=4;
proc print data=orion.Order_Fact;
where Quantity > &units;
var Order_Date Product_ID Quantity;
title "Orders exceeding &units units";
run;
Note: When the value of a macro variable is numeric, it does
not have to be enclosed in double quotations
Example (Selecting a date)
%let date1=25may2007;
%let date2=15jun2007;
proc print data=orion.Order_Fact;
where Order_Date between "&date1"d and "&date2"d;
var Order_Date Product_ID Quantity;
title "Orders between &date1 and &date2";
run;
Note: 1) When the value of a macro variable is date, it has to be
enclosed in double quotations followed by a letter ‘d’ to
convert it into the SAS date format
2) In footnotes and titles the character, numeric and date
values of macro variables behave like a text and they
need not be enclosed in double quotations
Symbolgen
Writes macro variable values to the SAS log as they are
resolved and it’s useful to debug macros
Syntax:
Options Symbolgen;
Example:
options symbolgen;
%let date1=25may2007;
%let date2=15jun2007
proc print data=orion.Order_Fact;
where Order_Date between "&date1"d and “&date2”d ;
var Order_Date Product_ID Quantity;
title "Orders between &date1 and &date2";
run;
Log Window:
SYMBOLGEN: Macro variable DATE1 resolves to 25may2007
SYMBOLGEN: Macro variable DATE2 resolves to 15jun2007
113 title "Orders between &date1 and &date2";
114 run;
115
116 title;
Note: When Symbolgen is no longer needed it can be turned
off by using:
Options NOSYMBOLGEN;
Deleting user defined macro variables
%SYMDEL :
Deletes one or more user defined macro variables form the global
symbol table
Example:
%put _user_ ;
Log window
GLOBAL OFFICE Sydney
GLOBAL DATE1 25may2007
GLOBAL PET1 Paisley
GLOBAL DATE2 15jun2007
GLOBAL PET2 Sitka
%symdel pet1 pet2;
%put _user_;
Log window
GLOBAL OFFICE Sydney
GLOBAL DATE1 25may2007
GLOBAL DATE2 15jun2007
It deleted the user defined macro variables pet1 and pet2
Combining macro variables with text
We can combine text with macro variables in following format:
Case 1: text & variable
We can place a text immediately before the macro variable as it
does not change the reference
Example:
%let month= jan;
proc chart data= orion.y2000&month;
hbar week/sumvar= sale;
run;
Resolves to:
proc chart data=orion.y2000jan;
Case 2: &variable text
We cannot place a text immediately after the macro
variable as it changes the reference
Example:
suppose we want to prepare a gplot
%let graphics= g;
proc &graphicschart data= y2000jan;
hbar week/sumvar= sale;
run;
The macro variables doesn't get resolved here, because SAS
looks for macro variable graphicschart and not graphics and
there is no macro variable of such name, so we need to use a
delimiter to separate the macro variable from trailing text
If we use this program:
%let graphics= g;
proc &graphics. chart data= y2000jan;
hbar week/sumvar= sale;
run;
SAS understands that macro variable is &graphics and
it gets resolved to gchart
Case 3: &variable &variable
We can place two or more macro variable one after
the other
Example:
%let lib=orion;
%let graphics=g;
%let month=jan;
%let year=2000;
%let var=sale;
Proc &graphics.chart data=&lib.y&year&month;
Hbar week/sumvar=&var;
Run;
Then this resolves to : oriony2000jan
Which is not correct, this can be solved by
using 2 periods at once. The 1st period is
treated as delimiter and the 2nd as text
Proc &graphics.chartdata=&lib..y&year&month;
Now this resolves to: orion.y2000jan
CALL SYMPUT
CALL SYMPUT takes a value from a DATA step and assigns it
to a macro variable. Then this macro variable can be used in
any data set. To assign a value to a single macro variable, you
use CALL SYMPUT
Syntax:
Call SYMPUT (“macro-variable-name” , value);
where macro-variable-name is the name of a macro variable
and value is the value you want to assign to that macro
variable
Example:
data trees;
input name $1-8 height 9-11;
datalines;
Maple 123
Oak 78
Birch 90
Elm 155
Poplar 65
;
run;
proc means data=trees mean std;
var height;
output out=meansd mean=meanheight std=sdheight;
run;
data _null_;
set meansd;
call symput('treemean',meanheight);
call symput('treesd',sdheight);
run;
data tree;
set trees;
standard=(height-&treemean)/&treesd;
run;
These statements create macro variables named &treemean
and &treesd and assign to it a value of meanheight and
standard deviation of height respectively
Macro Functions
SAS macro language includes a
number of macro functions. Many
of these functions are similar to
functions in the DATA step.
Macro function can be used to do
the following:
Manipulate text
Perform arithmetic Operations
Execute SAS functions
Macro
Function
Task
%INDEX
First occurrence of
a text string is located
%LENGTH
Character count
%STR
Quotes special
characters
%SUBSTR
Select text based on
position
%UPCASE
Up case Convert to
upper case
%eval
Performs arithmetic and
logical operations
%SCAN
Search for the nth
word in a text string
%INDEX:
The %INDEX function searches the first argument (ARGUMENT1
for the first occurrence of the text string which is contained in the second
argument (ARGUMENT2). If the target string is found, the position of its
first character is returned as the function’s response (0 if not found).
Syntax:
%INDEX(argument1,argument2);
Example:
%LET X=LONG TALL SALLY;
%LET Y=%INDEX(&X,TALL);
Y will be resolved to 6
%LENGTH
The %LENGTH function determines the length (number of characters)
of it’s argument. The number of detected characters is then returned.
When the argument is a null string the 0 is returned
Syntax:
%LENGTH(argument);
Example:
%LET X=LONG TALL SALLY;
%LET Y = %length(&X);
Y resolves to 15
%SCAN
Returns the nth word of argument, where words are stings of
characters separated by delimiters. Uses a default set of delimiters if none is
specified. Returns a null string if there are fewer than n words in argument
Syntax:
%SCAN( argument , n, delimiter);
Example:
%let x=%SCAN(he/is/a/good/boy,3,/);
Log window:
X=a
%STR
This function masks (removes the normal meaning of ) these special
tokens + _ * / , < > = ; ’ “ LT EQ GT LE GE NE
AND OR NOT blank
Syntax:
%STR(argument);
Example:
%macro specialchars(name);
proc print data=orion.employee_addresses;
where Employee_Name="&name";
var Employee_ID Street_Number Street_Name City State Postal_Code;
title "Data for &name";
run;
%mend specialchars;
%specialchars(%str(Abbott, Ray));
%SUBSTR :
It returns the portion of string in the first argument. The
substring starts at position in the second argument and for a
length of n characters. If n is not supplied its gives till the end
form the position
Syntax:
%SUBSTR(argument,position,n);
Example:
%let x=%substr("abcd",2,1);
Log window:
X=a
(Since it considers the double quotes as a character)
%EVAL
Performs arithmetic and logical operations. It truncates
non-integer results. Returns 1(true) or 0(false) for logical
operations. Returns a null value and issues an error message
when non-integer values are used in arithmetic operations
Syntax:
%EVAL(expression);
Example:
%let x=%eval(2+2);
Log window:
x=4
Additional Examples
Example 1:
Creating macro variables using proc sql:
Proc sql can create SAS macro variables that contains values from a
query result. In the following example we create a macro variable called
higher55, which contains the number of students whose writing scores are
higher than or equal to 55
proc sql;
select sum(write>55) into :x
from hsb2;
quit;
%put higher55 is &x;
higher55 is 9
Example 2
%macro calc(stats,vars);
proc means data=dataset.order_item &stats;
var &vars;
run;
%mend calc;
%calc ( min max, quantity)
Here Calc gives the minimum and maximum value of quantity
Example 3:
%macro orderstats (var=total_retail_price, class=order_type,
stats=mean, decimals=2);
options nolabel;
title 'Order Stats';
proc means data=orion.order_fact maxdec=&decimals &stats;
var &var;
class &class;
run;
%mend orderstats;
%orderstats()
%orderstats(var=costprice_per_unit, class=quantity, stats=min mean
max, decimals=0)
%orderstats(stats=min mean max, decimals=0)
Example 4: (Nesting of Macro Variables)
Macro variables can contain other macro variables.
%let name=newpay;
%let chart=%str(proc chart data=&name;vbar emp;run;);
data &name;
input emp$ rate;
datalines;
tom 10
jim 10
;
&char
proc print data=&name;
title "print of dataset &name";
run;
Example 5 (Macros with loops)
%macro dailyreports;
%if &SYSDAY=Monday %THEN %DO;
proc print data=flowersales;
format SaleDate WORDDATE18.;
title 'Monday Report: Current Flower Sales';
%end;
%else %if &SYSDAY= Tuesday %then %do;
proc means DATA=flowersales mean min max;
class Variety;
var Quantity;
title 'Tuesday Report: Summary of Flower Sales';
%end;
%mend dailyreports;
Thank You
Questions?