Solved
Contributor
Posts: 59

How to get a numeric value with 12 digits after decimal from excel worksheet

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.2052 95.8986 98.293

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.

Best Regards,

WTien1968

Accepted Solutions
Solution
‎07-19-2016 07:46 PM
Contributor
Posts: 59

Re: How to get a numeric value with 12 digits after decimal from excel worksheet

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.

WTien1968

All Replies
Super User
Posts: 3,920

Re: How to get a numeric value with 12 digits after decimal from excel worksheet

Apply a format statement to your SAS dataset like:

data want;
set have;
format F2 15.12;
run;

Posts: 5,535

Re: How to get a numeric value with 12 digits after decimal from excel worksheet

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.

PG
Contributor
Posts: 59

Re: How to get a numeric value with 12 digits after decimal from excel worksheet

Hi PGstats,

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

Solution
‎07-19-2016 07:46 PM
Contributor
Posts: 59

Re: How to get a numeric value with 12 digits after decimal from excel worksheet

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.

WTien1968

Posts: 5,535

Re: How to get a numeric value with 12 digits after decimal from excel worksheet

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:

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.

PG
☑ This topic is solved.