SUGI 28 Advanced Tutorials

Paper 12-28

Generating Custom Excel Spreadsheets using ODS

Chevell Parker, SAS Institute, Cary, NC

ABSTRACT GENERAL APPEARANCE AND COMMON

This paper will demonstrate techniques on how to effectively TASK
generate files that can be read with Microsoft Excel using the
Output Delivery System. This paper will further discuss a TITLES AND FOOTNOTES
variety of methods that will allow customization of the every Using the ODS HTML destination to create the .XLS or .CSV
part of the Excel file from ODS. Some of the tips provided will files will place the entire title or footnote in the first cell. The
work with Excel 97, 2000 and 2002. However, much of what effect of this is that the first column will become the width of
is covered especially, the advanced techniques using XML the title or footnote. This occurs because the ODS HTML
and the special Microsoft Office style properties apply to destination uses the non-standard <Table> tag for the titles
Excel 2000 and partially to 2002. and footnotes and bylines which Excel does not expect for a
header. The width of the title or footnote will extend as much
as 4 cells before wrapping. To change this behavior, one of
INTRODUCTION the HTML tagsets can be used. Most of the HTML tagsets
As you will see, creating files with the Output Delivery use the header tags <h1> by default for titles, footnotes and
System that can be read with Excel is very easy, however, bylines. This is the tag that Excel expects for its headers
some additional work may be required to customize the and footers.
output as you like. Topics of discussion will include the
following: Techniques for creating files with the ODS that can
The HTML tagsets shipped for 9.0 are HTML4, which is the
be read by Excel, General appearance issues and common
default with the ODS HTML destination in 9.1, HTMLCSS,
task, Advanced techniques using XML and the ODS Markup
PHTML, CHTML and IMODE. The tagsets can be specified
destination to modify the Excel file, and Using Excel Macros
as a destination like the example below, or as a value of the
with ODS.
TAGSET= option on the ODS MARKUP statement. The
titles and footnotes can also be merged in Excel using the
COLSPAN= attribute in the titles or footnotes to determine
GENERATING EXCEL FILES how many columns to span. In the first example below, the
There are several methods of generating files that can be PHTML tagset is used to extend the titles beyond the first
read by Excel using the Output Delivery System. The cell. The second example spans the titles over 4 columns in
methods discussed in this paper will be using the ODS HTML the table using the COLSPAN= HTML attribute.
and CSV destinations to generate the Excel or spreadsheet
files. Generic XML files can be read with Excel 2002 and can ods phtml file=’c:\[Link]’
be generated with the XML engine on the LIBNAME stylesheet=”c:\[Link]”;
statement. proc print data=[Link];
run;
When you specify a procedure or data step within the ODS ods phtml close;
HTML statement with the .XLS or .CSV extensions, Microsoft
Excel is opened in the Results Viewer on the PC. Excel is ods html file="[Link]";
not an ODS destination and the fact that the file is opened in title "<td align=center colspan=4><font
Excel is not a product of ODS. Excel sees a file generated size=4><b>this is a test</b></font></td>";
with the registered extension of .XLS, or .CSV and attempts proc print data=[Link];
to open this file within the registered program which is Excel run;
on the PC. ods html close;

ods html file=”c:\[Link]”;

proc print data=[Link]; STARTING OUTPUT IN ROW 1
run;
ods html close; HTML
Output generated with the ODS HTML destination begins in
The new ODS CSV destination can also be used to create row 2 by default. This happens because of the non-breaking
files that can be read by Microsoft Excel. The acronym CSV space character (&nbsp; ) in the anchor tag. The only way
stands for Comma Separated Value. This new destination is to get rid of this anchor tag in the HTML destination is to post
experimental with Version 8.2 as part of the ODS Markup process the HTML file. The HTML tagsets of the Markup
Language. The New CSV destination defaults can be destination can also be used to begin the output in row 1.
changed by modifying the default tagset as we will see The HTML tagsets of the ODS Markup destination do not
shortly. Excel has the ability to read CSV files, so specifying have this non-breaking space character in the anchor tag.
the ODS CSV destination with the extension .CSV will create See the prior example for syntax.
a comma separated file that is opened in Excel by default.
Also, the delimiter can be changed from a comma to any CSV
other delimiter by modifying the CSV tagset. Use the The CSV destination generates output beginning in row 3 of
CSVALL destination to maintain the titles and footnotes and the Excel file. This is the default of the ODS CSV destination.
bylines. The defaults of the destination or tagset can be changed by
modifying the tagset and overriding the defaults. The sample
ods csv file=”c:\[Link]”; code below modifies the CSV tagset and starts the data in
proc print data=[Link];run; row 1 by removing the first 2 empty rows.
ods csv close;

