topic output to XML not opening in excel in ODS and Base Reporting

output to XML not opening in excel

KarenES — Thu, 24 Feb 2011 20:01:56 GMT

Oh help...
I am using SAS 9.1.3 and xlsx (MSoffice 2007).

I had a very nice little routine that would place my output into an excel spread sheet.
When I tried to replicate for a new project this month, I got an error when I tried to open the XML with excel.

The error I get when I go to open my ODS output is: Problem during load - Problem came up in the following areas during load: Workbook setting.
This file cannot be opened because of errors. Errors are listed in: C:\Documents and settings\ect..etc... .

The ODS output xml that opens in excel is very important to me.
Thank you in advance for any help provided,
Karen

HERE IS MY SAS CODE - tried it with the program that worked before, tried in my new program, and tried it in a sasuser data set. No mater which data set, the output would not open in excel.

data work.usethis ;
set sasuser.heart;
run;

ODS Escapechar = '~';
options nonumber /*supresses automatic page number at top of output*/
orientation=portrait /*sets the page orientation to either landscape or portrait - starting 9.2 we may change orientation within a document*/
topmargin = '.5in' bottommargin = '.5in' leftmargin = '.5in' rightmargin = '.5in'
FORMCHAR="|----|+|---+=|-/\<>*";

/*output |before and after| information to RTF for before recoding variables
- this way I can review as a document or in SAS OUTPUT window*/
ODS RTF FILE= 'h:\test Feb24.rtf'
startpage=no keepn contents=yes Style=theme;
ODS noproctitle;

ods tagsets.excelxp path='H:\' file='test24Feb.xml'
style=XLsansPrinter ;

ods tagsets.excelxp options
(contents = 'yes' /*creates a sheet for table of contents*/
sheet_name = 'contents' /*names the content sheet*/
EMBEDDED_FOOTNOTES = 'Feb 24, 2011' /*puts the quoted text - in this case date created - as a footnote*/
SHEET_INTERVAL = 'none' /*this controls where each work sheek is created - default is table*/
GRIDLINES = 'on'
doc='help');

proc freq data = usethis;
tables _all_;
run;

ods tagsets.excelxp close;
ODS _all_ Close;

ODS Listing;

Re: output to XML not opening in excel

Cynthia_sas — Fri, 25 Feb 2011 03:20:58 GMT

Hi:
Generally, when I run into that failure to open a workbook, it is for one of these reasons:
1) I need to update TAGSETS.EXCELXP
2) I have specified some combination of suboptions that generate invalid XML.

So I tried a simplified version of your code (with SASHELP.CLASS) and made a few changes/corrections/simplifications -- but I did not get a Workbook error and the XML file opened correctly in Excel -- I am using Excel 2010, SAS 9.2 and v 1.94 of TAGSETS.EXCELXP -- so you'll have to try the same code and see what you get with your versions of SAS, Excel and TAGSETS.EXCELXP.

(Or you may have to work with Tech Support, as they can test code on older versions of SAS.)

In my version of TAGSETS.EXCELXP, PRINT_FOOTER suboption takes a string and EMBEDDED_FOOTNOTES is only a YES/NO suboption. CONTENTS= with SHEET_INTERVAL='NONE' didn't make sense to me -- SHEET_INTERVAL='NONE' tells ODS to make one big table for ALL your PROC FREQ individual tables -- so essentially, the CONTENTS= will probably point to that one big table.

At any rate, with this simplifed code (I took out the ODS RTF step too), I do get output in Excel -- note that the CONTENTS= sheet is already named Contents and the SHEET_NAME='wombat' causes the separate sheets to be named wombat, wombat 2, wombat 3, etc -- so I don't see a name for sheet_name='contents'.

If you go into Print Preview mode, you will see the difference in placement of the SAS footnote and the text string in PRINT_FOOTERS suboption. Once you have working output, then I would go back and change 1 thing at a time -- change the style or change 1 and only 1 suboption, until you hit the combination that breaks. Then, if you think the combination of options should work, you'll have to open a track with Tech Support to see if they can 1) replicate your issue and 2) whether they agree that it's a bug or whether the XML creation is working as designed or 3) whether you are trying some combination of options that results in invalid XML.

cynthia
[pre]
data work.usethis ;
set sashelp.class;
run;

ODS noproctitle;

ods tagsets.excelxp file='c:\temp\test24Feb.xml' style=sasweb;

ods tagsets.excelxp options(embedded_footnotes='yes' sheet_interval='table'
contents='yes' sheet_name='wombat' doc='Help'
Print_footer='Print Footer: Feb 24, 2011');

title;
footnote 'My Footnote Statement: Feb 24, 2011';
proc freq data = usethis;
tables _all_;
run;

ODS _all_ Close;

ODS Listing;
[/pre]

Re: output to XML not opening in excel

KarenES — Fri, 25 Feb 2011 17:00:54 GMT

Thank you Cynthia.
It was updating the tagsets...
I made changes to my sasuser and updated all templates except that one...

That is a good type of error message to remember.

Karen S.

Re: output to XML not opening in excel

Gene_N — Wed, 09 Mar 2011 21:53:36 GMT

I'm having the same problem as Karen.
Using Excel XP tagset v1.116, Office 2007

%IF &TYPE_OP=EXCEL %THEN %DO;
%let RV=%sysfunc(appsrv_header(Content-type, application/vnd.ms-excel));
%let RV=%sysfunc(appsrv_header(Content-disposition,%str(attachment; filename=temp.xls)));
ODS _ALL_ CLOSE;
ODS TAGSETS.EXCELXP
FILE=_webout
OPTIONS(GRIDLINES='YES'
AUTOFILTER='NONE'
FROZEN_HEADERS='YES'
SHEET_INTERVAL='NONE'
EMBEDDED_TITLES='YES'
) style=sasweb;
%REPORT_PRINT;
%web_cnt;
ODS TAGSETS.EXCELXP close;
%END;

Re: output to XML not opening in excel

Gene_N — Wed, 09 Mar 2011 21:57:04 GMT

Forgot to mention...it is intermittent, works fine 95% of the time.

Re: output to XML not opening in excel

Cynthia_sas — Wed, 09 Mar 2011 23:17:53 GMT

Hi:
Are you doing this as a stored process or using SAS/IntrNet??? If the issue is intermittent, then I think your best bet for a solution is to work with Tech Support to figure out what is intermittently causing the Dispatcher program or stored process to fail.

cynthia