topic Re: 0 divided by 0 in an array in New SAS User

0 divided by 0 in an array

PierreYvesILY — Tue, 03 Sep 2019 16:35:29 GMT

Hello,

I wrote the following code to calculate ratios with data included in a Dataset called 'CABU'. Many values in CABU are empty or 0.

The problem: the following code doesn't work properly at least when both variables (n and d) are empty ; the result I get is a void instead of a value = 0.

How should I handle this?

Thank you in advance

data CABU_array;

set CABU;

array n ABL_Anzahl_Akt ABL_Anzahl_Gen ABL_mit_DEROU

MTB_NGV MTB_RSV_Anz ABL_Anzahl_Gen_Online

MTB_Anzahl MTB_Anzahl_Online MTB_Anzahl_RKR

ABL_Anzahl_Gruen MTB_Vertriebserfolg AAb_Volumen

ABL_Anzahl_Gen_Verm MTB_NGV_Online /* MTB_NGV_Verm */

MTB_RSV_Anz_Online /* MTB_RSV_Anz_Verm */;

array d ABL_Anzahl_vzk ABL_Anzahl_Akt ABL_Anzahl_Akt

MTB_Anzahl MTB_Anzahl ABL_Anzahl_Akt_Online

ABL_Anzahl_Gen ABL_Anzahl_Gen_Online ABL_Anzahl_VZK

ABL_Anzahl_Gen ABL_Anzahl_VZK AAb_Anzahl

ABL_Anzahl_Akt_Verm MTB_Anzahl_Online /* MTB_Anzahl_Verm */

MTB_Anzahl_Online /* MTB_Anzahl_Verm */;

array r ABL_vzk_Ratio ABL_Gen_Quote ABL_DEROU_Quote

MTB_TICKET_AVG RSV_QUOTE ABL_Gen_Quote_Online

MTB_AuszahlumtbQ MTB_AuszahlumtbQ_Online MTB_Anzahl_VZK

ABL_GRUEN_Quote MTB_VOL_je_VZK AAb_TktGR

ABL_Gen_Quote_Verm MTB_TICKET_AVG_Onl /* MTB_TICKET_AVG_Verm */

RSV_QUOTE_Onl /* RSV_QUOTE_Verm */;

do i=1 to dim(n) ;

if missing(n[i]) and not missing(d[i]) then do;

n[i] = 0;

r[i] = 0;

end;

if missing(d[i]) and not missing(n[i]) then do;

d[i] = 0;

r[i] = 1;

end;

if missing(d[i]) and missing(n[i]) then do;

n[i] = 0;

d[i] = 0;

r[i] = 0;

end;

else do;

r[i] = n[i]/d[i];

end;

format abl: mtb: AAb: commax17.4;

run;

proc sort data=cabu_array (drop=i); by stufe marktregion_bt nlbez_bt mbrbez_bt jahr monat; run;

Re: 0 divided by 0 in an array

Kurt_Bremser — Tue, 03 Sep 2019 16:43:46 GMT

