CODEWITHSUNDEEP
db2sas
Nick Herro
Nick Herro

Reputation: 23

SAS getting wrong result when subtracting two values

All,

I'm running into some issues related to how SAS is processing the subtraction of two two-decimal variables. These results are written to a DB2 database. All of the fields being used here are imported to SAS and written to DB2 fields with a datatype of DECIMAL(19,2) Here is the issue:

             AL_AMT       - PL_AMT       = DIF_AMT
 From SAS:   9,918,322.38 - 9,942,322.30 = (23,999.91)
 Expected:   9,918,322.38 - 9,942,322.30 = (23,999.92)

Here are some very trimmed snippets of code. SAS is quirky, no doubt. I'm hoping that someone can help me discover which of its many quirks may be causing this.

/* CAmt and PPmt are retrieved from a lengthy PROC SQL statement, */
/* their formats are unaltered.                                   */
data WORK.TABLE1;
set WORK.TABLE0;
Difference = CAmt - PPmt;
run;

data WORK.TABLE2(keep=Rep:);
 set WORK.TABLE1 end=last;

If _N_=1 then do;
    Rep1CAmt=0;
    Rep1PPmt=0;
    Rep1Diff=0;

end;

  Rep1CAmt+CAmt;
  Rep1PPmt+PPmt;
  Rep1Diff+Difference;

if last;

Rep1Diff=Rep1CAmt-Rep1PPmt;
Rep1Diff=round(Rep1Diff,.01);

/* I realize these two lines are redundant/unnecessary, but I was trying 
   different things to get the numbers to add up correctly, no such luck */    

run;

data WORK.TABLE3;
set work.TABLE2;
AL_AMT=round(Rep1CAmt,.01);
PL_AMT=round(Rep1PPmt,.01);
DIF_AMT=AL_AMT-PL_AMT;
run;

proc append data=WORK.TABLE3 base=LIBNAME1.DB2TABLE(drop=ID) force; 
run;

Upvotes: 2

Views: 4157

Answers (1)

Joe
Joe

Reputation: 63424

Of course, SAS doesn't get that direct subtraction wrong:

data test;
x=9918322.38;
y=9942322.30;
z=x-y;
put _all_;
run;

Odds are you have some numeric precision issues here from earlier calculations (or from the translation from DB2?). Think about the following in decimal notation:

1 - (2/3) = 0.333

0.333 + (1/3) = 0.666

0.666 + (1/3) = 0.999

Binary arithmetic has similar, but nonidentical, issues. In a very few cases, when doing certain kinds of math, you end up with a number like 1.0000000000000000000001423 instead of 1. Thus when you compare the two numbers, or you do further math, you might not get the answer you expect.

To avoid this issue, you have a few options, all of which boil down to using some form of rounding. You can round the number off at some earlier point in the calculations that will not hurt your accuracy, but might avoid this particular issue; you can use the FUZZ function or one of its brethren, which is specifically designed for this purpose (it returns the nearest integer if a number is within 1E-12 of an integer - if you're dealing with decimal values, though, you may not be able to use this). ROUNDZ (one of the fuzz family of functions) might also be helpful - this example is cobbled from the manual page on ROUNDZ but modified to round to 2.50 or 2.51 rather than 2 or 3.

data test;
format value round roundz BEST32.;
   do i=12 to 19;
      Value=2.505 - 10**(-i);
      Roundz=roundz(value,0.01);
      Round=round(value,0.01);
      output;
   end;
   do i=18 to 12 by -1;
      value=2.505 + 10**(-i);
      roundz=roundz(value,0.01);
      round=round(value,0.01);
      output;
   end;
run;

Since you're using floating point numbers, I'd recommend rounding to something in the 1E-12 range - so, [number] = roundz([number],1E-12);

That will generally cut off the fuzziness and make sure your numbers consistently behave. You might need to pick something slightly larger, like 1E-10 - I'm only really familiar with solving this for integer math cases, for FP cases I think it's theoretically the same but not entirely confident.

Upvotes: 3

Related Questions