BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
wtien196838
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
wtien196838
Quartz | Level 8

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

5 REPLIES 5
SASKiwi
PROC Star

Apply a format statement to your SAS dataset like:

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

 

PGStats
Opal | Level 21

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
wtien196838
Quartz | Level 8

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
Quartz | Level 8

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
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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