CODEWITHSUNDEEP
sqlsumsas
PinkyL
PinkyL

Reputation: 351

proc sql sum doesn't add up correctly

I have data that looks like this:

ID  Date1           Date2       Paid Amount
A               1/25/2012   -168.48
A               1/25/2012   -59.44
A               1/25/2012   -13.18
A               1/25/2012   -8.02
A               1/25/2012   8.02
A               1/25/2012   13.18
A               1/25/2012   59.44
A               1/25/2012   168.48
A   12/28/2011  1/25/2012   50.00
A   12/28/2011  1/25/2012   61.00


Proc sql;
Create table SUM as
Select id, date1, date2, sum(paid_amount) as paid_amount
From SUM_0
Group by id, date1, date2;

I get something like this:

ID Date1     Date2     paid_amount
A            1/25/2012 4.547474E-13
A 12/28/2011 1/25/2012 111.00

Just by eyeballing, it's obvious that the sum of the paid_amount for the blank date1, but 1/25/2012 date2 should be 0. For some reason for this and other similar setups, I get various sums with E-13 values.

Upvotes: 1

Views: 2837

Answers (2)

Joe
Joe

Reputation: 63424

As others have noted, this is a floating point issue. Just like:

2/3 - 1/3 - 1/3 = 0

but

.6667 - .3333 - .3333 > 0

Floating point numbers are inherently imprecise.

In SAS, you can deal with this a few different ways. The easiest two:

  • Round. round(sum(...),0.01) will round it to the nearest 0.01, and you can round to the nearest 0.000001 if you like; typically E-12 is where you start seeing floating point precision issues creep in, so any number of zeroes less than 10 or so will do.
  • Fuzz. Fuzz(...) automatically rounds numbers close to an integer to that integer. It doesn't round otherwise, just things close to a real integer become one.

For example:

data test;
  input ID  $ Date1 :mmddyy10. Date2 :mmddyy10. Amount;
  datalines;
A      .         1/25/2012   -168.48
A      .         1/25/2012   -59.44
A      .         1/25/2012   -13.18
A      .         1/25/2012   -8.02
A      .         1/25/2012   8.02
A      .         1/25/2012   13.18
A      .         1/25/2012   59.44
A      .         1/25/2012   168.48
A   12/28/2011  1/25/2012   50.00
A   12/28/2011  1/25/2012   61.00
;;;;
run;

proc sql;
  select id, date1, date2, round(sum(amount),.01)
    from test
    group by 1,2,3;
quit;

Upvotes: 3

shawnt00
shawnt00

Reputation: 17915

You should probably add a cast like numeric(10, 2) inside the sum().

sum(cast(paid_amount as decimal(10, 2)))

Upvotes: 0

Related Questions