topic Re: Reading excel file in SAS Programming

Reading excel file

tabraz — Thu, 16 Aug 2018 16:06:56 GMT

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

Re: Reading excel file

Reeza — Thu, 16 Aug 2018 16:47:07 GMT

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#p0ji1unv6thm0dn1gp4t01a1u0g6.htm

Re: Reading excel file

HB — Thu, 16 Aug 2018 16:47:56 GMT

See if this helps:

https://communities.sas.com/t5/General-SAS-Programming/Proc-import-XLSX-NOW-reading-numbers-correctly/td-p/426483

Re: Reading excel file

Vince_SAS — Thu, 16 Aug 2018 16:49:51 GMT

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

Re: Reading excel file

tabraz — Thu, 16 Aug 2018 17:19:33 GMT

Yes it has to be character

Re: Reading excel file

Vince_SAS — Thu, 16 Aug 2018 17:27:55 GMT

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

Re: Reading excel file

tabraz — Fri, 17 Aug 2018 07:05:19 GMT

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

Regards

Re: Reading excel file

Vince_SAS — Fri, 17 Aug 2018 15:23:57 GMT

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

Re: Reading excel file

tabraz — Mon, 20 Aug 2018 11:22:20 GMT

Thanks for your time and effort