Help using Base SAS procedures

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

Reply
Contributor
Posts: 32

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

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

PROC Star
Posts: 7,363

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

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

proc compare data=A compare=original brief

fuzz=.001;

run;

Contributor
Posts: 32

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

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.

PROC Star
Posts: 7,363

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

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

Contributor
Posts: 32

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

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

Super User
Posts: 3,110

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

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;

Occasional Contributor
Posts: 15

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

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!

Ask a Question
Discussion stats
  • 6 replies
  • 5740 views
  • 1 like
  • 4 in conversation