5869  ods tagsets.excelxp
5870        options(doc='help')
5871        file='c:\temp\xlsfile.xls' record_separator=none
5872        style=sansprinter;
NOTE: Writing TAGSETS.EXCELXP Body file: c:\temp\xlsfile.xls
==============================================================================
The EXCELXP Tagset Help Text.

This Tagset/Destination creates Microsoft's spreadsheetML XML.
It is used specifically for importing data into Excel.

Each table will be placed in its own worksheet within a workbook.
This destination supports ODS styles, traffic lighting, and custom formats.

Numbers, Currency and percentages are correctly detected and displayed.
Custom formats can be given by supplying a style override on the tagattr
style element.

By default, titles and footnotes are part of the spreadsheet, but are part
of the header and footer.

Also by default, printing will be in 'Portrait'.
The orientation can be changed to landscape.

The specification for this xml is here.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlsmlinss.asp

See Also:
http://support.sas.com/rnd/base/topics/odsmarkup/
http://support.sas.com/rnd/papers/index.html#excelxml

==============================================================================

These are the options supported by this tagset.

Sample usage:

ods tagsets.excelxp file='test.xml' data='test.ini' options(doc='Help');

ods tagsets.excelxp options(doc='Quick');

ods tagsets.excelxp options(embedded_titles='No' Orientation='Landscape');

Doc:  No default value.
     Help: Displays introductory text and options.
     Quick: Displays available options.

Orientation:   Default Value 'Portrait'
     Tells excel how to format the page when printing.
     The only other value is 'landscape'.
     Also available as a macro variable.

Embedded_Titles:   Default Value 'No'
     If 'Yes' titles will appear in the worksheet.
     By default, titles are a part of the print header and footer.
     Also available as a macro variable.

Embedded_Footnotes:   Default Value 'No'
     If 'Yes' footnotes will appear in the worksheet.
     By default, footnotes are a part of the print header and footer.
     Also available as a macro variable.

Embed_Titles_Once:   Default Value 'No'
     If 'Yes' Embedded titles will only appear at the top of each worksheet.

Print_Header:   Default Value ''
     If Embedded titles are on, this value will be used as the header for
     printing.  Everything about the appearance of the 3 part header can
     be controlled with this value.  The Excel syntax for this string follows.
     Of course the easiest way to create a header or footer is to do it in
     Excel.  Then save the workbook to xml.  Search the XML for <Header or
     <footer.  A simple cut and past will make it a part of your SAS program.

          &amp;L <Left header>  &amp;C  <Center header>  &ampR  <Right Header>

     A very simple example is this:

          &amp;LLeft header text&amp;CCenter header text&ampRRight Header Text

     Newlines can be introduced by inserting &#13; within the text.
     Other special values follow

          Newline:     &#13;
          Page Number: &amp;P
          Pages:       &amp;N
          Date:        &amp;D
          Time:        &amp;T
          File Path:   &amp;Z&amp;F
          File:        &amp;F
          Sheet Name:  &amp;A
          Underline:   &amp;U    One to start underlining, another to stop it.
          Font Size:   &amp;8

     The font size can be controlled by placing the font size in points right
     before the text.  This is a left sided header with a font size of 8.

          &amp;L&amp;&amp;8This is a test;

     The font, bold and Italic can be changed using this syntax.

          &quot; <font name>, <Bold><Italic> &quot;

     This example changes the font, turns on bold and Italic, changes the font size
     and turns underline on and off.

     &amp;L&amp;&quot;Palatino,Bold Italic&quot;&amp;9&amp;UThis is a test&amp;U

     This is a complete example, showing the various possibilities

&amp;L&amp;&quot;Palatino,Bold Italic&quot;&amp;9&amp;UThis is underlined &amp;U
This is not &#13;&amp;12This is bigger&amp;CThis is the Center&#13;Page: &amp;P&#13;
Pages: &amp;N&#13;Date: &amp;D&#13;Time: &amp;T&#13;Path: &amp;Z&amp;F&#13;
File: &amp;F&#13;Sheet: &amp;A&amp;R&amp;14This is bigger and on the right&#13;&amp;P


