The SAS Output Delivery System and reporting techniques

ExcelXP Tagset Parsing Error

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

ExcelXP Tagset Parsing Error

Hello All:

I have been trying to correct an issue I am having with the ExcelXP tagset (which I am new to).  I have searched all over but have found no reference to a similar problem.  First, the hardware and software context.

I am running SAS 9.2 TS Level 2MO on a Windows machine with XP Professional version 5.1.2600 Service Pack 3.  The version of Excel I have is 2003 (11.8346.8341). The ExcelXP tagset I am using is version 1.127 (although I have the same exact problems with 1.116 and 1.37).

The code I am running just as a test is very simple (and copied right from http://support.sas.com/rnd/base/ods/odsmarkup/p236-31.pdf):

ods tagsets.excelxp file = "c:\test\ExcelXP.xls";

     proc reg data = sashelp.class;

     model Weight = Height Age;

     run;

     quit;

ods _all_ close;

The XML file I get is malformed.  The only two closing tags that use a "?" are rendered instead as a "]" (closed bracket).

<?xml version="1.0" encoding="windows-1252"]

<?mso-application progid="Excel.Sheet"]

If I go in and change the output file by hand and replace the "]" with "?>" I am then able to open the file in Excel, a web-browser or an XML editor.

I thought the problem might have been in the tagset code but it looks like I would expect it to - it places a "?>" closing tag.

put '<?xml version = "1.0"';

putq " encoding=" encoding;

put "?>" CR CR;

putl '<?mso-application progid="Excel.Sheet"?>';

Funny thing is that the WML tagset has an identical line for the "xml version" as the excelxp tagset, and when I run that one, it generates the proper XML.  None of the ExcelXP tagsets, however, generate the proper close tag.

In addition, the log shows a parsing error in a regex expression  (see attached log).  The error it points to seems to involve the replacement of a paren with a curly brace.  I checked the tagset and it does not have a curly brace but a paren.

If anyone has any suggestions, I would be most appreciative.

Gene

Attachment

Accepted Solutions
Solution
‎06-21-2012 12:08 PM
Occasional Contributor
Posts: 6

Re: ExcelXP Tagset Parsing Error

Well

Bari at SAS Tech Support gets credit for solving this one.  I needed to set the following option:

options nocharchode;

Problem solved!  Now to figure out how to give him the correct answer green star!

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,743

Re: ExcelXP Tagset Parsing Error

Hi,

  Using SAS 9.3 and the following (older) version of TAGSETS.EXCELXP, I do NOT get malformed XML:

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.122, 01/04/2011)

    .

  I note from your LOG that you are using the following version:

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.127, 09/26/2011)

  Have you tried a different version of TAGSETS.EXCELXP? Given the number of error messages in the log, it looks like something happened that possibly "broke" this version of your TAGSETS.EXCELXP template. I've never seen messages like that from any of the versions of TAGSETS.EXCELXP that I've used. I'd recommend working with Tech Support to figure out whether it's the V1.127 version or something else that's going on.

cynthia

Occasional Contributor
Posts: 6

Re: ExcelXP Tagset Parsing Error

Cynthia

Thanks for helping me out with this.  I tried with several different versions of the tagset which I loaded form the SAS site.  I got the same error with each.  I thought about it being an Excel problem but I am inclined to think not because the problem pops up when SAS is applying the tagset to the output.  The bad XML is there whether I open up the output in Excel, SAS, Notepad or XML Notepad, and whether I give the file an XLS or XML extension.

The "something broke" comment feels right to me.  It almost looks like the process by which the tags are applied is expecting the ?> to be escaped and when it is not it interprets it as a close bracket. I tried several escape characters and got nothing but the sequence with the bracket witht he escape character preceeding it.  I am going to try the old standby, cold reboot and see and reload the tagsets anew and see if that works.  I wonder also if there is a hot fix that we have not applied.  We will be moving to Windows 7 and SAS 9.3 in the fall, so perhaps if nothing else that will take care of it.  I will keep tinkering until then.

Thanks

Gene

Super Contributor
Posts: 273

Re: ExcelXP Tagset Parsing Error

Tested

under 9.3.0 with tagsets 127 i encounter no problem of opening with Excel 2010

8602  ods graphics off;

8603  ods tagsets.excelxp file = "d:\temp\ExcelXP.xls";

NOTE: Writing TAGSETS.EXCELXP Body file: d:\temp\ExcelXP.xls

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.127, 09/26/2011). Add

options(doc='help') to the ods statement for more information.

8604       proc reg data = sashelp.class;

8605       model Weight = Height Age;

8606       run;

8607       quit;

NOTE: PROCEDURE REG used (Total process time):

      real time           0.05 secondes

      cpu time            0.06 secondes

8608  ods _all_ close;

8609  ods graphics on;

=================================================

Under 9.2.3 with tagsets 127   no problem at all

8589  ods tagsets.excelxp file = "d:\temp\ExcelXP923.xls";

NOTE: Ecriture du corps du fichier TAGSETS.EXCELXP : d:\temp\ExcelXP923.xls

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.127, 09/26/2011).

Add options(doc='help') to the ods statement for more information.

8590       proc reg data = sashelp.class;

8591       model Weight = Height Age;

8592       run;

8593 quit;

NOTE: Procédure REG a utilisé (Durée totale du traitement) :

temps réel    1.03 secondes
temps UC      0.26 secondes

Conclusion   update your version of SAS?

here is the tpl file

Attachment
SAS Super FREQ
Posts: 8,743

Re: ExcelXP Tagset Parsing Error

Good to know that Version 1.127 is OK. I used Excel 2010 to open the file and Andre was also using 2010, but the original question indicated Excel 2003 was being used. So, this is another reason to check with Tech Support.

cynthia

Occasional Contributor
Posts: 6

Re: ExcelXP Tagset Parsing Error

Andre

Thank you.  See my reply to Cynthia.   We will be upgrading in the fall.  That is my last fallback position on this.

Best,

Gene

Solution
‎06-21-2012 12:08 PM
Occasional Contributor
Posts: 6

Re: ExcelXP Tagset Parsing Error

Well

Bari at SAS Tech Support gets credit for solving this one.  I needed to set the following option:

options nocharchode;

Problem solved!  Now to figure out how to give him the correct answer green star!

Occasional Learner
Posts: 1

Re: ExcelXP Tagset Parsing Error

geneD,

Do you mean options nocharcode;

rather than nocharchode;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1318 views
  • 8 likes
  • 4 in conversation