When using Sum function on dollar amounts additional decimal places are introduced

Accepted Solution Solved
Reply
Super Contributor
Posts: 334
Accepted Solution

When using Sum function on dollar amounts additional decimal places are introduced

Im sure this might be an easy question that we cant see right now being in the midst of problem, but we are summing a numeric variable using a number of methods (sum function sql, proc means, data step with retain) by an id.

Looking at the raw data there appears to be nothing beyond two decimal places in the data. We confirm this after getting it in sas by taking the difference of the variable round to 2 places and the variable without rounding and get no differences between the two.

However after we sum by any of the methods and we take the difference between the rounded sum and the actual sum we are getting differences.

We cant figure out why this is happening or if we are causing it somehow.


Thanks


EJ


Accepted Solutions
Solution
‎10-07-2013 09:05 AM
Respected Advisor
Posts: 3,799

Re: When using Sum function on dollar amounts additional decimal places are introduced

http://support.sas.com/techsup/technote/ts654.pdf


17         data _null_;
18            array a[10] (10*.1);
19            retain b 1;
20            c = sum(of a
  • );
  • 21            put (b c)(=hex16.);
    22            put (b c)(=best32.);
    23            run;

    b=3FF0000000000000 c=3FEFFFFFFFFFFFFF
    b=
    1 c=1

    View solution in original post


    All Replies
    Respected Advisor
    Posts: 3,156

    Re: When using Sum function on dollar amounts additional decimal places are introduced

    Eric, not sure if this is your case, but it has been long known that there could be some precision issue if 'minus' operation or summary with negative numbers involves.  Please see the following example:

    data _null_;

    a=2.16;b=-1.17;

    raw_c=sum(a,b);

    round_c=round(raw_c,0.01);

    put raw_c= round_c=;

    if raw_c=round_c then put "equal" ; else put "not equal";

    run;

    They are indeed not equal to SAS if you put hex20. to check it out. The safe way to deal with it is to use round() to maintain the precision level.

    Haikuo

    Super User
    Posts: 5,516

    Re: When using Sum function on dollar amounts additional decimal places are introduced

    Another approach to dealing with the problem:

    Multiply all values by 100.  Round to the nearest integer.  Sum.  Divide the sum by 100.

    Precision issues are nearly nonexistent when dealing with integers. 

    Super Contributor
    Posts: 334

    Re: When using Sum function on dollar amounts additional decimal places are introduced

    Posted in reply to Astounding

    Thanks ! Something like this is where we will end up Im sure. We are actually trying to validate a stored procedure in SQL using SAS so we were trying to reduce the rounding as much as possible to eliminate steps were mismatching can occur.

    EJ

    Super Contributor
    Posts: 334

    Re: When using Sum function on dollar amounts additional decimal places are introduced

    Thanks for the response but our values are all positive or zero.

    EJ

    Solution
    ‎10-07-2013 09:05 AM
    Respected Advisor
    Posts: 3,799

    Re: When using Sum function on dollar amounts additional decimal places are introduced

    http://support.sas.com/techsup/technote/ts654.pdf


    17         data _null_;
    18            array a[10] (10*.1);
    19            retain b 1;
    20            c = sum(of a
  • );
  • 21            put (b c)(=hex16.);
    22            put (b c)(=best32.);
    23            run;

    b=3FF0000000000000 c=3FEFFFFFFFFFFFFF
    b=
    1 c=1
    Respected Advisor
    Posts: 3,156

    Re: When using Sum function on dollar amounts additional decimal places are introduced

    Posted in reply to data_null__

    Bingo!

    Super Contributor
    Posts: 334

    Re: When using Sum function on dollar amounts additional decimal places are introduced

    Posted in reply to data_null__

    Thanks everyone ... I thought it was something like that but I couldnt explain it very well but the technote was perfect

    EJ

    🔒 This topic is solved and locked.

    Need further help from the community? Please ask a new question.

    Discussion stats
    • 7 replies
    • 913 views
    • 0 likes
    • 4 in conversation