BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

1 ACCEPTED SOLUTION

Accepted Solutions
Vince_SAS
Rhodochrosite | Level 12

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

9 REPLIES 9
thomp7050
Pyrite | Level 9

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
Obsidian | Level 7

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. 

vineet7878_gmail_com
Obsidian | Level 7

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.

 

 

Tom
Super User Tom
Super User

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
Obsidian | Level 7

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
Rhodochrosite | Level 12

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
Obsidian | Level 7
Thanks a lot. I didn't know about starting SAS in Unicode mode directly. This works.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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