proc sql sum 加起来不正确 [英] proc sql sum doesn't add up correctly

问题描述

我有这样的数据:

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; 

我得到了这样的东西:

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

仅凭眼球，很明显空白日期1的payed_amount总和，但1/25/2012 date2应该是0.出于某种原因，这个和其他类似的设置，我得到了E-13值的各种总和.

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.

推荐答案

正如其他人所指出的，这是一个浮点问题.就像:

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

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

但是

.6667 - .3333 - .3333 > 0

浮点数本质上是不精确的.

Floating point numbers are inherently imprecise.

在SAS 中，您可以通过几种不同的方式来处理这个问题.最简单的两个:

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

• 圆形.round(sum(...),0.01) 会将其四舍五入到最接近的 0.01，如果您愿意，您可以四舍五入到最接近的 0.000001；通常 E-12 是您开始发现浮点精度问题的地方，因此任何数量小于 10 的零都可以.
• 模糊.Fuzz(...) 自动将接近整数的数字四舍五入为该整数.它不会舍入否则，只是接近实数的东西变成一.

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

例如:

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;

这篇关于proc sql sum 加起来不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！