Authors: Puja Gautam Reshmi Vaidhyanathan Zenitha Shah

1. Authors: Puja Gautam Reshmi Vaidhyanathan Zenitha Shah SAS Macro Language

2. OBJECTIVE • Introduction • Macro Variables • Macro Functions

3. Introduction Benefits of Macro Language • It is a very versatile and useful tool • Often used to reduce the amount of regular SAS code • We can use it to write SAS programs that are “dynamic” and flexible.

4. SAS Program (Input Stack) SAS Word scanner % and & Macro Processor Non-Macros SAS Compiler

5. Global Symbol Table Macro variables are stored in a Memory area called the global symbol table. When SAS is invoked the global symbol table is created and initialized with macro variable. Note: They store numerical values as text

6. Macro Variables • Are called symbolic variables because they behave like symbols in SAS code. • Are used to store and manipulate character strings • Follow SAS naming rules • Are NOT same as DATA step variables • Are stored in memory in a macro symbol table • Every name of the macro variable is preceded with an ampersand (&) sign each time it is used

7. Macro Statements • Begin with a % and a macro keyword and end with semicolon (;) • Assign values, substitute values, and change macro variables • Can branch or generate SAS statements conditionally.

8. Types of Macro variables • Automatic • User Defined

9. Automatic Macro Variables SAS has many system-defined macro variables. These macro variables are created automatically when SAS is started. Some examples of system defined macro variables are:

11. Illustration of automatic macro variable with the help of an example: title "This report was run on &sysday, &sysdate"; Resolves to: This report was run on Sunday, 30MAY10

12. Displaying Macro Variables %PUT • Displays macro variables to the log by using %put x=&x; • Can display text, macro variables. • Is often the easiest way to debug macros. %PUT can display special variables such as:

13. User Defined Macro Variables Macro variables defined by macro programmers are called user-defined macro variables. Creating a Macro variable: %LET is used to create a macro variable Syntax: %LET macro-variable=value; The value of the macro variable can be changed and SAS will repeat the new value throughout the program until unless its changed again

14. Examples:

15. Example (Selecting a character value) %let office=Sydney; proc print data=orion.Employee_Addresses; where City="&office"; var Employee_Name; title "&office Employees"; run; Note: 1) When the value of a macro variable is a character, it has to be enclosed in double quotations 2)Wrong way to assign a character macro variable: ‘&office’ and &office

16. Example (Selecting a Numeric value) %let units=4; proc print data=orion.Order_Fact; where Quantity > &units; var Order_Date Product_ID Quantity; title "Orders exceeding &units units"; run; Note: When the value of a macro variable is numeric, it does not have to be enclosed in double quotations

17. Example (Selecting a date) %let date1=25may2007; %let date2=15jun2007; proc print data=orion.Order_Fact; where Order_Date between "&date1"d and "&date2"d; var Order_Date Product_ID Quantity; title "Orders between &date1 and &date2"; run; Note: 1) When the value of a macro variable is date, it has to be enclosed in double quotations followed by a letter ‘d’ to convert it into the SAS date format 2) In footnotes and titles the character, numeric and date values of macro variables behave like a text and they need not be enclosed in double quotations

18. Symbolgen Writes macro variable values to the SAS log as they are resolved and it’s useful to debug macros Syntax: Options Symbolgen; Example: options symbolgen; %let date1=25may2007; %let date2=15jun2007 proc print data=orion.Order_Fact; where Order_Date between "&date1"d and “&date2”d ; var Order_Date Product_ID Quantity; title "Orders between &date1 and &date2"; run;

19. SYMBOLGEN: Macro variable DATE1 resolves to 25may2007 SYMBOLGEN: Macro variable DATE2 resolves to 15jun2007 113 title "Orders between &date1 and &date2"; 114 run; 115 116 title; Note: When Symbolgen is no longer needed it can be turned off by using: Options NOSYMBOLGEN;

20. Deleting user defined macro variables %SYMDEL : Deletes one or more user defined macro variables form the global symbol table Example: %put _user_ ; Log window GLOBAL OFFICE Sydney GLOBAL DATE1 25may2007 GLOBAL PET1 Paisley GLOBAL DATE2 15jun2007 GLOBAL PET2 Sitka

21. %symdel pet1 pet2; %put _user_; Log window GLOBAL OFFICE Sydney GLOBAL DATE1 25may2007 GLOBAL DATE2 15jun2007 It deleted the user defined macro variables pet1 and pet2

22. Combining macro variables with text We can combine text with macro variables in following format: Case 1: text & variable We can place a text immediately before the macro variable as it does not change the reference Example: %let month= jan; procchart data= orion.y2000&month; hbar week/sumvar= sale; run; Resolves to: proc chart data=orion.y2000jan;

23. Case 2: &variable text We cannot place a text immediately after the macro variable as it changes the reference Example: suppose we want to prepare a gplot %let graphics= g; proc &graphicschart data= y2000jan; hbar week/sumvar= sale; run; The macro variables doesn't get resolved here, because SAS looks for macro variable graphicschart and not graphics and there is no macro variable of such name, so we need to use a delimiter to separate the macro variable from trailing text

