DATA Step, Macro, Functions and more

Creation of complex Excel reports

Frequent Contributor
Posts: 83

Creation of complex Excel reports

Hi ,

Please could somebody share with links of materials which would help build
complex excel based reports from sas.

Actually wat i need to create is a report with multiple layers of hedings and rows
in the same page with aggregations , etc..
Valued Guide
Posts: 2,191

Re: Creation of complex Excel reports

have a look at
"ODS MARKUP: The SAS Reports You've Always Dreamed Of (.pdf)" at and appreciate that there have been some significant improvements since then.
Posts: 9,373

Re: Creation of complex Excel reports

I don't know what you mean by "multiple layers of headings and rows", however, ODS MARKUP, specifically, TAGSETS.EXCELXP will create multi-sheet Excel workbooks which allows extensive control over print headings and print footers and also allow multiple tables to be placed together in one sheet. In addition TAGSETS.EXCELXP allows you to freeze Excel column headers for easier "scrolling" of long tables and allows you to turn on autofilter. This all works because TAGSETS.EXCELXP creates Spreadsheet Markup Language XML which is the Microsoft specification for Office 2003 XML.

In addition, you can create HTML output using ODS HTML, ODS MSOFFICE2K or ODS TAGSETS.TABLEEDITOR. While most HTML methods will not give you multi-sheet workbooks, you can do different types of workbooks with HTML-based methods. TAGSETS.TABLEEDITOR, in particular embeds JavaScript in the HTML tags so that you can build pivot tables, for example. Pretty cool stuff.

The link that Peter gave you is the place to start. Also using a Google search to find user group papers, such as this one:

might also be helpful.


Also, since you have previously posted of using Web Report Studio and the BI Platform, please keep in mind that not all of these destinations' output will work with the SAS Add-in for Microsoft Office. For example, TAGSETS.EXCELXP type of XML is not an output type that would be respected by the Add-in for Microsoft Office or Web Report Studio.
Ask a Question
Discussion stats
  • 2 replies
  • 3 in conversation