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

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

Yeti — Tue, 16 Aug 2011 21:27:44 GMT

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

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

art297 — Tue, 16 Aug 2011 21:32:24 GMT

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

proc compare data=A compare=original brief

fuzz=.001;

run;

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

Yeti — Tue, 16 Aug 2011 21:59:35 GMT

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 compare numeric (decimal) rounding up in the Base data set

art297 — Tue, 16 Aug 2011 22:13:27 GMT

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#a002473384

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

SASKiwi — Tue, 16 Aug 2011 22:26:22 GMT

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;

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

Yeti — Tue, 16 Aug 2011 23:07:03 GMT

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

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

dsmall2112 — Mon, 12 Dec 2016 15:55:25 GMT

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!