Data Structure and Algorithm of SAS

[Sebestian]

Hello There,

I'm a new user of SAS programming, and now I've some trouble related calculation values of SAS.

I use SAS Enterprise Guide to calculate some values and I discovered that it's unreasonable sometimes.

As an example, see below.

>>>>>          D = Divide(FLOOR(B),FLOOR(C))

 

If anyone has an answer or the way to solve this case, please describe or explain me.

I can't thank you enough for your help.

[PaigeMiller]

The value of either B or C (or both) is not an exact integer (even though it displays as an integer). B is less than C. Change the formatting of B and C to something like best32. to see what the true values are.

[Tom]

Please show the log from the full code you ran.

Please post data as text, not photographs.

[Reeza]

I think you need to use ROUND for what you intend.

   D = Divide(round(B,1),round(C,1))

---

@Sebestian wrote:

Hello There,

I'm a new user of SAS programming, and now I've some trouble related calculation values of SAS.

I use SAS Enterprise Guide to calculate some values and I discovered that it's unreasonable sometimes.

As an example, see below.

>>>>>          D = Divide(FLOOR(B),FLOOR(C))

 

If anyone has an answer or the way to solve this case, please describe or explain me.

I can't thank you enough for your help.

---

[ballardw]

Or try the FLOORZ function.

If a value is very close to an integer , i.e. within 1E-12 or so, then the Floor function can actually round up.

data example;
   x= 1.999999999999;
   y=floor(x);
   z=floorz(x);
run;

Note that y is 2 and z is 1 in the result.

So if your two variables are slightly off of the shown picture the results of Floor may be different than expected.

63   data example;
64      z= 17687/17688;
65      put z= best32.;
66   run;

z=0.9999434644957

This Z value agrees with your result when rounded to 9 decimal places so is likely the actual values used in the division shown.

[FreelanceReinh]

Hello @Sebestian and welcome to the SAS Support Communities!

Looking at the result in variable d (knowing from the formula that it is a ratio of integers, both of which must be close to 17688), it is easy to guess and then to verify that d=17687/17688. So, the question remains why floor(b)=17687 while b is displayed as 17688 (most likely in a default format such as BEST12.).

Of course, a small rounding error in a calculation might result in something like b=17687.99999..., but doesn't the documentation of the FLOOR function reassure us that the result is "fuzzed to avoid unexpected floating-point results"? In the "Details" section it says: "If the argument is within 1E-12 of an integer, the function returns that integer." What is the smallest possible rounding error that would diminish the exact result 17688? Let's look at the internal binary floating-point representation of 17688 (using SAS 9.4 under Windows):

707  data _null_;
708  x=17688;
709  put x binary64. / x hex16.;
710  run;

0100000011010001010001100000000000000000000000000000000000000000
40D1460000000000

Since 2**14 = 16384 <= 17688 < 32768 = 2**15, we know (from Numerical Accuracy in SAS Software) that the place value of the least significant bit (highlighted above) is 2**(14-52) = 2**-38 ≈ 3.6E-12. So it's greater than 1E-12 and thus escaping the fuzzing algorithm. This means that already b=input('40D145FFFFFFFFFF',hex16.) -- the largest internal numeric value less than 17688 -- yields floor(b)=17687.

How can a result like 40D145FFFFFFFFFF (in HEX16. format) arise? Easily! As shown below:

812  data test;
813  x=37688.2;
814  y=20000.2;
815  b=x-y;
816  if b ne 17688 then put 'Surprised?';
817  f=floor(b);
818  r=round(b,1e-9);
819  diff=r-b;
820  put b=best32.-L / b=32.26 / b=hex16. / r=hex16. / f= / diff=;
821  run;

Surprised?
b=17688
b=17688.00000000000000000000000000
b=40D145FFFFFFFFFF
r=40D1460000000000
f=17687
diff=3.637979E-12

Even the longest formats such as BEST32. conceal (due to rounding!) that b ne 17688, which is only revealed by special formats like HEX16. or by looking at the difference diff (=2**-38). Rounding with a suitable rounding unit greater than diff yields the desired result (see variable r).

The mathematically exact value stored in b in the above dataset test, i.e. 17688-2**-38, is

17687.99999999999636202119290828704833984375

Bottom line: @PaigeMiller nailed it. B is not an exact integer.

[mkeintz]

Study this. There will be a quiz in the morning.