41296_6bb04.pdf

How To Produce Almost Perfect Excel® Output

Suzanne M. Dorinski, U.S. Census Bureau, Washington DC

ABSTRACT

The U.S. Census Bureau performs an annual data collection for the National Center for Education Statistics. As

part of the file documentation, the Census Bureau produces a summary table that shows the minimum, maximum,

and mean value for each continuous variable, along with the number of records that have a value greater than or

equal to zero, the number of records with missing values, and the number of records with "not applicable" values.

The data collection has more than 140 variables.

The output can be easily produced as HTML, which can be read by Microsoft Excel, but we need to use a variety

of tricks to produce beautifully formatted Excel output. The little tricks include modifying the printer style, using

the MSOFFICE2K tagset, and using the Microsoft Office style properties to format numbers and cell widths. If we

need the output to be a native Excel file, we can use dynamic data exchange commands to automate that task as

well.

This paper shows what we can do with SAS® 9.1.3 and Excel 2000. Users who have access to Excel 2002 or

more recent versions may want to learn more about the ExcelXP tagset, which is not covered in this paper. This

paper assumes that the reader is comfortable with the DATA Step and PROC MEANS.

THE DATA USED IN THIS PAPER

The Census Bureau collects revenue and expenditure data from each state education agency for the National

Public Education Financial Survey (NPEFS) each year. The National Center for Education Statistics (NCES) is

the agency that sponsors the data collection. The revenue and expenditure data cover pre-kindergarten through

grade 12. Each year, NCES publishes a report based on the data and makes the dataset available to the public.

This paper uses the Excel file for the FY 2002 NPEFS, which is available at http://www.nces.ed.gov/ccd/stfis.asp.

NCES also publishes documentation for each file. The FY 2002 NPEFS file documentation is available at

http://nces.ed.gov/ccd/pdf/stfis02gen1c.pdf. The report based on the data is "Revenues and Expenditures for

Public Elementary and Secondary Education: School Year 2001-02", which is available at http://nces.ed.gov/pubsearch/pubsinfo.asp?pubid=2004341.

The file is edited. Missing data are imputed. Appendix G of the file documentation has two tables. Table G-2

shows the minimum, maximum, and mean value for each item on the survey, along with a count of the number of

missing values and the number of "not applicable" values.

In the Excel spreadsheet, if the item is missing, it has the value -1, and if the item is not applicable, it has the

value -2. We can use a macro to recode the negative values to special missing values and set dummy indicator

variables. Then PROC MEANS can calculate the minimum, maximum, and mean value for each item on the

survey. Another PROC MEANS can count the number of missing or not applicable values for each item on the

survey. The relevant information is stored in a data set, and PROC PRINT produces the report.

I created an Excel spreadsheet containing the variable names and labels. I used the label statement in the SAS

program provided on the NCES web site, Read_NPEFS_021C.sas, to create the label spreadsheet. RTF IS FINE, BUT WHAT IF WE WANT THE OUTPUT IN EXCEL?

I presented a paper at NESUG 2006 showing how to use this data to produce Table G-2 as RTF output (Dorinski

2006). But then somebody asked me, "Can we have that output in a nicely-formatted Excel spreadsheet?" In

addition to providing a data file, some surveys also like to provide users with the data in an Excel spreadsheet, so

that the users can access that data without writing their own SAS programs. The first page of Table G-2 from the NPEFS file documentation is shown on the next page. 1

Programming Beyond the BasicsNESUG 2007

2

Programming Beyond the BasicsNESUG 2007

HOW TO FORMAT THE OUTPUT IN TABLE G-2

Table G-2 is shown on the previous page. Lines above and below set off the table header row, but there are no

lines within the table. Column headings are either right or left justified. The minimum, maximum, and mean

values are displayed with one decimal place.

The relevant data are calculated by a macro and stored in a data set. Some of the formatting can be done in

PROC PRINT, which produces the table. We modify both the printer style and the MSOFFICE2K tagset in PROC

TEMPLATE to handle other format changes.

MODIFYING THE PRINTER STYLE

The modified printer style template is shown below. We modified the printer style to change the title font, the

heading font, the document font, and the footnote font. We also changed the background color to white, to get rid

