topic Importing xlsx file without scientific notation in SAS Procedures

Importing xlsx file without scientific notation

mjheever — Tue, 28 Jul 2015 09:34:55 GMT

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 !

Re: Importing xlsx file without scientific notation

RW9 — Tue, 28 Jul 2015 10:07:55 GMT

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

Re: Importing xlsx file without scientific notation

Tom — Tue, 28 Jul 2015 10:32:30 GMT

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.

Re: Importing xlsx file without scientific notation

Ksharp — Tue, 28 Jul 2015 12:24:50 GMT

Add an option MIXED=YES ?

Re: Importing xlsx file without scientific notation

mjheever — Wed, 29 Jul 2015 08:33:55 GMT

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

Re: Importing xlsx file without scientific notation

xiao — Fri, 22 Jul 2016 21:35:26 GMT

Not work....sad...