Part of the Following Document Comes From

Part of the following document comes from http://www.ats.ucla.edu/stat/sas/seminars/sas_macros_introduction/default.htm

SAS Macro

The SAS macro language is a very versatile and useful tool. It is often used to reduce the amount of regular SAS code and it facilitates passing information from one procedure to another procedure. Furthermore, we can use it to write SAS programs that are "dynamic" and flexible. Generally, we can consider macro language to be composed of macro variables and macro programs.

• Global macro variables

A macro variable in SAS is a string variable that allows you to dynamically modify the text in a SAS program through symbolic substitution.

data a;

infile C:\Documents and Settings\anna\Desktop\MyDesktop\597\597C\TropicalSales.dat';

input CustomerID $ @6 SaleDate MMDDYY10. @17 Variety $9. Quantity;

if Variety = "Ginger";

proc print data=a;

format SaleDate WORDDATE18.;

TITLE "Sales of Ginger";

run;

/*Using Macro variables*/

%let flowertype = Ginger;

data a;

infile 'C:\Documents and Settings\anna.UMASS-A9255FE97\Desktop\sas\Tropicalsales.dat';

input CustomerID $ @6 SaleDate MMDDYY10. @17 Variety $9. Quantity;

if Variety = "&flowertype";

proc print data=a;

format SaleDate WORDDATE18.;

TITLE "Sales of &flowertype";

run;

Global macro variables: for simple text substitution

Definition: %let macro-variable-name = value;

Reference: &macro-viable-name

Note:

1.  The length of the value has no limit. The value can have blank spaces, however, if it contains other special character, for example, &, %, ; , ", ect., or mnemomics, for example, and, or, etc., special care is needed in the definition

2.  The value is case-sensitive

3.  options SYMBOLGEN; Prints in the log the values of macro variables

4.  The macro variable defined this way is global in the sense that they can be used anywhere in the current SAS session

Examples of macro variables definition:

%let dir=C:\Documents and Settings\anna\Desktop\MyDesktop\597\597C;

%put &dir;

data a;

infile "&dir\TropicalSales.dat";

input CustomerID $ @6 SaleDate MMDDYY10. @17 Variety $9. Quantity;

proc print;run;

%let street=Maple;

%let num=123;

%let address=&num &street Avenue; /*Same as %let address= 123 Maple Avenue;*/

%let file=mysas.file#

%put &file;

See http://www.ats.ucla.edu/stat/sas/seminars/sas_macros_introduction/default.htm for more examples.

• Macro functions: There are many functions that are related to macro variables. They include string functions, evaluation functions and others.

Some of the most commonly used string functions include%upcase,%substrand%scan. The function%scantakes a string and an integerias arguments and returns the ith word in the string. The %substrfunction will pick out a subcomponent of a string variable; this function takes three arguments where the first argument is the string variable (a macro variable), the second is the start position of the substring and the third argument is the length of the substring. The%upcasefunction creates a new variable which contains the upper case version of a string variable.

The evaluation functions evaluate arithmetic and logical expressions. Function %eval uses integer arithmetic and function %sysevalf deals with decimal values.

See http://www.ats.ucla.edu/stat/sas/seminars/sas_macros_introduction/default.htm for examples.

·  Symput and symget function to pass information to and from a data step

There are two functions that are particularly useful when we want to get information in and out of a data step. These aresymputandsymget. You usesymputto get information from a data step into a macro variable andsymgetis used when we want to get information from a macro variable into a data step.

The syntax used iscall symput(argument1, argument2), whereargument1is the macro variable that we are creating which will store the value that is being passed out of the data step andargument2is the value in string format. Notice that the new macro variable has to be in single quotes.

See http://www.ats.ucla.edu/stat/sas/seminars/sas_macros_introduction/default.htm for an example. Another example:

data flowersales;

infile "&dir\TropicalSales.dat";

input CustomerID $ @6 SaleDate MMDDYY10. @17 Variety $9. Quantity;

proc sort data=flowersales;

BY DESCENDING quantity;

data _NULL_;

set flowersales;

if _N_=1;

CALL SYMPUT("selectedcustomer", CustomerID); /*This generates a global macro variable called selectedcustomer, whose value is the value of the variable CustomerID.*/

proc print data=flowersales;

where CustomerID = "&selectedcustomer";

FORMAT saledate WORDDATE18.;

TITLE "Customer &selectedcustomer won the PRIZE!!";

run;

data team1;

input position : $8. player : $12.;

call symput(position, player); /*This generates three global macro variables called shortstp, pitcher, and fsrstbase, with values Ann, Tom, Bill respectively*/

datalines;

shortstp Ann

Pitcher Tom

Fsrstbase Bill

;

data team2;

input position : $12. player : $12.;

call symput("pos"||left(_n_), position);

/*This generates three global macro variables

called pos1, pos2 and pos3, with values shortstp, pitcher and fsrstbase respectively*/

datalines;

shortstp Ann

Pitcher Tom

Fsrstbase Bill

;

run;

%put &pos2; /*This displays the value of macro variable, pos2, in the log window.*/

• Macro program: for more complex text substitution

