DATA Step, Macro, Functions and more

Importing excel file problem of reading special characters

Reply
Frequent Contributor
Posts: 111

Importing excel file problem of reading special characters

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;

Super User
Super User
Posts: 7,970

Re: Importing excel file problem of reading special characters

Posted in reply to Ravikumarkummari

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

Valued Guide
Posts: 505

Re: Importing excel file problem of reading special characters

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;

Valued Guide
Posts: 505

Re: Importing excel file problem of reading special characters

Posted in reply to 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;

Frequent Contributor
Posts: 111

Re: Importing excel file problem of reading special characters

Posted in reply to rogerjdeangelis

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.

SAS Super FREQ
Posts: 304

Re: Importing excel file problem of reading special characters

Posted in reply to Ravikumarkummari

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

 

Ask a Question
Discussion stats
  • 5 replies
  • 823 views
  • 1 like
  • 4 in conversation