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


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


Difference between revisions of "How To Produce Almost Perfect Excel® Output"

From sasCommunity
Jump to: navigation, search
(Created page for my NESUG 2007 paper with link to zip file on lexjansen.com)
 
(Add paper title, author and some wikilinks.)
 
(One intermediate revision by one user not shown)
Line 1: Line 1:
 +
<center>'''[http://www.lexjansen.com/nesug/nesug07/bb/bb04.pdf How To Produce Almost Perfect Excel® Output]'''
 +
 +
Suzanne M. Dorinski, U.S. Census Bureau, Washington DC
 +
</center>
 
== Abstract ==
 
== 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 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.
+
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.
+
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 [[MEANS procedure|PROC MEANS]].
  
 
== Online materials ==
 
== Online materials ==
Line 17: Line 21:
 
|profile=http://www.linkedin.com/in/suzannedorinski
 
|profile=http://www.linkedin.com/in/suzannedorinski
 
|name=Suzanne Dorinski
 
|name=Suzanne Dorinski
|related
+
|norelated
|popup
+
 
}}<br />
 
}}<br />
 
Suzanne's [[User:Dorinski|user page]]
 
Suzanne's [[User:Dorinski|user page]]

Latest revision as of 15:34, 7 February 2015

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.

Online materials

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

Contact Info

Suzanne.Marie.Dorinski@census.gov


Suzanne's user page