Transcription

Paper SAS1700-2015Creating Multi-Sheet Microsoft Excel Workbooks with SAS :The Basics and Beyond. Part 2Vincent DelGobbo, SAS Institute Inc.ABSTRACTThis presentation explains how to use Base SAS 9 software to create multi-sheet Excel workbooks. Youlearn step-by-step techniques for quickly and easily creating attractive multi-sheet Excel workbooks that contain your SAS output using the ExcelXP Output Delivery System (ODS) tagset. The techniques canbe used regardless of the platform on which SAS software is installed. You can even use them on amainframe! Creating and delivering your workbooks on-demand and in real time using SAS servertechnology is discussed. Although the title is similar to previous presentations by this author, thispresentation contains new and revised material not previously presented.INTRODUCTIONThis paper explains how to use Base SAS 9.1.3 or later to create the Excel workbook shown in Figure 1.Figure 1. Multi-Sheet Excel Workbook Generated by the ExcelXP ODS TagsetThe workbook includes two worksheets that contain vaccine adverse event data for the United Statesfrom 2004 to 2013.1

Notable features of this workbook include the following:1. Worksheet names are customized.2. Title and footnote text are displayed in the document body.3. Each worksheet prints on a single page.4. The values in the numeric columns and summary row cells are displayed using Excel formats, notSAS formats.The code in this paper was tested using SAS 9.4 (ODS ExcelXP tagset version 1.130) and MicrosoftExcel 2010 software.REQUIREMENTSTo use the techniques described in this paper, you must have the following software: Base SAS 9.1.3 Service Pack 4 or later on any supported operating system (z/OS, UNIX, etc.)and hardware. Microsoft Excel 2002 (also referred to as Microsoft Excel XP) or later.LIMITATIONSBecause the ExcelXP ODS tagset creates files that conform to the Microsoft XML SpreadsheetSpecification, you can create multi-sheet Excel workbooks that contain the output from almost any SASprocedure. The exception is that the Microsoft XML Spreadsheet Specification does not support images,so the output from graphics procedures cannot be used (Microsoft Corporation 2001).You can use ExcelXP tagset options with all procedure output, but ODS style overrides apply only to thePRINT, REPORT, and TABULATE procedures. Tagset options and style overrides are discussed in thesections "Understanding and Using the ExcelXP Tagset Options" and "Understanding and Using ODSStyle Overrides", respectively.You cannot use the techniques described in this paper to update existing workbooks. ODS creates theentire document on each execution, and cannot alter existing workbooks.SAMPLE DATATable 1 presents the column properties for the VaccineAE SAS table that is used to create the Excelworkbook shown in Figure 1.Column NameDescriptionTypical ValuesState22-digit state abbreviationAK, DC, NC, WYN2004N2005.N2013Number of adverse events reports for 2004to 201336, 817, 1144, 2906Pct2004Pct2005.Pct2013Percentage of total adverse events reportsfor 2004-20130.407602676, 1.3170141474,10.193765796Table 1. Column Properties and Representative Data Values for the VaccineAE SAS TableThe data was extracted from the Vaccine Adverse Event Reporting System (VAERS), available athttp://vaers.hhs.gov. It is intended only for illustrative purposes.2

