Ways to Create User Defined Macro Variables

Ways to Create User Defined Macro Variables:

%Let
%Let Creates a Macro variable and assigns a value.
Syntax: – %LET Macro variable=value;
Examples:-
%Let Name= Akshara;
%Let Age= 20;
%Let Date=%sysfunc(today(),date9.);
See the values of above macro variables in SAS Log. Using
%put &Name.;
%put &Age.;
%put &Date.;

Data models;
Infile datalines;
Input Model $ 1-12 Class $ Price Frame $ 28-38;
Datalines;
Black Bora Track 796 Aluminum
Delta Breeze Road 399 CroMoly
Jet Stream Track 1130 CroMoly
Mistral Road 1995 Carbon Comp
Nor’easter Mountain 899 Aluminum
Santa Ana Mountain 459 Aluminum
Scirocco Mountain 2256 Titanium
Trade Wind Road 759 Aluminum
;
Run;
%Let bikeclass = Mountain;
/* Use a macro variable to subset;*/
Proc print data = models noobs;
Where Class = “&bikeclass”;
Format Price dollar6.;
Title “Current Models of &bikeclass Bicycles”;
Run;
Note that the macro variable &bikeclass will be global because it is created outside of a macro in open code. When you submit the program with a value of “Mountain”, then the macro processor will resolve the macro variable and create below standard SAS code.
Proc print data = models noobs;
Where Class = “Mountain”;
Format Price dollar6.;
Title “Current Models of Mountain Bicycles”;
Run;

Using let we can create both global & local macro variables. If it is within a macro it is Local or outside a macro or open code it is global.

%Global
Creates a Macro variable and assigns null value
If any macro variable is global that you call anywhere in the SAS system, Global macro variable is available during the execution of an entire SAS session
Syntax:- %Global Macrovariable;
%Global a;
%Put &a.;
%MACRO DATES;
%GLOBAL TODAY1 YESTERDAY THIS_MON LAST_MON DAY T_MON L_MON;
%LET TODAY1=%SYSFUNC(TODAY(),DATE9.);
%LET YESTERDAY=%SYSFUNC(INTNX(DAYS,”&TODAY1.”D,-1),DATE9.);
%LET THIS_MON=%SYSFUNC(INTNX(MON,”&TODAY1.”D,0,S),DATE9.);
%LET LAST_MON=%SYSFUNC(INTNX(MON,”&TODAY1.”D,-1,S),DATE9.);
%LET DAY=%SYSFUNC(SUBSTR(&YESTERDAY.D,1,5));
%LET T_MON=%SYSFUNC(SUBSTR(&TODAY1.D,3,3));
%LET L_MON=%SYSFUNC(SUBSTR(&LAST_MON.D,3,3));
%LET BDATE=%SYSFUNC(INTNX(MONTH,”&TODAY1.”D,0,B),DATE9.);
%LET EDATE=%SYSFUNC(INTNX(MONTH,”&TODAY1.”D,0,E),DATE9.);
%MEND;
%DATES;
%PUT &TODAY1.;
%PUT &YESTERDAY.;
%PUT &THIS_MON.;
%PUT &LAST_MON.;
%PUT &DAY.;
%PUT &T_MON.;
%PUT &L_MON.;
%PUT &BDATE.;
%PUT &EDATE.;

Proc sql;
Create table USCAR1A_temp as
Select CARRIER,
MAX(ANNIV) as ANNIV
From USMBRAMI_TEMP See how I am using Global Macro
WHERE END_DATE GE “&EDATE”D variable EDATE in SAS SQL program
And KP EQ ‘Y’
And CARRIER NOT IN(‘RZ’,’KS’,’OK’,’MO’,’TP’)
Group by CARRIER;
Quit;

