Hi SAS community,
I am working to import data from excel worksheet into sas datasets.
I use proc import :
proc import datafile=<infile>. out=<outfile>. dbms=excel replace;
DBDSOPTS="FIRSTOBS=9 DBSASTYPE=( " F1='CHAR(20)' F2='NUMERIC(8)' F3='NUMERIC(8)' F4='NUMERIC(8)' ");
sheet="sheet1";
getnames=no;
run;
One of rows on excel worksheet is : (12 digits after decimal)
location A | 89.205247050640 | 95.898600110359 | 98.293041120003 |
My proc import only read: ( 9 digits after decimal)
location A | 89.205247050 | 95.898600110 |
98.293041120 |
Can you show me any method to get the full digits (12 digits) after decimal.
Thanks for your support.
Best Regards,
WTien1968
Hi PGStats,
Your solution is correct. It is my mistake that I set up the format with new variable name.
After proc import:
Data test;
set <outfile>;
rename F1=location F2=per_1 F3=per_2 F4=per_3 ;
format per_1 per_2 per_3 15.12;
run;
It suppose to code: format F2 F3 F4 15.12 ;
then rename later.
Thanks again for your solution.
WTien1968
Apply a format statement to your SAS dataset like:
data want;
set have;
format F2 15.12;
run;
Both SAS and Excel store the same floating point numbers, their precision is dictated by the computer floating point unit. What you see is a representation of those numbers dictated by the numeric format of spreadsheet cells (Excel) or variables (SAS). Change the format, as @SASKiwi suggested, and you will see a different representation of the number.
Hi PGstats,
Thanks for your quick response.
Even I set the format of variable to 15.12, the number that I got in sas dataset still is 9 digits after decimal.
Is it due to my computer hardware restricted or sas options set up ?
I use base SAS 9.3 both local and server.
Any one welcome to suggest your idea.
Thanks
WTien1968
Hi PGStats,
Your solution is correct. It is my mistake that I set up the format with new variable name.
After proc import:
Data test;
set <outfile>;
rename F1=location F2=per_1 F3=per_2 F4=per_3 ;
format per_1 per_2 per_3 15.12;
run;
It suppose to code: format F2 F3 F4 15.12 ;
then rename later.
Thanks again for your solution.
WTien1968
I can't reproduce your proc import step. There seems to be some incompatibility between FIRSTOBS= and getnames=no with proc Import.
However I could do this:
libname xl excel "&sasforum\datasets\test12.xlsx" access=readonly header=no;
proc sql;
create table outfile as
select F1,
F2 format=15.12,
F3 format=15.12,
F4 format=15.12
from xl.'Sheet1$'n(firstobs=9 DBSASTYPE=(F1='CHAR(20)' F2='NUMERIC(8)' F3='NUMERIC(8)' F4='NUMERIC(8)'));
select * from outfile;
quit;
libname xl clear;
and get all 12 decimals in the printout.
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.