Check if both values are zero and set the result to zero per force. 0/0 is undefined by mathematical convention, so SAS is right here (https://brilliant.org/wiki/what-is-0-0/).

And SAS will always return a missing value when a division by zero is detected, because SAS has no value for infinite.

Re: 0 divided by 0 in an array

PierreYvesILY — Tue, 03 Sep 2019 17:03:09 GMT

Actually, the code I wrote to do this doesn't work: when both values n and d are 0, the result is still void, instead of beeng set to 0.

The code used is the following:

data CABU_array;

set CABU;

array n ABL_Anzahl_Akt ABL_Anzahl_Gen ABL_mit_DEROU

MTB_NGV MTB_RSV_Anz ABL_Anzahl_Gen_Online

MTB_Anzahl MTB_Anzahl_Online MTB_Anzahl_RKR

ABL_Anzahl_Gruen MTB_Vertriebserfolg AAb_Volumen

ABL_Anzahl_Gen_Verm MTB_NGV_Online /* MTB_NGV_Verm */

MTB_RSV_Anz_Online /* MTB_RSV_Anz_Verm */;

array d ABL_Anzahl_vzk ABL_Anzahl_Akt ABL_Anzahl_Akt

MTB_Anzahl MTB_Anzahl ABL_Anzahl_Akt_Online

ABL_Anzahl_Gen ABL_Anzahl_Gen_Online ABL_Anzahl_VZK

ABL_Anzahl_Gen ABL_Anzahl_VZK AAb_Anzahl

ABL_Anzahl_Akt_Verm MTB_Anzahl_Online /* MTB_Anzahl_Verm */

MTB_Anzahl_Online /* MTB_Anzahl_Verm */;

array r ABL_vzk_Ratio ABL_Gen_Quote ABL_DEROU_Quote

MTB_TICKET_AVG RSV_QUOTE ABL_Gen_Quote_Online

MTB_AuszahlumtbQ MTB_AuszahlumtbQ_Online MTB_Anzahl_VZK

ABL_GRUEN_Quote MTB_VOL_je_VZK AAb_TktGR

ABL_Gen_Quote_Verm MTB_TICKET_AVG_Onl /* MTB_TICKET_AVG_Verm */

RSV_QUOTE_Onl /* RSV_QUOTE_Verm */;

do i=1 to dim(n) ;

if missing(n[i]) and not missing(d[i]) then do;

n[i] = 0;

r[i] = 0;

end;

if missing(d[i]) and not missing(n[i]) then do;

d[i] = 0;

r[i] = 1;

end;

if missing(d[i]) and missing(n[i]) then do;

n[i] = 0;

d[i] = 0;

r[i] = 0;

end;

if d[i] = 0 and n[i] = 0 then do;

r[i] = 0;

end;

if d[i] = 0 and n[i] ne 0 then do;

r[i] = 1;

end;

if d[i] ne 0 and n[i] = 0 then do;

r[i] = -1;

end;

else do;

r[i] = (n[i]-d[i])/d[i];

end;

format orb: ngs: commax17.2;

run;

how can I handle this?

Re: 0 divided by 0 in an array

Kurt_Bremser — Tue, 03 Sep 2019 17:29:34 GMT

That's what this code does:

if d[i] ne 0 and n[i] = 0 then do;
  r[i] = -1;
end;
else do;
  r[i] = (n[i]-d[i])/d[i];
end;

When both are zero, the calculation is performed and returns a missing value. I guess you want a chain of

if .... then ....
else if ..... then ...
else if ..... then ....
else ....

Re: 0 divided by 0 in an array

PierreYvesILY — Tue, 03 Sep 2019 17:34:11 GMT

Yes, than would be a good answer, but I don't know how to write it properly.

In addition, what's wrong in the following code:

if missing(d[i]) and missing(n[i]) then do;

n[i] = 0;

d[i] = 0;

r[i] = 0;

end;

if d[i] = 0 and n[i] = 0 then do;

r[i] = 0;

end;

why is this code not working?

Re: 0 divided by 0 in an array

Tom — Tue, 03 Sep 2019 17:45:53 GMT

In general it is zeros in the DENOMINATOR that cause trouble with division.

So just test that one. Your test is not finding the cases with non-zero numerators that still have zero denominators.

Try this much simpler version:

d[i]=coalesce(d[i]),0);
n[i]=coalesce(n[i]),0);

if d[i]=0 then r[i]=0;
else r[i]=n[i]/d[i];

You can also use the DIVIDE() function. It will return a number of different types of missing values, check the documentation. But you can use COALESCE() to convert them all to zero if you want.

d[i]=coalesce(d[i]),0);
n[i]=coalesce(n[i]),0);
r[i]=coalesce(divide(n[i],d[i]),0);

Re: 0 divided by 0 in an array

PierreYvesILY — Wed, 04 Sep 2019 13:28:45 GMT

Thanks a lot for your response.

I got inspired by your script and finally wrote the following, because I need some particular results:

- if n(i) = 0 and d(i) = 0, the results r(i) is 0

- if n(i) = 0 and d(i) <> 0, the result is -1 (say: -100%)

- if n(i) <> 0 and d(i) = 0, the result is 1 (say: +100%)

- if both n and d are <> 0, the result is (n(i)-d(i))/d(i)

So, my final scrip - which works, is the following:

do i=1 to dim(n) ;

d[i]=coalesce(d[i],0);

n[i]=coalesce(n[i],0);

r[i] = (n[i]-d[i])/d[i];

if n[i] = 0 then r[i] = coalesce(r[i],0);

else r[i] = coalesce(r[i],1);

end;

Thanks a lot, I didn't know the coalesce Function, and your suggestion helped a LOT.

regards,

Re: 0 divided by 0 in an array

Tom — Wed, 04 Sep 2019 13:45:05 GMT

Note that your problem description can be converted directly into an IF/THEN/ELSE/IF... decision tree.

if n=0 and d=0 then r=0;
else if n=0 then r=-1;
else if d=0 then r=1;
else r=(n-d)/d;

NOTE: That I eliminated the array index to make it easier to type and read.