Import data from MS access/Excel while retaining special characters

[vineet7878_gmail_com]

Hi, 

We need to import data from MS access and excel into SAS while retaining special characters such as mentioned below.

 ≤  ≥ , µ  Δ  χ  χ2   ±  °  γ  β  º α – ö 

I have spent hours searching for a way but haven't found a way. Ultimately these need to be printed in rtf/pdf reports. Is there a generic way in SAS to smoothly take source data into SAS dataset and then print it, without us programmatically managing each such character independently. 

Thanks.

Vineet

[thomp7050]

Are you getting errors?  If so, what errors are you getting?  If the data import, what is displayed when the data are imported?

[vineet7878_gmail_com]

I used two different approaches - proc Import and Libname statement to define source database. Either way, I didn't get any error. The SAS dataset after importing the data does no longer have non-ascii characters. Most of these are replaced with '?' and  ≤ is replaced by =. I tried modifying the encoding in libname statement to UTF-8. That didn't help. 

[thomp7050]

This is related, but it may not be the best news:

https://communities.sas.com/t5/Base-SAS-Programming/Importing-special-characters-from-Excel/td-p/290...

[vineet7878_gmail_com]

Well, I am hoping SAS software has the ability to import external data in utf-8/unicode. Limiting data to just ASCII characters is primitive.

[thomp7050]

https://www.pharmasug.org/proceedings/2016/BB/PharmaSUG-2016-BB15.pdf

Perhaps page 4 could be the answer?

[Tom]

Is your SAS session using UTF-8 encoding?

Are the characters really messed up in the data? Or just in your display windows? What happens if you print the data to HTML or other format of output that can handle UTF-8?

[vineet7878_gmail_com]

Thanks, I need to experiment a bit. I will also try to change the session to UTF-8 and see if this works. To change the session ot UTF-8 I will need to modify the SAS configuration file.

Vineet

[Vince_SAS · Accepted solution]

I was able import special characters when SAS 9.4 M3 on Windows was started with Unicode support. Save the attached "ImportSpecialCharacters.xlsx" and "ImportSpecialCharacters.xls" files in C:\temp, and then try running this code:

*;
*  Start SAS with Unicode support.
*    Start > All Programs > SAS > Additional Languages > SAS 9.4 (Unicode Support)
*;

*  XLSX File;

libname myxlsx xlsx 'C:\temp\ImportSpecialCharacters.xlsx' access=read;

data work.libname_xlsx;
set myxlsx.sheet1;
run;

proc import out=work.import_xlsx
  file='C:\temp\ImportSpecialCharacters.xlsx'
  dbms=xlsx
  replace;
run; quit;

proc import out=work.import_excelcs
  file='C:\temp\ImportSpecialCharacters.xlsx'
  dbms=excelcs
  replace;
run; quit;

*  XLS File;

libname myxls pcfiles path='C:\temp\ImportSpecialCharacters.xls' access=read;

data work.libname_pcfiles;
set myxlsx.sheet1;
run;

proc import out=work.import_excelcs
  file='C:\temp\ImportSpecialCharacters.xls'
  dbms=excelcs
  replace;
run; quit;

Note that you must have the SAS/ACCESS Interface to PC File Formats in order to read/write native Excel/Access files.

Vince DelGobbo

SAS R&D

[vineet7878_gmail_com]

Thanks a lot. I didn't know about starting SAS in Unicode mode directly. This works.