Working with Macro Variable in SAS
Macro Variables Creation
Macrovariables are of 3 types
- Global macrovariable
- Local macrovariable
- Automatic macrovariable
- All macrovariables work like character variables.
a) Global macrovariable Creation
Note: In one macrovariable we can store upto 32,767 characters.
- Global macrovariable creation can be done anywhere in programming coding (inside/outside of he macroblock) and we can use anywhere in programming coding.
% global Statement: It can be used to create global macrovariable
Syntax: %global <macrovariable>;
% let Statement: It can be used to assign required value to macrovariable.
Syntax: % let <macrovariable(name)> = <value>;
Eg:
% global center group;% let center = applo;% let group = 100;
Macrovariable reference
If we want to report macrovariable then we will use macrovariable reference (&) & % put statement.
% put Statement: It can be used to print required text and macrovariable result in log window.
Syntax: % put & <macrovariable (name)>;
Eg:
% put macro var value is & center;% put macro var value is & group;
Eg:
% macro cre;% global cname;% let cname = satyam;% mend;% cre;% put & cname;
b) Local macrovariable creation
We can create inside of the macroblock and we can use in only in current macroblock.
- Local macrovariable values stores in local symbol tables.
% local Statement: It can be used to create macrovariables.
Eg:
% macro loc;% local pid age;% let pid = p100;% let age = 45;% put & pid age is & age;% mend;% loc;
c) Automatic macrovariables creation
Automatic variables created by SAS & values assigned by SAS. It is a system defined User can use automatic macrovariables but user can't reassign any value to automatic macrovariables. Its values stores into a global symbol tables.
Note: Global and local macrovariables, we can call as user defined macrovariables.
Learn the core features of SAS and become master with our expertise SAS Tutorials.
Passing Arguments to Macros
Argument: Macro arguments should be written in after the macro name within brackets. These arguments work like a local macrovariable.
% macro print (danem);proc print data=&dname;run;% mend;/* calling */% print (sasuser.demo);% print (sasuser.medi);% print (sashelp.buy);
- We can pass the arguments in two ways.
- Positional parameter or arguments.
- Keyword parameter.
1) Positional parameter
It can be used to pass the arguments based on position. Ascending, descending, duplicate observation, duplicate variables.
% macro sort (ename, new, s var);proc sort data = &ename out=&new'by & svar;run;% mend;% sort (demo, demo1, pid);%print (demo1);% sort (demo, demo2, descending pid);
automatic macrovariables. Its values stores into a global symbol tables.
Note: Global and local macrovariables, we can call as user defined macrovariables.
/* To report duplicate data values and variables */
data demo1;input pid age gender &;cards;100 34 male101 23 female102 45 male101 56 female;/* calling */% sort (demo1, demo2 nodupkey, pid);% print (demo2);
/* To climinate duplicate observations */
data lab;input pid test & units;cards;100 hr 78101 sbp 89102 dbp 90101 hr 70;/* calling */% sort (lab, lab2 nodupkey, pid test units);% print (lab2);
/* import procedure */
Eg:
% macro imp1 (file, dname, db);proc importdatafile="file" out=&dname dbms=&db replace;run;% mend;/* calling */% imp1 (d:\dataset.xls, demo1, excel);
Eg:
% macro imp2 (file, dname, db, sh);proc import datafile="file" out=&dnamedbms=&db replace;sheet="&sh";run;% mend;/* calling */% imp2 (d:\dataset.xls, demo1, excel, medi);
Eg:
% macro imp3 (file, dname, db, sh, gt);proc import datafile="file" out=&dnamedbms=&db replace;sheet="&sh";getnames=>run;% mend;/* calling */% imp3 (d:\dataset.xls, lab, excel, sheet1 & , gt);
Eg:
% macro imp4 (file, dname, db, sh, t, rg);proc import datafile="file" out=&dnamedbms=&db replace;sheet="&sh";getnames=>range="&rg";run;% mend;/* calling */% imp4 (d:\dataset.xls, demo, excel, demo, a1:g10);% imp4 (d:\dataset.xls, one, excel, demo, no, a10:g20);
/* To import required variables */
% imp4 (d:\ base.xls, three (keep=subid age color), (excel, demo);part of variables & part of data% imp4 (d:\ base.xls, three (keep=subid age gender), (excel, demo, no, a20:e30);
/* To rename the variables names */
% imp4 (d:\ base.xls, three (keep=f1 f3 f5)rename=(f1=stno f3=gender f5=color)),(excel, demo, no, a20:e30);
/* To develop macro application for access file */
% macro acc1 (table, dname, db, file);proc import table=&tab out=&dnamedbms=&db replace;database="&file";run;% mend;/* calling */% acc1 (labdata, lab, access, d:\data.mdb);% acc1 (labdata, lab1, (keep=pid units), access, d:\data.mdb);% acc1 (labdata, lab1, (drop=test), access, d:\data.mdb);% acc1 (labdata, lab2, (where=(test='hr')), access, d:\data.mdb);
2) Keyword parameters
It can be used to pass the arguments to macro by using parameters or argument names.
Eg:
% macro acc1(tab=1, dname= , db= , file= );proc import table=&tab out=&dname dbms=&db replace;database="&file";run;% mend;% acc1 (tab=labdata, dname=lab, db=access, file=d:\ data.mdb');% acc1 (tab=labdata, dname=lab1, (keep=pid units), file=d:\ data.mdb, db=access);
Note: If we want to use keyword and positional parameters in same application we must use below case only.
positional first next keyword
% macro imp4 (file, dname, db, sh= , gt= , rg= );
3) Macro Quoting functions
Using with macro quoting functions, we can mask the special characters at compilation time. These are of 2 types
- % str
- % nrstr
- % str: Using this function, we can mask all special characters except macro triggers and unmatched quotations and unmatched brackets.
- % nrstr: Using this function, we can mask all special characters include macro triggers at compilation time.
/* To develop macro application for subset of data */
Eg:
% macro subset(new, ename, con);data &new;set &ename;where &con;run;% mend;% macro print(ename);proc print data= &ename;run;% mend;/* calling */% subset (demo1, demofile, %str (color='B'));% print (demo1);/* calling */% subset (demo2, demofile, age>25);% print (demo2);/* calling */% subset (demo3, demofile, %nrstr (age>=25 & color='W'));% print (demo3);/* calling */% subset (demo4, (keep=pid age race), demofile, %str (race='AF'));% print (demo4);
(or)
% subset (demo4, %str(demofile(keep=pid age race), %str (race='AF'));% print (demo4);/* calling */% subset (demo5, (keep=pid age race), demofile, %str ((color='W'));% print (demo5);/* calling */% subset (demo6, demofile(rename=(patid=subid)), %nrstr (race='AS' & color='W'));% print (demo6);
/* To develop merge application in macros */
Eg:
% macro sort(ename, new, svar);proc sort data=&ename out=&new;by &svar;run;% mend;% macro print(ename);proc print data= &ename; input pid age;run; cards;% mend; 100 90% macro merge (new, enames, mvar); 101 89data &new; ;merge &enames; data lab;by &mvar; input pid test & units;run; cards;% mend; 100 hr 89/* calling */ 100 sbp 78% sort (demo, demo1, pid); 101 hr 64% sort (lab, lab1, pid); 101 dbp 56% sort (dlab, demo1 lab1, pid); 100 sbp 85% sort (dlab); 101 dbp 96;
/* To report matching data */
Eg:
data Uex;input stno uextype&;cards;100 Eyedis101 Eardis;/* calling */% sort (Uex, uex1, stno);% sort (sad, sad1, stno);% merge (Usad, % str)Uex(in=var1) sad1(in=var2)),% nrstr (stno; if var1=1 & var2=1));% print (Usad);
/* To report non-matching data from 2 datasets */
/* calling */% merge (usad1, % str(Uex(in=var1) sad1(in=var2)),% nrstr (stno; if var1=0 / var2=0));% print (usad1);
/* To report non-matching data from hte required dataset(Uex) */
/* calling */% merge (usad2, % str(Uex sad1(in=var)),% nrstr (stno; if var1=0));% print (usad2);
/* To report non-matching data from the sad */
/* calling */% merge (usad3, % str(Uex (in=var)sad1)),% nrstr (stno; if var=0));% print (usad3);
/* To develop joins coding in macros */
proc sql;select * from Uex, sad where Uex stno=sad.stno;select * from Uex, sad on Uex stno=sad.stno;select * from Uex, sad inner join Uex stno=sad.stno;
Note: If we want to use period between two macrovariable, instead of single period we should use two period(. .) because in macro single dot denotes default delimeter.
% macro join (e1, e2, jo, con, mvar);proc sql;select 8 from &e1 &jo &e2 &con &e1 . . &mvar= &e2 . . &mvar;quit;% mend;% join (Uex, sad, %str(,), where, stno); - simple join% join (Uex, sad, inner join, on, stno);% join (Uex, sad, left join, on, stno);% join (Uex, sad, right join, on, stno);% join (Uex, sad, full join, on, stno);
/* To develop macro application with pass through totally */
% macro connect (db= , file= , tab= );proc sql;connect to &db (&file);select * from connection to &db(select * from &tab);disconnectquit;% mend;/* calling */ - for excel% connect (db=excel, file=%str(path='d:\base.xls'), tab=[demo&]);/* for access */% connect (db=access, file=%str(path='d:\base.mdb'), tab=rawdata 1);/* for oracle */% connect (db=oracle, file=%str(User=scott password=tiger path='Ora'), tab=lab);
4) Macro Options
Macro options is a type of global options. Its default working whenever we run the macro application.
- Macro options can be changed by using option statement. This statement should be written ouside of the macro block.
- It dispalys a warning message in log window whenever macrocall is not resolved.
- Using with this option, we can trace out required catalog(macrocall) existed or not. If we want to change nomerror. Default working is merror. More efficient is merror.
Serror:
Its default working and prints warning message whenever macro variable is not resolved.
- If we want to change - noserror.
Mprint:
using Mprint option, we can trace out required macrocall to report errors in SAS coding. Default is noMprint.
Eg:
Opitons mprint;% connect (db=oracle, file=%str(User=scott password=tiger path='Ora'), tab=lab);
Symbolgen option:
It can be used to trace out macrovariable value (or) It prints message in logwindow how to resolve macrovariable. Default is nosymbolgen.
Eg:
Options mprint symbolgen;% connect (db=oracle, file=%str(User= scott password=tiger path='Ora'), tab=lab);
Nested macros:
If we write macroblock or macrocall inside of the another macroblock, then these macroblocks are called nested macros.
Mprint nest options:
It can be used to trace out nested macro with relation. Default is nomprintnest.
Eg:
% macro print (dname);proc print data= &dname;run;% mend;% macro sprint(ename, new, var);proc sort data= &enameout= &new;by &var;run;% print (&new);% mend;options mprintnest;% sprint(sasuser.medi, medi4, drug);
5) Macro Expressions
It requires operators and operands (var) .
Eg: sale1 + sale2
sale > = 50
These are 3 types they are
- Text expressions
- Arithematic expressions
- logical expressions
a) Text expression:
Macro coding is also called as text expression.
b) Arthematic expression:
It can be used to run arthematic operations in macros.
i) %eval function: It can be used to do arthematic operations using macrovariables.
% global var1 var2;% let var1 = 80;% let var2 = 90;% global var3;% let var3 = % eval (&var1+&var2);% put &var3;
Note: % eval function temporarily convert macrovariable value character to numeric format.
ii) % sysevalif function: If macrovariables have period of characters (float values) then we will use sysevalif for arthematic operations.
% global var var5;% let var4 = 80.20;% let var5 = 90.30;% global var6;% let var6 = % sysevalif (&var4+&var5);% put &var6;
For indepth knowledge on SAS, click on below