Definition:

%macro macro-name(parameter_1=, parameter_2=,..., parameter_n=);

macro-text;

%mend macro-name;

Reference: %macro-name(parameter_1=value, parameter_2=value,..., parameter_n=value)

Note

1.  parameters are optional

2.  If parameters are present, they are macro variables and are referenced in macro-text as &parameter_n;

3.  Parameters are local macro variables, meaning that they exist only during the macro in which they are defined. That is, they are not recognized in the open code, which is the code outside the macro definition.

options MPRINT; Print in the log the standard SAS code generated by macro programs.

%macro varietyprint(flowertype=);

data a;

infile "&dir\TropicalSales.dat";

input CustomerID $ @6 SaleDate MMDDYY10. @17 Variety $9. Quantity;

if Variety = "&flowertype";

proc print data=a;

format SaleDate WORDDATE18.;

TITLE "Sales of &flowertype";

%mend varietyprint;

%varietyprint(flowertype=Ginger)

%varietyprint(flowertype=Protea)

run;

options ls=80 nodate;

title ' Macro processing of the two quabbin blocks';

%macro loopdat(dname,type);

data &dname;

infile "&dir\&dname.95.&type";

input block line plot type BA hunt dist herb wp1 wp45 hk1

hk45 bir1 bir45 map1 map45 oak1 oak45 ash1 ash45 oth1 oth45

total1 total45 total;

title "&dname";

proc means n mean stdev;

class line;

var total;

run;

%mend loopdat;

%loopdat(pl, dat);

%loopdat(peter, dat);

Macro statements

%IF condition %THEN action;

%ELSE %IF condition %THEN action;

%ELSE action;

%IF condition %THEN %DO;

actions;

%END;

%DO i=1 %to n;

actions;

%end;

So far we have learned:

a.  Macro statements: %IF, %DO, %let, %END, %macro, %put

b.  Macro functions: %str %scan %upcase %substr %eval %sysevalf

c.  Automatic macro variables: &SYSDATE, &SYSDAY

d.  Macro interface: CALL SYMPUT

Example 1: generating report of different flower types depending on whether today is Tuesday

options MPRINT SYMBOLGEN;

%macro ft;

%IF &SYSDAY = Tuesday %THEN %let flowertype = Ginger;

%ELSE %let flowertype = Protea;

data flowersales;

infile "&dir\TropicalSales.dat";

input CustomerID $ @6 SaleDate MMDDYY10. @17 Variety $9. Quantity;

if Variety = "&flowertype";

proc print data=flowersales;

format SaleDate WORDDATE18.;

TITLE "Sales of &flowertype";

run;

%mend ft;

%ft;

Example 2: If today is Monday, do a print procedure. If it’s a Tuesday, do a means procedure

%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;

%dailyreports;

Example 3: Same as example 2, but with choices of days as inputs to the macro program

%macro dailyreports(day1=, day2=,);

%IF &SYSDAY = &day1 %THEN %DO;

PROC PRINT DATA=flowersales;

FORMAT SaleDate WORDDATE18.;

TITLE "&day1 Report: Current Flower Sales";

%END;

%ELSE %IF &SYSDAY = &day2 %THEN %DO;

PROC MEANS DATA=flowersales MEAN MIN MAX;

CLASS variety;

VAR Quantity;

TITLE "&day2 Report: Summary of Flower Sales";

%END;

%mend dailyreports;

%dailyreports(day1=Monday, day2=Tuesday);

Example 4: an example where a macro program does not have to be a complete sas program with data steps and procedures.

%let printit=%str(proc print; run;);

%macro newvo(von=, newvon=);

if &von = . then &newvon=.;

else if &von > 25 then &newvon=1;

else &newvon=0;

%mend newvo;

%macro dropit1(size=);

%if &size=big %then drop i vo35--vo6;

%if &size=small %then drop i;

%mend dropit1;

data b;

infile "&dir\speed.dat";

input id sex vo35 vo4 vo45 vo5 vo55 vo6;

array vo[6] vo35 vo4 vo45 vo5 vo55 vo6;

array newvo[6];

do i = 1 to 6;

%newvo(von=vo[i], newvon=newvo[i]);

end;

%dropit1(size=small);

&printit

Example 5: an example of macro do used to generate a sequence of variable names

%macro names(name=);

%do speed=35 %to 60 %by 5;

&name&speed

%end;

%mend names;

data b;

infile "&dir\speed.dat";;

input id sex %names(name=vo);

array von[6] %names(name=vo);

array newvo[6] %names(name=newvo);

do i = 1 to 6;

%newvo(von=von[i], newvon=newvo[i]);

end;

&printit

Example 6: Extract the names of flower types and print the subsets of data for each flower type.

data a;

infile "&dir\TropicalSales.dat";

input CustomerID $ @6 SaleDate MMDDYY10. @17 Variety $9. Quantity;

proc sort data=a;

by Variety;

data b;

retain i 1;

set a;

by Variety;

if first.Variety then do;

CALL symput("variety"||left(i), Variety);

CALL symput("numVar", i);

i=i+1;

end;

run;

%macro varietyprint(flowertype=);

