topic Re: Retain variable output issue in SAS Programming

Retain variable output issue

Mushy — Tue, 28 Sep 2021 21:49:15 GMT

The last value of "TOT" in the table "Final" should have been 0, but some -ve number updated:

Any idea what is causing the issue?

data test;
format val 23.19;
input key val;
cards;
1 -0.16
1 0.15
1 0.01
;
run;

data Final;
set test;
retain TOT;
by key ;
if FIRST.key then do; TOT = 0;
end;
TOT = TOT+val;
output;
run;

Re: Retain variable output issue

PaigeMiller — Tue, 28 Sep 2021 21:59:26 GMT

Machine precision (or machine epsilon) is the issue. Most non-integer numbers cannot be represented exactly in computer binary arithmetic.

https://en.wikipedia.org/wiki/Machine_epsilon

https://go.documentation.sas.com/doc/en/pgmsascdc/v_014/lepg/p0dv87zb3bnse6n1mqo360be70qr.htm

Re: Retain variable output issue

Mushy — Tue, 28 Sep 2021 22:26:09 GMT

Thank you Miller for the information.

Do we have an alternate/solution for this issue for my example?

Re: Retain variable output issue

ballardw — Tue, 28 Sep 2021 22:53:07 GMT

One way that may be a bit fragile:

data Final;
   set test;
   retain TOT;
   by key ;
   if FIRST.key then do; TOT = 0;
   end;
   tempval = val*100;
   TOT = TOT+tempval;
   if last.key then tot=tot/100;
   output;
run;

This is attempting to use integer arithmetic as much as practical. The fragile part comes partially in knowing the minimum multiplier. There is also some behind the scenes rounding going on. Works for this small example.

If you have enough decimal places this still likely run into precision issues though.

Re: Retain variable output issue

mkeintz — Wed, 29 Sep 2021 02:46:44 GMT

Or you could round each sum as it is calculated. If the highest precision of the original data is in the hundredths, then round to nearest .01, as in:

data test;
format val 23.19;
input key val;
cards;
1 -0.16
1 0.15
1 0.01
run;

data Final;
   set test;
   retain TOT;
   by key ;
   if FIRST.key then do; TOT = 0;end;

   tot=round(sum(tot,val),.01);
   output;
run;

Re: Retain variable output issue

FreelanceReinh — Wed, 29 Sep 2021 12:43:45 GMT

Hello @Mushy,

I mostly use the ROUND function (as mkeintz suggested) in situations like this, often with a very small rounding unit (e.g., 1E-9) -- small enough not to influence results, but large enough to remove the tiny rounding error (!) that would occur otherwise. In your example the absolute rounding error is <1E-17.

Even if you want to switch to integer calculations (as ballardw suggested), you should guard against such tiny rounding errors while performing the conversion to integers, so that you need to round anyway:

data check;
do n=0 to 99;
  val=n/100;
  tempval = val*100; /* no rounding --> non-integers can occur */
  r = round(val*100, 1e-9);
  clean  = (tempval=n);
  cleanr = (r=n);
  output;
end;
run;

proc freq data=check;
tables clean cleanr;
run;

Result (with SAS 9.4M5 under Windows):

                                  Cumulative    Cumulative
clean    Frequency     Percent     Frequency      Percent
----------------------------------------------------------
    0           8        8.00             8         8.00
    1          92       92.00           100       100.00


                                   Cumulative    Cumulative
cleanr    Frequency     Percent     Frequency      Percent
-----------------------------------------------------------
     1         100      100.00           100       100.00