I am am trying to read excel file in sas with both (character and numeric values).But its not working the way it should as row with decimal values .07 is getting converted to 7.0000000000000007E-2
22 7.0000000000000007E-2
PROC IMPORT
DATAFILE="C:\Users\txxx\Desktop\Data\7777.xlsx"
OUT=Result
DBMS=XLSX
REPLACE;
GETNAMES=YES;
RUN;
Can you help me on this?attached output file and source file
You can experiment with something like this:
proc import
datafile='C:\Users\txxx\Desktop\Data\7777.xlsx'
out=work.result1
dbms=xlsx
replace;
getnames=yes;
run; quit;
data work.result2;
set work.result1;
length test1_numeric 8;
drop test1_numeric;
test1_numeric = input(test1, ?? comma32.);
if (test1_numeric ne .)
then test1 = strip(put(test1_numeric, best.));
run;
Vince DelGobbo
SAS R&D
The column TEST1 was imported as a CHARACTER type because some cells contain the value "NR".
Do you really want TEST1 to be CHARACTER type?
Vince DelGobbo
SAS R&D
You can experiment with something like this:
proc import
datafile='C:\Users\txxx\Desktop\Data\7777.xlsx'
out=work.result1
dbms=xlsx
replace;
getnames=yes;
run; quit;
data work.result2;
set work.result1;
length test1_numeric 8;
drop test1_numeric;
if (lowcase(test1) not in ('nr', 'ns')) then do;
test1_numeric = input(test1, e32.);
test1 = strip(put(test1_numeric, best.));
end;
run;
Vince DelGobbo
SAS R&D
Thanks for your work however there are many different types of charters with many combination.We cant list all characters.
Regards
You can experiment with something like this:
proc import
datafile='C:\Users\txxx\Desktop\Data\7777.xlsx'
out=work.result1
dbms=xlsx
replace;
getnames=yes;
run; quit;
data work.result2;
set work.result1;
length test1_numeric 8;
drop test1_numeric;
test1_numeric = input(test1, ?? comma32.);
if (test1_numeric ne .)
then test1 = strip(put(test1_numeric, best.));
run;
Vince DelGobbo
SAS R&D
Thanks for your time and effort
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: