topic Re: Round function NOT WORKING in SAS Programming

Round function NOT WORKING

JohnChen_TW — Fri, 06 Jul 2018 09:30:37 GMT

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

Re: Round function NOT WORKING

SASKiwi — Fri, 06 Jul 2018 03:45:45 GMT

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&docsetVersion=9.4&locale=en

Re: Round function NOT WORKING

Kurt_Bremser — Fri, 06 Jul 2018 05:56:52 GMT

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

Re: Round function NOT WORKING

JohnChen_TW — Fri, 06 Jul 2018 07:37:52 GMT

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.

Re: Round function NOT WORKING

Kurt_Bremser — Fri, 06 Jul 2018 08:05:22 GMT

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.

Re: Round function NOT WORKING

JohnChen_TW — Fri, 06 Jul 2018 08:50:03 GMT

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.

Re: Round function NOT WORKING

FreelanceReinh — Fri, 06 Jul 2018 08:56:54 GMT

@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).

Re: Round function NOT WORKING

Kurt_Bremser — Fri, 06 Jul 2018 09:09:41 GMT

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.

Re: Round function NOT WORKING

JohnChen_TW — Fri, 06 Jul 2018 09:44:08 GMT

@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)?

Re: Round function NOT WORKING

JohnChen_TW — Fri, 06 Jul 2018 09:58:28 GMT

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.

Re: Round function NOT WORKING

FreelanceReinh — Fri, 06 Jul 2018 10:33:34 GMT

@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.)

Re: Round function NOT WORKING

FreelanceReinh — Fri, 06 Jul 2018 13:23:53 GMT

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

Re: Round function NOT WORKING

SASKiwi — Fri, 06 Jul 2018 23:19:45 GMT

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.

Re: Round function NOT WORKING

JohnChen_TW — Mon, 16 Jul 2018 09:24:01 GMT

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

Re: Round function NOT WORKING

SASKiwi — Mon, 16 Jul 2018 09:42:18 GMT

@JohnChen_TW - unfortunately I don't think that is possible with "normal" SAS. Maybe SAS's DS2 is worth checking out? I'm pretty sure that is only available with SAS 9.4 though.

Re: Round function NOT WORKING

JohnChen_TW — Mon, 16 Jul 2018 09:55:36 GMT

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

Re: Round function NOT WORKING

JohnChen_TW — Mon, 16 Jul 2018 10:00:49 GMT

@SASKiwi I've discussed with our vendor and they will provide us the results with 14 decimal places at most in the future. (although I'm still curious how to fix it)

Thanks again for all your replies:)

Re: Round function NOT WORKING

Kurt_Bremser — Tue, 17 Jul 2018 05:48:35 GMT

@JohnChen_TW wrote:

@SASKiwi I've discussed with our vendor and they will provide us the results with 14 decimal places at most in the future. (although I'm still curious how to fix it)

Thanks again for all your replies:)

JC

Since the 15-decimal-digits precision is a hard limit imposed by the use of the 8-byte real format for numerical storage, there is no fix until software that works with numbers switches to 128 bits for numerical storage. Given the non-existent statistical relevance of such digits, I don't expect that switch for statistical software in the near future. You might see something in accounting software, where people would want precision down to cents in amounts like the GDP of the US.