Reading excel file

[tabraz]

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

[Reeza]

There's nothing wrong with the import you need to change the format to see it without exponential format, but you also need to understand numerical precision. Basically, computers can't necessarily handle information as accurately as we think....


http://support.sas.com/documentation/cdl/en/lrcon/65287/HTML/default/viewer.htm#p0ji1unv6thm0dn1gp4t...

[HB]

See if this helps:

https://communities.sas.com/t5/General-SAS-Programming/Proc-import-XLSX-NOW-reading-numbers-correctl...

[Vince_SAS]

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

[tabraz]

Yes it has to be character

[Vince_SAS]

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

[tabraz]

Thanks for your work however there are many different types of charters with many combination.We cant list all characters.

Regards

[Vince_SAS · Accepted solution]

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

[tabraz]

Thanks for your time and effort