Print_Footer:   Default Value ''
     If Embedded footers are on, this value will be used as the footer for
     printing.  Everything about the appearance of the 3 part header can
     be controlled with this value.  The syntax for this value is the same
     as that for the Print_Header option.

Suppress_Bylines:   Default Value 'No'
     If 'Yes' Bylines will not appear in the worksheet.  This is useful with
     Proc Print because turning off bylines will defeat the tagset's bygroup
     processing abilities when using proc print.

Zoom:   Default Value '100'
     This value determines the zoom level on the worksheet.

Scale:   Default Value '100'
     This value determines the scale level for printing

DPI:   Default Value '300'
     This value determines the dots per inch for printing

Pages_FitWidth:   Default Value '1'
     This value determines the number of pages to fit the worksheet across
     when printing.

Pages_FitHeight:   Default Value '1'
     This value determines the number of pages down to fit the worksheet
     when printing.

FitToPage:   Default Value 'no'
     Fit to Page when printing.

Page_Order_Across:   Default Value 'no'
     If set to yes, the worksheet page order will be set to print across,
     then down.

Center_Vertical:   Default Value 'no'
     This value controls vertical centering for printing

Center_Horizontal:   Default Value 'no'
     This value controls horizontal centering for printing

Row_Repeat:   Default Value 'none'
     Values: None, number, range, header.
     If a number is specified that row will be repeated across pages.
     When a worksheet breaks across pages when printing.
     If a range such as '3-5' is given, that range of rows will be
     repeated.  If 'header' is given,  the table headers for the first
     table of the worksheet will be repeated.

Column_Repeat:   Default Value 'none'
     Values: None, number, range, header.
     If a number is specified that column will be repeated across pages.
     When a worksheet breaks across pages when printing.
     If a range such as '3-5' is given, that range of columns will be
     repeated.  If 'header' is given,  the columns that contain the
     row headers for the first table of the worksheet will be repeated.

Frozen_Headers:   Default Value 'No'
     Values: Yes, No, number.
     If 'Yes' The rows down to the bottom of the headers will be frozen when
     the table data scrolls.  This includes any titles created with the
     embedded titles option.  If a number is given, that is the row count
     that will be frozen.
     Also available as a macro variable.

Frozen_RowHeaders:   Default Value 'No'
     Values: Yes, No, number.
     If 'Yes' The header columns on the left will be frozen when
     the table data scrolls.  If a number is given, that is the column
     count that will be frozen.
     Also available as a macro variable.

AutoFilter:   Default Value 'none'
     Values: None, All, range.
     If 'all' An auto filter will be applied to all columns.
     If a range such as '3-5' The auto filter will be applied to the
          in that range of columns.

AutoFilter_Table:   Default Value '1'
     Values: Any number
     If sheet interval is anything but table or bygroup, this value
     Determines which table gets the autofilter applied.  If the sheet
     interval is table, or bygroup the only table get's the autofilter
     regardless of this setting.

Formulas:   Default Value 'yes'
     Values: yes, no
     By default, data values that start with an '=' will become formulas
     instead of cell values.  This behavior can be turned off by setting
     this option to 'no'.  Excel only understands relative column references
     in it's XML.  A formula like sum(C2,C3) or A2+B3 will not work.
     An equivalent might be sum(R[-2]C,R[-1]C) or RC[-2]+RC[-1].
     See the Proc Print example under Default_Column_Width.

Width_Fudge:   Default Value '0.75'
     Values: None, Number.
     By default this value is used along with Width_Points and column width
     to calculate an approximate width for the table columns.
     width = Data_Font_Points * number_Of_Chars * Width_Fudge.
     If 'none' this feature is turned off.

