Numeric precision on export to a flat file

[LuisValença]

I have a problem when I try to export numbers to a csv file for use in another application. The example shows that the results are not the same when I use 3 different formats

data _null_;
a = 152677201.1333332;
put "Without format " a;
put "Format Best32." a best32.;
put "Format E32." a e32.;

b = 327653072.38999998;
put "Without format " b;
put "Format Best32." b best32.;
put "Format E32." b e32.;

 run;

Output

Without format 152677201.13
Format Best32. 152677201.133333
Format E32. 1.5267720113333320000000000E+08

Without format 327653072.39
Format Best32. 327653072.39
Format E32. 3.2765307239000000000000000E+08

This is a real case. What is the best way to export these values without loss of precision, so that they don't interfere with another application?

[yabwon]

Hi,

I guess you have already read throug thi part of the documentation about numerical precision:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lepg/p0dv87zb3bnse6n1mqo360be70qr.htm

and you know that in IEEE standard you are able to present "precisely" numbers up to 15 digits.  

In your case both a and b are longer, so even:

a = 152677201.1333332;

might give rounding "issues".

If that other application is running on SAS you could use binary64. to prints bits:

data _null_;
a = 152677201.1333332;
put "Format binary64." a binary64.;

b = 327653072.38999998;
put "Format binary64." b binary64.;
run;

Log:

Format binary64.
0100000110100010001100110101011010100010010001000100010001000000
Format binary64.
0100000110110011100001111001011011010000011000111101011100001010

Bart

[PaigeMiller]

SAS can handle numbers up to about 15 digits without loss of precision. After that you will lose precision. You can use PROC DS2 to handle numbers with twice the normal precision.

[Tom]

I don't see any issue here.

If you don't use a format the data step will use BEST12.  That explains the only difference I can see.

152677201.13
152677201.133333

But your first number cannot be represented exactly as a floating point binary number. Which is why your attempt to assign that value to it ends up rounded.

152677201.1333332
152677201.133333

[FreelanceReinh]

Hello @LuisValença,

If I saw those two example values in a dataset, my first thought would be: These values are affected by rounding errors already, based on the (possibly false) assumption that the first actually represents 152677201 + 2/15 = 152677201.133333333... and the second 327653072.39.

Are they the result of some arithmetic calculation?

My second thought would be concerned with the limited precision of numeric variables in general when so many decimal digits are involved. A closer look confirms that at least the second number, b, is beyond the limit mentioned by @yabwon and @PaigeMiller: Translated back to the decimal system, the internal binary 64-bit floating-point representation (see BINARY64. format) of that number under Windows or Unix stands for

327653072.38999998569488525390625

So the eighth decimal place (8) is not correctly represented. In fact, the least significant bit of the internal representation has a place value of 2**-24≈5.96E-8, which is the difference between b and the largest (64-bit floating-point) number less than b,

327653072.389999926090240478515625

or the smallest number greater than b,

327653072.390000045299530029296875

The first number, a, is properly represented, though, in spite of its 16 decimal digits: The decimal equivalent of the internal representation is

152677201.1333332061767578125

and the least significant bit has a place value of 2**-25≈2.98E-8, which is sufficiently small for the seven decimals 1333332.

If you want to keep the numeric values to the precision as stored by SAS, including the unavoidable numeric representation errors (due to rounding the infinitely many binary digits needed for an exact representation of those decimal fractions), a format showing the internal representation such as BINARY64. (as suggested by @yabwon) or HEX16. might be an option. But your other "application" must be able to read those binary or hexadecimal representations properly.

If this is not the case, @PaigeMiller's suggestion might come to the rescue: While I haven't seen "twice the normal precision" in the DS2 documentation, the BIGINT data type should accommodate all the 17 decimal digits of b in an integer value. (Note the rounding errors that a naive integer conversion can produce, though, e.g., 100*0.07 ne 7.)

However, I think it would be simpler to write the integer and fractional parts of the numbers separately:

348   data _null_;
349   do x=152677201.1333332, 327653072.38999998;
350     i=int(x);
351     f=x-i;
352     length c $65;
353     if -1<x<0 then c=put(f,32.29);
354     else c=catx('.',put(i, 32.),scan(put(abs(f), 32.30),2,'.'));
355     put c;
356   end;
357   run;

152677201.133333206176750000000000000000
327653072.389999985694880000000000000000

Edit: In the first version of this post I used the BEST32. and BEST16. format, respectively, for the integer and fractional parts, but this can cause problems in cases where those formats switch to scientific notation. Example: 0.00000123 would be written as 1.23E-6.

Also, the sign of negative numbers between -1 and 0 would have been lost.

Caution: The suggested code above fails for extremely large and extremely small numbers which require scientific notation, e.g., 1.23E50 or 1.23E-50.