BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
geneD
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
geneD
Calcite | Level 5

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

7 REPLIES 7
Cynthia_sas
SAS Super FREQ

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

geneD
Calcite | Level 5

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

Andre
Obsidian | Level 7

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

Cynthia_sas
SAS Super FREQ

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

geneD
Calcite | Level 5

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

geneD
Calcite | Level 5

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!

troy_le_cba
Calcite | Level 5

geneD,

Do you mean options nocharcode;

rather than nocharchode;

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
  • 7 replies
  • 2821 views
  • 8 likes
  • 4 in conversation