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.

1. Positional parameter or arguments.
2. 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   male
101   23   female
102   45   male
101   56   female
;
/* calling */
% sort (demo1, demo2 nodupkey, pid);
% print (demo2);

/* To climinate duplicate observations */

data lab;
input pid test & units;
cards;
100   hr   78
101   sbp  89
102   dbp  90
101   hr     70
;
/* calling */
% sort (lab, lab2 nodupkey, pid test units);
% print (lab2);

/* import procedure */

Eg:         

% macro imp1 (file, dname, db);
proc import
datafile="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=&dname
dbms=&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=&dname
dbms=&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=&dname
dbms=&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=&dname
dbms=&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

1. % str
2. % nrstr
3. % str: Using this function, we can mask all special characters except macro triggers and unmatched quotations and unmatched brackets.
4. % 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   89
data &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   Eyedis
101   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);
disconnect
quit;
% 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= &ename
out= &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

1. Text expressions
2. Arithematic expressions
3. 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

• Normalization in SAS
• SQL Aggregations & DataSet(Row & colummn operations) in SAS
• SQL Statements in SAS
• SAS Interact With Oracle
• SAS Interview Questions & Answers