BookmarkSubscribeRSS Feed
Ravikumarkummari
Quartz | Level 8

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;

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Barite | Level 11
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
Barite | Level 11
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
Quartz | Level 8

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
Rhodochrosite | Level 12

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

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 11194 views
  • 1 like
  • 4 in conversation