1
SUGI 28 Advanced Tutorials

proc template;
define tagset [Link]; • Numbers with lengths greater than 11 characters are
parent = [Link]; displayed in scientific notation.
define event table;
finish:
• Unformatted dates in SAS will be totally different in
put NL; Excel because their beginning date starts with January
end; 1, 1900 by default.
define event row;
finish:
put NL; NUMBER FORMATS
end ; Importing the cells as text using the Text format for the cell
end; values allow the cell values to come over without any
run; interpretation and does not strip the leading or trailing
zeroes. Using the mso-number-format:\@ style property
ods [Link] body='c:\[Link]' ; allows the cell values to be imported using the Text format
proc print data=[Link] label; run; for Excel 2000 and above. For Excel 97, the style property is
ods [Link] close; [Link]:@ . Below are examples of
applying the Text format and the more common number
formats.

REDUCING FILE SIZE /* Apply text format to all cells */

There are a few techniques that can be employed to reduce
the size of Excel files and reduce the time it takes for the data one;
files to load. The first method involves creating a CSS style input acc_no zipcode;
sheet with the ODS HTML destination. This allows you to cards;
separate the formatting instructions from the data and the 0111 023560
need for each record to have formatting instructions. If you 0333 023334
specify the STYLESHEET= option with a file, an external ;
CSS file is generated. Excel 97 ignores this CSS style sheet. run;

