The SAS Output Delivery System and reporting techniques

Tagsets.excelxp Error (9.3)

Reply
Contributor
Posts: 61

Tagsets.excelxp Error (9.3)

Producing a tabulate output with tagsets.excelxp into .XML format.  Worked fine under 9.2, but with 9.3 I get an excel error "Problems During Load.  Problems came up in the following areas during load: Table".

Anyone know how I can restore this to working order?

If anyone with SAS is reading this, I'm not too pleased that I have to keep rewriting output codes due to this "upgrade".

Super Contributor
Posts: 543

Re: Tagsets.excelxp Error (9.3)

Could you try to delete any existing .xml file that you've created (or make a copy with a different name) then try to run the code again?

SAS Super FREQ
Posts: 8,819

Re: Tagsets.excelxp Error (9.3)

Hi:

  Without seeing code, it's hard to guess what you're running into. However, searching on this string

tabulate excelxp 9.3

on http://support.sas.com (search box upper right side of page) yielded this as the first hit:

45306 - Specifying a page dimension with PROC TABULATE and the ExcelXP tagset generates an invalid X...

  So if you are using a page dimension, then perhaps the workaround in the note will help you. Otherwise, when you note differences between versions of SAS (code ran in one version that does not run in the new version), that is the time to open a track with Tech Support. When Tech Support finds a difference like this, (and they have many previous versions of SAS to test with, so they can confirm exactly when and where the problem started), then they can open a defect with the developers for a fix.

  To open a track with Tech Support, fill out the form at this link:

http://support.sas.com/ctx/supportform/createForm

cynthia

ps another possible workaround if you are using a PAGE dimension is to see whether you can rework your table to use BY instead of PAGE. This will work best for single variables and if you do not have ALL in the page dimension.

Contributor
Posts: 61

Re: Tagsets.excelxp Error (9.3)

Thanks Cynthia.  That gets me in the right direction, but now my spreadsheet style is very broken.  No longer fits the pages and my column\row widths and lengths are all wrong.

Contributor
Posts: 61

Re: Tagsets.excelxp Error (9.3)

Oh!  And my first row is hidden!

Contributor
Posts: 61

Re: Tagsets.excelxp Error (9.3)

Here's a sample code that works with my coworkers 9.2, but gives excel errors on 9.3


ods tagsets.excelxp  file="c:\junk.xml"

    style=sasweb

  options (SHEET_INTERVAL="NONE"

  absolute_column_width = '14,6.5,6.5,6.5,6.5,6.5,6.5,6.5,6.5,6.5,6.5,6.5,6.5,6.5,6.5'

  embedded_titles='no'

  PAGES_FITWIDTH="1" FitToPages="YES"

  orientation='Landscape');

ods tagsets.excelxp options (Sheet_Name = "Portfolio");

title'Delinquency Trends by Portfolio';*/;

proc tabulate data=sashelp.class missing;

class name sex age;

var height weight;

   tables  (name='')*(

   height = "height" *mean='' *f=comma24.0

   weight = "weight" *mean='' *f=comma24.0

),(sex='') ,(age='')

/

box = '' row=float;

run;

ods _all_ close;title;

SAS Super FREQ
Posts: 8,819

Re: Tagsets.excelxp Error (9.3)

Ah, well, ignore the BY group idea, that won't work in this case. It's an interesting set of tables. I still think your best bet is to work with Tech Support.

cynthia

Super Contributor
Posts: 543

Re: Tagsets.excelxp Error (9.3)

I find it interesting that the code works (probably not creating the table you want) when you remove one comma:

....

weight = "weight" *mean='' *f=comma24.0

) ,   (sex='') ,(age='')....

Without the comma, the code runs.

With the comma, it doesn't.


ods tagsets.excelxp  file="c:\junk.xml"

    style=sasweb

  options (SHEET_INTERVAL="NONE"

  absolute_column_width = '14,6.5,6.5,6.5,6.5,6.5,6.5,6.5,6.5,6.5,6.5,6.5,6.5,6.5,6.5'

  embedded_titles='no'

  PAGES_FITWIDTH="1" FitToPages="YES"

  orientation='Landscape');