Width_Points:   Default Value 'None'
     Values: None, Number.
     By Default the point size from the data or header style
     elements are used to calculate a pseudo column width.
     The column width is calculated from the given column width or
     the length of the column's header text.  If the header is bigger.
     In the case the header length is used, so is the header's point size.
     This value overrides that point size.
     This value is used along with WidthFudge and column width
     to calculate an approximate width for the table columns.
     width = Width_Points * number_Of_Chars * Width_Fudge.

Default_Column_Width:   Default Value 'None'
     Values: None, Number, list of numbers.
     Most procedures provide column widths, but occasionally a column
     will not have a width.  Excel will resize the column to fit any
     numbers but will not auto-size for character string headings.
     In the case that a column does not have a width, this value will be
     used instead.  The value should be the width in characters.
     If the value of this option is a comma separated list.
     Each number will be used for the column in the same position.  If
     the table has more columns, the list will start over again.

Absolute_Column_Width:   Default Value 'None'
     Values: None, Number, list of numbers.
     This option works similarly to the default column width option
     The difference is that these widths will be used regardless
     of any column widths the procedure might provide.
     The value should be the width in characters.
     If the value of this option is a comma separated list.
     Each number will be used for the column in the same position.  If
     the table has more columns, the list will start over again.

Row_Heights:   Default Value '0,0,0,0,0,0,0'
     This option controls how tall the rows will be for each type of row.
     The numbers are in points.  By default the values will be taken from the
     font size used for the row.  The font sizes are collected from the style
     definitions for each item.  The table row height is defined by the font
     size in the header style.

     The parameters of this option are positional, but not all values must be
     specified.   A value of 0 means that the height should be taken from the style.
     The first value is the height for table header rows. The next is the height
     for the table body rows.
     The next value is the row height for bylines.  The fourth is for titles,
     the fifth is for footers, the sixth is the pagebreak height,
     and the last value is the height for paragraph skip

     The default values are:
     Table_head  : 0
     Table  : 0
     Byline : 0
     Title  : 0
     Footer : 0
     PageBreak : 0
     Parskip : 0

row_height_fudge:   Default Value '4'
     Values: Number.
     This value is added to the row height for each row.  The additional height
     makes the spreadsheet easier to read.

Autofit_height:   Default Value 'no'
     Values: yes, no
     If yes no row heights will be specified.  This allows the auto fit height
     of Excel to do it's job, sometimes not so well.

Sheet_Interval:   Default Value 'Table'
     Values: Table, Page, Bygroup, Proc, None.
     This option controls how many tables will go in a worksheet.
     In reality only one table is allowed per worksheet.  To get more
     than one table, the tables are actually combined into one.

     Specifying a sheet interval will cause the current worksheet to close.
     It is recommended that this always be the first option to insure that
     The options following it apply to the new worksheet rather than the
     last worksheet.

Sheet_Name:   Default Value 'None'
     Values: Any string
     Worksheet names can be up to 31 characters long.  This name will
     be used in combination with a worksheet counter to create a unique name.

Sheet_Label:   Default Value 'None'
     Values: Any String
     This option is used in combination with the various worksheet naming.
     heuristics which are based on the sheet interval.
     This string will be used as the first part of the name instead of the
     predefined string it would normally use.

     These are the defaults:

     'Proc ' total_Proc_count  - label
     'Page ' total_page_count  - label
     'By '   numberOfWorksheets byGroupLabel - label
     'Table ' numberOfWorksheets  - label

Contents_Workbook:   Default Value 'Contents, Index'
     Values: Contents, Index, Workbooks, All
     If set to all, The contents file will contain 3 worksheets,
     a list of workbooks,  a hierarchical table of contents, and a
     list of worksheets.

Contents:   Default Value 'No'
     Values: Yes, No
     If set to yes, The first worksheet will contain a table of contents
     With links to each worksheet in the workbook.

Index:   Default Value 'No'
     Values: Yes, No
     If set to yes, The first worksheet will contain a table of contents
     With a single link to each worksheet in the workbook. If both this
     option and the Contents option are set, then the index of worksheets
     will be the second worksheet and it will be named 'Worksheets'.