of the default gray shading. ODS PATH WORK.TEMPLAT(UPDATE) SASHELP.Tmplmst(READ); proc template; * need proc template to format Excel output ; define style Styles.newPrinter; parent = styles.printer; replace color_list "Colors used in the default style" / 'link'= blue 'bgH'= white /* default is graybb */ 'fg' = black 'bg' = white; replace fonts / 'TitleFont2' = ("Times Roman",12pt,Bold Italic) 'TitleFont' = ("Times New Roman",10pt,Bold) /* default is 13 point bold italic */ 'StrongFont' = ("Times Roman",10pt,Bold) 'EmphasisFont' = ("Times Roman",10pt,Italic) 'FixedEmphasisFont' = ("Courier",9pt,Italic) 'FixedStrongFont' = ("Courier",9pt,Bold) 'FixedHeadingFont' = ("Courier",9pt,Bold) 'BatchFixedFont' = ("SAS Monospace, Courier",6.7pt) 'FixedFont' = ("Courier",9pt) 'headingEmphasisFont' = ("Times New Roman",11pt,Bold Italic) 'headingFont' = ("Times New Roman",10pt,Bold) /* default is 11 pt */ 'docFont' = ("Times New Roman",9pt); /* default is 10 pt */ style SystemFooter from TitleAndFooters / font=("Times New Roman",9pt); /* default is TitleFont setting */ end;

MODIFYING THE MSOFFICE2K TAGSET

We needed to use special formatting to get the minimum, maximum, and mean to display one decimal place in

Excel. We needed to use special formatting to prevent the label text from wrapping. We wanted to force one

footnote to be underlined, and specify page margins in the spreadsheet. Titles and footnotes use the

tag in

HTML, which means that successive titles or footnotes are placed in the spreadsheet with blank rows between

them. We wanted the footnotes to be on adjacent rows in the spreadsheet, so we had to modify the system footer

event in the MSOFFICE2K tagset.

Chevell Parker (Parker 2003) discusses common formats to use in Excel so that the data displays properly. To

get the minimum, maximum, and mean to display with one decimal place and commas, we use the mso-number-

format style property. To prevent long text from wrapping in a cell, we use the mso-width-source:userset style

3

Programming Beyond the BasicsNESUG 2007

property. Parker shows how to specify the formatting with the headtext= option on the ODS MSOFFICE2K

statement, but notes later in his paper that the headtext= option is limited to 256 characters, including spaces.

We are adding enough formatting information that we exceed the headtext= option limit, so we modify the

doc_head event in the tagset. There is no limit to what you can add to the doc_head event. Parker also

discusses setting the page margins using the margin style property.

To underline a specific footnote, we use the text-decoration style. This and other style attributes can be found in

the "Microsoft Office HTML and XML Reference".

To get the footnotes displayed on adjacent rows, we modified the system_footer event to use the

" NL; /* was put "" NL; */ end; end ;

FORMATTING SPECIFIE

D IN PROC PRINT

We can specify the borders with style elements in the PROC PRINT statement. We use the "wide_text" style

defined in the modified tagset on the var statement for the description variable to ensure that the text does not

wrap. We use the "one_decimal" style defined in the modified tagset on the var statement for the min_value,

max_value, and mean_value variables to apply the desired number format. We use the "underline_footnote" style

in the first footnote to make that footnote underlined in the output. The PROC PRINT is shown on the next page.

4

Programming Beyond the BasicsNESUG 2007

proc print data=min_max_summary_report label noobs style(table)={htmlstyle="border-top:0.5pt; border-bottom:0.5pt; border-left:none; border-right:none"} style(header)={htmlstyle="border-top:0.5pt; border-bottom:0.5pt; border-left:none; border-right:none"} style(data)={htmlstyle="border:none"}; var field / style(header)={just=l}; var description / style(column)={htmlclass="wide_text"}; var count / style(header)={just=r}; var min_value max_value mean_value / style(column)={htmlclass="one_decimal"}; var missing_sum na_sum; label field='Variable' description='Label' count='N' min_value='Minimum' max_value='Maximum' mean_value='Mean' missing_sum='-1' na_sum='-2'; title "Table G-2. Minimum, maximum, and mean values for continuous variables, state finance survey: 2001-02"; footnote "^S={htmlclass='underline_footnote'}Note:"; footnote2 "-1='Missing'"; footnote3 "-2='Not applicable'"; footnote4 "Source: Data reported by states to the U.S. Department of Education, Na tional Center for Education Statistics, Common Core of Data"; footnote5 "(CCD), National Public Education Finance Survey (NPEFS) FY 2002, (stfis021c)."; run; MODIFIED MSOFFICE2K TAGSET PRODUCES HTML, REGARDLESS OF EXTENSION

You can use the modified tagset to produce HTML, but note that it is HTML, even if you give the file the .xls

extension instead. You can verify that it really is HTML by opening the file with Excel. It looks like an Excel

spreadsheet, but when you click on "Save As" from the "File" menu, you see that the file type is "web page".

If the user really wants the output to be native Excel, the user can make that happen manually, by using the drop

down box under file type. "Microsoft Excel Workbook" is the first choice on the file type drop down list.

Of course, some users would prefer that this change take place automatically in the SAS program. We can do

