BookmarkSubscribeRSS Feed
Calcite | Level 5

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


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 valueEqual True or False
9385.7940C254E51EB851EB   9385.7940C254E51EB851EC   0
48661.0440E7C2A147AE147A   48661.0440E7C2A147AE147B   0
46133.1640E686A51EB851EB   46133.1640E686A51EB851EC   0
68121.4740F0A197851EB851   68121.4740F0A197851EB852   0
33270.6640E03ED51EB851EB   33270.6640E03ED51EB851EC   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.



Opal | Level 21

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

proc compare data=A compare=original brief



Calcite | Level 5

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.

Calcite | Level 5

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! Smiley Happy

Time to go home now.



Opal | Level 21

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



             method = absolute

             criterion = 0.0001  



Obsidian | Level 7

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.





Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1 like
  • 4 in conversation