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".
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?
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:
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.
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.
Oh! And my first row is hidden!
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;
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
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;
Yeah, that is odd. 9.3 is the first time I've been disappointed like this by SAS.
If anyone has an alternative to excelxp and xml, that maybe works?
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
Can you try this?
It turns out that the "systemtitle__c__c" is already known as problematic.
That already came up in this thread. It "fixes" the error message in that it allows the output, but it causes other problems.
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.)
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: