The MSOffice2K_x Tagset Adds Options to the MSOffice2K Tagset

Chevell Parker, SAS Institute

Introduction

This section provides example code created with the sample Tagsets.MSOffice2K_x which modifies the MSOffice2K destination to add options to this new tagset. This code can be run in SAS 9.1.3 or later and opened in Microsoft Excel 97 or greater. One of the benefits of using this tagset is that it parents from the MSOffice2K tagset, and therefore the output can contain both graphics and tables, unlike the ExcelXP destination, which cannot handle graphics currently. Tagsets.MSOffice2K_x also adds options to perform many of the common tasks in Excel, much like ExcelXP. Some things that the ExcelXP tagset does very well, such as creating multiple worksheets per workbook, can be done here as well, but not as dynamically.

To use this tagset, save the file msoffice2k_x.sas (Updated October 2015) to your computer and read Note 32394: Installing and Storing Updated Tagsets. For links to documentation for using and customizing tagsets, see the ODS MARKUP page. To see a list of all of the options, use the option DOC="Help".

Page Setup

You can modify the values that make up the page setup such as the scaling for printing using the SCALE=. The header and footer values can be modified using the PRINT_HEADER= and the PRINT_FOOTER= options. The left, right, top, and bottom margins can be modified using the MARGIN= option. The margins can also be modified using the LEFT_MARGIN=, RIGHT_MARGIN=, TOP_MARGIN=, and BOTTOM_MARGIN= options. The header and footer margins can be modified using the PRNT_HEADER_MARGIN and PRINT_FOOTER_MARGIN options, the sheet orientation using the ORIENTATION= option, and the paper size using the PAPERSIZE= option. The FITTOPAGE= option can be used to fit the output to a page. Also the PAGES_FITHEIGHT= and the PAGES_FITWIDTH= options can be use to fit the output to a specified number of pages. Other options such as DRAFTQUALITY=, GRIDLINES=, and BLACKANDWHITE= can be used to modify the default print options. 

  ods tagsets.msoffice2k_x file="c:\temp\pagestup.xls" style=normal
      options( doc="help"
               scale="85"
               print_header='&RPage &P Of &N'
               print_footer="This is a footer"
               margin="1in 2in 3in 4in"
               print_header_margin="1in"
               print_footer_margin="1in"
               orientation="landscape"
               papersize="legal"
               draftquality="yes"
               gridlines="yes"
               blackandwhite="yes"
               Print_Fitheight="2"
               Print_fitwidth="2");


  proc print data=sashelp.class;
  run;

  ods tagsets.msoffice2k_x close;

Worksheet Options

You can use worksheet options such as ZOOM= to change the worksheet zoom, and FITTOPAGE= to change the fittopage setting. The SHEET_NAME= option can be used to change the sheet name of the worksheet. The TABCOLOR= option can also be specified to add color to the tab. The end of the table contains a blue horizontal rule which can be controlled by specifying the PAGEBREAK= option. Titles and footnotes can be removed by specifying the EMBEDDED_TITLES=, EMBEDDED_FOOTNOTES=, and EMBEDDED_TITLES_ONCE=. 

  ods tagsets.msoffice2k_x file="c:\temp\worksheetoption.xls" style=normal
      options( zoom="70"
               fittopage="yes"
               sheet_name="New sheet"
               tabcolor="yellow"
               pagebreaks="no"
               embedded_titles="no"
               embedded_footnotes="no" );


  proc print data=sashelp.prdsale(obs=50);
  run;

  ods tagsets.msoffice2k_x close;

Display Options

Display options can be added to the output to change the appearance, such as AUTOFILTER= to add filters to the column headers. Headers and row headers can be frozen by using the FROZEN_HEADER= and the FROZEN_ROWHEADERS= options. These options allow you specify the ROW or column number to freeze in the output. Images or logos can be added using the IMAGE_PATH= option to specify the path an name of the image to be displayed. The height and width of the image can be specified using the IMAGE_HEIGHT= and IMAGE_WIDTH= options. 

  ods tagsets.msoffice2k_x file="c:\temp\display.xls" style=normal
      options( frozen_headers="3"
               frozen_rowheaders="1"
               autofilter="yes"
               image_path="c:\temp\tableeditor_faq\sas.gif"
               image_height="85"
               image_width="75");


  proc print data=sashelp.prdsale(obs=50);
  run;

  ods tagsets.msoffice2k_x close;

Formatting Data

When Excel imports the data created by ODS, it uses the general format to import this data unless there is a specific Excel format applied. There are things that occur that you should be aware of such as leading zeros are dropped. There are other various formatting issues that can occur if the SAS format does not match the Excel format. The Excel format is applied using the Microsoft mso-number-format CSS style property within the HTMLSTYLE= attribute. Specifying the LEADING_ZERO= option preserves leading zeros on all data values. If a value has a leading zero it is detected and the text format is applied. 

