The SAS Output Delivery System and reporting techniques


Occasional Contributor
Posts: 14


Please help...

code below using sas data set...

First, I get two unrecognized options in the Log,


Unrecognized option: INDEX

Unrecognized option: PAGEBREAKS

PAGEBREAKS no big deal, I can live without that option.

The first one INDEX was oh so helpful with multple sheets.

I really miss that option.

Second, the SHEET_INTERVAL option is unrecognized....

          Unrecognized option: SHEET_INTERVAL

With excelxp tag sets, I was able to just insert the following line of code and voila! A newpage was started and an automatic table of contents started.

tagsets.excelxp options (sheet_interval='none' frozen_headers='yes' autofilter='yes' sheet_name = 'TABLES by AGE');

The TOC is easy to create but being able to automate the sheet interval was incredibly useful.

Sometimes it is needed after a series of PROCs or just one proc.

Separating sheets using a BY variable is nice, but not as needed as being able to set the sheet interval.

Any suggestions or improvements in my code would be much appreciated.

ods tagsets.msoffice2k_x          file="h:worksheetoption.xls" style=normal

                  options( zoom="70"




                                 sheet_name="New sheet"




                               embedded_footnotes="no" );

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


  ods tagsets.msoffice2k_x

      options( sheet_name="Second





                proc freq data=sashelp.prdsale;

                   table year;


    ods tagsets.msoffice2k_x close;

Posts: 8,641

Re: tagsets.msoffice2k_x


  TAGSETS.EXCELXP makes Spreadsheet Markup Language Office 2003 XML. TAGSETS.MSOFFICE2K_X makes Office 2000 "flavor" of HTML with embedded JavaScript controls. So they are 2 different ODS methods.

  They have DIFFERENT sub-options. To reveal the sub-options that are allowed with each, use options(doc='Help') and then look in the log. Just because a sub-option works for TAGSETS.EXCELXP does not guarantee that the sub-option will work for the other TAGSET destinations.

  So, once you figure out what sub-options are allowed, if you still have issues, you might clarify your question. I'm not sure what you want. If you want the sub-options with TAGSETS.EXCELXP, then why not just use that destination?


Occasional Contributor
Posts: 14

Re: tagsets.msoffice2k_x

Hi Cynthia,

Ah.... if Microsoft would be as stable as SAS....

There are some things, such as graphics produced by SAS, that export nicely in the MSOFFICE2K_X.

We present both tabular and graphic information to a variety of stakeholders.

Plus some formatting issues that are better for us in 2k_x.

So from the doc= 'HELP' it seems what I need is additional information about:

WORKSHEET_SOURCE :   Adds multiple worksheets per workbook by taking separates HTML files

The one example I found from the SAS website is Chevelle Parker and the tabs are broken out using the BY grouping of the output.

I want to be able to create tabs within the excel workbook containing information by at least each proc


know how to use whorksheet_source to place output into different tabs of the excel workbook.

Thank you,


Posts: 8,641

Re: tagsets.msoffice2k_x


  I have only experimented with this a little bit. But Microsoft has always treated HTML files differently from XML files. The original way that Microsoft implemented multi-sheet workbooks for HTML was that you had to have a separate HTML file for every sheet you wanted to make. All of those HTML files had to be in a separate, physical directory and you had to provide a set of linked HTML files that told Excel which HTML file went on sheet 1, sheet 2, sheet 3. So those are the rules that TAGSETS.MSOFFICE2K_X works within. TAGSETS.EXCELXP follows different rules (non-HTML rules). So that is the fundamental reason that suboptions built for one do not work for the other.

  So, back to your multi-sheet question with MSOFFICE2K_X: I believe that for WORKSHEET_SOURCE to work with MSOFFICE2K_X,  you need to create each HTML file as separate steps PRIOR to lumping them together in the multi-sheet workbook form of HTML. If you do this:

1) create proc1.html

2) create proc2.html

3) create proc3.html 3 separate steps then they are all sitting out someplace waiting to be gathered into the fold. Let's say they all live in c:\temp directory.

Then, you create your FINAL MSOFFICE2K_X file with WORKSHEET_SOURCE pointing to these 3 (already created) files. But the key is that the 3 files need to be created
-AHEAD- of time. With TAGSETS.EXCELXP, you create everything at the same time and use sheet_name and sheet_interval suboptions for controlling things. But that is in Microsoft-2003-XML-land, and you are in Microsoft-2000-HTML-land with MSOFFICE2K_X.

OK, given the existence of those 3 files in a location like c:\temp directory, then your final (after you made sure that all 3 files were created) step would be something like:

ods tagsets.msoffice2k_x path="c:\temp" file="alltogether.xls"


     options( worksheet_source="Wombat#c:\temp\proc1.html,




data _null_;

   file print;

   put _all_;



ods tagsets.msoffice2k_x close;

Where "Wombat", "Koala" and "Fred" are the 3 sheet names you want. Also, you must have previously run the template code to create TAGSETS.MSOFFICE2K_X in either a temporary or permanent item store.

Now, you may see something scary in the SAS Result Viewer, like _ERROR_=0 _N_=1 and I will admit, that I don't know why it's there, but I know that if I open the result file in Excel (ALLTOGETHER.XLS in my example), then I get 3 separate sheets -- one for each HTML page.

For better explanations or more help with your sub-options, you might consider looking at this web site ( or opening a track with Tech Support.


Occasional Contributor
Posts: 14

Re: tagsets.msoffice2k_x


This works fine,

Thank you for your response.

I hope others find the information usefull.


Post a Question
Discussion Stats
  • 4 replies
  • 2 in conversation