topic Error opening excel files produced with ODS tagsets.excelxp in SAS Programming

Error opening excel files produced with ODS tagsets.excelxp

saras — Wed, 13 Aug 2014 16:53:54 GMT

Hello everyone,

I am trying to use ODS tagsets.excelxp in Sas V9.3 on a system that has Microsoft Excel 2010 installed.

Here is the simplified version my code:

ODS TAGSETS.EXCELXP file="C:\file.xls"

OPTIONS(EMBEDDED_TITLES='YES' EMBEDDED_FOOTNOTES='YES');

PROC TABULATE DATA=test MISSING;

CLASS PGM YEAR;

TABLE PGM, YEAR;

RUN;

ODS TAGSETS.EXCELXP CLOSE;

The problem:

After I run the code, an excel file is produced in the target location. If I double click to open, Excel does not open and instead I get this "Microsoft Excel has stopped working".

If i open the file from inside excel, it opens fine after I get a warning telling me the file is in a different format from that specified by the extension.

Once open, if i go file>saveas, the default 'save as type' is XML spreadsheet 2003. If change this to xls and save then the file opens fine when i simply double-click it.

Is there any way of getting around having this extra step?

I have tried instead tagsets.msoffice2k. I have tried putting ODS _all_ close before the bit of code. And I recently installed microsoft data connectivity components which sorted out my proc import issues with excel. However none of these have resolved this issue i have described.

Any suggestions would be appreciated!

Thank you!

Sarasvati

Re: Error opening excel files produced with ODS tagsets.excelxp

Quentin — Wed, 13 Aug 2014 17:11:49 GMT

Odd. Tagsets.excelxp does produce an xml file, but they've always opened fine for me in Excel (with that warning), except when there was an error in the XML. (and that never caused Excel to crash).

Can you open any XML files using Excel?

You might try changing the file extension to file="C:\file.xml" (That shouldn't fix it, but worth a shot).

Re: Error opening excel files produced with ODS tagsets.excelxp

Quentin — Wed, 13 Aug 2014 17:21:15 GMT

Attaching an xml file made with below code. THis opens okay for me. I'm running SAS 9.3 on Win 7 and Excel 2010

ODS TAGSETS.EXCELXP file="d:\junk\file.xls"

OPTIONS(EMBEDDED_TITLES='YES' EMBEDDED_FOOTNOTES='YES');

PROC TABULATE DATA=sashelp.class MISSING;

CLASS sex age;

TABLE sex, age;

RUN;

ODS  TAGSETS.EXCELXP CLOSE;

Re: Error opening excel files produced with ODS tagsets.excelxp

Reeza — Wed, 13 Aug 2014 17:27:09 GMT

If you have SAS 9.4 you can try ODS EXCEL - I don't so can't try it yet.

Otherwise, you may also want to look at adding a short VB script after that will automatically convert those files to native Excel files.

43496 - Convert files created using an ODS destination to native Excel files