Help using Base SAS procedures

Importing xlsx file without scientific notation

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

Importing xlsx file without scientific notation

Hi everyone,

I have the following xlsx file:

IDDateProcess_instance
11112015/01/01  12:09:00 AM1049330054220024321
22222015/01/01  12:15:43 AM2139335044321124229
33332015/01/03  10:12:40 AM7839320104333478310

where process_instance is an unique id.

When I try to import this xlsx file into SAS then it converts it into scientific notation:

1.049330054220024E+18

2.139335044321124E+18

7.839320104333478E+18 respectively and converts it to $22. format.


Is there a way to import the xlsx file and keeping the long format ? That is, keeping it as numeric and showing the whole 1049330054220024321.


Thank you !


Accepted Solutions
Solution
‎07-28-2015 06:07 AM
Super User
Super User
Posts: 7,401

Re: Importing xlsx file without scientific notation

Another good example of why not to use Excel or pro import.  Save the data from Excel in a proper data transfer format - CSV, XML etc.  Then write a datastep import which reads the CSV file and tells SAS what the columns should be read in as.  Currently your letting proc import guess what you want to do.

data want;

     informat process_instance 18.;

     file "xyz.csv";

     input id date $ process_instance;

run;

(Or similar to that.)

View solution in original post


All Replies
Solution
‎07-28-2015 06:07 AM
Super User
Super User
Posts: 7,401

Re: Importing xlsx file without scientific notation

Another good example of why not to use Excel or pro import.  Save the data from Excel in a proper data transfer format - CSV, XML etc.  Then write a datastep import which reads the CSV file and tells SAS what the columns should be read in as.  Currently your letting proc import guess what you want to do.

data want;

     informat process_instance 18.;

     file "xyz.csv";

     input id date $ process_instance;

run;

(Or similar to that.)

Contributor
Posts: 42

Re: Importing xlsx file without scientific notation

@RW9 your solution seems to work the best in my opinion.

I saved the xlsx Excel file in csv format and imported the file from there:

proc import datafile="C\Dir\filename.csv"

     out=Want

     dbms=csv

     replace;

     getnames=no;

run;

It puts all the fields in string/character format without any alterations, so it is easy to take it from there and edit the variable formats.

Thank you once again Smiley Happy.

Super User
Super User
Posts: 6,499

Re: Importing xlsx file without scientific notation

NO. 

You cannot store that many significant digits in an 8 byte IEEE floating point number.

45    data x ;

46     a=1049330054220024321 ;

47     put a comma32. ;

48    run;

       1,049,330,054,220,024,320

I doubt that you are going to take the means of the process id, so store it as a character string.

Super User
Posts: 9,676

Re: Importing xlsx file without scientific notation

Add an option   MIXED=YES   ?

New User
Posts: 1

Re: Importing xlsx file without scientific notation

Not work....sad...

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 1641 views
  • 0 likes
  • 5 in conversation