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

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

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

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

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

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

7 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. The problem occurs because Excel uses aGeneral format to import cell values. The General formatreads the cell values as they are typed, however, there aresome common problems that you should be aware of. Both numeric and character variables will lose leadingand trailing zeroes when creating Excel files with theODS HTML and CSV destinations.

8 You will not realizethe problem until the leading or trailing zeroes areomitted from an account number, an ID, or a zip code. Numbers with lengths greater than 11 characters aredisplayed in scientific notation. Unformatted dates in SAS will be totally different inExcel because their beginning date starts with January1, 1900 by FORMATSI mporting the cells as text Using the Text format for the cellvalues allow the cell values to come over without anyinterpretation and does not strip the leading or trailingzeroes. Using the mso-number-format:\@ style propertyallows the cell values to be imported Using the Text formatfor Excel 2000 and above. For Excel 97, the style property :@ . Below are examples ofapplying the Text format and the more common * Apply text format to all cells */data one; input acc_no zipcode; cards; 0111 023560 0333 023334;run;ods html file= headtext= <style> td {mso-number-format:\@}</style> ; proc print data=one; run;ods html close;/* Text format applied to a single column */ods html file= headtext= <style>.

9 Zero {mso-number-format:\@}</style> ; proc print data=one; var acct_no / style={htmlclass= zero }; var zipcode; run;ods html close; /* Excel 97 solution */ods html file=' '; proc print data=one; var acct_no / style={htmlstyle=" :@"}; var zipcode; run;ods html close;COMMON NUMBER FORMATSmso-number-format:0 NO Decimalsmso-number-format:"0\.000" 3 Decimalsmso-number-format:"\#\,\#\#0\.00 0" Comma w\3 decmso-number-format:"mm\/dd\/yy" Date7mso-number-format:"mmmm\\ d\\\,\\ yyyy" Date9mso-number-format:"m\/d\/yy\\ h\:mm\\ AM\/PM" D -T AMPMmso-number-format:"Medium Date" 01-mar-98mso-number-format:"d\\-mmm\\-yy yy" 01-mar-1998mso-number-format:"Short Time" 5:16mso-number-format:"Medium Time" 5:16 ammso-number-format:"Long Time" 5:16:21:00mso-number-format:Percent; PercentSUGI 28 Advanced Tutorials3mso-number-format:0% No percentmso-number-format:"0\.

10 E+00"; Fractionsmso-number-format:"\@" TextCELL FORMATING IN THE CSV DESTINATIONTo prevent losing the leading zeroes when Using the CSVdestination, an = can be added in front of the characterstrings. This allows the fields to be read Using the textformat. This solution also works with the HTML CSV tagset can also be modified to add the = beforethe data values. To modify a specific field, add the = in frontof the data value within the data template; define tagset ; parent= ; define event data; put "," / if !cmp( COLSTART , "1" ); put '=' """" / if cmp( TYPE , string" ); put VALUE; put """" / if cmp( TYPE , "string" ); end;end;run;ods markup file= c:\ tagset= ; proc print data=one; run;ods markup close;ROW HEIGHT AND COLUMN WIDTHWhen the row height and column width are set with a style inODS, they are ignored by Microsoft Excel .