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

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

wtien196838 — Tue, 19 Jul 2016 20:39:02 GMT

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

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

SASKiwi — Tue, 19 Jul 2016 20:46:22 GMT

Apply a format statement to your SAS dataset like:

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

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

PGStats — Tue, 19 Jul 2016 21:48:31 GMT

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.

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

wtien196838 — Tue, 19 Jul 2016 23:29:26 GMT

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

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

wtien196838 — Tue, 19 Jul 2016 23:45:56 GMT

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

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

PGStats — Wed, 20 Jul 2016 02:31:10 GMT

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.