BookmarkSubscribeRSS Feed
wcpatton
Calcite | Level 5

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

18 REPLIES 18
AncaTilea
Pyrite | Level 9

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?

Cynthia_sas
SAS Super FREQ

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.

wcpatton
Calcite | Level 5

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.

wcpatton
Calcite | Level 5

Oh!  And my first row is hidden!

wcpatton
Calcite | Level 5

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;

Cynthia_sas
SAS Super FREQ

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

AncaTilea
Pyrite | Level 9

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;

wcpatton
Calcite | Level 5

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

wcpatton
Calcite | Level 5

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

AncaTilea
Pyrite | Level 9

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

AncaTilea
Pyrite | Level 9

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.

wcpatton
Calcite | Level 5

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

OS2Rules
Obsidian | Level 7

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.)

Cynthia_sas
SAS Super FREQ

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;

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
  • 18 replies
  • 3229 views
  • 7 likes
  • 4 in conversation