The second method of reducing the size of the .XLS files ods html file=‘[Link]’ headtext=“<style>
created is to use one of the HTML tagsets of the ODS td {mso-number-format:\@}</style>”;
Markup destination. All of the HTML tagsets of the ODS proc print data=one;
Markup destination follow the HTML 4.0 standard which run;
separates the formatting instructions from the data. All of the ods html close;
HTML tagsets except CHTML allow formatting with the use of
a CSS style sheet. The CHTML tagset does not allow the /* Text format applied to a single column */
use of a CSS file. These HTML tagsets all have minimal
formatting such as the borders without the use of the CSS ods html file=‘[Link]’ headtext= “<style>
file. .zero {mso-number-format:\@}</style>”;
proc print data=one;
The final method for reducing the size of the Excel file is to var acct_no / style={htmlclass=”zero”};
use the Minimal style. The Minimal style is one of the default var zipcode;
styles shipped with SAS. The Minimal style has very few run;
formatting instructions, which reduces the size of the file. ods html close;
Referenced are the statistics of the 5 variable, 19
observation [Link] data set. This was done in
Version 8.2. As the observations grew, PHTML became /* Excel 97 solution */
more efficient than its HTMLCSS counterpart. Not listed, the ods html file='[Link]';
CSV destination was the smallest of all at 1K. proc print data=one;
var acct_no / style={htmlstyle="[Link]-
8.2 Benchmark [Link]:@"};
HTML HTML/ PHTML CHTML MINIMAL var zipcode;
CSS HTMLCSS run;
21K 5k 5k 4k 5k ods html close;

COMMON NUMBER FORMATS

CELL FORMATING
mso-number-format:0 NO Decimals
One of the most problematic areas that you will face when
mso-number-format:"0\.000" 3 Decimals
creating Excel files from ODS is with cell formatting. The
problems are the same whether using the CSV or the HTML mso-number-format:"\#\,\#\#0\.000" Comma w\3 dec
destinations. The problem occurs because Excel uses a mso-number-format:"mm\/dd\/yy" Date7
General format to import cell values. The General format mso-number-format:"mmmm\\ d\\\,\\ yyyy" Date9
reads the cell values as they are typed, however, there are mso-number-format:"m\/d\/yy\\ h\:mm\\ AM\/PM" D -T AMPM
some common problems that you should be aware of. mso-number-format:"Medium Date" 01-mar-98
mso-number-format:"d\\-mmm\\-yyyy" 01-mar-1998
• Both numeric and character variables will lose leading mso-number-format:"Short Time" 5:16
and trailing zeroes when creating Excel files with the mso-number-format:"Medium Time" 5:16 am
ODS HTML and CSV destinations. You will not realize mso-number-format:"Long Time" 5:16:21:00
the problem until the leading or trailing zeroes are mso-number-format:Percent; Percent
omitted from an account number, an ID, or a zip code.

2
SUGI 28 Advanced Tutorials

mso-number-format:0% No percent border-bottom style properties control the various parts of

mso-number-format:"0\.E+00"; Fractions the border. The mso-pattern style property can be used to
mso-number-format:"\@" Text specify the various patterns or the various shades of gray.
The alignment is controlled with the JUST= attribute or the
text-align style property. The text orientation can be
modified by using the layout-flow style property which takes
CELL FORMATING IN THE CSV DESTINATION the value of vertical and horizontal and the mso-rotate style
To prevent losing the leading zeroes when using the CSV property which allows the rotation based on degrees. The
destination, an “=” can be added in front of the character mso-text-control: shrinktofit style attribute and value is
strings. This allows the fields to be read using the text used to force the value to fit in the cell by reducing the size.
format. This solution also works with the HTML destination. Other style attributes that affect how the text is rendered are
The CSV tagset can also be modified to add the “=” before the white-space style property with the values wrap, to wrap
the data values. To modify a specific field, add the “=” in front the text on the blank spaces and normal which is the default.
of the data value within the data step. The last style property that I will mention is the text-indent.
This allows the indentation of the cell values. Below is an
proc template; example that shows how this is done.
define tagset [Link];
parent=[Link]; ods html file='[Link]';
define event data; title;
put "," / if !cmp( COLSTART , "1" ); proc report data=[Link](obs=5) nowd
put '=' """" / if cmp( TYPE ,”string" ); style(report)={rules=none }
put VALUE; style(column)={background=white
put """" / if cmp( TYPE , "string" ); htmlstyle='border:none'}
end; style(header)={htmlstyle="mso-rotate:45;
end; height:50pt; border:none"
run; background=_undef_};
col name age sex height weight;
ods markup file=”c:\[Link]” compute after;
tagset=[Link]; name="Total";
proc print data=one; run; endcomp;
ods markup close; rbreak after / summarize
style={font_weight=bold htmlstyle="border-
bottom:5px double red;border-
ROW HEIGHT AND COLUMN WIDTH left:none;border-right:none;border-
When the row height and column width are set with a style in top:5px dashed red"};
ODS, they are ignored by Microsoft Excel. A special MSO run;
CSS style property has to be set before Excel will recognize ods html close;
the row height and column width set. If numbers have widths
greater than the column width, the number will be displayed
as ####. For character values, they will appear truncated if
the cell to its right is not empty. When applying the height or Figure 1. Customized Borders
the width, the special MSO style property mso-height-
source:userset and the mso-width-source:userset have
to be set before specifying a width or a height. The example
below applies the width to a single column by defining the
class with the HEADTEXT= ODS HTML option.

ods html file='[Link]' headtext=

'<style> .test {mso-width-source:
userset;width:200pt}</style>';
proc print data=[Link];
var age / style(column)={htmlclass="test"};
var sex height weight;
run;
ods html close;

BORDERS, ALIGNMENT AND PATTERNS

Generating customized borders can be done by using PROC
TEMPLATE, procedures that support the STYLE= option, or
with CSS style sheets. This section will demonstrate how to
generate customized borders for a table. The first thing that
is done is to turn off the borders at the table level so that the
borders can be customized for individual cells. To do this,
use the CSS style property Border. The Border style property
has 3 separate values: weight, style, and color. The border
style property can be used with the style attribute
HTMLSTYLE= to control the borders on an individual level.

The border style property will control the overall border,

however, the border-left, border-right, border-top and

3
SUGI 28 Advanced Tutorials

PAGE SETUP
Page setup options can be set with a combination of style ods htmlcss file='[Link]'
stylesheet=”[Link]” headtext=
properties and XML. In the page set up, we have the ability
'<style> @Page {mso-header-data:”Page &P of
to modify all of the various items within the page set up such
&N”; mso-footer-data:"&Lleft text &Cpage
as the margins of the page, the margins of the header and &P&R&D&T"};
footer, the page orientation, the DPI (data per inch) of the </style>';
output, the paper size, the first page number and every other proc print data=[Link];
item. Many of these items can be set using the CSS style
run;
properties and the Microsoft Office specific style properties.
ods htmlcss close;
The remainder can be set using XML.

USING XML TO MODIFY EXCEL

MARGINS AND PAGE ORIENTATION
XML can be used to modify Excel applications created with
Margins can be set for the page to include the top, bottom, ODS. With the use of XML and the CSS style properties,
left and right margins. The margins can also be specified for virtually every part of the Excel file can be modified from
the headers and footers and the justification of the page ODS. The XML included is added between the <head> and
vertically and horizontally. Other items that can be specified </head> tags of the HTML file. The various XML elements
such as the paper size, the page orientation, and headers control the different actions or options within Excel. A
and footers all can be set using the CSS @Page rule. complete list of all of the XML elements and style properties
that can be used to modify your Excel applications can be
The margins for the page can be set using the style property found at the URL located in the references at the end of the
Margin. The margins for the headers and footers can be paper. The ODS MARKUP destination is used in the below
specified using the Microsoft Office specific mso-header- examples to supply the XML. The reason the Markup
margin and mso-footer-margin style properties. The destinations was chosen is because of its flexibility. With the
alignment of the table horizontally and vertically on the page Markup destination, you have the ability to control the flow of
can be set using the mso-horizontal-page-align and the the HTML generated. With the doc event, the Microsoft
mso-vertical-page-align style properties. The paper size Office and the Excel namespace are added to the opening
can be modified with the size style property with the <HTML> tag. The XML is added to the event doc_head
appropriate paper size. This can also be set with XML which which is structured by adding new line characters at the end
is shown in a later example. The page orientation can be of each statement. Unlike the HEADTEXT= option which
modified with the mso-page-orientation style property, has a 256 character limit, adding the values to this event has
however, for Office 2000 at least, this has to be augmented no physical limit. The data step can also be used to append
with the XML <ValidPrinter> tag within the Print element. the header. We will just touch on the power that XML plays
in modifying your Excel applications.
ods html file='[Link]' headtext=
'<style> @page{margin:1.0in .75in 1.0in With the use of XML, we can perform such functions as
75in; generating multiple worksheets per workbook, naming
mso-header-margin:.5in; worksheets within the workbook, activating and selecting
mso-footer-margin:.5in; cells, hiding worksheets, supply worksheet options, add
mso-horizontal-page-lign:center; formulas, name formulas, modify the resolution of the printed
mso-vertical-page-align:center; output, selecting the number of copies to print, scaling the
mso-page-number-start:1;} printed output, generating backups, splitting windows, modify
</style>'; the window size, data validation, sorting, conditional
proc print data=[Link]; formatting, set filters, supply and remove gridlines, protect
run; cells, supply or remove scroll bars, generate charts, define
ods html close; macros, and so on. I will show some examples of using XML
to modify your Excel applications.

