topic Re: EXCELXP and Special Characters in ODS and Base Reporting

EXCELXP and Special Characters

OS2Rules — Wed, 16 Feb 2011 15:25:40 GMT

Hi All:

I've got a big problem and I'm just hoping someone can help.

In a nutshell - we have some data that we are reporting with EXCELXP and it stopped working. The data now has special characters (french with accents) and the XML generated has these characters in it (when I look at it with an editor). Problem is that EXCEL will not read the file and it I try to look at it with a browser (Explorer) it only loads the XML up to the character and stops.

Some background - we recently changed from 8.2 to 9.1 on our mainframe (z/OS) where we read data and download SAS data. I believe that the encoding or translate tables have changed in the new version. Problem is - we don't know what they were in 8.2 because the options for ENCODE= or LOCALE= are defaulted and do not display any values.

I think that previously the special characters were translated to something that EXCELXP could handle and they are not now. I would like to use the special characters since they are actually correct now (if I can). It seems that the download is correct but EXCELXP can't handle the new characters.

In 9.1 we are using the 'open_ed-1047' encoding and the local is 'en_US' (these are the 'new' defaults).

Thanks in advance.

Re: EXCELXP and Special Characters

Cynthia_sas — Wed, 16 Feb 2011 16:13:29 GMT

Hi:
My Language Control settings are:
[pre]
58 proc options GROUP=LANGUAGECONTROL;
59 run;

SAS (r) Proprietary Software Release 9.2 TS2M3

DATESTYLE=MDY Identify sequence of month, day and year when ANYDATE informat data is
ambiguous
DFLANG=ENGLISH Language for EURDF date/time formats and informats
NOLOCALELANGCHG Do not change the language of SAS message text in ODS output when the LOCALE
option is specified
PAPERSIZE=LETTER Size of paper to print on
RSASIOTRANSERROR Display a transcoding error when illegal data values for a remote application
TRANTAB=(lat1lat1,lat1lat1,wlt1_ucs,wlt1_lcs,wlt1_ccl,,,)
Names of translate tables
NODBCS Do not process double byte character sets
DBCSLANG=NONE Specifies the double-byte character set (DBCS) language to use
DBCSTYPE=NONE Specifies a double-byte character set (DBCS) encoding method
ENCODING=WLATIN1 Specifies default encoding for internal processing of data.
LOCALE=EN_US Specifies the current locale for the SAS session.
NONLSCOMPATMODE Uses the user specified encoding to process character data

[/pre]

And with the program below, I have no problem opening the output created with TAGSETS.EXCELXP, using Excel 2010.

Have you looked at this doc on National Language Support:
http://support.sas.com/documentation/cdl/en/nlsref/61893/HTML/default/viewer.htm#nls-overview.htm

I don't think the problem is with TAGSETS.EXCELXP, you might want to open a track with Tech Support on this issue to see if they can help you get to the right settings.

cynthia
[pre]
data phrases;
length with_acc $12 means_with $20 no_acc $12 means_no $20;
infile datalines dlm=',' dsd;
input with_acc $ means_with $ no_acc $ means_no $;
return;
datalines;
"çà et là", "hither and thither", "ca et la","it and the"
"où","where","ou","or"
"dès","since","des","some"
"ès","in","es","are"
;
run;

ods listing;
proc options GROUP=LANGUAGECONTROL;
run;
ods listing close;
title; footnote;

ods html file='c:\temp\output\grave_accent.html' style=sasweb;
ods tagsets.excelxp file='c:\temp\output\grave_accent.xls' style=sasweb;
proc print data=phrases;
run;
ods _all_ close;
[/pre]

Re: EXCELXP and Special Characters

OS2Rules — Wed, 16 Feb 2011 18:24:35 GMT

Cynthia:

Thanks for the prompt answer - but it looks like the problem is deeper that I first thought.

My problem seems to occur on record 43,449 of my input file - this being the first record that has french characters. However - if I exclude the first 40,000 records from the file the Excel file will load without errors!

Just for fun I have downloaded the latest tagset and I am using that.

I can generate a valid XML file with my source data is reduced (ie: there are less records), but whe I run my full dataset into ExcelXP the results are unusable. I'm stumped - it looks like there is something in my source data that is causing the tagset to hiccup.

I am running tests with various subsets of my data right now to see if I can localize where the error occurs. It's too bad that we only get a useless cryptic error when the table fails to load in Excel.

I will keep you up to date if I can find out what is causing this.

Re: EXCELXP and Special Characters

Cynthia_sas — Wed, 16 Feb 2011 18:40:19 GMT

Hi:
I really recommend working with Tech Support on this issue. They may have some debugging tips and ways to get at the cryptic log file that Excel creates when a table fails to load.

cynthia

Re: EXCELXP and Special Characters

OS2Rules — Wed, 16 Feb 2011 19:15:22 GMT

Cynthia:

Already have a track open.

Thanks again.

I'll let you know what the 'fix' is.

Re: EXCELXP and Special Characters

Ksharp — Mon, 21 Feb 2011 10:26:09 GMT

Hi.
I also meet this problem ,but it is when copy tables from DB2 to SAS.
This problem is often occurred because variables(i.e. columns) have some problem such as too long or zero length.

Do you try to only keep variables you need , drop else variables you do not need.
Or just keep only one good variable to see whether it is success to import datasets.

Ksharp

Re: EXCELXP and Special Characters

Squashman — Fri, 09 Jan 2015 18:11:44 GMT

OS2Rules,

Did you ever get this resolved?

Re: EXCELXP and Special Characters

OS2Rules — Fri, 09 Jan 2015 19:46:55 GMT

Hi:

We must have but for the life of me I can't figure what we did....

If I remember (or figure it out again), I'll let you know.

Re: EXCELXP and Special Characters

Squashman — Mon, 12 Jan 2015 16:46:10 GMT

We kept getting a table error trying to open the xml file in excel. We figured it was the two extended ascii characters (éó) in the data. If we changed them to a normal e and o the xml file would open just fine. But then I finally figured out if I changed the first line of the XML file to this:

<?xml version="1.0" encoding="ISO-8859-1"?>

then the XML file opened just fine with the two accented characters in the file.

I tried adding encoding="ISO-8859-1" to the SAS TAGSETS.EXCELXP line but it didn't show up in the first line of the XML output.

So how do we get the encoding to show up in the first line of the XML output?

Re: EXCELXP and Special Characters

Squashman — Mon, 26 Jan 2015 21:04:27 GMT

Now we can't get the TRADEMARK symbol to display in Excel. The XML file opens up just fine but it won't display the trademark symbol in excel.

If I change the first line of the XML file in a text editor to: <?xml version="1.0" encoding="ISO-8859-1"?>

It then displays the trademark when opened with Excel.

Re: EXCELXP and Special Characters

kchan — Thu, 03 Mar 2016 12:42:28 GMT

Have the same issue that adding the encoding="ISO-8859-1" with ODS TAGSETS EXCELXP (SAS z/OS 9.1.3) will NOT

create the Excel with the encoding added on the first XML line. The file looks weird then.

Adding the encoding on the first line the file gets open by Excel.

How to add the encoding attribute to the first XML line?

Thank you