Round function NOT WORKING

[JohnChen_TW]

Hi everyone,

I'm using SAS version 9.3.

I'd like to ask why the number 0.1254931228765450 imported from an excel file cannot be rounded off to 0.12549312287655, but another number 0.8216087968183150 can be rounded off to 0.82160879681832?

My code is X=ROUND(Y, 0.00000000000001); format X BEST32.;

[SASKiwi]

The reason is the numerical precision of SAS number's is limited to a maximum of 15 digits and you are trying to round off at the extremity of the range SAS can handle. So some numbers may work and others may not.

http://documentation.sas.com/?docsetId=lrcon&docsetTarget=p0ji1unv6thm0dn1gp4t01a1u0g6.htm&docsetVer...

[Kurt_Bremser]

You are on the limits of Excel also. Add 10 to that number, and then subtract 10, and then look at the result.

[JohnChen_TW]

Thanks @Kurt_Bremser

I tried it in excel to add 10 to both numbers and got 10.1254895654575450 and 10.8216087968183150, and then subtracted 10 to get 0.1254895654575450 and 0.8216087968183160.

However, their 15th decimal place is still greater than or equal to 5.

I still have no idea why there is the difference between the rounded results of them.

[Kurt_Bremser]

Well, the operation in Excel 2013 with the first number got me these:

0,12548956545754500000

10,12548956545750000000

0,12548956545754600

And the difference between the first and the third turns out to be this:

-0,00000000000000063838

Bottom line: don't expect reliable results beyond 15 decimal digits in any kind of software that uses 8-byte real.

And be aware that the peculiarities of handling decimal fractions in binary can cause some quite interesting effects (Note that the +10 number ends at 54575, and the re-calculated number at the end gets two decimal digits after that "back") anyway.

[JohnChen_TW]

Thank you @Kurt_Bremservery much!
However, could this test method be applied to any decimal number?
I've tried another number 0.1254931228765450 which also cannot be rounded to 0.12549312287655, and I got the same number after +/- 10.

[Kurt_Bremser]

Some numbers will work better because they convert to a finite binary sequence that fits into the 8-byte space. Others will turn into a periodic binary number that can't be converted back reliably.

Those that work are those that either don't need more binary digits or where the (binary) rounding doesn't change the last decimal digit.

[JohnChen_TW]

Thank you, @Kurt_Bremser and @SASKiwi for all your help and reference!

Although I have no any basis of binary arithmetic or decimal arithmetic, I will try to understand it at first. 

[FreelanceReinh]

---

@JohnChen_TW wrote:

I'm using SAS version 9.3.

---

These numeric representation issues are platform dependent. What is your operating environment?

In my Windows operating environment ("X64_7PRO platform", using SAS 9.4) I cannot replicate your results. Both numbers (x1, x2) are rounded correctly: r1, r2 are identical to the numbers you wanted to obtain (w1, w2).

data _null_;
x1=0.1254895654575450;
w1=0.12548956545755;
x2=0.8216087968183150;
w2=0.82160879681832;
r1=round(x1, 0.00000000000001);
r2=round(x2, 0.00000000000001);
put (r:) (=best32. /) /;
put (r1 w1 r2 w2) (=hex16. /);
run;

Result:

r1=0.12548956545755
r2=0.82160879681832

r1=3FC0100AC5D0911D
w1=3FC0100AC5D0911D
r2=3FEA4A9E880E1957
w2=3FEA4A9E880E1957

Please note that format BEST32. does not display numbers with their ultimate (internal) precision. Only formats HEX16. and BINARY64. tell you the absolute truth about the contents of a numeric variable.

For x1 the least significant bit of the internal representation (Windows environment) represents 2**-55 (approx. 2.78E-17). For x2 it represents 2**-53 (approx. 1.11E-16). So, in both cases this is still well below your rounding unit (1E-14).

[JohnChen_TW]

@FreelanceReinhThanks for your reply.

My operating environment is X32_7PRO. I've updated the decimal number I posted previously to be 0.1254931228765450, and I've tried the format HEX16. to get the result as below.

data _null_;
x1=0.1254931228765450;
w1=0.12549312287654;
w2=0.12549312287655;
x3=0.8216087968183150;
w3=0.82160879681832;
r1=round(x1, 0.00000000000001);
r3=round(x3, 0.00000000000001);
put (r:) (=best32. /) /;
put (r1 w1 w2 r3 w3) (=hex16. /);
run;

