topic tagsets.msoffice2k_x in ODS and Base Reporting

tagsets.msoffice2k_x

KarenES — Fri, 28 Sep 2012 17:08:22 GMT

Please help...

code below using sas data set...

First, I get two unrecognized options in the Log,

v2.55

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"

index='yes'

sheet_name='Contents'

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

options( sheet_name="Second
sheet"

tabcolor="yellow"

pagebreaks="no"

embedded_titles="no"

embedded_footnotes="no");

proc freq data=sashelp.prdsale;

table year;

run;

ods tagsets.msoffice2k_x close;

Re: tagsets.msoffice2k_x

Cynthia_sas — Fri, 28 Sep 2012 18:27:10 GMT

Hi:

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?

cynthia

Re: tagsets.msoffice2k_x

KarenES — Fri, 28 Sep 2012 18:50:49 GMT

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

-OR-

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

Thank you,

Karen

Re: tagsets.msoffice2k_x

Cynthia_sas — Fri, 28 Sep 2012 23:23:37 GMT

Hi:

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 ...in 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"

style=statistical

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

Koala#c:\temp\proc2.html,

Fred#c:\temp\proc3.html");

data _null_;

file print;

put _all_;

run;

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 (http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html) or opening a track with Tech Support.

cynthia

Re: tagsets.msoffice2k_x

KarenES — Tue, 02 Oct 2012 20:43:15 GMT

Cynthia,

This works fine,

Thank you for your response.

I hope others find the information usefull.

Karen