09-28-2012 01:08 PM
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
proc print data=sashelp.prdsale(obs=50);
proc freq data=sashelp.prdsale;
ods tagsets.msoffice2k_x close;
09-28-2012 02:27 PM
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?
09-28-2012 02:50 PM
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.
09-28-2012 07:23 PM
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"
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.