See how I am using above Global Macro variables in Proc Report
OPTIONS MISSING=0;
Proc report data =My_SAS.SBB headline headskip missing nowindows SPLIT=’*’ ;
Column TM_CODE TM_NAME STATE_HEAD_CODE SH_NAME AM_CODE AM_NAME aa,(INWARDDAY INWARDMON INWARDLMON inwarddiff)
ab,(NCADAY NCAMON NCALMON ncadiff);
define TM_CODE/group ‘TM Code’ noprint ;
define TM_NAME/group ‘TM Name’ ;
define STATE_HEAD_CODE/group ‘SH Code’ noprint ;
define SH_NAME/group ‘SH Name’ ;
define AM_CODE/group ‘AM Code’ ;
define AM_NAME/group ‘AM Name’ ;
define aa/across ‘Inwards’;
define ab/across ‘NCA’;
define INWARDDAY/analysis “&DAY”; See how I am using Global
define NCADAY/analysis “&DAY”; Macro variables DAY,T_MON,
define INWARDMON/analysis “&T_MON”; L_MON in Proc report
define INWARDLMON/analysis “&L_MON”;
define inwarddiff/analysis “Difference*in MTD” ;
define NCAMON/analysis “&T_MON”;
define NCALMON/analysis “&L_MON”;
define ncadiff/analysis “Difference*in MTD”;
break after TM_NAME/summarize skip style={foreground=rose font_weight=bold};
break after SH_NAME/summarize style={foreground=green font_weight=bold};
RBREAK AFTER/SKIP SUMMARIZE style={foreground=VIBG font_weight=bold};
compute after TM_NAME;
TM_NAME=trim(TM_NAME)||’ Total’; SH_NAME=”;
endcomp;
compute after SH_NAME;
SH_NAME=trim(SH_NAME)||’ Total’;
endcomp;
compute after;
TM_NAME=’Grand Total’;
endcomp;
title “Sub-Broker Active AM Report As on &YESTERDAY”;
footnote1;
footnote2;
footnote3;
footnote4 “Note: This is a system generated mail. Please do not respond to this mail.”;
run;
ODS HTML CLOSE;

%Local
Creates a Macro variable and assigns null value
Syntax:- %Local Macrovariable;
It is useful to create a Macro variable and assigns null value that is available only during the execution of particular macro.
IT SHOULD BE WRITTEN WITH IN A MACRO, SHOULD NOT WRITE AS OPEN CODE.
%Local b;
%Put &b.;
%MACRO mac2;
%Local Name Age Date;
%Let Name=Akshara;
%Let Age=20;
%let Date=%sysfunc(today(),date9.);
%MEND;
%mac2
%put &Name. ;
%put &Age. ;
%put &Date. ;
Remember below rules while using Global & Local variables.
-> You can’t use %Global statement to convert local macro variables into global macro variables.
-> While creating global macro variables inside the %macro and %mend definition you must specify the %global statement before you use the macro variable.
Example:-
%MACRO mac3;
%Global Name Amount Date;
%Let Name=Akshara;
%Let Age =20;
%let Date=%sysfunc(today(),date9.);
%MEND;
%mac3
%put &Name. ;
%put &Age. ;
%put &Date. ;
-> The %global statement creates macro variables with null values, you still have initialized them using the %let statement or call symput function
-> If you define both %global and %local with same name the macro processor uses the value of local variable during the execution of macro, means contains local variable.
Example:-
%Let x=10;
%Macro mac1;
%let x=20;
%Put &x;
%Mend;
%mac1
%Put &x;

Observe below programs and see the result of programs.
1) %Let x=10;
%Macro mac1;
%let x=20;
%Mend;
%Put &x;

2) %Let x=10;
%Macro mac1;
%let x=20;
%Mend;
%MAC1;
%Put &x;

3) %Let x=10;
%Macro mac1;
%local x;
%let x=20;
%Mend;
%MAC1;
%Put &x;

