macro variables make life easier: applications with sas® to excel

Macro Variables Make Life Macro Variables Make Life Easier: Applications with Easier: Applications with

SAS® to ExcelSAS® to ExcelMisty JohnsonMisty Johnson

Research Analyst-SeniorResearch Analyst-SeniorState of Wisconsin Department of Health ServicesState of Wisconsin Department of Health Services

Madison, WI, USAMadison, WI, USA

MWSUG 2010 Milwaukee, WIMWSUG 2010 Milwaukee, WIPaper 40-2010Paper 40-2010

IntroductionIntroduction Research Analyst, WI DHSResearch Analyst, WI DHS

Medicaid WaiversMedicaid Waivers Reports with sensitive info for CountiesReports with sensitive info for Counties

Don’t mix up the Counties!!!Don’t mix up the Counties!!! HIPAA violation!!!HIPAA violation!!!

Repetitive effortsRepetitive efforts Macros and macro variables simplify taskMacros and macro variables simplify task

Minimize error potential too!Minimize error potential too!

AbstractAbstract Use macro variables toUse macro variables to

Simplify repetitive actionsSimplify repetitive actions Know what you’re pullingKnow what you’re pulling Verify report before printingVerify report before printing Guide Dynamic Data Exchange (DDE)Guide Dynamic Data Exchange (DDE)

Set Macro Variables on TopSet Macro Variables on Top

*>ENTER DATA YEAR HERE>>>>>>; %LET YEAR=2010; *>ENTER AGENCY NUMBER HERE>; %LET C_AGENCY=201300; *>ENTER AGENCY NAME HERE>>>; %LET AGENAME=Dane; *>ENTER REPORT NAME HERE>>>>; %LET RPTNAME=Dane HSD L300; *>ENTER PASSWORD HERE>>>>>>>; %LET PASSWORD=Dane2; *>ENTER RECIPIENT HERE>>>>>>>; %LET RECIP=Jean French; *>ENTER PAPER ORIENTATION>>>>; %LET ORIENT=Landscape; *>ENTER PAPER SIZE>>>>>>>>>>>>; %LET PAPER1=Legal; **++++++++++++++++++++++++++++++++++++++++++++++++++++++++++;

Tell the Output “Where to go…”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";

Create ReportCreate Report

Before you print, VERIFY!!Before you print, VERIFY!! Grab Agency Name from the top lineGrab Agency Name from the top line Is it what you thought?Is it what you thought?

Yes? Then ship to ExcelYes? Then ship to Excel No? Do nothing furtherNo? Do nothing further

Before you print, VERIFY!!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;

Ok to print??Ok to print??%MACRO PRINT_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;

PROC DBLOAD = Ugly….PROC DBLOAD = Ugly….

SPC ServiceTotal

Cost095.0

1 Participant Cost Share ($4,684)107.5

0Transportation Specialized -

Items $4,097 112.5

5Specialized Medical Supplies -

Items $163 202.0

1Adult Family Home 1 to 2 Beds -

Days $17,216

Would like to see this… but how?Would like to see this… but how?

SPC Service Total Cost

095.01 Participant Cost Share ($4,684)

107.50 Transportation Specialized - Items $4,097

112.55 Specialized Medical Supplies - Items $163

202.01 Adult Family Home 1 to 2 Beds - Days $17,216

Behold, the Power of……Behold, the Power of……

Dynamic Data ExchangeDynamic Data Exchange

What DDE can do for you…What DDE can do for you… Format Excel documents FOR youFormat Excel documents FOR you In SAS codeIn SAS code Examples:Examples:

Change font type, font size, font colorChange font type, font size, font color Add headers, footersAdd headers, footers Set margins, paper sizeSet margins, paper size Apply password protectionApply password protection And many more…And many more…

See MWSUG 2007 paper # A01-2007See MWSUG 2007 paper # A01-2007

What you can do for DDE …What you can do for DDE … Many settings within DDE…Many settings within DDE… Many repetitive settings…Many repetitive settings… Remember what you set them to…Remember what you set them to… Solution?Solution?

Use macro variables!!!Use macro variables!!!

Pre-work for DDEPre-work for DDE Communicate to DDE:Communicate to DDE:

Paper orientationPaper orientation Paper sizePaper size Number of observations (rows)Number of observations (rows) Number of variables (columns)Number of variables (columns)

Set Macro Variables on TopSet Macro Variables on Top

*>ENTER DATA YEAR HERE>>>>>>; %LET YEAR=2010; *>ENTER AGENCY NUMBER HERE>; %LET C_AGENCY=201300; *>ENTER AGENCY NAME HERE>>>; %LET AGENAME=Dane; *>ENTER REPORT NAME HERE>>>>; %LET RPTNAME=Dane HSD L300; *>ENTER PASSWORD HERE>>>>>>>; %LET PASSWORD=Dane2; *>ENTER RECIPIENT HERE>>>>>>>; %LET RECIP=Jean French; *>ENTER PAPER ORIENTATION>>>>; %LET ORIENT=Landscape; *>ENTER PAPER SIZE>>>>>>>>>>>>; %LET PAPER1=Legal; **++++++++++++++++++++++++++++++++++++++++++++++++++++++++++;

Paper Size, OrientationPaper Size, Orientation%GLOBAL ORIENTATION PAPER;OPTIONS SYMBOLGEN;%MACRO COMPILE_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;%MEND COMPILE_DDE;

%COMPILE_DDE(ORIENT=&ORIENT.,PAPER1=&PAPER1.);%PUT ORIENTATION=&ORIENTATION.;%PUT PAPER=&PAPER.;

Obs x Vars = Rows x ColsObs 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 Credit: SAS TECH SUPPORT: Usage Note 8743

Put DDE to work Put DDE to work Use it in a macroUse it in a macro

Use macro variables to guide it!Use macro variables to guide it!

%MACRO FORMATME (PREPATH1=, PATH1=, PASSWORD=, NVAR=, NOBS=, ORIENTATION=, PAPER=);

/* DDE CODE */%MEND FORMATME

%FORMATME (PREPATH1=&PREPATH1.,PATH1=&PATH1.,PASSWORD=&PASSWORD., NVAR=&NVAR.,NOBS=&NOBS., ORIENTATION=&ORIENTATION.,PAPER=&PAPER.);

Within DDE: Format Output Within DDE: Format Output

Within DDE: Format Output 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)]%'));

Within DDE: Save and Apply Within DDE: Save and Apply Password to Final OutputPassword to Final Output

PUT %unquote (%str(%'[SAVE.AS(&PATH1.,,"&PASSWORD.")]%'));

ConclusionsConclusions Macro variables make life easierMacro variables make life easier Use for repetitive actionsUse for repetitive actions Keep things straight!Keep things straight!

DDE is awesome! Check it out!DDE is awesome! Check it out!

Questions/ Contact InfoQuestions/ Contact Info

Misty JohnsonMisty JohnsonMisty.Johnson@WI.govMisty.Johnson@WI.gov

Thank you!!Thank you!!