24. If we use this program: %let graphics= g; proc &graphics. chart data= y2000jan; hbar week/sumvar= sale; run; SAS understands that macro variable is &graphics and it gets resolved to gchart

25. Case 3: &variable &variable We can place two or more macro variable one after the other Example: %let lib=orion; %let graphics=g; %let month=jan; %let year=2000; %let var=sale; Proc &graphics.chart data=&lib.y&year&month; Hbar week/sumvar=&var; Run;

26. Then this resolves to : oriony2000jan Which is not correct, this can be solved by using 2 periods at once. The 1st period is treated as delimiter and the 2nd as text Proc &graphics.chartdata=&lib..y&year&month; Now this resolves to: orion.y2000jan

27. CALL SYMPUT CALL SYMPUT takes a value from a DATA step and assigns it to a macro variable. Then this macro variable can be used in any data set. To assign a value to a single macro variable, you use CALL SYMPUT Syntax: Call SYMPUT (“macro-variable-name” , value); where macro-variable-name is the name of a macro variable and value is the value you want to assign to that macro variable

28. Example: data trees; input name $1-8 height 9-11; datalines; Maple 123 Oak 78 Birch 90 Elm 155 Poplar 65 ; run; procmeans data=trees mean std; var height; output out=meansd mean=meanheight std=sdheight; run;

29. data _null_; set meansd; call symput('treemean',meanheight); call symput('treesd',sdheight); run; data tree; set trees; standard=(height-&treemean)/&treesd; run; These statements create macro variables named &treemean and &treesd and assign to it a value of meanheight and standard deviation of height respectively

30. Macro Functions SAS macro language includes a number of macro functions. Many of these functions are similar to functions in the DATA step. Macro function can be used to do the following: • Manipulate text • Perform arithmetic Operations • Execute SAS functions

31. %INDEX: The %INDEX function searches the first argument (ARGUMENT1 for the first occurrence of the text string which is contained in the second argument (ARGUMENT2). If the target string is found, the position of its first character is returned as the function’s response (0 if not found). Syntax: %INDEX(argument1,argument2); Example: %LET X=LONG TALL SALLY; %LET Y=%INDEX(&X,TALL); Y will be resolved to 6

32. %LENGTH The %LENGTH function determines the length (number of characters) of it’s argument. The number of detected characters is then returned. When the argument is a null string the 0 is returned Syntax: %LENGTH(argument); Example: %LET X=LONG TALL SALLY; %LET Y = %length(&X); Y resolves to 15

33. %SCAN Returns the nth word of argument, where words are stings of characters separated by delimiters. Uses a default set of delimiters if none is specified. Returns a null string if there are fewer than n words in argument Syntax: %SCAN( argument , n, delimiter); Example: %let x=%SCAN(he/is/a/good/boy,3,/); Log window: X=a

34. %STR This function masks (removes the normal meaning of ) these special tokens + _ * / , < > = ; ’ “ LT EQ GT LE GE NE AND OR NOT blank Syntax: %STR(argument); Example: %macro specialchars(name); proc print data=orion.employee_addresses; where Employee_Name="&name"; var Employee_ID Street_Number Street_Name City State Postal_Code; title "Data for &name"; run; %mend specialchars; %specialchars(%str(Abbott, Ray));

35. %SUBSTR : It returns the portion of string in the first argument. The substring starts at position in the second argument and for a length of n characters. If n is not supplied its gives till the end form the position Syntax: %SUBSTR(argument,position,n); Example: %let x=%substr("abcd",2,1); Log window: X=a (Since it considers the double quotes as a character)

36. %EVAL Performs arithmetic and logical operations. It truncates non-integer results. Returns 1(true) or 0(false) for logical operations. Returns a null value and issues an error message when non-integer values are used in arithmetic operations Syntax: %EVAL(expression); Example: %let x=%eval(2+2); Log window: x=4

37. Additional Examples Example 1: Creating macro variables using proc sql: Proc sql can create SAS macro variables that contains values from a query result. In the following example we create a macro variable called higher55, which contains the number of students whose writing scores are higher than or equal to 55 proc sql; select sum(write>55) into :x from hsb2; quit; %put higher55 is &x; higher55 is 9

38. Example 2 %macro calc(stats,vars); proc means data=dataset.order_item &stats; var &vars; run; %mend calc; %calc ( min max, quantity) Here Calc gives the minimum and maximum value of quantity

39. Example 3: %macro orderstats (var=total_retail_price, class=order_type, stats=mean, decimals=2); options nolabel; title 'Order Stats'; proc means data=orion.order_fact maxdec=&decimals &stats; var &var; class &class; run; %mend orderstats; %orderstats() %orderstats(var=costprice_per_unit, class=quantity, stats=min mean max, decimals=0) %orderstats(stats=min mean max, decimals=0)

40. Example 4: (Nesting of Macro Variables) Macro variables can contain other macro variables. %let name=newpay; %let chart=%str(proc chart data=&name;vbar emp;run;); data &name; input emp$ rate; datalines; tom 10 jim 10 ; &char proc print data=&name; title "print of dataset &name"; run;

41. Example 5 (Macros with loops) %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;

42. Thank You Questions?