Importing excel file problem of reading special characters

[Ravikumarkummari]

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;

[RW9]

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

[rogerjdeangelis]

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;

[rogerjdeangelis]

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;

[Ravikumarkummari]

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.

[Vince_SAS]

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