topic Re: Can't read some uncommon/special Chinese word from excel in SAS Programming

Can't read some uncommon/special Chinese word from excel

Jack2012 — Fri, 05 May 2023 02:24:28 GMT

Dear All,

Just facing a terrible issue when I try to reading from an simple external Excel file, and found there is Warning(figure1) and the problomatic result(figure2).

The sas session is EUC-CN, I am hunting for the solution, please let me know if you have, and I would be highly appreciated.

SAS CODE:

filename x "D:\sas code\encodingIssue\test.xlsx" encoding="utf-8";

proc import datafile=x out=test dbms=xlsx replace;getnames=yes;run;

Re: Can't read some uncommon/special Chinese word from excel

SASKiwi — Fri, 05 May 2023 02:46:56 GMT

I suggest you try the XLSX and EXCEL LIBNAMEs and see what your spreadsheet looks like in VIEWTABLE:

libname x XLSX "D:\sas code\encodingIssue\test.xlsx" encoding="utf-8";

libname x EXCEL "D:\sas code\encodingIssue\test.xlsx" encoding="utf-8";

Re: Can't read some uncommon/special Chinese word from excel

Jack2012 — Fri, 05 May 2023 05:19:09 GMT

ERROR: Libref X is not assigned. ERROR: Error in the LIBNAME statement. ERROR 22-7: Invalid option name ENCODING. Thank you, but both snippet have above error.

Re: Can't read some uncommon/special Chinese word from excel

ballardw — Fri, 05 May 2023 06:54:19 GMT

See if UTF-16 helps.

Note: search this forum for Excel and Import data problems.

If you are likely to have more than one file with the same data structure in the long run saving XLSX to a CSV format and reading that with a data step is much more consistent.

Re: Can't read some uncommon/special Chinese word from excel

SASKiwi — Fri, 05 May 2023 22:21:19 GMT

Did you try without the ENCODING option? If not please try without that.

Re: Can't read some uncommon/special Chinese word from excel

RD2 — Sat, 06 May 2023 07:41:45 GMT

You need using SAS (Unicode Support) to run your code.