topic Re: ods csv large number leads exponential values in excel in SAS Programming

ods csv large number leads exponential values in excel

khs_chaitanya — Fri, 03 Aug 2012 06:10:15 GMT

HI, I have a column with large numbers defined as character datatype. When i write it to csv using an ODS statement or a proc export, the CSV file writes data properly, but when i view it in excel, it converts to exponential values.

Alternatively, i added double quotes with an = at the beginning, but due to some reason, a pair of double quotes gets appended in the CSV file. This time, the excel view is OK but the CSV data is not as expected. Any inputs is highly appreciated. I need the data to be accurate in CSV at the same time, excel should present it accurately.

I prefer not to generate an xls file as the number of records run into millions.

data source;

input x $22.;

datalines;

123456789123456789

987654321123456789

;

data csvdata;

set source;

new_x = '="'||trim(x)||'"';

run;

--first method --

proc export data = csvdata

outfile='/users/test/procexport.csv'

dbms='csv'

replace;

run;

--second method--

ODS CSV FILE='/users/test/ods.csv';

PROC PRINT DATA=csvdata;

RUN;

ODS CSV CLOSE;

output file ods.csv when viewed a notepad

"Obs","x","new_x"

"1",123456789123456789,"=""123456789123456789"""

"2",987654321123456789,"=""987654321123456789"""

We see that additional pair of double quotes gets appended at the end.

Results when viewed in excel

Obs	x	new_x
1	1.23457E+17	123456789123456789
2	9.87654E+17	987654321123456789

Re: ods csv large number leads exponential values in excel

ballardw — Fri, 03 Aug 2012 14:52:03 GMT

Make the cell wider in Excel. It is not a SAS behavior causing this.

Re: ods csv large number leads exponential values in excel

TomKari — Fri, 03 Aug 2012 19:13:47 GMT

Excel and SAS cannot accurately store a number beyond 15 digits, so if you want your data in Excel as a number, it will be somewhat incorrect, and exponential is the most appropriate way to show it.

To demonstrate, if in Excel you type 123456789123456789, Excel will display it as 1.23457E+17. If you then explicitly display it with a "Number" format, it will come out as 123456789123456000, so you can see you've lost the digits after the 15th.

On the other hand, if you want it in Excel as character, which is the only way to present a representation of an 18 digit number, I think that your code is functioning correctly. Note that as soon as you try to execute numeric operations on it in Excel, however, it will revert to 15 digits of accuracy.

Tom