SAS Macro Language

SAS Macro Language

SAS macro is a very powerful tool in the global SAS environment. It can perform, but not limited to, the following tasks:

• Displaying system information
• Conditional processing out side the SAS data step and procedures
• Automation for repetitive tasks
• Create data-driven applications
• Dynamically generate SAS code at run time

How the SAS compiler and word scanner works

Tokenization: The SAS word scanner recognize four class of tokens:

• Literals: String of characters in quote. E.g. ‘VAR1=’ “Today is”
• Numbers: 101 3.14 ‘29mar2003’d
• Names: data proc infile date7. dollar8.2
• Special: Characters that have reserved meanings to the compiler.
• / + - ** ; $ ( ) . & %

Tokenization example:

Input var x1-x10 z7. ;

Tokens: 1. Input 2. var 3. x1 4. - 5. x10 6. z7. ;

Macro Trigger: % followed by a name token ( Example: %let)

& followed by a name token (&sysdate)

SAS macro maintains a separate Symbol Table for macro variables. Once a

macro trigger is detected, the macro processor will process the token and send the

results to the Symbol Table.

Global Macro Variables: There are two types of global macro variables.

Automatic Variables (or System defined variables)

User-defined variables

To display automatic variables, use: %put _automatic_;

To display user defined variables, use: %put _user_;

To display all macro variables, use: %put _all_;

To Reference a macro variable: Use ampersand (&) followed by the variable name.

Example: %put &sysdate; The macro processor will return the macro

Variable sysdate’s value and put it into the log.

Example: var1=&sysdate; The macro processor will return the macro

Variable sysdate’s value and assign it to var1.

To reference a macro variable within a literal, enclose the literal in double quotes.

Example: Title “Sale Report As of &sysdate”;

How to create user defined macro variables

1. Use %LET statement: %LET varname=value;

• varname can be any name following the SAS naming convention.
• If the value is already exist in the symbol table, it will over write.
• If varname or value contains macro trigger, the trigger is evaluated first then perform assignment.
• The maximum length for value can be 32K characters
• All values are character strings
• Mathematical expression are not evaluated
• Values are case sensitive
• No quote is needed. If quote exist, it is treated as part of the value
• Leading and trailing blanks are removed

Examples:

Valur

%let name = John Smith;John Smith

%let title = ‘As of &sysdate’;‘As of &sysdate’

%let title = “As of &sysdate”;“As of 29MAR2003”

%let blank = ;

%let math = 1+1;1+1

%let myvar= var1;var1

%let myvar= Annual Report;Annual Report

2. Use CALL SYMPUT routine: CALL SYMPUT(varname, value);

• Varname is the name of the macro variable.
• The varname can be character literal. Eg: ‘myvar’
• The name can be data step variable name
• Value can be a data step variable name, a macro reference, or hard coded character literal.
• Maximum length of the value can be 32767 characters.
• The leading and trailing blanks in the data step variable’s value are preserved.
• Numeric expressions are automatically converted to character using best12. format.

Examples:

Libname cdat '/courses/ddbf9765ba27fe300';

%let numobs=2;

data_null_;

set cdat.stocks2 nobs=totobs;

call symput('numobs',totobs);

stop;

run;

%put &numobs;

data_null_;

set cdat.stocks2;

call symput(ticker,price);

run;

%put _user_;

data_null_;

call symput('ATT2', &att);

run;

%put &att2;

1. Us SQL Procedure
2. Use %do statement
3. Use Macro Call Parameters

How to DELETE user defined macro variables

%symdel macro-variable(s);

Example:

%let a=aaaa;

%put &a;

%symdel a;

%put &a;

Comments in Macro Programs

1. Use %* : Tocomment one line in SAS macro program. The comment will be ended at a semicolon (;).

Example:

%* The following is a macro program;

%macroget_ymd; %* Start the macro;

%let dt=&sysdate9.;%* get system date;

%put &dt;

%let year =%sysfunc(year("&dt"d)); %* get year;

%put &year;