HEADERS AND FOOTERS The first example demonstrates generating multiple

Headers and footers can also be defined within the @Page worksheets for a workbook. Within the Worksheet element, I
rule using the MSO style properties mso-header-data and have named 3 separate worksheets named Claims,
mso-footer-data. This allows you to specify customized Approved, and Paid by specifying the names in the Name
headers for the printed output. The headers and footers can tag. Within the WorksheetSource tag, the URL is specified
be a generic page number, to the more sophisticated page X for the sheet. In this example, the HTML files were
of Y, date time, a signature, very customized headers and generated in a prior step. This creates a workbook with the
footers with text on the left, right, top and bottom that include name temp and 3 worksheets: Claim, Approved, and Paid.
a variety of the fore-mentioned. The below example uses the The ActiveSheet tag is used to select the active worksheet.
Page X of Y header at the top of the page and some
customized text at the left, center and right at the bottom of proc template;
the page. &P is the current page number, &N is the total define tagset [Link];
number of pages. In the footer &L, left justifies the text parent=[Link];
following and &C and &R center and right justify, define event doc;
respectively. The font name, style and size all can also be start:
modified for the headers and footers as well. Also the CRLF put '<html xmlns:o="urn:schemas-
character can be specified using the \000A to split text over microsoft-com:office: office"' NL;
multiple lines. put 'xmlns:x="urn:schemas-
microsoft-com:office:excel"' NL;
Header and Footer codes finish:
&P &N &T &D &F &B &I put "</html>" NL;
end;
Page # Pages Time Date File Bold Italic
define event doc_head;
start:

4
SUGI 28 Advanced Tutorials