data one;
retain zip account;
set sashelp.class;
zip="06555";
account="02345";
run;

ods tagsets.msoffice2k_x file="c:\temp\formatting.xls"
      options( leading_zero="yes") style=normal;


  proc print data=one;
  Title;
  var zip account;
  var age height / style(data)={htmlstyle="mso-number-format:'##.00'"};
  var weight;
  run;

  ods tagsets.msoffice2k_x close;

Multiple Worksheets per Workbook

The HTML format does not support the multiple worksheets per workbook as the SpreadsheetML format which is created by the ExcelXP destination. The output from the ExcelXP destination embeds the data in the worksheet. To emulate this same behavior using the HTML format, you basically supply a link to an existing file that you want to add to the workbook.

You can add multiple workbooks per worksheet using the WORKSHEET_SOURCE= option. To specify multiple worksheets per workbook, specify each file separated by commas with this option. To name the worksheet, prefix the file with the sheet name. If no sheet name is given, the sheet name is Table 1, Table 2. This method requires that the sheet name be generated first. 


  proc sort data=sashelp.class out=test;
  by age;
  run;

  ods tagsets.msoffice2k path="c:\temp" file="temp.html" newfile=output;

  proc print data=test;
  by age;
  run;

  ods tagsets.msofice2k close;



  ods tagsets.msoffice2k_x file="multiple.xls" style=statistical
      options( worksheet_source="11#c:\temp\temp.html,
                                 12#c:\temp\temp1.html,
                                 13#c:\temp\temp2.html,
                                 14#c:\temp\temp3.html,
                                 15#c:\temp\temp4.html,
                                 16#c:\temp\temp5.html"
               );

  data _null_;
    file print;
    put "test";
  run;


  ods tagsets.msoffice2k_x close;

Paneling

You can create simple paneling using the PANCOLS= option, which places tables and graphs side-by-side in the worksheet. You can you also add this with other options to enhance the presentation of the worksheet. 

  ods tagsets.msoffice2k_x  file="c:\temp\trash\test\panels.xls"
      options(panelcols="3") style=normal;

  proc print data=sashelp.prdsale(obs=10);
  var actual predict country region;
  where country="CANADA";
  title "Canada Sales";
  run;

  proc print data=sashelp.prdsale(obs=10);
  var actual predict country region;
  where country="U.S.A.";
  title "USA Sales";
  run;

  proc print data=sashelp.prdsale(obs=10);
  var actual predict country region;
  where country="GERMANY";
  title "Germany Sales";
  run;

  ods tagsets.msoffice2k_x  options(panelcols="2") ;

 proc print data=sashelp.prdsale(obs=20);
 where region="EAST";
 title "East Sales";
 run;

 proc print data=sashelp.prdsale(obs=20);
 where region="WEST";
 title "West Sales";
 run;


 ods tagsets.msoffice2k_x close;

Rotated Column Headers

Column headers can be rotated by specifying the ROTATE_HEADERS= option with the degree you want the header rotated. You should specify this in conjunction with the HEIGHT= option. To specify the header be rotated on a single column, the HTMLSTYLE= option can be specified with the XXXX Microsoft CSS style property. 

  ods tagsets.msoffice2k_x file="c:\temp\rotate.xls" style=normal
      options( rotate_headers="45"
               height="60"  );


  proc print data=sashelp.prdsale;
  title "Rotate header";
  run;

  ods tagsets.msoffice2k_x close;

Page Breaks

Page Breaks can be added the output file by adding the PAGEBREAK_ROW= option. This option specifies the row(s) that the page break should be added. The arguments for the page break are added and separated by commas to specify multiple rows that the page break should be added. 

 ods tagsets.msoffice2k_x path="c:\temp\trash\test" file="pagebreak.xls"
 options(embedded_titles="no"
        pagebreak_row="20,30,40,90");

proc print data=sashelp.orsales;
run;


ods tagsets.msoffice2k_x close;;

Excel Macros

Excel macros can be executed when the file is opened or when the file is closed. This is done by using either the OPEN_MACRO= or CLOSE_MACRO= options. These options pass the path of an Excel file that includes the macro that you want to execute. 

 ods tagsets.msoffice2k_x file="c:\temp\trash\mcro.xls" style=normal
     options( open_macro="'c:\temp\macro.xls'!test" );

 proc print data=sashelp.prdsale(obs=10);
 run;

 ods tagsets.msoffice2k_x close;

Excel Style Options

 ods tagsets.msoffice2k_x file="c:\temp.xls"
     options(header_bgcolor="beige"
             header_fgcolor="brown"
             rowheader_bgcolor="brown"
             rowheader_fgcolor="black"
             data_bgcolor="beige"
             data_fgcolor="brown"
             banner_color_even="pink"
             banner_color_odd="yellow"
             fbanner_color_even="cyan"
             background_color="white") ;
 proc print data=sashelp.class ;
 run;

 ods tagsets.msoffice2k_x close;