that with a bit of dynamic data exchange programming. DYNAMIC DATA EXCHANGE PROGRAMMING TO SAVE FILE AS NATIVE EXCEL

We can use dynamic data exchange (DDE) to save the output as native Excel, if we are running the program on a

Windows machine. Koen Vyverman's papers (Vyverman 2002a, Vyverman 2002b) contain much more detail about the DDE commands.

The DDE code is shown below.

* now create native Excel output! ; options xsync noxwait xmin; filename cmds dde 'excel|system'; 5

Programming Beyond the BasicsNESUG 2007

/* this data _null_ opens Excel if not already open */ data _null_; length fid rc start stop time 8; fid=open('cmds','s'); if (fid le 0) then do ; rc=system('start excel'); start=datetime(); stop=start+10; do while (fid le 0); fid=open('cmds','s'); time=datetime(); if (time ge stop) then fid=1; end; end ; rc=fclose(fid); run ; data _null_; file cmds; ddecmdopen= '[open("'||"&out_folder"||'\'||"&spreadsheet_out._HTML_VERSION.html"||'")]'; put ddecmdopen; put '[error(false)]'; ddecmdsave='[SAVE.AS("'||"&out_folder"||'\'||"&spreadsheet_out..xls"||'",1)]'; put ddecmdsave; put '[File.Close(TRUE)]'; put '[quit()]'; run ;

WHY ALMOST PERFECT EXCEL OUTPUT?

The Excel output doesn't look exactly like the first page shown earlier in this paper. The "Table G-2" title and the

footnotes are left justified, while the "Appendix G" title and "Revised File" titles are centered. I couldn't figure out

how to switch from centered to left justified titles, so I used the nocenter option in my program, which

automatically left justifies the output, and I excluded the titles that should be centered.

If you print the table from Excel, the headers do not repeat across the pages like they do in the documentation.

REFERENCES

Dorinski, Suzanne. 2006. "How To Produce Almost Perfect RTF Output", Proceedings of the 19 th Annual NorthEast SAS Users Group Conference. Philadelphia, PA. Available at http://www.nesug.org/proceedings/nesug06/io/io12.pdf. Dorinski, Suzanne. 2007. "A Lazy Programmer Case Study: Dynamic Macro Code To Deal With Changing Number of Variables Over Time", Proceedings of the 20 th Annual NorthEast SAS Users Group Conference.

Baltimore, MD. This paper uses the same data set, but explains what the macro is doing to produce the results.

The Proceedings also include a zip file with the data and SAS programs. "Microsoft Office HTML and XML Reference". Available at http://msdn2.microsoft.com/en-US/library/aa155477(office.10).aspx

Parker, Chevell. 2003. "Generating Custom Excel Spreadsheets Using ODS", Proceedings of the Twenty-Eighth

Annual SAS Users Group International Conference. Seattle, WA. Available at http://www2.sas.com/proceedings/sugi28/012-28.pdf. 6

Programming Beyond the BasicsNESUG 2007

Parker, Chevell. "SAS 9.1 MS OFFICE Integration". Available at http://support.sas.com/rnd/base/topics/templateFAQ/office91.pdf. Parker, Chevell. "Using ODS to Generate Excel Files". Available at http://support.sas.com/rnd/base/topics/templateFAQ/Excel1.pdf.

Vyverman, Koen. 2002a. "Creating Custom Excel Workbooks from Base SAS® with Dynamic Data Exchange: A

Complete Walkthrough", Proceedings of the Twenty-Seventh Annual SAS Users Group International Conference.

Orlando, FL. Available at

http://www2.sas.com/proceedings/sugi27/p190-27.pdf.

Vyverman, Koen. 2002b. "Using Dynamic Data Exchange to Export Your SAS® Data to MS Excel - Against All

ODS, Part I - ", Proceedings of the Twenty-Seventh Annual SAS Users Group International Conference.

Orlando, FL. Available at

http://www2.sas.com/proceedings/sugi27/p005-27.pdf.

ACKNOWLEDGEMENTS

SAS is a Registered Trademark of the SAS Institute, Inc. of Cary, North Carolina. Other brand and product names are trademarks of their respective companies.

Thanks to Brittani Trusty for reading my NESUG 2006 paper and suggesting that I write about producing

formatted Excel output. Thanks to Freda Spence, Carma Hogue, Rita Petroni, and James Clement for reading

the draft of this paper and providing helpful comments.

DISCLAIMER

This report is released to inform interested parties of research and to encourage discussion. The views

expressed on technical issues are those of the author and not necessarily those of the U.S. Census Bureau.

CONTACT INFORMATION

Your comments and questions are valued and encouraged. Contact the author at:

Suzanne Dorinski