Missing_Align:   Default Value 'right'
     Values: left, center, right
     Sets the alignment for missing values.
     By default a dataMissing style is created from the data style, the
     dataMissing style is created in 3 versions.  One for each justification.
     When a style has the string 'data' in it's name, the value is checked.
     If it is missing then the dataMissing style will be used instead.
     A dataMissing style can be provided in the style.  If found, the tagset
     will use that style as a basis for the 3 dataMissing styles.

Auto_SubTotals:   Default Value 'No'
     Values: Yes, No
     If yes, this option causes a subtotal formula to be placed in the
     subtotal cells on the last table row of the Print Procedure's tables.
     WARNING: This does not work with Sum By.  It only works if the
     totals only happen once per table.

Convert_Percentages:   Default Value 'Yes'
     Remove percent symbol, apply excel percent format, and multiply by 100.
     This causes percentage values to display as numeric percentages in Excel.
     If 'No' percentage values will be untouched and will appear as
     strings in Excel.
     Will be deprecated in a future release when it is no longer needed.

Currency_symbol:   Default Value '$'
     Used for detection of currency formats and for
     removing those symbols so excel will like them.
     Will be deprecated in a future release when it is
     no longer needed.

Currency_format:   Default Value 'Currency'
     The currency format specified for excel to use.
     Another possible value is 'Euro Currency'.
     Will be deprecated in a future release when it is
     no longer needed.

Decimal_separator:   Default Value '.'
     The character used for the decimal point.
     Will be deprecated in a future release when it is no longer needed.

Thousands_separator:   Default Value ','
     The character used for indicating thousands in numeric values.
     Used for removing those symbols from numerics so excel will like them.
     Will be deprecated in a future release when it is no longer needed.

Numeric_Test_Format:   Default Value '12.'
     Used for determining if a value is numeric or not.
     Other useful values might be COMMAX or NLNUM formats.
     Will be deprecated in a future release when it is no longer needed.

Minimize_Style:   Default Value 'No'
     If set to 'yes' the stylesheet will be filtered so that only the most.
     necessary definitions are printed.  This can have the reverse effect
     if style attribute over rides are used on the proc statements.
     It is best to define a new style with the appropriate over rides built in.
     The proc can use the new style, but without individual attribute over-rides.
     The result is a much smaller style section. - In that case, this option
     should be set to No.

Skip_Space:   Default Value '1,0,1,1,1'
     This option controls how much space follows the different types of output
     that can occur within a worksheet.  The number given is a multiplier that
     is used against the height given in the Parskip style element.  In the
     absence of the Parskip style element the font size from the Header
     style is used.

     The parameters of this option are positional, but not all values must be
     specified.   The first value is for the space following each table.  The
     second value is the space following bylines.  The third is for titles,
     the fourth is for footers and the last value is the space following
     pagebreaks if the do_pagebreak option is turned on and a pagebreak style
     element exists.

     The default values are:
     Table  : 1
     Byline : 0
     Title  : 1
     Footer : 1
     PageBreak : 1

PageBreaks:   Default Value 'No'
     If set to 'yes' page breaks will be inserted into the stylesheet.  The
     pagebreak style element will be used to define what that pagebreak looks
     like.  A sample style definition looks like this.

                 style pagebreak /
                     cellheight=8
                     foreground=black
                     tagattr="HorzStripe";

     It is not necessary to have a style element.  In it's absence a blank row
     will be inserted.



