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;
No good posting Excel files, they are dangerous to download. As for your question, have you et your SAS session as UTF8:
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;
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;
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.