OUTPUT DELIVERY SYSTEM (ODS) BASICSODS is the part of Base SAS software that enables you to generate different types of output from yourprocedure code. An ODS destination controls the type of output that is generated (HTML, RTF, PDF,etc.). An ODS style controls the appearance of the output. In this paper, we use a type of ODSdestination, called a tagset, that creates XML output that can be opened with Excel. This tagset, namedExcelXP, creates an Excel workbook that has multiple worksheets.The Excel workbook in Figure 1 was created using the ExcelXP ODS tagset and the PRINTER ODS stylesupplied by SAS. The ExcelXP tagset creates an XML file that, when opened by Excel, is rendered as amulti-sheet workbook. All formatting and layout are performed by SAS; there is no need to "hand-edit"the Excel workbook. You simply use Excel to open the file created by ODS.Here are the general ODS statements to generate XML output that is compatible with Excel 2002 andlater: ods all close; ods tagsets.ExcelXP file 'file-name.xml' style style-name . ;* Your SAS procedure code here; ods tagsets.ExcelXP close;The first ODS statement ( ) closes all destinations that are open because we want to generate only XMLoutput for use with Excel.The second ODS statement ( ) uses the ExcelXP tagset to generate the XML output and then store theoutput in a file. You should use the XML extension instead of XLSX or XLSX because Excel 2007 andlater display a warning if the XML extension is not used (Microsoft Corporation 2015). The STYLE optioncontrols the appearance of the output, such as the font and color scheme. To see a list of ODS stylesthat are available for use at your site, submit the following SAS code:ods all close;ods listing;proc template; list styles; run; quit;To find the SAS code that generates sample output for the ODS styles available on your system, click theFull Code tab in SAS Sample 36900 (SAS Institute Inc. 2009).The third ODS statement ( ) closes the ExcelXP destination and releases the XML file so that it can beopened with Excel.Note: If you place the files where users can access them over a network, you should set file permissionsto prevent accidental alteration.OPENING THE OUTPUT WITH EXCELFollow these steps to open an ODS-generated XML file:1. In Excel 2002, 2003, or 2010, select File Open.In Excel 2007 select Office Button Open.2. Navigate to the file or enter the path and filename in the File name field.3. Click Open to import the XML file.You can also navigate to the file using Microsoft Windows Explorer, and then double-click the file to openit with Excel.3