Result:

r1=0.12549312287654
r3=0.82160879681832

r1=3FC010289D5056CB
w1=3FC010289D5056CB
w2=3FC010289D505834
r3=3FEA4A9E880E1957
w3=3FEA4A9E880E1957

Does the difference between r1 and w2 mean that the number 0.1254931228765450 cannot be rounded to 0.12549312287655 because of the limits of SAS system (maximum of 15 digits)?

[FreelanceReinh]

@JohnChen_TW: Thanks for providing this interesting example. I think, in this case the relevant "limits" are primarily those of the internal workings of the ROUND function.

To quote from the technical paper "Dealing with Numeric Representation Error in SAS® Applications", p. 11:

"If your applications need more control over rounding than is provided for in the ROUND function, you must define your own rounding routine to be used instead of the SAS ROUND function." (Details on how to do this are then presented.)

[FreelanceReinh]

@JohnChen_TW: I've just noticed that you ran into an additional problem: Numeric literals can be written in different ways, e.g. by using scientific notation or not or by appending trailing zeros to the decimals. Although these different notations represent the same value from a mathematical perspective, it can very well happen that SAS interprets these literals differently. That is, the internal representation of the number (visible in HEX16. format) varies.

Example:

data _null_;
if 0.00001>1.0E-5 & 1.00E-5<1.000E-5 then put '?!';
run;

In my Windows operating environment both "nonsense" inequalities are evaluated as true. This is due to two different internal representations. (A couple of years ago I found that the sequence 1.0E-5, 1.00E-5, 1.000E-5, ... switches 96 [!] times between four different internal representations, rather erratically.)

In fact, your latest example, 0.125493122876545, would have been rounded to 0.12549312287655, as desired, if you had entered it as 1.25493122876545e-1. Unfortunately, rounding would have failed again, had you entered it as 1.254931228765450e-1.

A few variations of your example:

data _null_;
array x x4_ x40-x56
  (0.12549312287654
   0.125493122876540
   0.125493122876541
   0.125493122876542
   0.125493122876543
   0.125493122876544
   0.125493122876545
   0.125493122876546
   0.125493122876547
   0.125493122876548
   0.125493122876549
   0.125493122876550
   0.125493122876551
   0.125493122876552
   0.125493122876553
   0.125493122876554
   0.125493122876555
   0.125493122876556);
file print;
put (x:) (=hex16. /);
run;

data _null_;
array x x4_ x40-x56
  (1.2549312287654e-1
   1.25493122876540e-1
   1.25493122876541e-1
   1.25493122876542e-1
   1.25493122876543e-1
   1.25493122876544e-1
   1.25493122876545e-1
   1.25493122876546e-1
   1.25493122876547e-1
   1.25493122876548e-1
   1.25493122876549e-1
   1.25493122876550e-1
   1.25493122876551e-1
   1.25493122876552e-1
   1.25493122876553e-1
   1.25493122876554e-1
   1.25493122876555e-1
   1.25493122876556e-1);
file print;
put (x:) (=hex16. /);
run;

A comparison of the results shows that the values of x40-x47 (but not the values of x4_ and x48-x56) increase by one unit in the least significant bit (2**-55) just by switching to scientific notation. (Your example value is x45.)

Not surprisingly, this minor increase can impact rounding in borderline cases, i.e., when the number to be rounded is halfway between the two nearest multiples of the rounding unit, as is the case in your example.

[JohnChen_TW]

@FreelanceReinhMany thanks for your input and examples.

Although it may be read in different ways by SAS when using scientific notation or appending trailing zero as you said, the number 1.25493122876545e-1 is still rounded off to 0.12549312287654 instead of 0.12549312287655.

JC

[SASKiwi]

I am also curious to know why you have a need for so many significant digits. Is this required for a particular use case? My point being is if you don't need as many significant digits in your application then you can ROUND reliably with less.

[JohnChen_TW]

@SASKiwi Thanks for your input.

Yes, that is a particular use case that our vendor requested.

The decimal places of the measurement results are always 15 or 16, so that is why I'd like to know how to import them in SAS completely and accurately.