Proc compare numeric (decimal) rounding up in the Base data set

[Yeti]

Hello all,

I am very happy to find this forum to ask for help.  I am fairly new to SAS and I was wondering why my compare is failing on me. 

I would really apprecate if somebody could help me figure out why.

Code used was regular:

proc compare data=source2 compare=compare2
    maxprint = (40, 3000);
quit;

It fails on this particular field:

Obs ||  OUT_TOTAL  OUT_TOTAL      Diff.     % Diff                                     

       ||   _CHARGES   _CHARGES                                                           

__   ||  _________  _________  _________  _________                                     

      ||                                                                                 

12  ||       9386    9385.79  1.819E-12  1.938E-14                                     

16  ||      48661   48661.04  7.276E-12  1.495E-14                                     

22  ||      46133   46133.16  7.276E-12  1.577E-14                                     

23  ||      68121   68121.47  1.455E-11  2.136E-14                                     

24  ||      68121   68121.47  1.455E-11  2.136E-14                                     

25  ||      68121   68121.47  1.455E-11  2.136E-14                                     

30  ||      33271   33270.66  7.276E-12  2.187E-14 

When I query the values in the source2 table, the values are in decimal, e.g. 9385.79, 48661.04 but for some reason it is rounding up in the compare proc.  Interestingly, there are other decimal values that had match with the compare2 table.  Both the fields are NUM data types.  I formated the format and informat the compare to the same as compare and I got this report:

Obs ||  OUT_TOTAL  OUT_TOTAL      Diff.     % Diff                                     

       ||   _CHARGES   _CHARGES                                                           

__   ||  _________  _________  _________  _________                                     

      ||                                                                                 

12  ||     9385.79          9385.79          1.819E-12  1.938E-14                                     

16  ||    48661.04         48661.04         7.276E-12  1.495E-14                                     

22  ||    46133.16        46133.16         7.276E-12  1.577E-14                                     

23  ||    68121.47        68121.47          1.455E-11  2.136E-14                                     

24  ||    68121.47       68121.47          1.455E-11  2.136E-14                                     

25  ||    68121.47       68121.47          1.455E-11  2.136E-14                                     

30  ||    33270.66       33270.66          7.276E-12  2.187E-14 

I really don't understand why it is not matching.

Digging in the code for hex values, I got this using

s.OUT_TOTAL_CHARGES format hex16

s.out_total_charges	hex value	c.out_total_charge	hex value	Equal True or False
9385.79	40C254E51EB851EB	9385.79	40C254E51EB851EC	0
48661.04	40E7C2A147AE147A	48661.04	40E7C2A147AE147B	0
46133.16	40E686A51EB851EB	46133.16	40E686A51EB851EC	0
68121.47	40F0A197851EB851	68121.47	40F0A197851EB852	0
33270.66	40E03ED51EB851EB	33270.66	40E03ED51EB851EC	0

Obviously, the hex values are not same hence they are not matching in the compare proc.

Has anybody come across  something like this?  Does NUM data type contain non printable values as well?  My impression was it was possible only in the CHAR data type.

I would really appreciate if you could help me figure this out.

Thank you very much.

Sincerely,

Yeti

[art297]

Numeric precision problem.  Include a fuzz factor, e.g.

proc compare data=A compare=original brief

fuzz=.001;

run;

[Yeti]

Thank you very much for your response.  Unfortunately, I am still getting unmatched values. 

I tried all the variations such as fuzz=.01  .001  .0001  .00001 etc, still they all gave me unmatched values.

I will dig around more on this fuzz modifier.  This is something new I learnt.

Thank you.

[art297]

Then, two more options to look at: criterion and method.

Both are described at: http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a000146742.htm#a00247...

[Yeti]

Thank you art for the information.  I needed that to explain to my team why criterion and method worked.

Kiwi, thanks mate.  It worked like a charm.  You da Kiwi!

Time to go home now.

Cheers,

Yeti

[SASKiwi]

Try the following.

The COMPARE is reporting differences in the 12th decimal place so the differences are tiny. Setting the criterion will force COMPARE to treat these differences as "equal" or "matched". Please note the source data still contains precision differences so if you tried matching them in a data join then they would not match.

proc compare  base = compare1

             compare = compare2

             out = difs

             OUTNOEQUAL LISTEQUALVAR LISTCOMPVAR LISTBASEVAR

             MAXPRINT=300

             method = absolute

             criterion = 0.0001  

             ;

run;

[dsmall2112]

this was helpful, thanks.  I was trying to get more information on METHOD= options (exact, absolute, relative, percent) but the SAS 9.4 PROC COMPARE Syntax documentation (link) under "Option(s)" didn't list "METHOD=" 😞

Even with CRITERION=0.001 I am still getting differences reported.  When I try the METHOD=ABSOLUTE (as in above code suggestion) I get zero differences, including records where I expect differences 😞 so "absolute" must be doing something I don't expect.  Thus the desire for more info on the options.

Thanks!