Call Symput
Creates Macro variable in data step.
Gets information from dataset to Macro variable.
Syntax:- Call symput(‘Macrovariable’, Value);
Examples:-
Data ds;
Call symput(‘Amount’,10000);
Run;
%Put &Amount;
Data ds;
Set sashelp.class;
Call symput(‘Stdname’, Name);
Run;
%Put &Stdname.;
In above program Stdname macro variable gets last value of Name variable, but if you want pick some or all the values use below program
Data ds;
Set sashelp.class;
Call symput(‘Stdname’ ||compress(_n_), Name);
Run;
%Put &Stdname1.;
%Put &Stdname2.;
%Put &Stdname3.;
%Put &Stdname4.;
%Put &Stdname5.;
Data ds;
Set sashelp.class;
If Age >= 18 then call symput(“status”, “Adult”);
Else call symput(“status”, “Minor”);
Run;
%Put &status.;
Proc sql;
Connect to oracle as krishna
(User=misrep Password=”{sas001}QzBycDByX3Qz” Path=srmprod);
Create table dates as
Select * from connection to krishna
(Select
to_char(to_date(SYSDATE-1),’dd-Month-yyyy’) as last_1 ,
to_char(add_months(to_date(SYSDATE-1,’dd-Mon-yyyy’),-1),’Month’) as last_2,
to_char(add_months(to_date(SYSDATE-1,’dd-Mon-yyyy’),-2),’Month’) as last_3,
to_char(add_months(to_date(SYSDATE-1,’dd-Mon-yyyy’),-2),’Month’) as last_4, to_char(add_months(to_date(SYSDATE-1),-1),’dd-Month-yyyy’) as last_5,
to_char(add_months(to_date(SYSDATE-1,’dd-Mon-yyyy’),-2),’Month’) as last_6,
to_char(add_months(to_date(SYSDATE-1,’dd-Mon-yyyy’),-3),’Month’) as last_7,
to_char(add_months(to_date(SYSDATE-1,’dd-Mon-yyyy’),-3),’Month’) as last_8,
to_char(add_months (to_char (trunc(SYSDATE-1, ‘MM’)),-1),’Month’) as last_mon,
to_char (trunc(SYSDATE-1),’DD-Mon-YYYY’) as this_day ,
to_char (trunc(SYSDATE-1),’Month’) as this_mon from dual
);
Disconnect from krishna;
Quit;
Data _null_;
Set dates;
ka=last_1;
kb=last_2;
kc=last_3;
kd=last_4;
ke=last_5;
kf=last_6;
kg=last_7;
kh=last_8;
ki=last_mon;
kj=this_day ;
kk=this_mon ;
call symput(‘last_1’,ka);
call symput(‘last_2’,kb);
call symput(‘last_3’,kc);
call symput(‘last_4’,kd);
call symput(‘last_5’,ke);
call symput(‘last_6’,kf);
call symput(‘last_7’,kg);
call symput(‘last_8’,kh);
call symput(‘last_mon’,ki);
call symput(‘this_day’,kj);
call symput(‘this_mon’,kk);
Put ka kb kc kd ke kf kg kh ki kj kk;
Run;

Into in SQL
Creates a Macro variable in Proc SQL (SAS SQL Language)
Data ds1;
infile datalines;
input id name$ age sex$ sal;
datalines;
001 abc 23 F 23000
002 dfe 25 M 24500
002 mno 21 F 25000
004 rst 28 M 23000
005 xyz 30 M 34000
;
Run;

proc sql noprint;
select sum(sal) into: salary from ds1;
Quit;
%Put &salary.;
Proc sql noprint;
Select name into:empname from ds1 ;
Quit;
%Put &empname.;
Above macro variable &empname prints only first empname(abc) but if you required all the values of empname use below program.
Proc sql noprint;
Select name into:empname separated by ‘ ‘ from ds1 ;
Quit;
%Put &empname.;

Observe in below real time Report how I created Macros thru Sql and how I used those macros in required places