0 divided by 0 in an array

[PierreYvesILY]

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

PY

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;

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;

[Kurt_Bremser · Accepted solution]

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.

[PierreYvesILY]

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;

end;

format orb: ngs: commax17.2;

run;

how can I handle this?

[Kurt_Bremser]

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

[PierreYvesILY]

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?

[Tom]

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);

[PierreYvesILY]

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,

PY

[Tom]

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.