As the first step in the decommissioning of the site has been converted to read-only mode.

Here are some tips for How to share your SAS knowledge with your professional network.

How To Produce Almost Perfect Excel® Output

From sasCommunity
Jump to: navigation, search
How To Produce Almost Perfect Excel® Output

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


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.

Online materials

The NESUG 2007 paper
Zip file of data, SAS code, and output

Contact Info

Suzanne's user page