topic Import data from MS access/Excel while retaining special characters in SAS Programming

Import data from MS access/Excel while retaining special characters

vineet7878_gmail_com — Fri, 21 Apr 2017 16:36:50 GMT

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

Re: Import data from MS access/Excel while retaining special characters

thomp7050 — Fri, 21 Apr 2017 18:23:48 GMT

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

Re: Import data from MS access/Excel while retaining special characters

vineet7878_gmail_com — Fri, 21 Apr 2017 18:37:31 GMT

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.

Re: Import data from MS access/Excel while retaining special characters

thomp7050 — Fri, 21 Apr 2017 19:01:37 GMT

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/29014

Re: Import data from MS access/Excel while retaining special characters

vineet7878_gmail_com — Fri, 21 Apr 2017 19:33:10 GMT

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.

Re: Import data from MS access/Excel while retaining special characters

thomp7050 — Fri, 21 Apr 2017 19:35:50 GMT

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

Perhaps page 4 could be the answer?

Re: Import data from MS access/Excel while retaining special characters

Tom — Fri, 21 Apr 2017 20:00:18 GMT

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?

Re: Import data from MS access/Excel while retaining special characters

vineet7878_gmail_com — Fri, 21 Apr 2017 21:20:22 GMT

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

Re: Import data from MS access/Excel while retaining special characters

Vince_SAS — Fri, 21 Apr 2017 23:22:48 GMT

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

Re: Import data from MS access/Excel while retaining special characters

vineet7878_gmail_com — Sat, 22 Apr 2017 12:40:40 GMT

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