topic Proc Compare- Why two same integer values are different? in SAS Programming

Proc Compare- Why two same integer values are different?

XPrime — Tue, 24 Apr 2018 08:40:28 GMT

Hi everyone, I got a question about proc compare. Why two same integer values are seen different in my compared datasets ?

_______________________________________________________________

|| Base Compare

Obs || Tab1 Tab2 Diff. % Diff

____________ || _________ _________ _________ _________

710779 || 4761 4761 -9.09E-13 -1.91E-14

Thanks in advance.

Re: Proc Compare- Why two same integer values are different?

RW9 — Tue, 24 Apr 2018 08:50:29 GMT

This has been covered many times. SAS stores numbers in 8 bits. I can't remember the exact maths about it, but sometimes a number gets stored which has a very small tiny fraction part due to calculations, so your raw number maybe:

4761

4761.000000000000001

Utilise the round, int, floor etc. functions on it - in general in any calculation I round or otherwise fix the result to make sure. You can use the fuzz option:

https://communities.sas.com/t5/SAS-Procedures/Proc-compare-showing-matching-values-even-after-using-OUTNOEQUAL/m-p/413334#M67459

But in general I would recommend fixing the number.

Re: Proc Compare- Why two same integer values are different?

SASKiwi — Tue, 24 Apr 2018 09:55:34 GMT

As @RW9 says this comes up all the time and there are many reference to the issue of numeric precision in the documentation including this:

http://documentation.sas.com/?docsetId=hostwin&docsetTarget=n04ccixfia6l2pn1f8szvttqg3hm.htm&docsetVersion=9.4&locale=en

Because SAS stores numerics in 8 bytes of memory / disk it can only accurately represent any number for the first 15 digits (reading left to right). PROC COMPARE is quite accurately reporting differences beyond the first 15 digits. You can control what PROC COMPARE reports as "numerically equal" by tweaking the CRITERION = and METHOD = options:

http://documentation.sas.com/?docsetId=proc&docsetTarget=n0c1y14wyd3u7yn1dmfcpaejllsn.htm&docsetVersion=9.4&locale=en

Re: Proc Compare- Why two same integer values are different?

ballardw — Thu, 26 Apr 2018 15:42:39 GMT

One or both of those values in not an integer. The Proc compare output shows values using a default format that apparently does not want to show 12 decimal places for them.

If you think that variable should be an integer round or truncate it before use/compare.

If you don't think that a difference like 0.000000000909 will not cause problems for further use you could also run your proc compare code with the FUZZ= numericvalue option to ignore very small results.

Example fuzz= 1E-8 would treat any absolute difference lest than 0.00000001 as "good enough" and not report as a difference in the comparison.

Re: Proc Compare- Why two same integer values are different?

XPrime — Thu, 26 Apr 2018 13:36:32 GMT

Thank you @RW9 @ballardw @SASKiwi ! It really helped me.