put "<head>" NL; and has to be less than 0. The Type and Qualifier tags
put '<meta name="Excel Workbook within the DataValidation element determine this. When the
Frameset">'; field B4 is selected, the input title will be displayed along with
finish: the input message. If you attempt to change this value and
put "<!--[if gte mso 9]><xml>" NL; the data is not validated, the error message is displayed
put "<x:ExcelWorkbook>" NL; along with the error title. In the below example, the cell B4 is
put " <x:ExcelWorksheets>" NL; selected automatically when the .XLS file is opened. This is
put " <x:ExcelWorksheet>" NL; done by adding the Activerow and Activecol tags within the
put " <x:Name>Claims</x:Name>" NL; WorksheetOptions element. Warning, the active row and
put " <x:WorksheetSource active column is 1 less than it needs to be to select the cell
HRef='c:\[Link]'/>" NL; correctly. We could have easily checked an entire range
put " </x:ExcelWorksheet>" NL; rather than a single cell using the RangeSelection tag with
put " <x:ExcelWorksheet>" NL; the appropriate ranges.
put " <x:Name>Approved</x:Name>" NL;
put " <x:WorksheetSource proc template;
HRef='c:\[Link]'/>" NL; define tagset [Link];
put " </x:ExcelWorksheet>" NL; parent=[Link];
define event doc;
put " <x:ExcelWorksheet> " NL;
start:
put " <x:Name>Paid</x:Name>" NL; put '<html xmlns:o="urn:schemas-
put " <x:WorksheetSource microsoft-com:office:office"' NL;
HRef='C:\[Link]'/>" NL; put ' xmlns:x="urn:schemas-microsoft-
put " </x:ExcelWorksheet>" NL; com:office:excel" ' NL;
put " </x:ExcelWorksheets>" NL; finish:
put "<x:WindowHeight>5000 put "</html>" NL;
</x:WindowHeight>" NL; end;
put " <x:WindowWidth>10380 define event doc_head;
start:
</x:WindowWidth>" NL;
put "<head>" NL;
put "<x:WindowTopX>480</x:WindowTopX>" NL; put VALUE NL;
put "<x:WindowTopY>45</x:WindowTopY>" NL; put "<style>" NL;
put "<x:ActiveSheet>3</x:ActiveSheet>" NL; put "<!--" NL;
put "</x:ExcelWorkbook>" NL; trigger alignstyle;
put "</xml><![endif]-->" NL; put "-->" NL;
put "</head>" NL; put "</style>" NL;
end; finish:
end; put '<!--[if gte mso 9]><xml>' NL;
put '<x:ExcelWorkbook>' NL;
run; put ' <x:ExcelWorksheets>' NL;
put ' <x:ExcelWorksheet>' NL;
ods markup file="c:\[Link]" put ' <x:Name>testing1</x:Name>' NL;
tagset=[Link];; put ' <x:WorksheetOptions>' NL;
data _null _; put ' <x:Selected/>' NL;
file print; put ' <x:DoNotDisplayGridlines/>' NL;
put "testing"; put ' <x:Panes>' NL;
run; put ' <x:Pane>' NL;
ods markup close; put ' <x:Number>3</x:Number>' NL;
put ' <x:ActiveRow>3
</x:ActiveRow>' NL;
put ' <x:ActiveCol>1
Figure2. Multiple Worksheets in a Workbook </x:ActiveCol>' NL;
put ' </x:Pane>' NL;
put ' </x:Panes>' NL;
put ' </x:WorksheetOptions>' NL;
put ' <x:DataValidation>' NL;
put ' <x:Range>B4</x:Range>' NL;
put ' <x:Type>Whole</x:Type>' NL;
put ' <x:Qualifier>Less
</x:Qualifier>' NL;
put ' <x:Value>0</x:Value>' NL;
put ' <x:InputTitle>Tip
</x:InputTitle>' NL
put ' <x:InputMessage>Verify number
</x:InputMessage>' NL;
put ' <x:ErrorMessage>incorrect
number </x:ErrorMessage>' NL;
put ' <x:ErrorTitle>stop
</x:ErrorTitle>' NL;
put ' </x:DataValidation>' NL;
put ' </x:ExcelWorksheet>' NL;
put ' </x:ExcelWorksheets>' NL;
put '</x:ExcelWorkbook>' NL;
put "</xml><![endif]-->" NL;
put "</head>" NL;
end;
end;
The example below uses XML to validate data that is passed run;
to Excel. For the cell B4, the value has to be a whole number

5
SUGI 28 Advanced Tutorials

ods markup file="c:\[Link]" put "<o:Company>SAS</o:Company>" NL;

tagset=[Link] stylesheet='c:\[Link]'; put "<o:Manager>[Link]</o:Manager>" NL;
proc print data=[Link](obs=5); put "<o:Category>A</o:Category>" NL;
var age sex height weight; put "<o:Keywords>Test</o:Keywords>" NL;
run; put "<o:Description>Monthly Report
ods markup close;
</o:Description>" NL;
put "</o:DocumentProperties>" NL;
put "</xml><![endif]--> " NL;
Figure 3. Data validation put "</head>" NL;
end;
end;
run;

ods markup
file="c:\[Link]"(title="sugi28")
tagset=[Link]
stylesheet='c:\[Link]'
headtext='<base href="c:\sugi28">';
proc print data=[Link];
run;
ods markup close;

Figure 4. Summary Tab of the Document Properties

The example below writes information to the Summary tab

of the document properties. Values that can be supplied are
the title, subject, author, manager, company, category,
keywords, comments, and hyperlink base. The values can
all be supplied with the below like named tags within the
DocumentProperties element. The title will get its value from
the <title> HTML tag if it’s present, therefore we add the
Title= ODS HTML sub-option to supply a value to this tag.
Otherwise, the value defaults to “SAS Output”. If the title tag
were not present, then it would use the value specified within
the Title XML tag. The hyperlink base specifies the defaults
for all unqualified files. To populate this value use the <BASE
> HTML tag with the HREF= attribute and the appropriate
location of where Excel should look for these files.

