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

[wtien196838]

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

[SASKiwi]

Apply a format statement to your SAS dataset like:

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

[PGStats]

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.

[wtien196838]

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

[wtien196838 · Accepted solution]

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

[PGStats]

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.