Creating Output Files with ODS for Use by Excel - Exchanging Data Between SAS and Microsoft Excel - page 138

Figure 8.37: Code to Prepare a Worksheet for Printing Using ExcelXP Automatically.
ODS TAGSETS.EXCELXP BODY='c:\temp\ExcelXP\shoes5.xml'
OPTIONS(ABSOLUTE_COLUMN_WIDTH='4,9,9,10,5,8,9,8'
FROZEN_HEADERS='1'
ORIENTATION='Landscape'
WRAPTEXT='Yes'
ZOOM='50');
PROC PRINT DATA=sashelp.shoes;
RUN;
ODS TAGSETS.EXCELXP CLOSE;

The Excel 2010 screen print shown below contains the spreadsheet image and the “Page Setup” popup
menu image that can be found in the toolbar “File” and “Page Layout” options. The same “Page Setup”
screen can be found on the “Page Setup” and “Page Layout” tabs when using Excel 2003 or Excel 2007.

Figure 8.38 Excel Output Showing Print Ready Options That ExcelXP Can Provide.

In Figure 8.38 I want to point out a few of the changes that show up here.

Column “C” was defined a little smaller for this example. See the WRAPTEXT option detail.

●

We are looking at the bottom of the file, but the header row is still visible because the ODS
FROZEN_HEADERS option says to freeze the first line.

●

The “PAGE SETUP” page indicates that the output when printed would print in Landscape mode.

●

The WRAPTEXT option forces long lines to wrap onto the next line, and column “C” was
shortened to get it to wrap. All rows are also output at the same height.

●

The ZOOM option allows the image of the spreadsheet to fit on one half of the page, and the slider
bar in the right-hand corner is scaled to 50%.

●

Example 8.16.6 Creating a Table of Contents in an Excel Workbook
The following code will perform functions that have not been shown yet. First, the PROC PRINT step of
the SAS code will create several worksheets in the workbook. One sheet for each value of the “BY
variable” will be output by SAS. Second, the ODS commands will set the CONTENTS option in the
ExcelXP tagset template, and this will cause the creation of a sheet in the output workbook with hyper-
links to each page in the workbook. Clicking on any of the hyperlinks (for example, “Region=Africa”) will
open the worksheet for that data. The ExcelXP option “BY” causes multiple worksheets to be created in
this workbook.