%let month=%sysfunc(month("&dt"d)); %* get month;

%put &month;

%let day =%sysfunc(day("&dt"d)); %* get day;

%put &day;

%mend;

%get_ymd;

1. Use /* : You can start your comments with /*

and end your comments with */

Macro functions

%UPCASE(argument): %let var1=%upcase(abcd); %put &var1;

%SUBSTR(ARGUMENT, POSITION, <,N>): Returns the portion of the

ARGUMENT from the POSITIONth character to Nth character.

Example:

%let var1=A23456789; %put &var1;

%let var2=%substr(&var1,4); %put &var2;

%let var3=%substr(&var1,4,3); %put &var3;

%let var4=%substr(A23456789,4,3); %put &var4;

%let var5=%substr('A23456789',4,3); %put &var5;

%LENGTH(argumet); Return the length of the argument.

%let var1=A23456;

%let vlen =%length(&var1);

%let vlen2=%length(A23456);

%put &vlen &vlen2;

%INDEX(source, except); Search for the first occurrence of the string identified in

except.

Examples;

%let msg=The message is: ERROR detected;

%let msg2=%index(&msg,ERROR);

%let msg3=%index(&msg,%upcase(error));

%let msg4=%index(&msg,error);

%put &msg2;

%put &msg3;

%put &msg4;

%SCAN(argument, n <,delimiters>); Return the nth word of the argument separated

by dlimiters. If the delimiter is not specified, the following default delimiters will

be used:

blank . ( & ! $ * ) ; - / , %

Examples:

%let var1=Today is 29MAR03.;

%let dt =%scan(&var1,3); /* ,%str( ) */

%put &dt;

%EVAL(expression); Evaluate arithmetic and logical expressions. The %eval function only perform integer arithmetic.

Example:

%let a=1;

%let b=4;

%let c=1+4; %put c= &c;

%let d=%eval(1+4); %put d= &d;

%let e=%eval(&a+&b); %put e= &e;

%let f=%eval(1/3); %put f= &f; %* &f=0 not &f=0.33333333333333;

%SYSEVALF(expression); Evaluate floating point arithmetic.

This is a new feature for SAS version 8.

Example:

%let f=%sysevalf(1/3); %put f= &f; %* &f=0.33333333333333;

%SYSFUNC(function(argument(s)) <,format>); All SAS functions used in SAS data step can be used with %SYSFUN except:

DIFDIMHBOUND

IORCMSGINPUTLAG

LBOUNDMISSINGPUT

RESOLVESYMGETAll variable information functions

Example:

%let td =%sysfunc(today()); %put td = &td;

%let td2=%sysfunc(today(),date9.); %put td2= &td2;

%let modv=%sysfunc(mod(8,3)); %put modv=&modv;

%let maxv=%sysfunc(max(3,6,9)); %put maxv=&maxv;

Combing Macro Variable References with Text

Examples:

%let yy=2003;

%let mm=03;

%let ext=txt;

%let fname =data&yy&mm; %put fname = &fname;

%let fname2=_&yy&mm.data; %put fname2= &fname2;

%let fname3=report.&ext; %put fname3= &fname3;

%let fname4=rpt&yy&mm.txt; %put fname4= &fname4;

%let fname5=rpt&yy&mm..txt; %put fname5= &fname5;

Note: Macro variable name delimiter: The period (.) is a special character that is treated as part of the macro variable reference and does not appear when the macro variable is resolved.

filename myfile "c:\sas_class\data\rpt&yy&mm..txt";

%* The following program create a macro variable serious, their values are the value of variable TICKER in SAS data set stocks2. ;

%let in=cdat;

data_null_;

setin..stocks2;

call symput('var'||left(_n_), ticker); %* left function hac to be used to

remove the leading blanks;

run;

%* The following program print the values of macro variables var1 to var16 into log for checking. The macro language will be explained later;

%macrocheck;

%do i=1%to16;

%put var&i = &var&i;

%end;

%mend;

%check;

Quoting in SAS MACRO

