Make Easy Style Templates Using SAS Macro

1. Make Easy Style Templates Using SAS Macro Barbara Harlan

2. About the paper • This paper’s primary purpose is to show you how to create a style template so that you can output all of your SAS tables into Word without having to format in Word • Why not just format in Word? It saves time if you are output lots of tables that will need the same formatting (like the Dashboards). Also if the table needs to be twirked later (like editing an observation) you can quickly output the new table without having to redo all the formatting.

3. Outputting SAS Tables Barbara Harlan

4. Output Delivery System (ODS) • LISTING produces traditional SAS output • HTML produces output for online viewing • MARKUP produces output for markup language tagsets (like Excel) • OUTPUT produces SAS output datasets • PRINTER produces presentation-ready printed reports • RTF produces output suitable for Microsoft Word reports

5. ODS Code Syntax: ODS destination_name FILE=“filename”; * Your SAS procedure code here; ODS destination_name CLOSE; Example: ODS RTF FILE= “myoutput.rtf” ; PROC PRINTDATA=sashelp.class; RUN; ODS RTF CLOSE; Note: You must close the ODS for the file to be created Be sure to attach the appropriate extension on the end of the filename (i.e. “filename.rtf for an rtf destination”)

6. Style Option By default, ODS uses the default SAS style template for the selected destination. By using the STYLE= setting you can switch to a different SAS supplied style or your custom style. Example: ODS RTF FILE= “myoutput.rtf” STYLE=brick; PROC PRINTDATA=sashelp.class; RUN; ODS RTF CLOSE;

7. Sample of the default style for RTF output: Sample of the “Brick” style supplied by SAS:

8. What you can do with ODS and Excel

9. Requirements • Base SAS Software, 9.1.3 or later, any operating system • recent version of the ExcelXP ODS tagset http://support.sas.com/rnd/base/ods/odsmarkup/excltags.tpl • Microsoft Excel 2002 (aka Excel XP) or later

10. Basic Code odstagsets.ExcelXP file='MyWorkbook.xml'style=sansPrinter options(<options>); * Your SAS procedure code here; odstagsets.ExcelXP close; Note Only the PRINT, REPORT, and TABULATE procedures support ODS style overrides

11. Options- Titles • EMBEDDED_FOOTNOTES default value: ‘No’ EMBEDDED_TITLES values: ‘Yes’ ,‘No’ By default, the titles and footnotes of your output will not be displayed on the Excel worksheet. To display them, turn on the EMBEDDED_FOOTNOTES, EMBEDDED_TITLES options.

12. Options- Print Headers • PRINT_HEADER value: character string PRINT_FOOTER • PRINT_FOOTER=“&C&A&RPage &P of &N”; Sheetname Page 1 of 12

13. Options- Autofilter Values can be ‘None’ ‘All’ or a range ODS tagsets.ExcelXP FILE="C:\Documents and Settings\Barbie\Desktop\TEMPLATE MACRO\AutofilterEx.xls" STYLE=XLsansprinter OPTIONS(autofilter="All" absolute_column_width='10'); PROC PRINTDATA=sasuser.autofilterNOOBS LABEL; VARsurveyidfixed_mobile sitetype org siteidpat_volume epmsepms_product emr q10_emr_product q10_emr_date; LABELpat_volume='Patient Volume' epms_product='EPMS Product' q10_emr_product='EMR Product' q10_emr_date='EMR Date Acquired'; RUN; ODS tagsets.ExcelXP CLOSE;

