SUGI 28: Generating Custom Excel Spreadsheets Using ODS

1 Paper 12-28 1 Generating Custom Excel Spreadsheets Using ODSC hevell Parker, SAS Institute, Cary, NCABSTRACTThis paper will demonstrate techniques on how to effectivelygenerate files that can be read with Microsoft Excel Using theOutput Delivery System. This paper will further discuss avariety of methods that will allow customization of the everypart of the Excel file from ODS. Some of the tips provided willwork with Excel 97, 2000 and 2002. However, much of whatis covered especially, the advanced techniques Using XMLand the special Microsoft Office style properties apply toExcel 2000 and partially to As you will see, creating files with the Output DeliverySystem that can be read with Excel is very easy, however,some additional work may be required to customize theoutput as you like.

2 Topics of discussion will include thefollowing: Techniques for creating files with the ODS that canbe read by Excel , General appearance issues and commontask, Advanced techniques Using XML and the ODS Markupdestination to modify the Excel file, and Using Excel Macroswith Excel FILEST here are several methods of Generating files that can beread by Excel Using the Output Delivery System. Themethods discussed in this paper will be Using the ODS HTMLand CSV destinations to generate the Excel or spreadsheetfiles. Generic XML files can be read with Excel 2002 and canbe generated with the XML engine on the you specify a procedure or data step within the ODSHTML statement with the.

3 XLS or .CSV extensions, MicrosoftExcel is opened in the Results Viewer on the PC. Excel isnot an ODS destination and the fact that the file is opened inExcel is not a product of ODS. Excel sees a file generatedwith the registered extension of .XLS, or .CSV and attemptsto open this file within the registered program which is Excelon the html file= c:\ ; proc print data= ; run;ods html close;The new ODS CSV destination can also be used to createfiles that can be read by Microsoft Excel . The acronym CSVstands for Comma Separated Value. This new destination isexperimental with Version as part of the ODS MarkupLanguage.

4 The New CSV destination defaults can bechanged by modifying the default tagset as we will seeshortly. Excel has the ability to read CSV files, so specifyingthe ODS CSV destination with the extension .CSV will createa comma separated file that is opened in Excel by , the delimiter can be changed from a comma to anyother delimiter by modifying the CSV tagset. Use theCSVALL destination to maintain the titles and footnotes csv file= c:\ ; proc print data= ;run;ods csv close;GENERAL APPEARANCE AND COMMONTASKTITLES AND FOOTNOTESU sing the ODS HTML destination to create the .XLS or .CSVfiles will place the entire title or footnote in the first cell.

5 Theeffect of this is that the first column will become the width ofthe title or footnote. This occurs because the ODS HTML destination uses the non-standard <Table> tag for the titlesand footnotes and bylines which Excel does not expect for aheader. The width of the title or footnote will extend as muchas 4 cells before wrapping. To change this behavior, one ofthe HTML tagsets can be used. Most of the HTML tagsetsuse the header tags <h1> by default for titles, footnotes andbylines. This is the tag that Excel expects for its headersand HTML tagsets shipped for are HTML4, which is thedefault with the ODS HTML destination in , HTMLCSS,PHTML, CHTML and IMODE.

6 The tagsets can be specifiedas a destination like the example below, or as a value of theTAGSET= option on the ODS MARKUP statement. Thetitles and footnotes can also be merged in Excel Using theCOLSPAN= attribute in the titles or footnotes to determinehow many columns to span. In the first example below, thePHTML tagset is used to extend the titles beyond the firstcell. The second example spans the titles over 4 columns inthe table Using the COLSPAN= HTML phtml file= c:\ stylesheet= c:\ ; proc print data= ; run;ods phtml close;ods html file=" ";title "<td align=center colspan=4> <fontsize=4> <b>this is a test</b> </font> </td>"; proc print data= ; run;ods html close;STARTING OUTPUT IN ROW 1 HTMLO utput generated with the ODS HTML destination begins inrow 2 by default.

7 This happens because of the non-breakingspace character ( ) in the anchor tag. The only wayto get rid of this anchor tag in the HTML destination is to postprocess the HTML file. The HTML tagsets of the Markupdestination can also be used to begin the output in row HTML tagsets of the ODS Markup destination do nothave this non-breaking space character in the anchor the prior example for CSV destination generates output beginning in row 3 ofthe Excel file. This is the default of the ODS CSV defaults of the destination or tagset can be changed bymodifying the tagset and overriding the defaults.

8 The samplecode below modifies the CSV tagset and starts the data inrow 1 by removing the first 2 empty 28 Advanced Tutorials2 proc template; define tagset ; parent = ;define event table; finish: put NL; end;define event row; finish: put NL;end ; end; run;ods body='c:\ ' ;proc print data= label; run;ods close;REDUCING FILE SIZET here are a few techniques that can be employed to reducethe size of Excel files and reduce the time it takes for thefiles to load. The first method involves creating a CSS stylesheet with the ODS HTML destination. This allows you toseparate the formatting instructions from the data and theneed for each record to have formatting instructions.

9 If youspecify the STYLESHEET= option with a file, an externalCSS file is generated. Excel 97 ignores this CSS style second method of reducing the size of the .XLS filescreated is to use one of the HTML tagsets of the ODSM arkup destination. All of the HTML tagsets of the ODSM arkup destination follow the HTML standard whichseparates the formatting instructions from the data. All of theHTML tagsets except CHTML allow formatting with the use ofa CSS style sheet. The CHTML tagset does not allow theuse of a CSS file. These HTML tagsets all have minimalformatting such as the borders without the use of the final method for reducing the size of the Excel file is touse the Minimal style.

10 The Minimal style is one of the defaultstyles shipped with SAS. The Minimal style has very fewformatting instructions, which reduces the size of the are the statistics of the 5 variable, 19observation data set. This was done inVersion As the observations grew, PHTML becamemore efficient than its HTMLCSS counterpart. Not listed, theCSV destination was the smallest of all at BenchmarkHTMLHTML/CSSPHTMLHTMLCSSCHTMLMI NIMAL 21K5k5k4k5kCELL FORMATINGOne of the most problematic areas that you will face whencreating Excel files from ODS is with cell formatting. Theproblems are the same whether Using the CSV or the HTML destinations.