DATA Step, Macro, Functions and more

Importing data with special characters with SAS 9.1.3 with dbms = xls

Reply
New Contributor
Posts: 3

Importing data with special characters with SAS 9.1.3 with dbms = xls

hi all,

 

I am facing problem while importing excel file data with special characters. I am working with SAS 9.1.3 Unix platform and using proc import with dbms = xls statement.

 

proc import datafile = "patient_data.xls" out = lot_file dbms = xls replace;
getnames = no;
mixed=yes;
sheet = "lot";;
textsize = 32767;
run;

 

Please suggest.

 

Thanks,

manjan

Super User
Posts: 7,762

Re: Importing data with special characters with SAS 9.1.3 with dbms = xls

And what IS your problem?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 3

Re: Importing data with special characters with SAS 9.1.3 with dbms = xls

Posted in reply to KurtBremser

hi KurtBremser,

Thank you for your response.

 

The issue is special characters in the data are not imported correctly.

For example:  The excel file has data:

"Analysis of  Number (%) of Subjects Reporting Ctrough >20 μg/mL  at Cycle 5 (Pre dose Cycle 6 ) - Per Protocol Population".

 

But after importing the μ is replaced with some ¼ .

 

Please see attached screen shot of the same.

 

Thanks,

Manjan

 

Super User
Posts: 7,762

Re: Importing data with special characters with SAS 9.1.3 with dbms = xls

Looks like you have a discrepancy between the session encoding of SAS and the encoding of the Excel file (most probably UTF-8).

Either you need to tinker around with the SAS session encoding, or you could correct those characters by using the translate() function.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 3

Re: Importing data with special characters with SAS 9.1.3 with dbms = xls

Posted in reply to KurtBremser

hi KurtBremser,

 

Thank you for your quick response.

 

My SAS sesson encoding is WLATIN1 and i cant use translate as this code should be working dynamically.

 

Also i see one article

"The Impact of Change from wlatin1 to UTF-8 Encoding in SAS Environment" suggesting that

"DBMS=XLS does not support the multiple-byte characters! Thus, you cannot create
(or import) via PROC EXPORT/PROC IMPORT a .XLS file with characters like μ in UTF-8 encoding SAS
environment.".

 

So is their a way to import XLS files with special characters with SAS 9.1.3 on unix environment ?

 

Thanks,

manjan

 

Super User
Posts: 7,762

Re: Importing data with special characters with SAS 9.1.3 with dbms = xls

I handled a similar problem (mainframe ebcdic data with german codepage to UNIX(AIX) with en-US) by creating a macro that contains the translate function for all the German "Umlauts" and applying that to all strings read from the mainframe. After that, a single change to the macro will have an impact in all programs using it, making maintenance quite easy.

 

Since SAS obviously does not support UTF in the dbms=xls in proc import 8as per your quoted article), I suggest to switch from xls to a more sensible file format. Use CSV or another text-based file format for transfer, and it is that much easier to deal with codepage and multibyte character problems.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 5 replies
  • 583 views
  • 0 likes
  • 2 in conversation