Creating EXCEL Output with ODS

The ODS EXCEL Statement

To open, manage, or close the Excel destinations that produce Excel spreadsheet files that are compatible with Microsoft 2010 and later versions, use the ODS EXCEL statement:
Syntax, ODS EXCEL statement:
ODS EXCEL <(<ID=>identifier)> <action>;
ODS EXCEL <(<ID=>identifier)> <option(s)>;
  • (<ID=>identifier) enables you to open multiple instances of the same destination at the same time. Each instance can have different options.
    • identifier can be numeric or can be a series of characters that begin with a letter or an underscore. Subsequent characters can include letters, underscores, and numerals.
  • action can be one of the following:
    • CLOSE action closes the destination and any files that are associated with it.
    • EXCLUDE exclusions| ALL | NONE action excludes one or more output objects from the destination.
      Note: The default is NONE. A destination must be open for this action to take effect.
    • SELECT selections| ALL | NONE action selects output objects for the specified destination.
      Note: The default is ALL. A destination must be open for this action to take effect.
    • SHOW action writes the current selection list or exclusion list for the destination to the SAS log.
      Note: If the selection or exclusion list is the default list (SELECT ALL), then SHOW also writes the entire selection or exclusion list. The destination must be open for this action to take effect.

Details About the Excel ODS Destination

The ODS destination for Excel uses Microsoft Open Office XML Format for Office 2010 and later. This statement produces XML and represents a way to define and format data for easy exchange.
The ODS destination for Excel creates Microsoft spreadsheet in ML XML. Each table is placed in its own worksheet within a workbook. This destination supports ODS styles, trafficlighting, and custom formats. Numbers, currency, and percentages are correctly detected and displayed. Style override, a TAGATTR= style attribute, can be used to create custom formats for the data. By default, titles and footnotes are included in the worksheet, but they are part of the header and footer of the worksheet.
Portrait is the default printing orientation. The orientation can be changed to landscape.

Example: Customizing Your Excel Output

The following example illustrates a customized Excel workbook that contains PROC MEANS output. 
ods excel file='multitablefinal.xlsx'   /*#1*/
  options (sheet_interval="bygroup"             /*#2*/
    suppress_bylines='yes'                      /*#3*/
    sheet_label='country'                       /*#4*/
    embedded_titles='yes');                     /*#5*/
title 'Wage Rates By Manager';
proc means data=cert.usa;
  by manager;
  var wagerate;
run;
ods excel close;                                /*#6*/
1 The ODS EXCEL statement opens an instance of an Excel workbook and creates a new Excel workbook called Multitablefinal.xlsx.
2 The SHEET_INTERVAL= option creates a new worksheet for each BY group.
3 The SUPPRESS_BYLINES= option suppresses the BY lines for each BY group.
4 The SHEET_LABEL= option customizes the worksheet label.
5 The EMBEDDED_TITLES= option embeds the title that is created by the TITLE statement in the output.
6 THE ODS CLOSE statement closes the destination and any associated files.
Figure 16.11 Customized Excel Output
Customized Excel Output
Last updated: August 23, 2018
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.143.222.225