DATA Step, Macro, Functions and more

Import data from MS access/Excel while retaining special characters

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Import data from MS access/Excel while retaining special characters

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


Accepted Solutions
Solution
‎04-22-2017 08:41 AM
SAS Employee
Posts: 286

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

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

View solution in original post


All Replies
Frequent Contributor
Posts: 93

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

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

 

 

Occasional Contributor
Posts: 17

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

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. 

Frequent Contributor
Posts: 93

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

Occasional Contributor
Posts: 17

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

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.

 

 

Frequent Contributor
Posts: 93

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

Super User
Super User
Posts: 6,502

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

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?

Occasional Contributor
Posts: 17

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

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

Solution
‎04-22-2017 08:41 AM
SAS Employee
Posts: 286

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

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

Occasional Contributor
Posts: 17

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

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

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

Discussion stats
  • 9 replies
  • 202 views
  • 1 like
  • 4 in conversation