BookmarkSubscribeRSS Feed
saras
Fluorite | Level 6

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

3 REPLIES 3
Quentin
Super User

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

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Quentin
Super User

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;
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Reeza
Super User

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

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
  • 3 replies
  • 2615 views
  • 0 likes
  • 3 in conversation