topic Re: How can I import a xlsx file with both dates and Chinese characters? in SAS Programming

How can I import a xlsx file with both dates and Chinese characters?

xyxu — Sun, 15 Apr 2018 22:54:00 GMT

I have been stuck by this for several hours. I first tried

proc import datafile = "&path\example"
out = x
dbms = xlsx
replace;
getnames = yes;
run;

it can work, but the date variable is recognized as character. I also tried to save the excel file as csv, but Chinese characters will be missing.

A small example data is attached.

Re: How can I import a xlsx file with both dates and Chinese characters?

Tom — Sun, 15 Apr 2018 23:54:39 GMT

What encoding are you using in your SAS session? Check the ENCODING option. You should be using UTF-8.

You need to start SAS with the right encoding setting if you want to be able to read multi-byte character sets.

But if your only issue is that your date variable is character then just convert it.

data want ;
  set have ;
  numdate = input(chardate,yymmdd10.);
  format numdate yymmdd10. ;
run;

Perhaps the things that look like hyphens between the parts of the date values are not really hyphens? If so then you could remove them using COMPRESS() function.

Re: How can I import a xlsx file with both dates and Chinese characters?

mnjtrana — Mon, 16 Apr 2018 08:21:10 GMT

You can use the dbencoding option.

More info in link below:
http://documentation.sas.com/?docsetId=acpcref&docsetTarget=p0okk0tdn5y5arn1x7zky4cz3m41.htm&docsetVersion=9.4&locale=en

Re: How can I import a xlsx file with both dates and Chinese characters?

xyxu — Mon, 16 Apr 2018 14:28:53 GMT

Thanks for the reply! Yes, I was using SAS 9.4 (Chinese) version. I can use proc import and then convert chardate to date. However, when I sort nodupkey with Chinese-valued variables, the result messes up.

Would this be caused by the length of these variables? One of them is $152.