proc template;
define tagset [Link];
parent=[Link];
define event doc;
start:
put '<html xmlns:o="urn:schemas-
microsoft-com:office:office"' NL;
finish:
put "</html>" NL;
end; This example is a continuation of the page set up options
define event doc_head; that can be specified within ODS. The example shows how to
start: set the remaining options for a worksheet within page set up.
put "<head>" NL; Within the Print element, we specify that the output is printed
put VALUE NL; in black and white, draft quality, legal paper size, scaled to
put "<style>" NL; 85%, gridlines are printed, row and column headers are
put "<!--" NL; printed, and that the horizontal resolution is 300 DPI. I don't
trigger alignstyle; think we would want this along with draft quality, but wanted
put "-->" NL; to show that this can be used. The column headers on row 3
put "</style>" NL; are repeated for each page. This is done by adding the value
Print_Titles in the Name tag within the ExcelName element.
finish:
The page orientation is also landscape because the mso-
put "<!--[if gte mso 9]><xml>" NL;
put "<o:DocumentProperties>" NL; page-orientation :landscape style is specified in
conjunction with the ValidPrintInfo XML tag of the Print
put "<o:Title>Sugi 28</o:Title>" NL;
element. To print only a specified area, the Print_Area value
put "<o:Author>[Link]</o:Author>" NL;
can be specified for the Name node within ExcelName
put "<o:Subject>Demo</o:Subject>" NL;
element. The PaperSizeIndex tag is used to control the

6
SUGI 28 Advanced Tutorials

paper size. This is specified within the Print element and can proc print data=[Link];
have the following values. run;
ods markup close;
Paper Size values
Legal Executive A4 A5 B5 No.10 A2. DL C6
5 7 9 11 13 15 17 19 21

proc template; Figure 6. Page Setup and Sheet options

define tagset [Link];
parent=[Link];
define event doc;
start:
put '<html xmlns:o="urn:schemas-
microsoft-com:office:
office"' NL;
put 'xmlns:x="urn:schemas-microsoft-
com:office:excel">' NL;
finish:
put "</html>" NL;
end;
define event doc_head;
start:
put "<head>" NL;
put VALUE NL;
put "<style>" NL;
put "<!--" NL;
trigger alignstyle;
put "-->" NL;
put "</style>" NL; Window options can be specified for the Excel file using the
finish: WorksheetOptions element. The below example changes
put "<!--[if gte mso 9]><xml>" NL; all of the window options. The gridlines are removed, zeroes
put "<x:ExcelWorkbook>" NL; are not displayed, the column headers are not displayed, and
put "<x:ExcelWorksheets>" NL; the outline is not specified. The Workbook element is
put " <x:ExcelWorksheet>" NL; responsible for removing the horizontal and vertical scroll
put " <x:Name>Sheet1</x:Name>" NL; bars, and hiding the workbook tabs.
put " <x:WorksheetOptions>" NL;
put " <x:DisplayPageBreak/>" NL;
put " <x:Print>" NL; proc template;
put " <x:BlackAndWhite/>" NL; define tagset [Link];
put " <x:DraftQuality/>" NL; parent=[Link];
put " <x:ValidPrinterInfo/>" NL; define event doc;
put " <x:PaperSizeIndex>5 start:
</x:PaperSizeIndex>" NL; put '<html xmlns:o="urn:schemas-
put " <x:Scale>85</x:Scale>" NL; microsoft-com:office:office"' NL;
put " <x:HorizontalResolution>300 put 'xmlns:x="urn:schemas-microsoft-
</x:HorizontalResolution>" NL; com:office:excel">' NL;
put " <x:Gridlines/>" NL; finish:
put " <x:RowColHeadings/>" NL; put "</html>" NL;
put " </x:Print>" NL; end;
put " </x:WorksheetOptions>" NL; define event doc_head;
put " </x:ExcelWorksheet>" NL; start:
put " </x:ExcelWorksheets>" NL; put "<head>" NL;
put "</x:ExcelWorkbook>" NL; put VALUE NL;
put "<x:ExcelName>" NL; put "<style>" NL ;
put "<x:Name>Print_Titles</x:Name>" NL; put "<!--" NL;
put "<x:SheetIndex>1 trigger alignstyle;
</x:SheetIndex>" NL; put "-->" NL;
put "<x:Formula>=Sheet1!$3:$3 put "</style>" NL;
</x:Formula>" NL; finish:
put "</x:ExcelName>" NL; put "<!--[if gte mso 9]><xml>" NL;
put "</xml><![endif]-->" NL; put "<x:ExcelWorkbook>" NL;
put "</head>" NL; put " <x:ExcelWorksheets>" NL;
end; put " <x:ExcelWorksheet>" NL;
end; put " <x:Name>Sheet1</x:Name>" NL;
run; put " <x:WorksheetOptions>" NL;
put " <x:DisplayPageBreak/>" NL;
ods markup file="c:\[Link]" tagset=[Link] put " <x:Selected/>" NL;
stylesheet='c:\[Link]' put " <x:DoNotDisplayGridlines/>" NL;
headtext="<style> @page {mso-page-orientation:landscape} put " <x:DoNotDisplayZeros/>" NL;
</style>" ; put " <x:DoNotDisplayHeadings/>" NL;

