Macro Variables Make Life Easier: Applications with SAS® to Excel

1. Macro Variables Make Life Easier: Applications with SAS® to Excel Misty Johnson Research Analyst-Senior State of Wisconsin Department of Health Services Madison, WI, USA MWSUG 2010 Milwaukee, WI Paper 40-2010

2. Introduction • Research Analyst, WI DHS • Medicaid Waivers • Reports with sensitive info for Counties • Don’t mix up the Counties!!! • HIPAA violation!!! • Repetitive efforts • Macros and macro variables simplify task • Minimize error potential too!

3. Abstract • Use macro variables to • Simplify repetitive actions • Know what you’re pulling • Verify report before printing • Guide Dynamic Data Exchange (DDE)

4. Set Macro Variables on Top * >ENTER DATA YEAR HERE>>>>>>; %LETYEAR=2010; * >ENTER AGENCY NUMBER HERE>; %LETC_AGENCY=201300; * >ENTER AGENCY NAME HERE>>>; %LETAGENAME=Dane; * >ENTER REPORT NAME HERE>>>>; %LETRPTNAME=Dane HSD L300; * >ENTER PASSWORD HERE>>>>>>>; %LETPASSWORD=Dane2; * >ENTER RECIPIENT HERE>>>>>>>; %LETRECIP=Jean French; * >ENTER PAPER ORIENTATION>>>>; %LETORIENT=Landscape; * >ENTER PAPER SIZE>>>>>>>>>>>>; %LETPAPER1=Legal; * *++++++++++++++++++++++++++++++++++++++++++++++++++++++++++;

5. Tell the Output “Where to go…” DATA _NULL_; SET CY&YEAR..asofdate; CALL SYMPUT('currdtlong',put(today(),DATE9.)); CALL SYMPUT('asofdatelong',PUT(asofdate,DATE9.)); RUN; >ENTER OUTPUT PATH HERE>>>>; %LET PREPATH1= "H:\TEMP\PreRpt_for_&agename._rtn_&currdtlong..xls"; %LET PATH1= "H:\&YEAR.\&AGENAME.\Rpt_for_&agename._&year._asof_&asofdatelong._rtn_&currdtlong..xls";

6. Create Report

7. Before you print, VERIFY!! • Grab Agency Name from the top line • Is it what you thought? • Yes? Then ship to Excel • No? Do nothing further

8. Before you print, VERIFY!! DATA _NULL_; SET REPORT; IF _N_=1 THEN CALL SYMPUT('AGENCYNAME',PUT(AGENCY,$25.)); RUN; DATA _NULL_; SET CY&YEAR..asofdate; IF (LOWCASE(SCAN("&AGENAME.",1)))= (LOWCASE(SCAN("&AGENCYNAME.",1))) THEN CALL SYMPUT('ANSWER',PUT("YES”, $5.)); ELSE CALL SYMPUT('ANSWER',PUT("NO”, $5.)); RUN;

9. Ok to print?? %MACROPRINT_REPORT; %IF &ANSWER.=YES %THEN %DO; PROC DBLOAD DBMS=EXCEL DATA= REPORT; PATH=&PREPATH1.; PUTNAMES=YES; LIMIT=0; label; reset all; LOAD; RUN; %END; RUN; %MEND PRINT_REPORT; /* INVOKE MACRO TO WRITE TO EXCEL IF CORRECT COUNTY */ %PRINT_REPORT; RUN;

10. PROC DBLOAD = Ugly….

11. Would like to see this… but how?

12. Behold, the Power of…… DDE Dynamic Data Exchange

13. What DDE can do for you… • Format Excel documents FOR you • In SAS code • Examples: • Change font type, font size, font color • Add headers, footers • Set margins, paper size • Apply password protection • And many more… See MWSUG 2007 paper # A01-2007

14. What you can do for DDE … • Many settings within DDE… • Many repetitive settings… • Remember what you set them to… • Solution? • Use macro variables!!!

