topic Re: Unable to read chinese characters in New SAS User

Unable to read chinese characters

parmesh — Wed, 19 Jun 2019 05:18:52 GMT

I am unable to read chinese characters from my source file. I will not be sharing the source file coz its has customer details.

I have used the below code for importing.

libname sample xlsx "C:\MBR\property.xlsx";

data Sheetnames;
set sashelp.vtable;
where libname = 'SAMPLE';
run;

libname SAMPLE clear;

proc sql ;
select count(*)
into :NObs
from Sheetnames;
select compress(memname,'$')
into :Name1-:Name%left(&NObs)
from Sheetnames;
select compress(memname,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','K') AS CNAME
into :data1-:data%left(&nobs)
FROM Sheetnames;
quit;

%macro import;
%do i=1 %to &NObs;
proc import datafile="C:\MBR\property.xlsx "
out= &&data&i
dbms=xlsx replace;
sheet="&&name&i";
getnames=no;
datarow=6;
run;
%end;
%mend;
%import;

It would be very helpful if someone has the solution.

Re: Unable to read chinese characters

Ksharp — Wed, 19 Jun 2019 12:55:12 GMT

What is your SAS encoding ? if it was UTF-8 or EUN_CN ,should be OK.

proc options option=encoding value;
run;

Re: Unable to read chinese characters

parmesh — Wed, 19 Jun 2019 12:57:59 GMT

UAT-8

Re: Unable to read chinese characters

Ksharp — Wed, 19 Jun 2019 13:11:55 GMT

An workaround way is save your excel as a CSV file .

and import it again.

filename x 'c:\property.xlsx ' encoding='utf-8';

proc import ........

Re: Unable to read chinese characters

parmesh — Wed, 19 Jun 2019 13:17:59 GMT

But ll have to do this for 50 files.

Re: Unable to read chinese characters

Ksharp — Wed, 19 Jun 2019 13:39:31 GMT

There are many VBS in internet to convert excel to csv , google it you will find ( Excel2Csv.bat ) .

Re: Unable to read chinese characters

Ksharp — Wed, 19 Jun 2019 13:41:20 GMT

And could you change your sas encoding as euc-cn

Re: Unable to read chinese characters

Ksharp — Wed, 19 Jun 2019 13:47:59 GMT

Also try these options.

libname x xlsx 'c:\temp\x.xlsx' inencoding='utf-8' outencoding='utf-8';

Re: Unable to read chinese characters

parmesh — Wed, 19 Jun 2019 13:51:59 GMT

Ok will try those and get back to you thank you for your time.

Re: Unable to read chinese characters

parmesh — Thu, 20 Jun 2019 04:56:29 GMT

I got this error in the log after running the below code that was shared yesterday.

24 libname x xlsx 'C:\MBR\property.xlsx' inencoding='utf-8' outencoding='utf-8';
_______
278
WARNING 278-63: The option INENCODING is not implemented in the XLSX engine.

NOTE: Libref X was successfully assigned as follows:
Engine: XLSX
Physical Name: C:\MBR\property.xlsx
WARNING 278-63: The option OUTENCODING is not implemented in the XLSX engine.

Re: Unable to read chinese characters

parmesh — Mon, 24 Jun 2019 04:28:06 GMT

Can anyone help on this please.

Re: Unable to read chinese characters

andreas_lds — Mon, 24 Jun 2019 05:35:48 GMT

Interesting problem, afaik xlsx-files are stored in something fancy like utf-16 or even utf-32 - this was the case when I extracted an excel-file some time ago. The best solution starts with getting rid of excel as data source at all, because sooner or later you will run into other problems. But this has been recommended in the community so many times,that I stopped counting. You could, of course, open a track with tech-support, maybe theyhave a better idea.

Re: Unable to read chinese characters

parmesh — Mon, 24 Jun 2019 05:55:59 GMT

Ok thanks a lot for your advice.