topic Re: Import xls file using encoding UTF-8 in SAS Programming

Import xls file using encoding UTF-8

J_J_J — Wed, 23 Oct 2019 11:07:04 GMT

I'm trying to import excel file in .xls format to SAS using code:

PROC IMPORT out=WORK.test

DATAFILE='/place/where/file.xls'
DBMS=xls REPLACE;
sheet='name';
getnames=YES;
mixed=no;
RUN;

I need to use encoding='UTF-8' to replace some symbols in different columns.

How should I write this code to get right result?

Re: Import xls file using encoding UTF-8

Ksharp — Wed, 23 Oct 2019 11:24:18 GMT

filename x '/place/where/file.xls' encoding='utf-8';


PROC IMPORT out=WORK.test 

DATAFILE=x

DBMS=xls REPLACE;
sheet='name';
getnames=YES;
mixed=no;
RUN;

Re: Import xls file using encoding UTF-8

J_J_J — Wed, 23 Oct 2019 11:26:32 GMT

Thanks, but it doesn't work. Symbols like � stay.

Re: Import xls file using encoding UTF-8

Ksharp — Wed, 23 Oct 2019 11:29:00 GMT

Then Save this xls file into CSV file . and try import again .

Re: Import xls file using encoding UTF-8

andreas_lds — Wed, 23 Oct 2019 11:40:13 GMT

@J_J_J wrote:

Thanks, but it doesn't work. Symbols like � stay.

Interesting. When importing text-files that contain unicode-chars which aren't in the active session encoding (Latin1 on our server), sas writes an error/warning to the log and removes the char from the string. I am wondering why sas inserts fancy chars when reading an excel file containing unicode chars.

Re: Import xls file using encoding UTF-8

Ksharp — Wed, 23 Oct 2019 11:51:33 GMT

Oh. I just be ware of that you need change your sas session into UTF-8 encoding as well to make � appeared.

Re: Import xls file using encoding UTF-8

Shmuel — Wed, 23 Oct 2019 12:46:26 GMT

I had similar issues when retrieving data from external origin into SAS_UE, though it is predefined with encoding='utf-8' .

I found that enlarging LENGTH of specific variables was the solution.

Re: Import xls file using encoding UTF-8

J_J_J — Fri, 25 Oct 2019 04:29:54 GMT

Thanks to everybody. None of the proposed options unfortunately helped. The problem was solved only by changing the format of the file from .xls to .xlsx, fortunately it was possible.