topic Re: How to import from xls to sas dataset and keep all figures after 'dot' in SAS Programming

How to import from xls to sas dataset and keep all figures after 'dot'

DmytroYermak — Mon, 05 Mar 2018 10:23:21 GMT

Hi all,

Could you please help with importing .xls dataset into sas dataset and keep all figures after dot.

Re: How to import from xls to sas dataset and keep all figures after 'dot'

Kurt_Bremser — Mon, 05 Feb 2018 13:52:59 GMT

Keep in mind that a SAS dataset has a maximum precision of ~ 15 decimal digits, owing to the way numbers are stored (8-byte real).

Do a google search for "SAS numeric precision" for the hard facts behind it.

Also keep in mind that (most) decimal fractions cannot be converted to binary without loss, because the binary numbers end up being periodic. 0.1 is just the simplest case.

Re: How to import from xls to sas dataset and keep all figures after 'dot'

DmytroYermak — Mon, 05 Feb 2018 14:18:54 GMT

Do I understand correctly that it worth to convert the xls into csv to keep all the figures after .dot? When I convert the file into .csv the max number of figures is 9, that is the case.

Re: How to import from xls to sas dataset and keep all figures after 'dot'

Kurt_Bremser — Mon, 05 Feb 2018 14:56:38 GMT

You need to assign a format with enough fractional digits before saving the sheet as .csv. Excel writes data to csv as displayed.

Note that a textual format is inherently more usable than the native Excel format for transfer of data into SAS. With the Excel format, you have to rely on the guesses that SAS makes, while with a csv file, you have full control by manually writing the data step for the import. And you can inspect the data with a tool like notepad++.

Re: How to import from xls to sas dataset and keep all figures after 'dot'

Vince_SAS — Mon, 05 Feb 2018 17:41:21 GMT

Have you tried adding a format when displaying the data, like @Kurt_Bremser suggested?

ods _all_ close;

ods listing;

proc import file='C:\temp\temp.xlsx'
  dbms=excel
  out=work.excel
  replace;
run; quit;

title 'EXCEL Engine';
proc print data=work.excel;
  format estimate 32.25;
run; quit;

proc import file='C:\temp\temp.xlsx'
  dbms=xlsx
  out=work.xlsx
  replace;
run; quit;

title 'XLSX Engine';
proc print data=work.xlsx;
  format estimate 32.25;
run; quit;

Vince DelGobbo

SAS R&D

Re: How to import from xls to sas dataset and keep all figures after 'dot'

DmytroYermak — Wed, 07 Feb 2018 11:36:19 GMT

I wanted to add that unfortunately I lost some figures after proc import. I do not know the reason but finally I had to use the following code:

1) to assign libname to the file;

2) to read directly from the 'sheet';

3) to convert numeric to character using best12. .

options validvarname=any;

libname MDD pcfiles path='D:\Projects\filename.xls' scantime=yes;


data pkmdd;
  set MDD."Sheet1$"n;
run;
 
data ...;
 ....
   ORRES  = put(parameter,??best12.);
 ...
run;