topic [EXCEL to SAS] getting extra digits in SAS Programming

[EXCEL to SAS] getting extra digits

michael90 — Fri, 25 Sep 2015 15:00:00 GMT

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.

Re: [EXCEL to SAS] getting extra digits

Reeza — Fri, 25 Sep 2015 15:34:19 GMT

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.

Re: [EXCEL to SAS] getting extra digits

RW9 — Fri, 25 Sep 2015 15:58:15 GMT

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.

Re: [EXCEL to SAS] getting extra digits

jakarman — Sun, 27 Sep 2015 16:05:46 GMT

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.

Re: [EXCEL to SAS] getting extra digits

arodriguez — Mon, 28 Sep 2015 06:22:44 GMT

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}