Excel reads and converts the XML file to the Excel format. After the conversion, you can perform anyExcel function on the data. To save a copy of the file in Excel binary (XLS) format using Excel 2002,2003, or 2010, select File Save As and then, from the Save as type drop-down list, selectMicrosoft Excel Workbook (*.xls). If you're using Excel 2007, click the Microsoft Office Button,and then select Save As Excel 97-2003 Workbook. If you're using Excel 2007 or 2010 and wantto save the document in the Microsoft Office Open XML format, choose Excel Workbook (*.xlsx)from the Save as type drop-down list.UPDATING THE EXCELXP TAGSETThe version of the ExcelXP tagset that is shipped with Base SAS is periodically updated. There iscurrently no notification system for tagset updates. To ensure that you have a recent version, comparethe ExcelXP tagset version, displayed in the SAS log whenever the tagset is used, to the versionavailable on the ODS website (SAS Institute Inc. 2015).Submit this code to display the tagset version number in the SAS log:filename temp temp;ods tagsets.ExcelXP file temp;ods tagsets.ExcelXP close;filename temp clear;All the code in this paper uses an up-to-date version of the ODS ExcelXP tagset (version 1.130). Ifyou're using a tagset that's more than 2 or 3 versions old, consider upgrading by following the steps inSAS Usage Note 32394 (SAS Institute Inc. 2008b). Otherwise, continue to the next section.USING ODS TO CREATE THE MULTI-SHEET EXCEL WORKBOOKHere is a listing of the basic SAS code used to create the Excel workbook:ods all close;options topmargin 0.5 inleftmargin 0.5 inbottommargin 0.5 inrightmargin 0.5 in; ods tagsets.ExcelXP file 'VaccineAE.xml' style Printer;title j c 'Vaccine Adverse Event Reporting System (VAERS) Report for theUnited States';footnote j l 'Source: http://vaers.hhs.gov';* First worksheet; proc report data sample.VaccineAE nowd;column State2 ('2004'('2005'('2006'('2007'('2008'definedefine %)';'N';4

define Pct2005 / analysis '(%)';. ;define N2008/ analysis 'N';define Pct2008 / analysis '(%)'; rbreak after / summarize;run; quit;* Second worksheet;proc report data sample.VaccineAE nowd;column State2 inedefinedefine. 3);'State';'N';'(%)';'N';'(%)';N2013/ analysis 'N';Pct2013 / analysis '(%)';rbreak after / summarize;run; quit; ods tagsets.ExcelXP close;Stepping through the code, all open ODS destinations are closed, and then margins are specified tocontrol the appearance of the printed output.As you can see in the ODS statement ( ), the ExcelXP tagset generates the output and the PRINTERstyle controls the appearance of the output. By default, the ExcelXP tagset creates a new worksheetwhen a SAS procedure creates new tabular output. Each instance of the REPORT procedure ( )creates one table showing half of the yearly data, and each table is created in a separate worksheet.The RBREAK statement ( ) creates a summary line listing the sum of all the analysis variables. The lastODS statement ( ) closes the ExcelXP destination and releases the XML file so that it can be openedwith Excel.Figure 2 displays the results of executing the basic SAS code, and then opening the resultingVaccineAE.xml file with Excel. Notice that Figure 2 does not match Figure 1. The following problems areexhibited in Figure 2:1. Unattractive, default worksheet names are used.2. Title and footnote text are missing.3. Printing results in more than one page per worksheet.4. Incorrect display formats are used for the values in the numeric columns and summary row cells.5. The background color of the summary row is not gray.5

In the following sections we change the basic SAS code to correct these problems. The complete SAScode used to create the workbook shown in Figure 1 is listed in the section "The Final SAS Code".Figure 2. Initial ODS ExcelXP Tagset-Generated WorkbookUNDERSTANDING AND USING THE EXCELXP TAGSET OPTIONSThe ExcelXP tagset supports many options that control both the appearance and functionality of theExcel workbook. Many of these tagset options are simply tied directly into existing Excel options orfeatures. For example, the SHEET NAME option is used to specify the worksheet name.Tagset options are specified in an ODS statement using the OPTIONS keyword:ods tagsets.ExcelXP options(option-name1 'value1'option-name2 'value2' .) . ;Note that the value that you specify for a tagset option remains in effect until the ExcelXP destination isclosed or the option is set to another value. Because multiple ODS statements are allowed, it is goodpractice, in terms of functionality and code readability, to explicitly reset tagset options to their defaultvalues when you are finished using them.For example:ods tagsets.ExcelXP file 'file-name.xml' style style-name . ;ods tagsets.ExcelXP options(option-name 'some-value');* Some SAS procedure code here;ods tagsets.ExcelXP options(option-name 'default-value');* Other SAS procedure code here;ods tagsets.ExcelXP close;When specifying multiple ODS statements as shown above, specify the FILE, STYLE, or any otherkeyword or option that is supported by ODS only in the initial ODS statement.6

To see a listing of the supported options printed to the SAS log, submit the following SAS code:filename temp temp;ods tagsets.ExcelXP file temp options(doc 'help');ods tagsets.ExcelXP close;filename temp clear;All of the tagset options and code work with any SAS procedure. However, ODS style overrides workonly with the PRINT, REPORT, and TABULATE procedures.SPECIFYING WORKSHEET NAMESODS generates a unique name for each worksheet, as required by Excel. Figure 2 shows the worksheetnames that result from running the initial SAS code. There are, however, several tagset options that youcan use to alter the names of the worksheets (DelGobbo 2013, 2014).Use the SHEET NAME option to specify a worksheet name. Recall that tagset options remain in effectuntil the ExcelXP destination is closed. We specify the option twice because we want a different name foreach worksheet.ods tagsets.ExcelXP file 'VaccineAE.xml' style Printer;title . ; footnote . ;* First worksheet;ods tagsets.ExcelXP options(sheet name '2004 - 2008');proc report data sample.VaccineAE nowd;. ;run; quit;* Second worksheet;ods tagsets.ExcelXP options(sheet name '2009 - 2013');proc report data sample.VaccineAE nowd;. ;run; quit;ods tagsets.ExcelXP close;Figure 4 shows the updated worksheet names.INCLUDING TITLE AND FOOTNOTE TEXT IN THE WORKSHEET BODYBy default, SAS titles and footnotes appear as Excel print headers and print footers, respectively, whichare displayed when the Excel document is printed. You can confirm this by viewing the ExcelHeader/Footer tab in the Page Setup dialog box, shown in Figure 3.7

Figure 3. Excel Page Setup Dialog Box Showing Title and Footnote Text in HeadersTo include title and footnote text on-screen, in the worksheet body, use the EMBEDDED TITLES andEMBEDDED FOOTNOTES options:ods tagsets.ExcelXP file 'VaccineAE.xml' style Printer;*"Global" tagset options;ods tagsets.ExcelXP options(embedded titles 'yes'embedded footnotes 'yes');title . ; footnote . ;* First worksheet;ods tagsets.ExcelXP options(sheet name '2004 - 2008');proc report data sample.VaccineAE nowd;. ;run; quit;8

* Second worksheet;ods tagsets.ExcelXP options(sheet name '2009 - 2013');proc report data sample.VaccineAE nowd;. ;run; quit;ods tagsets.ExcelXP close;Because these options are placed at the beginning of the code and are not changed later, they affect allworksheets. The title and footnote text are now included in the worksheet body (Figure 4).Figure 4. ODS ExcelXP Tagset-Generat