Importing xlsx file without scientific notation

[mjheever]

Hi everyone,

I have the following xlsx file:

ID	Date	Process_instance
1111	2015/01/01  12:09:00 AM	1049330054220024321
2222	2015/01/01  12:15:43 AM	2139335044321124229
3333	2015/01/03  10:12:40 AM	7839320104333478310

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 !

[RW9 · Accepted solution]

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

[mjheever]

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

[Tom]

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.

[Ksharp]

Add an option   MIXED=YES   ?

[xiao]

Not work....sad...