CODEWITHSUNDEEP
sqldatabasesasroundingproc
juniperbushes
juniperbushes

Reputation: 17

Dealing with rounding errors in SAS

I'm working on a large data application and have run into a rounding error that I can't seem to fix. The code is basically like this:

proc sql;
select round(amount,.01) - round(amount * (percentage/100),.01) as amount
from data;
quit;

I've tried various methods of fixing, but all seem to lead to other rounding errors in the other direction cropping up. For the row that produces the error amount = 56.45 and percentage = 10. I get the result equal to 50.80 and am hoping for the result to equal 50.81. I cannot accept the rounding error as there is a separate process that reverses the transactions that does not have a rounding error and in the end the reversals plus the part producing the rounding error must add up to zero.

Code I've tried:

select round((((100-percentage)/100)*amount), .01)
select round(amount,.01) - round(amount * (percentage/100),.001) as amount

the second of which fixes the issue but creates three rounding errors in the other direction.

Any help is very appreciated.

Thank you.

Upvotes: 0

Views: 1280

Answers (1)

APH
APH

Reputation: 4154

Without knowing your datatypes, I can't say for certain, but here are some changes that should help resolve your issue:

  1. Make sure you are working with decimal data types, not floats.
  2. Round after you finish the math. You are rounding each step of your calculation in two of your code snippets, which is likely to produce incorrect results.
  3. Be very careful with your order of operations/parentheses. For example, 100-percentage/100 evaluates to 100-10/100 = 100-0.1 = 99.9, which I think is not what you want. Similarly, you have one more close parenthesis than open on that line.

Upvotes: 5

Related Questions