topic tagsets.excelxp in ODS and Base Reporting

tagsets.excelxp

KevinC_ — Mon, 02 Aug 2010 22:09:22 GMT

Hello everyone,

I am using ODS tagsets.excelxp for the first time. I am just testing the code and writing the output to an .xls file on my desktop. The code runs fine on SAS; no errors. I can see the excel doc on the desktop with the .xls extension.

However, when I try to open the excel doc I get an error message saying "The file you are trying to open is in a different format than specified by the file extension". The file exists on the desktop and has the .xls extension. Does anyone have any idea why I am getting this error message?

Here is my code:

ods tagsets.excelxp file = "&out.refi_analysis_test.xls";

proc sort data = sashelp.class out = class;
by age;
run;

proc print data = class;
by age;
run;

ods tagsets.excelxp close;

Thank you for any input!!

Re: tagsets.excelxp

Cynthia_sas — Mon, 02 Aug 2010 22:21:45 GMT

Technically, when you call an XML file (what TAGSETS.EXCELXP creates) with a .XLS extension, that error message is correct, the file you are trying to open, '[filename]', is in a different format than the one specified by the file extension.

You just have to respond YES to the popup warning. Or else name the file .XML and do a File --> Open from Excel instead of launching Excel with a double click.
http://support.sas.com/kb/35/581.html

cynthia

Re: tagsets.excelxp

KevinC_ — Tue, 03 Aug 2010 13:49:21 GMT

Thank you, Cynthia.
I clicked "Yes" and tried to open the excel doc and was prompted "Unable to Read File". Then I changed the file extension to .xml and reran the code. When I opend the xml doc this is what's in it:

-
-
X000000
X000000
2010-08-03T09:38:15
2010-08-03T09:38:15
SAS Institute Inc. http://www.sas.com
9.01.01M3P07282004

Does anyone have any suggestion?

Thanks again.

Re: tagsets.excelxp

Cynthia_sas — Tue, 03 Aug 2010 14:47:10 GMT

Hi:
At this point, I would recommend that you work with Tech Support.

In all instances of running TAGSETS.EXCELXP and using Excel 2007 to open the file I am able to have Excel open the file when I click YES in response to that message -- using your code. When Excel opens, I have 6 worksheets, one for each AGE.

The XML that you see in the file is CORRECT -- TAGSETS.EXCELXP creates Spreadsheet Markup Language XML as defined by Microsoft for Office 2003. Excel 2007 should open the TAGSETS.EXCELXP XML file (even with a .XML extension). Did you open the file in the browser, in Notepad or with a lower version of Excel (such as Excel 2000)???

Usually, the only time I have issues with the Excel and the XML created by TAGSETS.EXCELXP is one of these circumstances:
1) try to name the file .XLSX
OR
2) perform something in the SAS code that causes the XML to be invalid (like using embedded_title with a version of the tagset that didn't support embedded_titles) -- which doesn't seem to be the case in your code
OR
3) Use EG and try to open the XML file with EG -- the file is OK -- but Enterprise Guide only "wants' to open SASReport XML -- not Spreadsheet XML
OR
4) Use SAS 9.1.3 and have an older version of the tagset template (very similar to #2)
OR
5) Use Office 97 or Office 2000 and try to open the file (since Spreadsheet XML was designed for Office 2002/2003, Excel 97 and 2000 will not open the XML file into a spreadsheet.

To open a track with Tech Support, fill out the form at this link:
http://support.sas.com/ctx/supportform/createForm

cynthia

Re: tagsets.excelxp

KevinC_ — Tue, 03 Aug 2010 15:06:40 GMT

Hi Cynthia,

Thank you so much for your input and suggestions! I have downloaded updated tagsets from http://support.sas.com/rnd/base/ods/odsmarkup/index.html

Apparently this was never installed on my PC. After I downloaded the tagsets the code worked beautifully.

Thank you again for your help!

Re: tagsets.excelxp

KevinC_ — Tue, 03 Aug 2010 15:23:14 GMT

Actually, one more question. Can we control the font size of the title or the content of the .xls ? I tried to look it up but couldn't seem to find any example.. Thanks.