topic Formula evaluates(totals) the same set of variables differently whereas I should get same result in SAS Programming

Formula evaluates(totals) the same set of variables differently whereas I should get same result

Q1983 — Thu, 12 Aug 2021 19:51:06 GMT

data have(keep= ln_no BKR_TRUSTEE_BA BKR_PPPMT_SUSP_BA BKR_DEBTOR_BA LN_SUSPENSE_BA MSP_Susp_BK_Sus);
set Suspense3;
if BKR_TRUSTEE_BA + BKR_PPPMT_SUSP_BA + BKR_DEBTOR_BA = LN_SUSPENSE_BA then MSP_Susp_BK_Sus = 1 ;else MSP_Susp_BK_Sus = 0;
if ln_no in ('9212','0129');
run;

The output is as follows:

LN_NO	LN_SUSPENSE_BA	BKR_DEBTOR_BA	BKR_TRUSTEE_BA	BKR_PPPMT_SUSP_BA	MSP_Susp_BK_Sus
9612	315.07	43.59	233.71	37.77	0
129	223.02	223.02	0.00	0.00	1

Please forgive as I cannot replicate the issue using sashelp or a sample dataset. I ran this from an existing program and modified the ln_no for security.. if BKR_TRUSTEE_BA + BKR_PPPMT_SUSP_BA + BKR_DEBTOR_BA = LN_SUSPENSE_BA then MSP_Susp_BK_Sus = 1 ;else MSP_Susp_BK_Sus = 0. as you can see it works for the second ln_no 129 however I should also get a 1 for the first entry too. Yet I get a 0. It appears sas is saying the there is a difference based on the above formula. However there is no difference. Does anyone know why sas would take the exact same statement and evaluate them differently??

Re: Formula evaluates(totals) the same set of variables differently whereas I should get same result

PaigeMiller — Thu, 12 Aug 2021 21:00:43 GMT

Possible machine precision issue?

See: https://communities.sas.com/t5/SAS-Programming/comparing-two-values/m-p/761113#M240787

Try this:

if fuzz(BKR_TRUSTEE_BA + BKR_PPPMT_SUSP_BA + BKR_DEBTOR_BA - LN_SUSPENSE_BA) = 0 then MSP_Susp_BK_Sus = 1 ;

Re: Formula evaluates(totals) the same set of variables differently whereas I should get same result

Reeza — Thu, 12 Aug 2021 20:42:09 GMT

If it's possible for any of those values to be missing you may want to use a SUM() function instead of operator as well.

Re: Formula evaluates(totals) the same set of variables differently whereas I should get same result

ballardw — Thu, 12 Aug 2021 20:52:54 GMT

For future reference, SAS will return 1 for true and 0 for false for a comparison, so instead of

if BKR_TRUSTEE_BA + BKR_PPPMT_SUSP_BA + BKR_DEBTOR_BA = LN_SUSPENSE_BA then MSP_Susp_BK_Sus = 1 ;else MSP_Susp_BK_Sus = 0;

You might try

MSP_Susp_BK_Sus = Sum( BKR_TRUSTEE_BA, BKR_PPPMT_SUSP_BA, BKR_DEBTOR_BA) = LN_SUSPENSE_BA);

Re: Formula evaluates(totals) the same set of variables differently whereas I should get same result

FreelanceReinh — Fri, 13 Aug 2021 07:56:43 GMT

Hello @Q1983,

@PaigeMiller has nailed it. It's a typical rounding issue due to numeric representation error in the numbers involved.

The DATA step below, using your example numbers, shows how a sum A+B+C can be different from B+C+A on a computer in spite of the well known commutative law. It also demonstrates that standard formats, unlike HEX16., often hide the issue (all sums look the same) and how the ROUND function with a suitable rounding unit can help to avoid the issue. In your case, if there are only up to two decimals, 0.01 can be a suitable rounding unit.

data test;
retain a b c;
input s_ a b c;
s1=a+b+c;
s0=b+c+a;
d=s0-s1;
rs=round(s0,1e-8);
put (s: rs d)(=/)//
    (s: rs)(=hex16./);
cards;
315.07 43.59 233.71 37.77
;

Result (obtained with SAS 9.4 under Windows):

s_=315.07
s1=315.07
s0=315.07
rs=315.07
d=5.684342E-14

s_=4073B11EB851EB85
s1=4073B11EB851EB85
s0=4073B11EB851EB86
rs=4073B11EB851EB85