topic Re: Importing excel file problem of reading special characters in SAS Programming

Importing excel file problem of reading special characters

Ravikumarkummari — Mon, 13 Mar 2017 10:47:34 GMT

Hi all,

I have data in excel, that contains special characters, when importing into sas instead of special characters

≥

≤

it reading only equals to symbol (=). how to import this data with actual data.

I follow below code for this.

/*1st method*/
proc import datafile="D:\sas\book1.xlsx"
out=data
dbms=excel
replace;
getnames=yes;
scantext=yes;
mixed=yes;
run;

/*2nd method*/
libname data excel "D:\sas\book1.xlsx";

data sheet_data;
set data."sheet1$"n;
run;

Re: Importing excel file problem of reading special characters

RW9 — Mon, 13 Mar 2017 11:11:05 GMT

No good posting Excel files, they are dangerous to download. As for your question, have you et your SAS session as UTF8:

http://support.sas.com/kb/42/831.html

Re: Importing excel file problem of reading special characters

rogerjdeangelis — Tue, 14 Mar 2017 02:14:27 GMT

Converting symbols in excel to SAS unicode

inspired by
https://goo.gl/kFXGEY
https://communities.sas.com/t5/Base-SAS-Programming/Importing-excel-file-problem-of-reading-special-characters/m-p/340360

HAVE an excel sheet with symbols

 see ops sheet

Here is what the ops sheet looks like with sasfont unicode

 e"
 d"
 ±
 ²
 ³
 ´
 $
 ^
 ~
WANT (Unicode for each symbol)

  2265  greater than or equal
  2264  less than or equal
  03B1  alpha
  03B2  beta
  03B3  gamma
  03B4  delta
  0024  $
  005E  ^
  007E  `

WORKING SOLUTION
===================

  Excel SQL convert var to unicode

   StrConv (var,64)  as v4

FULL SOLUTION
=============

proc sql dquote=ansi;
 connect to excel (Path="d:/xls/special.xlsx" mixed=yes);
    select
        put(cats(substr(v4,2,1),substr(v4,1,1)),$hex4.) as unicode

        as hope from connection to Excel
        (
         Select
            StrConv (var,64)  as v4
         from
           [sheet1$]
        );
    disconnect from Excel;
Quit;

Re: Importing excel file problem of reading special characters

rogerjdeangelis — Tue, 14 Mar 2017 02:18:05 GMT

Slight typo in previous post(still works though)

proc sql dquote=ansi;
 connect to excel (Path="d:/xls/special.xlsx" mixed=yes);
    select
        put(cats(substr(v4,2,1),substr(v4,1,1)),$hex4.) as unicode
        from connection to Excel
        (
         Select
            StrConv (var,64)  as v4
         from
           [sheet1$]
        );
    disconnect from Excel;
Quit;

Re: Importing excel file problem of reading special characters

Vince_SAS — Tue, 14 Mar 2017 12:12:49 GMT

This worked for me with SAS 9.4 M3 started with Unicode support:

options validvarname=any;

proc import out=work.book1
  file='C:\temp\book1.xlsx'
  dbms=xlsx
  replace;
run; quit;

Vince DelGobbo

SAS R&D

Re: Importing excel file problem of reading special characters

Ravikumarkummari — Tue, 14 Mar 2017 16:49:39 GMT

Hi,

Thanks for your effort.

I follow your code as it is but got error in log.

proc sql dquote=ansi;
 connect to excel (Path="D:\sas\book1.xlsx" mixed=yes);
    select
        put(cats(substr(v4,2,1),substr(v4,1,1)),$hex4.) as unicode

        as hope from connection to Excel
        (
         Select
            StrConv (var,64)  as v4
         from
           [sheet1$]
        );
    disconnect from Excel;
Quit;

ERROR: Open cursor: Too few parameters. Expected 1.