Hi all,
Could you please help with importing .xls dataset into sas dataset and keep all figures after dot.
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;
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.
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.
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++.
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.