1 / 24

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

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. Introduction. Research Analyst, WI DHS Medicaid Waivers

regina
Download Presentation

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

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. 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!!

More Related