topic Re: ExcelXP tagset update in Microsoft Integration with SAS

ExcelXP tagset update

deleted_user — Wed, 05 Dec 2007 13:41:50 GMT

Is the latest ExcelXp tagset included in SAS service pack 4???

Re: ExcelXP tagset update

Cynthia_sas — Wed, 05 Dec 2007 16:00:41 GMT

Hi:
This is what I had after I installed Service Pack 4:
[pre]
NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.28, 08/29/05)
[/pre]

Since the latest tagset is from June 07, you're better off downloading the newest tagset after you install Service Pack 4. Running this program (below) will not only show you the date, but will also show you the features/options included in the tagset (they'll be in the SAS Log).

cynthia

[pre]
ods path sashelp.tmplmst(read);

ods tagsets.excelxp file='c:\temp\yadayada.xls' options(doc='Help');

proc print data=sashelp.class(obs=1);
run;

ods tagsets.excelxp close;
[/pre]

Re: ExcelXP tagset update

deleted_user — Tue, 03 Mar 2009 17:29:58 GMT

Hi Cynthia,

I have SAS 9.1.3 SP4 and Excel 2000 in my computer.

I ran the same code in my SAS, but the generated Excel file looks like an XML file. I can't figure out why.

Here is the part of the Excel file:

xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">

lil602
lil602
2009-03-03T12:16:39
2009-03-03T12:16:39
SAS Institute Inc. http://www.sas.com
9.01.01M3P02022006

Re: ExcelXP tagset update

Cynthia_sas — Tue, 03 Mar 2009 18:45:16 GMT

Hi:
TAGSETS.EXCELXP does create XML -- it is Spreadsheet Markup Language XML that conforms to the Excel 2003 specification for an XML description of a Workbook and worksheets within that workbook.

For Excel to be able to render the XML that ODS creates, you have to use either Excel 2002, 2003 or higher. I believe that if you open the XML with Excel 2000, then you will see the unrendered XML -- which sounds like what's happening.

If you only have Office 2000/Excel 2000 available to you, then your choices are
1) use ODS MSOFFICE2K and create an HTML file (which will only give you 1 worksheet per workbook by default) or
2) upgrade your Office/Excel to at least 2002 or 2003.

cynthia

Re: ExcelXP tagset update

deleted_user — Tue, 03 Mar 2009 20:43:23 GMT

Hi Cynthia,

Thank you for your quick response. I thought Excel 2000 can read XML because I saw this in a paper (http://support.sas.com/rnd/base/ods/odsmarkup/p236-31.pdf, please see page 11). I am going to try ODS MSOFFICE2K.

thanks,
Lisa

Re: ExcelXP tagset update

Cynthia_sas — Wed, 04 Mar 2009 00:00:13 GMT

Hi:
Perhaps that's a typo in the paper or perhaps Microsoft issued a plug-in to make Office 2000 read Spreadsheet ML. I don't know which one applies. I know that when I tried (on a different computer) to open Spreadsheet ML files in Excel 2000, I only saw the unrendered XML. Now that I have Office 2007 on that computer, I can no longer do a test. You might want to check with Tech Support for sure.

At this Microsoft web site, however, they are fairly clear that Spreadsheet Markup Language goes with Excel 2003:
http://msdn.microsoft.com/en-us/library/bb226687(office.11).aspx
...and then, this page refers to Excel 2002 and Spreadsheet ML:
http://msdn.microsoft.com/en-us/library/aa140062.aspx

But I could not find any references to Excel 2000 being used with Spreadsheet Markup Language XML on the Microsoft site. In this blog, by Brian Jones (of Microsoft), I did find this Intro to Spreadsheet ML:
http://technet.microsoft.com/en-us/magazine/2006.01.blogtales.aspx
in which he says:
"To get you started, I’ll build a sample in XML that will illustrate how it all works. As you follow along, you can use Office XP or Office 2003 for this example since both support SpreadsheetML in their versions of Excel." (He doesn't refer to Office 2000. I thought Office XP was Office 2002.)

cynthia