15. Pre-work for DDE • Communicate to DDE: • Paper orientation • Paper size • Number of observations (rows) • Number of variables (columns)

16. Set Macro Variables on Top * >ENTER DATA YEAR HERE>>>>>>; %LETYEAR=2010; * >ENTER AGENCY NUMBER HERE>; %LETC_AGENCY=201300; * >ENTER AGENCY NAME HERE>>>; %LETAGENAME=Dane; * >ENTER REPORT NAME HERE>>>>; %LETRPTNAME=Dane HSD L300; * >ENTER PASSWORD HERE>>>>>>>; %LETPASSWORD=Dane2; * >ENTER RECIPIENT HERE>>>>>>>; %LETRECIP=Jean French; * >ENTER PAPER ORIENTATION>>>>; %LETORIENT=Landscape; * >ENTER PAPER SIZE>>>>>>>>>>>>; %LETPAPER1=Legal; * *++++++++++++++++++++++++++++++++++++++++++++++++++++++++++;

17. Paper Size, Orientation %GLOBAL ORIENTATION PAPER; OPTIONS SYMBOLGEN; %MACROCOMPILE_DDE(ORIENT=,PAPER1=); %IF &ORIENT.=Portrait %THEN %LET ORIENTATION=1; %ELSE %LET ORIENTATION=2; %IF &PAPER1.=Letter %THEN %LET PAPER=1; %ELSE %LET PAPER=5; %MENDCOMPILE_DDE; %COMPILE_DDE(ORIENT=&ORIENT.,PAPER1=&PAPER1.); %PUT ORIENTATION=&ORIENTATION.; %PUT PAPER=&PAPER.;

18. Obs x Vars = Rows x Cols %let nvar= %sysfunc(attrn(%sysfunc(open(&syslast.,i)),nvars)); %let nobs= %EVAL( %sysfunc(attrn(%sysfunc(open(&syslast.,i)),nobs))+1); %put nvar=&nvar.; %put nobs=&nobs.; Credit: SAS TECH SUPPORT: Usage Note 8743

19. Put DDE to work • Use it in a macro • Use macro variables to guide it! %MACROFORMATME (PREPATH1=, PATH1=, PASSWORD=, NVAR=, NOBS=, ORIENTATION=, PAPER=); /* DDE CODE */ %MENDFORMATME %FORMATME (PREPATH1=&PREPATH1.,PATH1=&PATH1.,PASSWORD=&PASSWORD., NVAR=&NVAR.,NOBS=&NOBS., ORIENTATION=&ORIENTATION.,PAPER=&PAPER.);

20. Within DDE: Format Output

21. Within DDE: Format Output * The hard way:; PUT '[PAGE.SETUP("&L REPORT NAME: Dane HSD L300' '0d'x 'RUNDATE: 11OCT2010 FOR: Jean French' '0d'x 'AUTHOR: Misty Johnson SOURCE: 2010 HSRS LTS Module","&L &F &R Page 1 of 100“ ,.25,.25,.75,.5, FALSE,TRUE,TRUE,TRUE,2,5,100,"AUTO",2,TRUE,600,.25,.25)]'; *Easier.; PUT %unquote(%str (%'[PAGE.SETUP("&L REPORT NAME: &RPTNAME.' '0d'x 'RUNDATE: &D FOR: &RECIP.' '0d'x 'AUTHOR: Misty Johnson SOURCE: &YEAR. HSRS LTS Module","&L &F &R Page &P of &N",.25,.25,.75,.5, FALSE,TRUE,TRUE,TRUE,&ORIENTATION.,&PAPER.,100,"AUTO",2,TRUE,600,.25,.25)]%'));

22. Within DDE: Save and Apply Password to Final Output PUT %unquote (%str(%'[SAVE.AS(&PATH1.,,"&PASSWORD.")]%'));

23. Conclusions • Macro variables make life easier • Use for repetitive actions • Keep things straight! • DDE is awesome! Check it out!

24. Questions/ Contact Info Misty Johnson Misty.Johnson@WI.gov Thank you!!