DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 210
Accepted Solution

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

[ Edited ]

Hi all,

 

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

 


Accepted Solutions
Solution
‎02-07-2018 06:37 AM
Regular Contributor
Posts: 210

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

[ Edited ]
Posted in reply to Vince_SAS

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;

View solution in original post


All Replies
Super User
Posts: 9,878

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

Posted in reply to DmytroYermak

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 210

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

[ Edited ]
Posted in reply to KurtBremser

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.

Super User
Posts: 9,878

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

Posted in reply to DmytroYermak

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++.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
SAS Super FREQ
Posts: 352

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

Posted in reply to DmytroYermak

Have you tried adding a format when displaying the data, like @KurtBremser 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

Solution
‎02-07-2018 06:37 AM
Regular Contributor
Posts: 210

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

[ Edited ]
Posted in reply to Vince_SAS

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 191 views
  • 5 likes
  • 3 in conversation