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

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

manjan — Tue, 30 Aug 2016 12:11:55 GMT

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

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

Kurt_Bremser — Tue, 30 Aug 2016 12:49:12 GMT

And what IS your problem?

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

manjan — Tue, 30 Aug 2016 13:08:32 GMT

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

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

Kurt_Bremser — Tue, 30 Aug 2016 13:13:58 GMT

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.

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

manjan — Wed, 31 Aug 2016 12:07:23 GMT

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

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

Kurt_Bremser — Wed, 31 Aug 2016 12:48:04 GMT

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.