turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- [EXCEL to SAS] getting extra digits

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-25-2015 11:00 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-25-2015 11:33 AM - edited 09-25-2015 11:34 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-25-2015 11:58 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-27-2015 12:05 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-28-2015 02:22 AM

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}