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,401

Re: Importing excel file problem of reading special characters

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

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

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 Employee
Posts: 285

Re: Importing excel file problem of reading special characters

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
  • 469 views
  • 1 like
  • 4 in conversation