BookmarkSubscribeRSS Feed
michael90
Calcite | Level 5

hi,

 

values that read in excel like 5.9 are imported as 5.8888888888888888888889, how is that possible? and how can i fix the addition of these extra digits?

 

maybe it has something to do with the 64 bit version? i am ussing a 32 bit computer, but it can read xls, xlsx. i am using proc import, using settings for DBMS.

4 REPLIES 4
Reeza
Super User

Are the cells formulas or is there a hardcoded value of 5.9 that is being imported as 5.8888?

Please post a sample of your file if it's hardcoded.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

It sounds to me like your importing an Excel file correct?  If so then I would suppose the fault lies in Excel.  One Excel's "benefits" is hiding the underlying data.  Open the file in Excel and check the formating on the cell.  Try exporting that same sheet to a CSV then open the CSV file in Notepad, what does the data look like there?  If in the CSV it shows 5.9 correctly then import the CSV.  In fact that is my advice in any situation regarding Excel (other than don't use it in the first place), save the data to a proper data transfer format, CSV.  Then write a datastep (as I presume your using proc import which is at best a guessing utility) to import the CSV data.  You then know what your importing, and how your importing it.  Currently your just guessing.

jakarman
Barite | Level 11

Excel is using the floating type, SAS is using the floating type. With a automatic type propagation don't by amazed on the effects arround floating numbers. RW9 advice makes a lot of sense. Know your data and know your toolsets. 

---->-- ja karman --<-----
arodriguez
Lapis Lazuli | Level 10

If all numbers in the column have the same number of digits, you could round it with something like

New_col=ROUND(Col,0.1);

If it's problem of how numbers are save it in floating number (I get a similar error with median of a pair number of obs, instead of .5 i get .4999999), to solve it I make a round in the 10^{-9}

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
  • 4 replies
  • 571 views
  • 0 likes
  • 5 in conversation