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

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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

Tom
Super User Tom
Super User

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
Super User

Add an option   MIXED=YES   ?

xiao
Calcite | Level 5

Not work....sad...

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 9235 views
  • 0 likes
  • 5 in conversation