BookmarkSubscribeRSS Feed
KarenES
Calcite | Level 5

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;

4 REPLIES 4
Cynthia_sas
SAS Super FREQ

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

KarenES
Calcite | Level 5

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

Cynthia_sas
SAS Super FREQ

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

KarenES
Calcite | Level 5

Cynthia,

This works fine,

Thank you for your response.

I hope others find the information usefull.

Karen

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2172 views
  • 0 likes
  • 2 in conversation