In SAS data step, the quote is by single or double quote. However, these quotes are treated as character value in sas macro. Therefore, different method has to be used in SAS macro quoting.

%STR function: This function remove the normal meaning of a semicolon (;) and the following special tokens:

+ = * / , < > = blank LT EQ GT AND OR NOT LE GE NE

Examples:

%* The following code will not run because of error. ;

%let prog= data aa; a=1; run;

&prog;

%* The following three programs are all run with no error.;

%let prog= %str(data aa; a=1; run;);

&prog;

%let prog=data aa %str(;) a=1 %str(;) run %str(;);

&prog;

%let s=%str(;);

%let prog=data aa&s a=1 &s run &s;

&prog;

%STR function can also quote the following characters: ‘ “ ( ). But before each of these characters in the argument, a percent sign (%) must be used.

Example:

%* The following code is wrong;

%let store =%str(John's Pizza); %put &store;

%* The following code is correct.;

%let store2=%str(John%'s Pizza); %put &store2;

%NRSTR function: This function is basically the same as %STR function, but it also quote (or mask) & and %.

Example:

%let a=ABC;

%let b=Value of &a is ABC; %put &b;

%let b=%nrstr(Value of &a is ABC); %put &b;

SAS Data Step Interface

Example:

data_null_;

a=1;

if a=1 then %let msg= Great! A equals to 1;

if a ne 1then %let msg= Why it is not working?;;

run;

%put msg= &msg;

In the log wondow:

356 %put msg= &msg;

msg= Why it is not working?

In th above code, even though a=1, but the value of macro variable msg is Why it is not working?. The reason is that the action of the %LET statement is performed at compile time, not at run time. So, the value of msg is always the value of the last assignment by %let statement.

To create global macro variable at run time from within data step, use CALL SYMPUT routine.

CALL SYMPUT(varname, value);

For th above example, using call symput will be:

data _null_;

a=1;

if a=1 then call symput('msg', 'Great! A equals to 1');

if a ne 1then call symput('msg', 'Why it is not working?');

run;

%put msg= &msg;

Example:

data _nul_;

pi=3.14;

td=today();

call symput('number',1/3);

call symput('number2',1/4);

call symput('number3',pi);

call symput('number4',left(pi));

call symput('number5',trim(left(pi)));

call symput('Today', put(td,date9.));

run;

%put ***&number ***;

%put ***&number2 ***;

%put ***&number3 ***;

%put ***&number4 ***;

%put ***&number5 ***;

%put &today;

Indirect Reference to Macro

If the first macro variable’s value is the second macro variable’s name, and you try to reference the value of the second macro variable by the first macro variable’s name, it is an indirect reference.

Rules for indirect reference

• Multiple ampersands or percent signs preceding a name token cause the macro processor to rescan the reference.
• Two ampersands (&) resolve to one ampersand (&).

Example:

%let vara=A123; %put &vara;|&vara = A123

%let varb=vara; %put &varb;|&varb = vara

%let varc=&varb; %put &varc;|&varc = vara

%let vard=&varb; %put &vard;| &vard = vara

%let vare=&varb; %put &vare;| &vare = &varb = A123

%let a2=AAA;

%let i =2;

%let b=&a&i; %put &b; %* &b&i = AAA;

Example:

data_null_;

set cdat.stocks2;

call symput(ticker,price);

call symput('indust',industry);

run;

%let var1=ATT;

data_null_;

set cdat.stocks2;

where ticker="&var1";

put"The price for &var1 is %left(&var1)";

run;

data_null_;

set cdat.stocks2;

call symput(ticker,industry);

run;

%let var1=ATT;

procprintdata=cdat.stocks2;

where ticker="&var1";

Title"The price for company &var1 is %trim(&var1)";

run;

Example:

procsortdata=cdat.for_proc out=for_proc;

by extractdt risklevel score1 score2;

run;

data for_proc(drop=cnt);

length dt $4;

retain cnt 0;

retain dt;

set for_proc;

by extractdt;

if first.extractdt then

do;

cnt=cnt+1;

dt='DT'||trim(left(cnt));

end;

run;

data_null_;

set for_proc;

by extractdt;

if first.extractdt;

call symput(dt,left(extractdt));

run;

options symbolgen mprint;

%let dtname=dt2;

procprintdata=for_proc;

by extractdt;

Where extractdt=&dtname * 1;

title"Data Extracted at &dtname";

run;

SYMGET function: Obtaining macro variable values during execution or run time.

Both SYMGET function and macro reference (&) obtain macro variable value. In

Most cases, they perform the same function, therefore, the macro reference (&) is

more convenient to use. But in the following case, SYMGET function is different.

• For compiled and stored SAS programs, SQL view and SCL language programs, the macro reference is constant, while the SYMGET function is not.
• SYMGET function is a data step function, therefore it can perform variable name concatenation before obtaining the macro variable value.

Example:

Libname cdat '/courses/ddbf9765ba27fe300';

data_null_;

set cdat.stocks2 end=last;

call symput('price'||left(_n_),price);

if last thencall symput('nobs',trim(left(_n_)));

run;

data test;

array p(&nobs) p1-p&nobs;

do i=1to &nobs;

p(i)=symget('price'||left(i)); *** p(i)=&price||left(i);

end;

run;

Macro Programs

Define a Macro:

%MACRO macro-name <(parameters)>;

macro-text

%MEND <macro-name>;

macro-name: The macro-name must be a valid SAS name. You cannot use a macro expression to generate a macro name in a %MACRO statement.

In addition, the following reserved words cannot be used as a macro name:

ABENDABORTACTACTIVATEBQUOTEBY

CLEARCLOSECMSCOMANDRCOPYDEACT

DELDELETEDISPLAYDMIDSPLYDMISPLITDO

EDITELSEENDEVALFILEGLOBAL

INFILEINPUTKEYDEFLENGTHLETLIST

LISTMLOCALMACROMENDMETAAYMNRBQUOTE

NRQUOTENRSTRONOPENPAUSEPUT

QSCANQSUBSTRQSYSFUNCQUOTEQUPCASERESOLVE

RETURNRUNSAVESCANSTOPSTR

SUBSTRSUPERQSYSCALLSYSEVALFSYSEXECSYSFUNC

SYSGETSYSRPUTTHENTOTSOUNQUOTE

UNSTRUNTILUPCASEWHILEWINDOW

Parameters: Will be discussed later.

Macro_text can be:

• Any text
• SAS data steps or procs
• Macro variables, functions, or statements
• Any combination of the above

%MEND<macro-name>; To end the macro. %mend is required for all macro. The macro-name is optional. If omitted, the last macro will be ended.

To call a macro:%macro-name <(parameters)>;;

Example: Once submitted, a macro can be called repeatedly and is very efficient.

%macroplast;

proc print data=&syslast (obs=10);

title "Listing of the %trim(&syslast) data set";

run;

%mend;

data a;

a=1;

run;

%plast;

data b;

b=2;

run;

%plast;

Macro Parameter List: Macro parameters are assigned in a macro call.

• They are enclosed in parentheses
• They are separated with commas
• They can be null values, text, macro variable reference, or macro calls
• If comma (,) is part of the text or macro reference value, it should be quoted by %str() function.

Two type of parameter list:

• Positional Parameters

General form:%macro-name(value-1, value-2,…,value-n);

Positional parameters can be defined in any order. But in macro invocation, their value must appear in the same order in which the parameter names appear in the %macro statement.

• Keyword Parameters

General form:%macro-name(key-word-prm-1=<value>,

key-word-prm-2=<value>,

…

key-word-prm-n=<value>,);

Keyword Parameters for macro definition and macro invocation don’t have to be in the same order, but it is a good habit to keep them in order.

• Priority: If both positional and keyword parameters appears in the parameter, the positional parameter must come first;

Example: Positional parameters

%let name=John Smith;

%let p2=BBB;

%macro test(a,b);

%put &a;

%put &b;

%mend;

%test(John Smith, BBB);

%test(&name, &p2);

%test(Smith, John, BBB); %* ERROR ERROR ;

%test(%str(Smith, John), BBB); %* use %str() to quote comma;

%macrotest2;%* Create a macro;

%str(Smith, John);

%mend;

%test(%test2, &p2);%* Use macro call as parameter;

Example: Keyword parameters;

%macro test(a,b);

%put a= &a;

%put b= &b;

%mend;

%test(a=%str(Smith, John), b=BBB);

%test(b=BBB, a=%str(Smith, John));

Reference Environments:Use the following rule

1. When a macro statement that can create macro variable is executed, the macro processor attempts to change the value of an existing macro variable, regardless of environment, rather than creating a new macro variable.

Therefore, always try to avoid using the same variable name in different reference environment of the same SAS session.

1. The macro processor creates the variable in the current environment if no macro variable with the same name in available to it.
2. The %GLOBAL statement creates a variable in the global environment if a variable with the same name does not already exist there, regardless of which environment is current.
3. The %LOCAL statement creates a variable in the current local environment if a variable with the same name does not already exist there, even if variable with those names exist in other environments.
4. CALL SYMPUT routine is a special case.

• When the routine is used outside any macro, it create global variable.
• When the routine is used inside a macro, it follows the following rules:

1. If the current reference environment is not empty, i.e. there is at least one macro parameter or macro statement, the CALL SYMPUT routine will create a local macro variable.
2. If the current reference environment is empty, i.e. there is no macro parameter or macrostatement, the CALL SYMPUT routine will create a global macro variable in the current reference environment.

1. One rule that was not stated in SAS book is: macro parameter always create local macro variable.

Example: Demonstration for rule 1, 2.

%let a=BBB;

%macro renv;

%let a=AAA; %put &a;

%mend;

%renv;

%put &a;

Example: Demonstration for rule 3.

%macrotest_global;

%global a;

%let a=GLOBAL; %put &a;

%let b=LOCAL; %PUT &b;

%mend;

%test_global;

%put &a;

%put &b;

Example: Demonstration for rule 4.

%let a=BBB; %put &a;

%macrorenv;

%local a;

%let a=AAA; %put &a;

%mend;

%renv;

%put &a;

Example: Demonstration for call symput routine, rule 5;

%let vara=AAA;

%macrotest; %* Reference environment is empty;

data _null_;

call symput('var_global','GLOBAL'); %* var_global is global;

run;

%mend;

%test;

%put &var_global;

%macro test(notemp);%* Reference environment is not empty;

data _null_;

call symput('var_local','LOCAL');%* var_local is local;

run;

%mend;

%test(Not Empty);

%put &var_local;

%macro test;%* Reference environment is not empty;

%let mstatment= There is a macro statement;

data _null_;

call symput('var_local','LOCAL');%* var_local is local;

run;

%mend;

%test;

%put &var_local;

Example:Demonstration for rule 6.

%macro a(a,b);

%global a b;

%mend;

%a(AAA, BBB);

%put &a;

%put &b;

An Integrated Example

The following program demonstrates a data driven automated process by using SAS macro. The purpose of the program is to separate the SAS data set cdat.for_proc into many small dataset according to the value of variable EXTRACTDT. After separation, perform some data manipulation for each data set, then produce report.

The %do … %to… %end statement will be discussed in detail in the next class.

Libname cdat '/courses/ddbf9765ba27fe300';

%let lib=cdat ;

%let dsn=for_proc ;

procsortdata=&lib..&dsn out=ym_list(keep=extractdt) nodupkey;

by extractdt;

run;

data_null_;

set ym_list end=last;

call symput('ym'||left(_n_),trim(left(extractdt)));

if last thencall symput('numobs',trim(left(_n_)));

run;

options mprint mlogic symbolgen;

options nonumber nodate center ls=100;

%macro separate(obsn);

%do i=1%to &numobs;