Using Style Elements

     There are a few style attributes that can be used to good effect
     in the ExcelXP tagset.  The TagAttr attribute can be used to add
     formula's and formats.  CellWidth can be used to control the column
     widths.  Flyover can be used to add comments to cells.
     A URL on a cell will cause it to be a link.
     Additionally, Formulas can be given as the actual data values.

     An alternative to setting widths is to use the cellwidth / width
     Style attribute.  This value will be used regardless of any other
     column width calculations.  Cellwidth can be specified in any of
     These units.  Inch, centimeter, millimeter, points  or pixel.
     If a cellwidth for column is given more than once, the first width
     is used.  This can happen when there is more than one table per
     worksheet.

     The following example shows formulas as data, comment text on a
     header, and absolute control of a columns width.


     ods tagsets.excelxp file='test.xml' options(zoom='75');

     data test;
     length a b 8 c $20;
     input a b c $;
     cards;
     1 2 3
     2 3 =RC[-2]+RC[-1]
     3 4 =RC[-2]+RC[-1]
     . . =SUM(R[-3]C:R[-1]C)
     ;
     run;


     proc print noobs;
         var a b;
         var c / style(head) = {flyover="Hello World"}";
             style(data) = {cellwidth=50pt};
     run;

     ods tagsets.excelxp close;


TagAttr Style Element:   Default Value ''
     Values: <ExcelFormat> or
     <Type: dataType>
     <Format: ExcelFormat>
     <Formula: ExcelFormula>
     <Rotate: degrees of rotation.>
     This is not a tagset option but a style attribute that the tagset will
     use to get formula's and column formats. The format and formula's given
     must be a valid to excel.  The rotation must be a valid angle for text.
     90 through -90.
     The Type should be General, String, Number, or DateTime. Excel is case sensitive
     It should be unecessary to specify type except when DateTime is being used.
     Even when doing numbers as text format

     A single value without a keyword is interpreted as a format.
     A formula, format and rotation can be specified together with keywords.
     There should be no spaces except for those between the two values
     The keyword and value must be separated by a ':'
     tagattr='format:###.## formula:SUM(R[-4]C:R[-1]C rotate:90').

      Text ---- @

      Type = DateTime
      Time - 0:00        ----  Short Time
      Time - 0:00:0      ----  h:mm:s
      Time - 00:00.0     ----  mm:ss.0
      Time - 00:00 AM    ----  Medium Time
      Time - 12:00:00 AM ----  Long Time
      Time - 24:00:00    ----  [h]:mm/:s
      Time - 3/14/01 1:30 PM ----  m/d/yy\ h:mm\ AM/P

      Percentage - 6 decimals  ----  0.00000%
      Special - zip code       ----  00000
      Special - zip code + 4   ----  00000\-0000

      Scientific                      ---- Scientific
      Scientific - 4 decimals         ---- 0.0000E+00
      Fraction - As sixteenths (8/16) ---- #\ ??/1


Margins:
     Margins can be set two ways.  With the system options or through styles.
     The system options win over the style settings.  In the style, the margins
     must be set on the 'Body' style element.

     Setting the margins with the options statement is the easiest.

     options topmargin=1in
             bottommargin=1in
             leftmargin=.5in
             rightmargin=.5in;

     As as style definition, the Body element might look like this.  This
     approach is more reusable since each program that uses the style
     automatically gets the margins

     style Body from Body /
         topmargin=.5in
         leftmargin=.25in;

Options Center/NoCenter
     Setting the center/nocenter option will cause titles and bylines to be
     centered or left justified within the worksheet.  If nocenter is set
     the cells are not merged.  This causes excel to do a better job of
     printing when the text is long.

     options nocenter;
    Configuration_Name:
         Description:  Name of the configuration to read or write
                       in the .ini file.

         Possible Values: Any reasonable string.
         Default value:   default
         Current value: default

    Configuration_File:
         Description:  Name of the configuration file to read.
                       This is a .ini formatted file as written
                       to the data file if one is given
                       If given, the options for the configuration
                       will be loaded on top of any options given on the
                       ods statement.  A file may contain more than one
                       configuration section.  Only the first section that
                       matches the configuration name will be loaded.

         Possible Values: A valid file name.
         Default value:

    Debug_Level:
         Description:  Determine what level of debugging information should
                       be printed to the log.  Higher numbers cause more
                       information to be printed.

         Possible Values: 0,1,2,3,4,5
         Default value:
         Current value: 0

==============================================================================
NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.37, 05/31/06). Add options(doc='help') to the ods statement for more
information.
