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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.