ods tagsets.excelxp options (Sheet_Name = "Portfolio");

title'Delinquency Trends by Portfolio';*/;

proc tabulate data=sashelp.class missing;

class name sex age;

var height weight;

   tables  (name='')*(

   height = "height" *mean='' *f=comma24.0

   weight = "weight" *mean='' *f=comma24.0

) (sex='') ,(age='')

/

box = '' row=float;

run;

ods _all_ close;title;

Contributor
Posts: 61

Re: Tagsets.excelxp Error (9.3)

Yeah, that is odd.  9.3 is the first time I've been disappointed like this by SAS.

Contributor
Posts: 61

Re: Tagsets.excelxp Error (9.3)

If anyone has an alternative to excelxp and xml, that maybe works?

Super Contributor
Posts: 543

Re: Tagsets.excelxp Error (9.3)

So,

the problem with the xml that you are creating is that it gets a "bad value" specifically it gets "systemtitle__c__c" and it doesn't know what to do with it

If you look at this comment

http://stackoverflow.com/questions/8833929/error-in-excel-file

in their case they got value 5...

well in your case you get value systemtitle__c__c

...

I google more

Super Contributor
Posts: 543

Re: Tagsets.excelxp Error (9.3)

Can you try this?

45306 - Specifying a page dimension with PROC TABULATE and the ExcelXP tagset generates an invalid X...

It turns out that the "systemtitle__c__c" is already known as problematic.

Contributor
Posts: 61

Re: Tagsets.excelxp Error (9.3)

That already came up in this thread.  It "fixes" the error message in that it allows the output, but it causes other problems.

Super Contributor
Posts: 358

Re: Tagsets.excelxp Error (9.3)

Might be a stupid question, but which version of the tagset are you running?

I remember getting similar errors (something about systemtitle__5) and when I changed to use a newer

tagset the error went away,

(We have limited access to the production environment so the tagsets don't get changed there often.)

SAS Super FREQ
Posts: 8,819

Re: Tagsets.excelxp Error (9.3)

Hi:

  Running your code on sashelp.class didn't give me a good idea of what you ultimately want to have, since each "NAME"  produces only a 1-celled table for either height or weight. And, since you have sheet_interval="none", it seems that you do NOT want a separate sheet for each table. So, have you tried simple ODS MSOFFICE2K or even ODS MSOFFICE2K_X (Base SAS: The MSOffice2K_x Tagset Adds Options to the MSOffice2K Tagset) with an xls file extension to fool the windows registry?  (MSOFFICE2K and MSOFFICE2K_X both make HTML files, but ODS TAGSETS.MSOFFICE2K_X has more suboptions for controlling the output in Excel.) However, for what you're doing, using HTML might work as an alternative, especially since you have SHEET_INTERVAL="NONE" which is the default way that HTML output is created when you have multiple tables.

  Otherwise, it's hard to figure out whether it might be possible to replicate your report using PROC REPORT or possibly DATA step. Can you explain a bit more about what your "real" data looks like?

cynthia

** if you do not have tagsets.msoffice2k_x on your system, may need to get  it from support web site;

 

ods tagsets.msoffice2k_x file='c:\temp\mso2k_x.xls' style=normal

     options(doc='Help' orientation='landscape'

             fittopage='yes' print_header="Delinquency Trends by Portfolio");

   

proc tabulate data=sashelp.class missing;

  class name sex age;

  var height weight;

  tables  (name='')*(height = "height" *mean='' *f=comma24.0

                     weight = "weight" *mean='' *f=comma24.0),

          (sex='') ,

          (age='')/box = '' row=float;

run;

 

ods _all_ close;

title;

Ask a Question
Discussion stats
  • 18 replies
  • 1821 views
  • 7 likes
  • 4 in conversation