DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 56
Accepted Solution

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


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

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.

 

Thanks again for your solution.

 

WTien1968

View solution in original post


All Replies
Super User
Posts: 3,102

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;

 

Respected Advisor
Posts: 4,641

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: 56

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

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

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

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.

 

Thanks again for your solution.

 

WTien1968

Respected Advisor
Posts: 4,641

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:

 

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.

PG
☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 372 views
  • 0 likes
  • 3 in conversation