DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

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

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.


Accepted Solutions
Solution
‎04-16-2018 10:34 PM
Super User
Super User
Posts: 8,279

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

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.

View solution in original post


All Replies
Solution
‎04-16-2018 10:34 PM
Super User
Super User
Posts: 8,279

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

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.

Occasional Contributor
Posts: 19

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

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. 

Frequent Contributor
Posts: 79

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

You can use the dbencoding option.

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

Cheers from India!

Manjeet
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 154 views
  • 1 like
  • 3 in conversation