7
SUGI 28 Advanced Tutorials

put " <x:DoNotDisplayOutline/>" NL; define event doc_head;

put " </x:ExcelWorksheet>" NL; start:
put " </x:ExcelWorksheets>" NL; put "<head>" NL;
put " <x:HideHorizontalScrollBar/>" NL; put VALUE NL;
put "<x:HideVerticalScrollBar/>" NL; put "<style>" NL;
put "<x:HideWorkbookTabs/>" NL; put "<!--" NL;
put "<x:DisplayFormulas/>" NL; trigger alignstyle;
put " </x:ExcelWorkbook>" NL; put "-->" NL
put "</xml><![endif]-->" NL; put "</style>" NL;
put "</head>" NL; finish:
end; put '<!--[if gte mso 9]><xml>' NL;
end; put ' <x:ExcelWorkbook>' NL;
run; put ' <x:ExcelWorksheets>' NL;
put ' <x:ExcelWorksheet>' NL;
ods markup file="c:\[Link]" put ' <x:Name>Sheet1</x:Name>' NL;
tagset=[Link] put ' <x:Sorting>' NL;
stylesheet='c:\[Link]'; put ' <x:Sort>name</x:Sort>' NL;
proc print data=[Link]; put ' <x:Descending/>' NL;
run; put ' <x:Sort>sex</x:Sort>' NL;
ods markup close; put ' <x:Descending/>' NL;
put ' <x:Sort>age</x:Sort>' NL;
put ' </x:Sorting>' NL;
put ' </x:ExcelWorksheet>' NL;
Figure 7. Window Options with View Tab put ' </x:ExcelWorkbook>' NL;
put ' </xml><![endif]-->' NL;
put '</head>' NL;
end;
end;
run;

ods markup file="c:\[Link]"

tagset=[Link] stylesheet='c:\[Link]';
proc print data=[Link];
run;
ods markup close;

Figure 8. Sorting Columns

The Excel files can be sorted based on the field names in the
output. The sort is done for the Excel output only. The data
set is not sorted for this example. In order for Excel to treat
the output as a database, the column headers are specified
in row 1. The Sort element is specified within the Worksheets
element. In the below example, the Name and Sex fields are
in descending order with the Age field appearing in
ascending order. A null title statement is specified so that the
headers begin in row 1.

proc template;
define tagset [Link];
parent=[Link];
define event doc; USING EXCEL MACROS WITH ODS
start: A macro is a program that contains a list of instructions.
put '<html xmlns:o="urn:schemas- Macros in Excel can be used to automate various tasks that
microsoft-com:office:office"' NL; are commonly used. Visual Basic for Applications (VBA) is
put 'xmlns:x="urn:schemas-microsoft- the programming language used to drive macros with the
com:office:excel">' NL; Microsoft Office products. VBA is now the standard
finish: programming language within Microsoft Office products as
put "</html>" NL; well as the ADOBE products. The use of macro in Excel is a
end; very powerful and dynamic feature that I cannot cover

8
SUGI 28 Advanced Tutorials

