1 / 32

SAS Code and Macros: How They Interact

SAS Code and Macros: How They Interact. Bruce Gilsen Federal Reserve Board February 2010. Introduction. Experienced users often confused about relationship between SAS code (DATA and PROC steps) Macros Have “the talk” with Federal Reserve users often Helpful, important to understand

melba
Download Presentation

SAS Code and Macros: How They Interact

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SAS Code and Macros: How They Interact Bruce Gilsen Federal Reserve Board February 2010

  2. Introduction • Experienced users often confused about relationship between • SAS code (DATA and PROC steps) • Macros • Have “the talk” with Federal Reserve users often • Helpful, important to understand • This paper: • Informal explanation • Simple examples

  3. Informal explanation • “Macros generate code” • Macros have: • Macro language elements (%DO, %IF, %PUT, %STR,…) • SAS (non-macro) code • During macro execution • Macro processor executes macro language elements, “generates” 0 or more lines of SAS code • Generate: place on input stack • When macro completes or at step boundary • Any generated SAS code processed as if typed at that location

  4. Informal explanation • Can use macros to generate SAS code • Large block of code: DATA step, PROC step, multiple steps • Single SAS statement • Part of a SAS statement

  5. Example 1: SAS code but no macro language elements • %macro mac1; • data one; (1) • x1=11; (2) • x2=22; (3) • x3=33; (4) • run; (5) • %mend mac1; (6) • %mac1; (7) • When %MAC1 invoked with (7) • SAS statements (1) – (5) generated • (5): Step boundary: DATA step ONE compiled, executed • (6): Ends macro • Useful if need DATA step in multiple places

  6. Slight change: move RUN; outside macro • %macro mac1; • data one; (1) • x1=11; (2) • x2=22; (3) • x3=33; (4) • %mend mac1; (5) • %mac1; (6) • run; (7) • When %MAC1 invoked with (6) • SAS statements (1) – (4) generated • (5): Ends macro. (1) – (4) are on input stack • (7): Step boundary: DATA step ONE compiled, executed

  7. Example 2: SAS code and macro language elements w/a step boundary • DATA steps • Macro language elements - %LET, %PUT • Illustrate effect of step boundary

  8. %macro mac2; • data one; (1) • x=1; (2) • %let blah=11; (3) • %put in DATA step before PUT statement blah=&blah; (4) • put x=; (5) • %let blah=22; (6) • %put in DATA step after PUT statement blah=&blah; (7) • run; (8) • %put after DATA step; (9) • %mend mac2; (10) • %mac2;

  9. %macro mac2; • data one; (1) • x=1; (2) • %let blah=11; (3) • %put in DATA step before PUT statement blah=&blah; (4) • put x=; (5) • %let blah=22; (6) • %put in DATA step after PUT statement blah=&blah; (7) • run; (8) • %put after DATA step; (9) • %mend mac2; (10) • (1), (2): SAS statements, generated • (3), (4): Macro language element, immediately executed • (5): SAS statement, generated • (6), (7): Macro language element, immediately executed • (8): Step boundary, DATA step ONE is (1), (2), (5), (8) • ONE compiled, executed • (5) writes x=1 to SAS log • (9): Macro language element, immediately executed • Order in log: (4), (7), (5), (9)

  10. %macro mac2; • data one; (1) • x=1; (2) • %let blah=11; (3) • %put in DATA step before PUT statement blah=&blah; (4) • put x=; (5) • %let blah=22; (6) • %put in DATA step after PUT statement blah=&blah; (7) • run; (8) • %put after DATA step; (9) • %mend mac2; (10) • SAS log: • in DATA step before PUT statement blah=11 • in DATA step after PUT statement blah=22 • x=1 • NOTE: The data set WORK.ONE has 1 observations and 1 variables. • NOTE: DATA statement used (Total process time): • real time 0.00 seconds • cpu time 0.01 seconds • after DATA step

  11. Example 3: Use macro language to conditionally generate SAS statement • Want “x2=22;” in DATA step only when &MACVAR=1 • If &MACVAR=1 • data one; • x1=11; • x2=22; • x3=33; • run; • If &MACVAR not = 1 • data one; • x1=11; • x3=33; • run;

  12. %let macvar=1; * Condition is true ; • %macro mac3; • data one; (1) • x1=11; (2) • %if &macvar=1 %then %do; (3) • x2=22; (4) • %end; • x3=33; (5) • run; (6) • %mend mac3; (7) • %mac3 • (1), (2): SAS statements, generated • (3): Macro processor replaces &MACVAR with its value, 1 • Original statement: %if &macvar=1 %then %do; • After replacement: %if 1=1 %then %do; • (3): True, so (4) is generated • (5), (6): SAS statements, generated • (6): Step boundary, ONE compiled, executed • (7): Ends macro execution

  13. %let macvar=-999; * Condition is NOT true ; • %macro mac3; • data one; (1) • x1=11; (2) • %if &macvar=1 %then %do; (3) • x2=22; (4) • %end; • x3=33; (5) • run; (6) • %mend mac3; (7) • %mac3 • (1), (2): SAS statements, generated • (3): Macro processor replaces &MACVAR with its value, -999 • Original statement: %if &macvar=1 %then %do; • After replacement: %if -999=1 %then %do; • (3): False, so (4) is not generated • (5), (6): SAS statements, generated • (6): Step boundary, ONE compiled, executed • (7): Ends macro execution

  14. Example 4: Use macro loop to generate clauses in a SAS statement • Rename variables X1, X2, X3, to Y1, Y2, Y3 in TWO • data one; • x1=11; • x2=22; • x3=33; • run; • data two; • set one; • rename • x1=y1 • x2=y2 • x3=y3 • ; • run;

  15. Can generate clauses in a macro loop • More generalized • Macro loop hard coded to 3 in example, data driven in real application • data two; • set one; • rename • x1=y1 • x2=y2 • x3=y3 • ; • run;

  16. data two; * Hard-coded ; • set one; • rename • x1=y1 • x2=y2 • x3=y3 • ; • run; • %macro mac4; * Same code generated in a macro ; • %local j; • data two; • set one; • rename • %do j = 1 %to 3; • x&j = y&j • %end; • ; • run; • %mend mac4; • %mac4;

  17. %macro mac4; Generated by MAC4: • %local j; • data two; (1) data two; • set one; (2) set one; • rename (3) rename • %do j = 1 %to 3; (4) x1=y1 • x&j = y&j (5) x2=y2 • %end; (6) x3=y3 • ; (7) ; • run; (8) run; • %mend mac4; (9) • %mac4; • In MAC4, RENAME has 3 components • RENAME precedes macro loop, generated by (3) • Each iteration of macro loop, (4) - (6), generates one clause • 1st time: x1=y1 • 2nd time: x2=y2 • 3rd time: x3=y3 • ; follows macro loop, generated by (7)

  18. %do j = 1 %to 3; (4) • x&j = y&j (5) • %end; (6) • (4): Replace J with value, 1. 1 within loop bounds, 1 to 3, %DO continues. • (5): SAS statement generated: x1= y1 • (6): %END ends %DO, macro returns to (4) • (4): Increment J to 2. 2 within loop bounds, %DO continues. • (5): SAS statement generated: x2= y2 • (6): %END ends %DO, macro returns to (4) • (4): Increment J to 3. 3 within loop bounds, %DO continues. • (5): SAS statement generated: x3= y3 • (6): %END ends %DO, macro returns to (4) • (4): Increment J to 4. 4 outside loop bounds, %DO ends.

  19. %macro mac4; • %local j; • data two; (1) • set one; (2) • rename (3) • %do j = 1 %to 3; (4) • x&j = y&j (5) • %end; (6) • ; (7) • run; (8) • %mend mac4; (9) • %mac4; • (1) - (3): SAS statements, generated • (4) - (6): Executed by macro processor, generates • 1st time: x1=y1 • 2nd time: x2=y2 • 3rd time: x3=y3 • (7), (8): SAS statements, generated • (8): Step boundary, TWO compiled, executed • (9): Ends macro execution

  20. Example 5: Macro loop to generate SAS statement clauses: alternate way • Macro just has macro loop to generate clauses • Same SAS code generated as in prior example %macro mac4; %macro mac5; %local j; %local j; data two; %do j = 1 %to 3; set one; x&j = y&j rename %end; %do j = 1 %to 3; %mend mac5; x&j = y&j data two; %end; set one; ; rename run; %mac5 %mend mac4; ; %mac4; run;

  21. %macro mac5; (1) • %local j; (2) • %do j = 1 %to 3; (3) • x&j = y&j (4) • %end; (5) • %mend mac5; (6) • data two; (7) • set one; (8) • rename (9) • %mac5 (10) • ; (11) • run; (12) • (1) - (6): Define MAC5. (6) ends macro definition, macro compiled • (7) - (9): SAS statements, generated • (10): Invokes MAC5 • (3) – (5): Execute 3 times, generates 1st time: x1=y1 2nd time: x2=y2 3rd time: x3=y3 • (6): Ends macro execution • (11), (12): SAS statements, generated • (12): Step boundary, TWO compiled, executed

  22. Example 6: Use “Data driven” code to rename variables • Rename all variables in data set that start with “X”, preface with “OLD_” • X  OLD_X • XYZ  OLD_XYZ • Unknown: # of variables starting with “X”, their names • Data driven, loop bounds • Hard coded in earlier examples • Build from data, more generalized

  23. DICTIONARY tables: introduction • Read-only SAS data views, current session information • Active data libraries, SAS data sets, external files • SAS macros, option settings, etc. • Updated as session progresses • To access a table • PROC SQL (this paper) • In any PROC or DATA step, refer to table’s PROC SQL view in SASHELP library • Documentation • Introduction: “SAS Language Reference: Concepts” • Details: PROC SQL chapter, “Base 9.1.3 Procedures Guide”

  24. Read variables starting with “X” from Dictionary table, copy to macro variables data one; proc sqlnoprint; x1=11; select count(*) (1) y=22; into :num_vars xxx=33; from dictionary.columns xyz=44; where libname="WORK“ run; and memname="ONE" and upcase(substr(name,1,1))="X"; %let num_vars=&num_vars; (2) select name (3) into :var1-:var&num_vars from dictionary.columns where libname="WORK" and memname="ONE" and upcase(substr(name,1,1))="X"; quit;

  25. Read variables starting with “X” from Dictionary table, copy to macro variables data one; proc sqlnoprint; x1=11; select count(*) (1) y=22; into :num_vars xxx=33; from dictionary.columns xyz=44; where libname="WORK“ run; and memname="ONE" and upcase(substr(name,1,1))="X"; %let num_vars=&num_vars; (2) • Summary function COUNT counts # variable names in WORK.ONE that start with “X” • Result, 3, stored in &NUM_VARS • Assign macro variable to itself: removes leading, trailing blanks

  26. Read variables starting with “X” from Dictionary table, copy to macro variables data one; select name (3) x1=11; into :var1-:var&num_vars y=22; from dictionary.columns xxx=33; where libname="WORK" xyz=44; and memname="ONE" run; and upcase(substr(name,1,1))="X"; quit; • 3 variable names meet selection criteria (in WORK.ONE, start with “x”), copied to macro variables Macro variable Value VAR1 x1 VAR2 xxx VAR3 xyz

  27. && in %DO: indirect reference • %do j = 1 %to &numvars; Macro variable Value • &&var&j = old_&&var&j VAR1 x1 • %end; VAR2 xxx • VAR3 xyz • &&VAR&J when J=1: • Pass 1: 1. && ===> & • 2. VAR is text, unchanged • 3. &J ===> 1 • Result: &&VAR&J ===> &VAR1 • Pass 2: 1. &VAR1 ===> x1 • What if &VAR&J instead?

  28. %do j = 1 %to &num_vars; (6)Macro variable Value • &&var&j = old_&&var&j (7) VAR1 x1 • %end; (8) VAR2 xxx • VAR3 xyz • (6): Replace J with value, 1. 1 within loop bounds, 1 to 3, %DO continues. • (7): SAS statement generated: x1= old_x1 • (8): %END ends %DO, macro returns to (4) • (6): Increment J to 2. 2 within loop bounds, %DO continues. • (7): SAS statement generated: xxx= old_xxx • (8): %END ends %DO, macro returns to (4) • (6): Increment J to 3. 3 within loop bounds, %DO continues. • (7): SAS statement generated: xyz= old_xyz • (8): %END ends %DO, macro returns to (4) • (6): Increment J to 4. 4 outside loop bounds, %DO ends.

  29. %macro mac6; (4) Macro variable Value • %local j; (5) VAR1 x1 • %do j = 1 %to &num_vars; (6) VAR2 xxx • &&var&j = old_&&var&j (7) VAR3 xyz • %end; (8) • %mend mac6; (9) • proc datasets library=work nolist; (10) • modify one; (11) • rename (12) • %mac6 (13) • ; (14) • quit; run; (15) • (4) - (9): Define MAC6. (9) ends macro definition, macro compiled • (10) - (12): SAS statements, generated • (13): Invokes MAC6 • (6) - (8): Execute 3 times, generates 1st time: x1=old_x1 2nd time: xxx=old_xxx 3rd time: xyz=old_xyz • (9): Ends macro execution • (14), (15): SAS statements, generated • (15): Step boundary, PROC DATASETS compiled, executed

  30. Robust version of this code • Ensure no variable name longer than 28 characters • Maximum variable name length 32 • OLD_ + 29 character name = 33 character name, error • Ensure no cases where both exist: • oldvariablename • OLD_oldvariablename • Example: • XXX, OLD_XXX in data set • Rename XXX to OLD_XXX generates error - duplicate name

  31. Conclusion • Experienced users often confused about relationship between • SAS code (DATA and PROC steps) • Macros • This paper: • Informal explanation • Simple examples • Essential to understand • Understanding existing code • Developing code

  32. For more information, please contact • Bruce Gilsen • Federal Reserve Board, mail stop 157 • Washington, DC 20551 • phone: 202-452-2494 • e-mail: bruce.gilsen@frb.gov • SAS is a registered trademark or trademark of SAS • Institute Inc. in the USA and other countries. ® • indicates USA registration. • Other brand and product names are registered • trademarks or trademarks of their respective • companies.

More Related