Division by 0

[Stepik]

Dear team,

I have a calculated column which includes division of two columns by min/max function as per below: IFN(MIN(first column, second column)/MAX(first column, second column)>=0.9, 1, 0).

Case: In the both columns I have combination of both 0 - meaning 0/0 in the result.

The results show 1 when there are numbers more than 0. 

Show 0 when the both numbers are 0. However, it does not show a warning "NOTE: Division by zero detected at line XXX column XX."

Can someone explain why?

Thanks,
Stepik

[PaigeMiller]

The results show 1 when there are numbers more than 0. 

Show 0 when the both numbers are 0. 

I am not experiencing this. 0 divided by 0 is missing. Which is what you would expect.

[ballardw]

2 comments, first related to apparent 0 and no divide by 0. WHAT is the format assigned to the variable that think has the value of 0? It may just be that the currently assigned format that you have for that variable appears as 0 because of the rounding from the actual value.

Consider this example. The value is not 0 for the variable X but the format assigned will display 0.00 when viewing the data table or proc print or most ways to examine the data as the format defaults to showing no more than 2 decimal places.

data example;
   x= 0.0000009;
   format x 6.2;
run;

Second, if you know that you problematic values and are doing division you may want to consider using the DIVIDE function instead of the division operator.

data example2;
   input x y;
   z=divide(x,y);
datalines;
0  10
10  0
0   0
10  5
5  10
;

The result will show special missing I (for infinity) when dividing by 0 which is treated as missing for other calculations, and a variety of other special missing values depending on combinations of 0 or missing values in the division. But not there are no "divide by 0" errors because the DIVIDE function handles them nicely.

[Tom]

Since you did show your full code perhaps you used SQL instead of a data step?  PROC SQL does not display those notes.

698  data test;
699    do x=.,0,1;
700      do y=.,0,1 ;
701         z1 = IFN(x/y>=0.9, 1, 0);
702         z2 = x/y>=0.9 ;
703         z3 = (x/y)>=0.9 ;
704         z4 = divide(x,y)>=0.9 ;
705         output;
706      end;
707    end;
708  run;

NOTE: Division by zero detected at line 701 column 18.
NOTE: Division by zero detected at line 702 column 14.
NOTE: Division by zero detected at line 703 column 15.
NOTE: Division by zero detected at line 701 column 18.
NOTE: Division by zero detected at line 702 column 14.
NOTE: Division by zero detected at line 703 column 15.
x=1 y=1 z1=1 z2=1 z3=1 z4=1 _ERROR_=1 _N_=1
NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      5 at 701:18   5 at 702:14   5 at 703:15
NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to
      missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      2 at 701:18   2 at 702:14   2 at 703:15
NOTE: The data set WORK.TEST has 9 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


709
710  proc sql;
711   select x,y,x/y>=0.9 as z
712   from test
713   ;
714  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

PS No need for the goofy IFN() function here.  Boolean results are already generated as 1 or 0.

[Stepik]

Hi Tom,

Exactly, I am using a proc SQL since Query Builder (SAS Ent Guide) was used to build the code. What would be the best modification of the code, so that I can assign a character value to the missing values in Proc SQL? For now it shows 1 when true, but 0 in all other cases. I would like to show the "div by 0" values as "missing"

[ballardw]

---

@Stepik wrote:
Hi Tom,

Exactly, I am using a proc SQL since Query Builder (SAS Ent Guide) was used to build the code. What would be the best modification of the code, so that I can assign a character value to the missing values in Proc SQL? For now it shows 1 when true, but 0 in all other cases. I would like to show the "div by 0" values as "missing"

---

Did you read my post about the DIVIDE function? It returns special missing for division of several cases, doesn't throw any errors.

You can create a custom format to display any desired text for the various missing values generated. Look up the documentation.

Just because the code generator didn't offer the Divide function, or you may not understand what it meant in that context, doesn't mean that you can't modify the generated code to use the Divide function.

[Stepik]

Thanks, Ballardw, I have just used the function divide, but it still shows 0 when both are zeros in values.

The code is per below:
PROC SQL;
CREATE TABLE WORK.'QUERY_123' n AS
SELECT
* Check min/max >= 0.9 */
(IFN(DIVIDE(MIN(column 1, column 2), Max(column 1, column 2) >=0.9, 1, 0)) FORMAT=BEST8. AS 'Check min/max >= 0.9'n
FROM WORK.'123'n t1;
QUIT;

[Stepik]

correction*

IFN(DIVIDE(MIN(column 1, column 2), Max(column 1, column 2)) >=0.9, 1, 0) FORMAT=BEST8. AS 'Check min/max >= 0.9'n
FROM WORK.'123'n t1;

[ballardw]

---

@Stepik wrote:
Thanks, Ballardw, I have just used the function divide, but it still shows 0 when both are zeros in values.

---

Below is an example with actual values of 0 and 0. Divide Does not assign 0 from the Divide when both operands are 0.

Your IFN may, but not the divide. You need something other than IFN with a single value if you expect any result to get something besides 1 or 0 as that is what you have provided as result possibilities. Any thing other than the result of the division > 0.9 gets assigned 0 because of IFN. If you expect the 0/0 case to be treated differently than you have coded your IFN then you need to do a separate test which would be a CASE When block in SQL.

1    data _null;
2      x = divide(0,0);
3      put x= ;
4    run;

x=.