U.S. Census Bureau OSMREP, CENHQ RM 7K042E 4600 SILVER HILL ROAD
WASHINGTON, DC 20233 301-763-4869
Suzanne.Marie.Dorinski@census.gov 7

Programming Beyond the BasicsNESUG 2007

This is what the native Excel file looks like. Note that the file is named NPEFS_min_max_table_2002.xls, as

shown in the title bar, but the sheet name is "NPEFS_min_max_table_2002_HTML_V". The sheet name is from

the HTML file that was created by the modified MSOFFICE2K tagset.

You can also automatically delete the HTML file in the SAS program, but I found it helpful to keep it around to

check for problems with the HTML coding. To delete the file, use the code shown below at the bottom of the

program. data _null_; fname="tempfile"; rc=filename(fname,"&out_folder\&spreadsheet_out._HTML_VERSION.html"); if rc=

0 and fexist(fname) then

rc=fdelete(fname); rc=filename(fname); run ; 8

Programming Beyond the BasicsNESUG 2007

APPENDIX: COMPLETE CODE

dm 'cle log; cle out'; ************************************************************************ **; * this is NPEFS_min_max_summary_in_Excel.sas *; * *; * Program creates the min/max/mean summary table for the NPEFS file *; * documentation. Need to count the number of records that have *; * nonnegative values, calculate the minimum value, the maximum value, *; * and the mean value. Also need to count the number of records with *; * -1 (Missing) or -2 (NA) for each variable. *; * *; * Each variable also needs its label to appear in the table. *; * *; * Suzanne M. Dorinski 7/9/07 *; ************************************************************************ **; ********************************** MACROS TO CONTROL PROGRAM FILES ; * in_folder is the folder where the final file spreadsheet is; * spreadsheet_in is the name of the final file spreadsheet (do NOT incl ude .xls!); * label_folder is the folder where Excel spreadsheet with variable label s is stored; * label_file is the name of the Excel spreadsheet with variable labels * (do NOT include .xls!) ; * out_folder is the folder where the Excel output will be stored; * spreadsheet_out is the name of the spreadsheet with the min, max, mean * summary table (do NOT include .xls!) ; %let in_folder=C:\Documents and Settings\My Documents\NESUG 2007 pape r; %let spreadsheet_in=stfis021c; %let label_folder=C:\Documents and Settings\My Documents\NESUG 2007 p aper; %let label_file=NPEFS_labels_without_quotes; %let out_folder=C:\Documents and Settings\My Documents\NESUG 2007 pap er; %let spreadsheet_out=NPEFS_min_max_table_2002; ********************************** END MACROS TO CONTROL PROGRAM FILES ; options mprint nodate nonumber nosymbolgen nocenter; * output is left-justified, so specify nocenter option here ; title; * clear out any title or footnote statements; footnote; ODS RESULTS OFF; * don't open up Excel viewer in SAS session. ; ODS LISTING CLOSE; * don't show results in SAS output window ; ODS ESCAPECHAR='^'; * escape char is for inline formatting; 9

Programming Beyond the BasicsNESUG 2007

ODS PATH WORK.TEMPLAT(UPDATE) SASHELP.Tmplmst(READ); proc template; * need proc template to format Excel output ; define style Styles.newPrinter; parent = styles.printer; replace color_list "Colors used in the default style" / 'link'= blue 'bgH'= white /* default is graybb */ 'fg' = black 'bg' = white; replace fonts / 'TitleFont2' = ("Times Roman",12pt,Bold Italic) 'TitleFont' = ("Times New Roman",10pt,Bold) /* default is 13 point bold italic */ 'StrongFont' = ("Times Roman",10pt,Bold) 'EmphasisFont' = ("Times Roman",10pt,Italic) 'FixedEmphasisFont' = ("Courier",9pt,Italic) 'FixedStrongFont' = ("Courier",9pt,Bold) 'FixedHeadingFont' = ("Courier",9pt,Bold) 'BatchFixedFont' = ("SAS Monospace, Courier",6.7pt) 'FixedFont' = ("Courier",9pt) 'headingEmphasisFont' = ("Times New Roman",11pt,Bold Italic) 'headingFont' = ("Times New Roman",10pt,Bold) /* default is 11 pt */ 'docFont' = ("Times New Roman",9pt); /* default is 10 pt */ style SystemFooter from TitleAndFooters / font=("Times New Roman",9pt); /* default is TitleFont setting */ end; define tagset tagsets.modified_MSOFFICE2K / store=work.templat; parent=tagsets.msoffice2k; define event doc_head; start: put "" NL; put VALUE NL; finish: /* special formatting for output spreadsheet */ put "" NL; /* end special formatting for output spreadsheet */ put "" NL; put "" NL; end; 10

Programming Beyond the BasicsNESUG 2007

define event system_footer; start: put ""; put VALUE; finish: put "