topic Re: Issue with msoffice2k_x unrecognized options in ODS and Base Reporting

Issue with msoffice2k_x unrecognized options

AlexanderSemel — Thu, 15 Nov 2012 19:37:55 GMT

Hi,

I am trying to use the msoffice2k_x tagset and imbed images and fit tables to one legal size page.

I am using options that are shown in the info on this tagset (http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html#Display)

and should be valid based on the syntax of the tagset code.

Also in the end I would like to center the results in the page (which i can do with excelxp tagset, but want image that is why trying msoffice_2k_x).

***** tried papersize 2 ways (neither is working);

%let psize2 = legal;

Options papersize=&psize2.;

****** tagest for excel for rp9 first sheet;

ods tagsets.MSOffice2K_x STYLE=Styles.smallerfont file="&cmp._Region_Breakdown.xls"

options(sheet_name="&rgn1. & &rgn2."

gridlines="yes"

papersize="legal"

fittopage="yes"

image_path="../../../../../lib/b_end/logo.gif"

orientation="portrait");

/* text from creating tagset

set $valid_options["FITTOPAGE"]

"Modifies the page settup fit to page option";

set $valid_options["LEADING_ZERO"]

"retains leading zeroes";

set $valid_options["ROTATE_HEADERS"]

"Provides rotation values for headers. Works in cunjunction with the HEIGHT= option";

set $valid_options["HEIGHT"] "Modifies the height of the headers";

set $valid_options["ZOOM"] "Applies zoom to each table individually by specifying zoom value separated by a comma";

set $valid_options["OPEN_MACRO"] " Adds a macro which executes when the worksheet loads ";

set $valid_options["CLOSE_MACRO"] " Adds a macro which executes when the worksheet closed";

set $valid_options["PANELCOLS"] "Panels tables and graphs";

set $valid_options["PANEL_SPACE"] "Adds the number of blanks cells to add between tables or graphs";

set $valid_options["IMAGE_PATH"] "Path to image or logo";

****

define event check_valid_options;

break /if ^$options;

iterate $options;

do /while _name_;

do /if ^$valid_options[_name_];

putlog "Unrecognized option: " _name_;

done;

next $options;

done;

*** from my log;

NOTE: Writing TAGSETS.MSOFFICE2K_X Body file: BD1211B5_Region_Breakdown.xls

v1.0

Unrecognized option: FITTOPAGE

Unrecognized option: IMAGE_PATH

Re: Issue with msoffice2k_x unrecognized options

Cynthia_sas — Thu, 15 Nov 2012 19:45:32 GMT

Hi:

If you find that documented sub-options do not work AND you are sure that you are using the newest version of the MSOFFICE2K_X destination tagset template, then your best resource for help is to open a track with Tech Support. If they determine that there is a problem with the tagset template behavior, then they will work with the developer to fix the problem. Otherwise, if this is a valid bug that goes unreported, there will be no fix for the problem.

cynthia

Re: Issue with msoffice2k_x unrecognized options

Reeza — Thu, 15 Nov 2012 19:50:33 GMT

I used the latest template and got an error with the sample but for pagebreak instead

1158	ods tagsets.msoffice2k_x file="c:\temp\worksheetoption.xls" style=normal
1159	options( zoom="70"
1160	fittopage="yes"
1161	sheet_name="New sheet"
1162	tabcolor="yellow"
1163	pagebreaks="no"
1164	embedded_titles="no"
1165	embedded_footnotes="no" );

NOTE: Writing TAGSETS.MSOFFICE2K_X Body file: c:\temp\worksheetoption.xls

v2.55

Unrecognized option: PAGEBREAKS

1166

1167

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

NOTE: There were 50 observations read from the data set SASHELP.PRDSALE.

NOTE: PROCEDURE PRINT used (Total process time):

	real time	0.04 seconds
	cpu time	0.04 seconds

Re: Issue with msoffice2k_x unrecognized options

Cynthia_sas — Thu, 15 Nov 2012 20:33:53 GMT

The documented spelling of that option, in my LOG (when I use doc='help') is PAGE_BREAK (note the underscore and no S).

cynthia

Re: Issue with msoffice2k_x unrecognized options

Reeza — Thu, 15 Nov 2012 23:03:03 GMT

I copied and pasted that from the SAS examples, so then the webpage has an error.

http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html

Re: Issue with msoffice2k_x unrecognized options

Cynthia_sas — Sat, 17 Nov 2012 19:40:30 GMT

Interesting. When I look at the site for MSOFFICE2K_X (drilling to pagebreak topic from the above site), this is what I see:

http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html#pagebreaks

ods tagsets.msoffice2k_x path="c:\temp\trash\test" file="pagebreak.xls"

options(embedded_titles="no"

pagebreak_row="20,30,40,90");

proc print data=sashelp.orsales;

run;

ods tagsets.msoffice2k_x close;

When I try a suboption of PAGEBREAKS, with MSOFFICE2K_X, I get the "unrecognized" warning:

45 ods tagsets.msoffice2k_x path="c:\temp\"

46 file="pb_wrong.xls"

47 options(pagebreaks='yes');

NOTE: Writing TAGSETS.MSOFFICE2K_X Body file: pb_wrong.xls

v2.1

Unrecognized option: PAGEBREAKS

49 proc print data=sashelp.class;

50 run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.10 seconds

cpu time 0.01 seconds

53 ods tagsets.msoffice2k_x close

The only option coded in the example on the web site is PAGEBREAK_ROW as shown above; although, when I do a doc='Help' suboption, these are the two pagebreak related suboptions for MSOFFICE2K_X:

NOTE: Writing TAGSETS.MSOFFICE2K_X Body file: pagebreak.xls
v2.1
==============================================================================
Short descriptions of the supported options
==============================================================================
Name : Description

... more suboptions ...

PAGEBREAK :
Provides the ability to modify pagebreak

PAGEBREAK_ROW :
Sets the rows break on

However, I do note that TAGSETS.EXCELXP does have a PAGEBREAKS suboption -- the suboptions are not interchangeable between tagsets, at least in my versions.

NOTE: Writing TAGSETS.EXCELXP Body file: c:\temp\xp_opt.xls
=============================================================================

==============================================================================

Long descriptions of the supported options
... more suboptions ....

PageBreaks:   Default Value 'No'
     Values: yes, no, on, off.
     If set to 'yes' page breaks will be inserted into the stylesheet. The
     pagebreak style element will be used to define what that pagebreak looks
     like. A sample style definition looks like this.

                 style pagebreak /
                     cellheight=8
                     foreground=black
                     tagattr="HorzStripe";

It is not necessary to have a style element. In it's absence a blank row
will be inserted.

==============================================================================
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.94, 09/09/12). Add
options(doc='help') to the ods statement for more information.

The bottom line is that I recommend using doc='Help' in your first run of any TAGSET destination after you update it to be sure that you understand the correct suboptions that can be specified for each destination.

cynthia