sufficiently here, but will briefly discuss this and how to cannot be edited without un-hiding the workbook. To edit the
implement macro with files generated with ODS. macros in the [Link] file without un-hiding this window,
the XML element ExcelName can be specified with the name
The type of macro that will be discussed in this section will of how we want to address the macros in current workbook.
be the command macro, or more commonly known as the The Formula node specifies how the macros are addressed.
“sub procedures” for obvious reasons. You might say that In the Formula node, the [Link] workbook is specified
this is well, and good, but you are not interested in learning a with the “ !” preceding the name of the macro. Keystrokes
new programming language. The best part is that you do can also be specified for the macro specifying the Keystroke
not have to learn this programming language to develop tag. The macros in the [Link] file can be run when a
great macro code. Excel allows you to cheat by turning on new workbook is opened without any intervention. When the
the macro recorder. This is done by going to Tools->Macro- name of the macro in the current workbook is named
>Recorder and turning on the macro recorder. This will place auto_open, the macro in the personal macro workbook that
a little icon on your worksheet. Until you turn the recorder we point to is executed automatically when a new workbook
off, it will record every action that is taken and translate this is opened. The reserved macro name auto_close can be
into VBA code. As you see, this reduces the need for you to specified to execute macros when the current workbook is
be a real expert in the language. However, to modify these closed. The name auto_activate can be specified to run
macros, you will need to know the basics of the language. when the workbook is activated. We are not limited to
running macros stored in the [Link] file. We can point
to macro located in any workbook as long as the location and
Unlike the old WordBasic or Excel 4.0 macro language, VBA
the name of the workbook are fully qualified in the formula
6.0 allows you to access almost every feature within the
tag with quotes. Only the path and the name of the workbook
Excel application. Macros can be executed by defining a
are quoted.
keystroke for the macro, going to: Tools->Macro and
selecting Run, or run when the workbook is opened by
naming the macro auto_open. There are various other ways Below is an example of running a macro when the workbook
to do this, such as adding it to the tool bar, or as an add-in, is opened using XML with the Markup destination to define a
but I will focus on the fore-mentioned three. Macros that are macro with the name auto_open. Because we use this
commonly used can also be placed in the [Link] reserved name for the macro, the workbook will attempt to
workbook, which is referred to as the “personal macro execute this macro when the workbook is opened. The
workbook”. The macros located in this [Link] file will be current workbook is pointing to a macro in the [Link]
available to all workbooks opened. You can think of this as file by the name myform. When this workbook is opened, the
an autoexec file. This is done by placing the [Link] workbook will bring up a form which I defined in the
workbook in the XLStart folder, which is located by default in [Link] file as userform2. The form contains buttons that
C:\Program Files\Microsoft Office\Office\XLSTART. After the allow you to select the various corporate styles using macro.
macros are stored in this [Link] workbook, the
workbook is hidden so that it is not displayed. This is done by proc template;
going to: Window-> Hide. After saving this file, every define tagset [Link];
workbook opened will have access to these macros. parent=[Link];
define event doc;
Excel files generated with ODS will have access to all of the start:
macros defined in the [Link] workbook when the .XLS put '<html xmlns:o="urn:schemas-
files are opened which causes very little overhead. What we microsoft-com:office: office"' NL;
can do with VBA is endless. I will only touch this subject and put 'xmlns:x="urn:schemas-microsoft-
present a few examples to show how this can be used com:office:excel"' NL;
effectively from ODS. finish:
put "</html>" NL;
SAMPLE MACRO SYNTAX end;
define event doc_head;
/* Displays user created form */ start:
put "<head>" NL;
Sub myform() put VALUE NL;
[Link] put "<style>" NL;
End Sub put "<!--" NL;
trigger alignstyle;
/* Changes window options */ put "-->" NL;
Sub options() put "</style>" NL;
With ActiveWindow finish:
.DisplayGridlines = False put "<!--[if gte mso 9]><xml>" nL;
.DisplayHeadings = False put " <x:ExcelName>" NL;
.DisplayOutline = False put " <x:Name>auto_open</x:Name>" NL;
.DisplayZeros = False put " <x:Macro>Command</x:/Macro>" NL;
.DisplayHorizontalScrollBar = False put " <x:Formula>=[Link]!myform
.DisplayVerticalScrollBar = False </x:Formula>" NL;
.DisplayWorkbookTabs = False put "</x:ExcelName>" NL;
End With put "</xml><![endif]-->" NL;
With Application put "</head>" NL;
.DisplayFormulaBar = False end;
.DisplayStatusBar = False end;
.DisplayCommentIndicator = 0 run;
End With
End Sub

While the [Link] file is hidden, the macros in this file

9
SUGI 28 Advanced Tutorials

ods markup file="c:\[Link]"

tagset=[Link];
proc print data=[Link];
title; CONTACT INFORMATION
run; Your comments and questions are valued and encouraged.
ods markup close; Contact the author at:
Chevell Parker
SAS
Figure 9. Executing Excel Macros SAS Campus Drive
Cary, NC 27513
Email: [Link]@[Link]

SAS and all other SAS Institute Inc. product or service

names are registered trademarks or trademarks of SAS
Institute Inc. in the USA and other countries. ® indicates
USA registration.

Other brand and product names are trademarks of their

respective companies.

CONCLUSION
As you can see, generating files that can be read with Excel
is very easy when using ODS. When you need more than
what you're getting from the defaults, you can use some of
the techniques mentioned in this document to fully customize
your Excel output. Also mentioned were common issues that
you should be aware of when generating Excel files from
ODS.

REFERENCES
“Microsoft Office HTML and XML Reference”
[Link]
rary/en-us/dnoffxml/html/[Link]

Parker, Chevell. “Tips for creating Excel files with ODS”.

[Link]
[Link]

“ODS FAQs” [Link]

[Link]

“Using ODS to Export Output in a Markup Language”

[Link]

10