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
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!
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
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
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
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
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
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!
geneD,
Do you mean options nocharcode;
rather than nocharchode;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.