[EXCEL to SAS] getting extra digits

Reply
Contributor
Posts: 20

[EXCEL to SAS] getting extra digits

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.

Super User
Posts: 19,194

Re: [EXCEL to SAS] getting extra digits

[ Edited ]

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.

Super User
Super User
Posts: 7,727

Re: [EXCEL to SAS] getting extra digits

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.

Trusted Advisor
Posts: 3,208

Re: [EXCEL to SAS] getting extra digits

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 --<-----
Frequent Contributor
Posts: 144

Re: [EXCEL to SAS] getting extra digits

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}

Ask a Question
Discussion stats
  • 4 replies
  • 271 views
  • 0 likes
  • 5 in conversation