BookmarkSubscribeRSS Feed
Yeti
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);

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

Sincerely,

Yeti

6 REPLIES 6
art297
Opal | Level 21

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

proc compare data=A compare=original brief

fuzz=.001;

run;

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

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

Cheers,

Yeti

SASKiwi
PROC Star

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

 

Thanks!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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