14. Options- By Group Processing ODS tagsets.ExcelXP FILE="C:\Documents and Settings\Barbie\Desktop\TEMPLATE MACRO\AutofilterEx.xls" STYLE=XLsansprinter OPTIONS(suppress_bylines=‘yes’ sheet_interval=‘bygroup’ sheet_label=‘ ‘ autofit_height=‘yes’; PROC PRINT DATA=dash.pat_encNOOBS LABEL; BY org; PAGEBY org; VARperiod; VARPC_Patients; VARPC_Encounters; VARBH_Patients; VARBH_Encounters; VARPC_and_BH_Patients; RUN; ODS tagsets.ExcelXP CLOSE;

15. From SAS to Word and not a drop of formatting

16. Templates • A template is a stored collection of formatting instructions that control the individual components of a report. There are over a 100 individual components or style elements that control your report. • ODS has two main types of templates: style templates and table templates. • Style templates provide output formatting for the entire SAS program while table templates are for selected procedure output within a program.

17. Style templates provide specifications such as: • Fonts • Colors • Borders • Margins • Backgrounds • Cell spacing and padding

18. Template Browser • To explore templates, open the Template Browser by selecting VIEW →TEMPLATES from the Toolbar (Note: The Results window must be selected). The Template Browser window will be displayed.

19. Anatomy of a Style Template Code

20. The Macro The complete macro contains two programs: The first, Custom Style Macro, contains the style template and attribute references macros. The second program, Custom Style Macro CALL, has the call statements for these macros. There is a %include statement in the second program that will run the Custom Style Macro program when Custom Style Macro CALL is run.

21. TimE OUT

22. A little about macros… Macros are programs that allow you to write dynamic code. %MACRO macro_name(<parameters>); * Your SAS procedure code here; %MEND; %macro_name(<parameter values>); Call Statement It’s dynamic since you can change the values of the parameters without retyping the whole program.

23. Custom Style Macro

24. Custom Style Macro CALL • The call statement uses lots of notes throughout to make it clear which attributes you are changing. • Parameters are divided into sections, such as TITLE or COLUMN HEADER, based on what attributes they affect. • Above each parameter, there is a note that describes what the parameter controls in the output. • In order to change an attribute’s value, you simply replace the default value written in the call statement with the value you would like the attribute to have.

25. %CUSTOM_STYLE_TEMPLATE ( /****************** NAME OF STYLE TEMPLATE ***********************/ STYLENAME=Superbowl, /****************** OUTPUT SETTINGS *******************************/ /****************** TITLE ***************************************/ /*Text size for titles from TITLE statement*/ TITLESIZE= 12pt, /****************** FOOTNOTE ************************************/ /*Text size for footers from FOOTNOTE statement*/ FOOTSIZE= 10pt, /*Font weight for footers from FOOTNOTE statement*/ FOOTWEIGHT= Medium, /****************** COLUMN HEADERS ******************************/ /*Text size for COLUMN and ROW headers in table*/ HSIZE= 11pt, /*Vertical justification of COLUMN and ROW headers in table*/ VHJUST= bottom, /*Background color for COLUMN headers in table*/ CHBACKGROUND= palegoldenrod, /****************** ROW HEADERS *********************************/ /*Background color for ROW headers in table*/ RHBACKGROUND= palegoldenrod, /****************** DATA CELLS ***********************************/ /*Justification of DATA in cells*/ DATAJUST= right, /*Vertical justification of DATA in cells*/ VDATAJUST= bottom, /****************** TABLE APPEARANCE ******************************/ /*Padding between cell border and cell contents in TABLE*/ CELLPADDING= 5pt, /*Type of rules (lines) that go inside TABLE (see %frames_and_rules for a list of values)*/ RULES= rows, /*Width of FRAMES (RULE width is dictated by cellspacing)*/ BORDERWIDTH= 1.5pt ); QUIT;

26. Applying Your Custom Style Template PROC FORMAT; VALUE yesno1='Yes‘0='No‘.='Missing'; VALUE $OrgType'PC'='Primary Care‘'BH'='Behavioral Health'; PICTURE pctfmt (default=7) low-high='009.9%'; RUN; ODS RTFFILE="C:\Documents and Settings\Barbie\Desktop\TEMPLATE MACRO\TemplateEx.rtf" STYLE=Superbowl STARTPAGE=yes BODYTITLE; TITLE 'Table 3: Availability of Electronic Practice Management Systems (EPMS)'; FOOTNOTE 'Patient volumes over 18 months (Sept 2007 to March 2009)'; FOOTNOTE2 'Volume data not available for 3 sites'; PROCTABULATEDATA=sasuser.autofilterFORMAT=comma7.; • CLASSsitetypeepmsemr; • VARpat_volume; • TABLEsitetype=' ' all='All Sites'*{style={font_weight=bold}},epms*(n rowpctn*F=pctfmt7.pat_volume='Volume') all='Total'*(n rowpctn*F=pctfmt7.) /misstext='0'BOX={LABEL="Organization Type"}; • KEYLABEL n='N' sum=' 'rowpctn='%'; • FORMATsitetype$orgtype.epmsemryesno.; RUN; ODS RTFCLOSE;

27. Note • Will not affect graphs! • To customize your graphs you must use GOPTIONS • SAS 9.2 now has ways you can make “graph templates”… Future paper? ;) • Graphs that output to Word will output as pictures- you will not be able to edit them!

28. Where do I get this magical macro? • The code is attached to the end of my paper • It is also available on my new website www.bharlan.weebly.com • It will be placed in a public folder on the L drive