data a;

infile "&dir\TropicalSales.dat";

input CustomerID $ @6 SaleDate MMDDYY10. @17 Variety $9. Quantity;

if Variety = "&flowertype";

proc print data=a;

format SaleDate WORDDATE18.;

TITLE "Sales of &flowertype";

%mend varietyprint;

%macro calls;

%do i = 1 %to &numVar;

%varietyprint(flowertype=&variety&i)

%end;

%mend;

%calls

run;

How does SAS process &variety&i:

1. Resolves & to &

2. Passes variety as text

3. resolves &i to the value of i, for example, 2

4. returns to the beginning of the macro variable reference &variety2, starts resolving from the beginning again.

Exercises 1: You are given 100 SAS data sets called data1,... data100. Write a macro that creates a dataset that stacks the files with odd numbers if today's date is an odd number and even numbers if otherwise.

%macro datanames(start=1);

%do i=&start %to 100 %by 2;

data&i

%end;

%mend datanames;

data stacked;

today_date=input("&sysdate", date9.);

today_day=day(today_date);

res=mod(today_day, 2);

if res ~=0 then do;

set %datanames;

end;

if res=0 then do;

set %datanames(start=2);

end;

Exercises 2: Read in the following data. For each observation, use its past lagn data to predict it, n=1,...,24. Find the optimal n

data price;

input date :mmddyy10. y;

datalines;

1/1/00 299223.1

2/1/00 343131.2

3/1/00 369341.5

4/1/00 358273.3

5/1/00 400074.9

6/1/00 410722.8

7/1/00 375614.1

8/1/00 445923

9/1/00 378285.8

10/1/00 503907.2

11/1/00 642369.8

12/1/00 375814.2

1/1/01 365263.4

2/1/01 323144.7

3/1/01 387253.4

4/1/01 353870.3

5/1/01 377816.2

6/1/01 364544.1

7/1/01 406043.4

8/1/01 425989.5

9/1/01 385504.6

10/1/01 541792.2

11/1/01 680312.9

12/1/01 392366.8

1/1/02 356128.5

2/1/02 306615.5

3/1/02 351145.5

4/1/02 386984.3

5/1/02 388427.2

6/1/02 351772.8

7/1/02 392378.8

8/1/02 459218.3

9/1/02 416132.6

10/1/02 530418.2

11/1/02 741333.6

12/1/02 427820.2

1/1/03 325276.4

2/1/03 349420.4

3/1/03 368285.8

4/1/03 410983.2

5/1/03 349778

6/1/03 376627.7

7/1/03 447555.6

8/1/03 421344.9

9/1/03 463314.6

10/1/03 536309

11/1/03 636522.9

12/1/03 388700.8

1/1/04 337318.8

2/1/04 315280

3/1/04 407955.9

4/1/04 375852.9

5/1/04 387118.7

6/1/04 400021.3

7/1/04 412941.4

8/1/04 416562.1

9/1/04 479155

10/1/04 472382.9

11/1/04 607353.5

12/1/04 399040.4

;

run;

data price;

set price;

y=log(y);

run;

%macro pricemacro(lags=);

data pricenew;

set price;

%do n=1 %to &lags;

lagy&n=lag&n(y);

%end;

%do n=1 %to &lags;

proc reg data=pricenew outest=AICdata&n;

model y=lagy1-lagy&n/AIC;

run;

%end;

data AICall;

set %do i=1 %to &lags;

AICdata&i

%end;

%mend;

%pricemacro(lags=24);

proc print data=AICall;

var _AIC_;

run;

Exercises 3: Each day you read a SAS dataset containing data from counties in Wisconsin. Anywhere between 1 and 72 counties might report that day. Do the following:

1. Create a separate dataset for each reporting county.

2. Produce a separate PROC PRINT for each reporting county.

3. In the TITLE print the county name.

4. Reset the page number to 1 at the beginning of each report.

5. In a footnote print the number of observations

data countydt;

length countyNM $ 10;

input countyNM $ pop;

datalines;

hampshire 100

monroe 200

;

run;

DATA _NULL_;

SET COUNTYDT END=EOF;

BY COUNTYNM;

IF FIRST.COUNTYNM THEN DO;

NUMCTY+1;

CTYOBS=0;

END;

CTYOBS+1;

IF LAST.COUNTYNM THEN DO;

CALL SYMPUT('MCTY'||LEFT(PUT(NUMCTY,3.)),COUNTYNM);

CALL SYMPUT('MOBS'||LEFT(PUT(NUMCTY,3.)),LEFT(CTYOBS));

END;

IF EOF THEN

CALL SYMPUT('MTOTCT',NUMCTY);

RUN;

%MACRO COUNTYMC;

%DO I=1 %TO &MTOTCT;

PROC PRINT DATA=COUNTYDT;

WHERE COUNTYNM="&MCTY&I";

OPTIONS PAGENO=1;

TITLE "REPORT FOR COUNTY &MCTY&I";

FOOTNOTE "TOTAL OBSERVATION COUNT WAS &MOBS&I";

run;

%END;

%MEND COUNTYMC;

%COUNTYMC