Comparison with missing values

[David_Billa]

In the following code, can someone explain how I got 0 in first record and missing (.) in second record in the field 'Bias (Oversell Only)'n?

data test_1;
input '1PD Lag'n 'Actual Shipments'n Material;
datalines;
. . 1234
. 1 123
1 . 12
;
run;

proc sql;
* Calculate Bias at the item level (to see if there is a demand planning issue nationally instead of just regionally);
create table work.bias_agg_1 as
select     MATERIAL,
        sum('1PD Lag'n) as '1PD Lag'n,
        sum('Actual Shipments'n) as 'Actual Shipments'n,
          case when sum('1PD Lag'n) < sum('Actual Shipments'n)
                  then (sum('1PD Lag'n) - sum('Actual Shipments'n)) / sum('Actual Shipments'n)
            else 0
           end as 'Bias (Oversell Only)'n
from     work.test_1
group by MATERIAL;
quit;

Result:

[PaigeMiller]

The second row is missing because you are doing a subtraction with a missing value, and the result is missing. The other rows are zero because the ELSE clause has executed.

[David_Billa]

ELSE clause should not have executed for second row? My understanding is missing is unknown value and you can't compare it with any value like 1. When 1 < . or . <1, else should execute is my guess. Correct me if I'm wrong.

[PaigeMiller]

Missing is considered less than 1 (missing is considered less than any number), so the ELSE clause does not execute on row 2

[David_Billa]

Aww, In general terms missing is unknown value and it can be any value and
it can be even greater than 1.

Is it not the case in SAS?

[Tom]

---

@David_Billa wrote:
Aww, In general terms missing is unknown value and it can be any value and
it can be even greater than 1.

Is it not the case in SAS?

---

SAS has 28 distinct ways to store missing values.  The normal missing value (represented by a period) and 27 special missing values ( represented by .A to .Z and ._) . They will all be excluded when performing arithmetic.

But for comparisons SAS only uses BOOLEAN logic.  It does not support the TRI-level logic used by some other languages.

They will all be treated by comparison operators as less than any actual number.  I suspect that is probably a side effect of the specific codes they picked to represent those 28 missing values, but that is the way it has worked for almost 50 years.

You can test if two values are using the same missing code or not with the equality operator.

Do some testing:

data test;
  do x=-1 to 1, . , ._, .A , .Z ;
    output;
  end;
run;

proc sort data=test;
  by x;
run;

proc print;
run;

[Tom · Accepted solution]

---

@David_Billa wrote:

ELSE clause should not have executed for second row? My understanding is missing is unknown value and you can't compare it with any value like 1. When 1 < . or . <1, else should execute is my guess. Correct me if I'm wrong.

---

SAS only using BOOLEAN logic.  It does not use the TRI-level logic used by some other languages.  Any comparison will always result in either TRUE or FALSE.  Missing values are smaller than any actual number.  So . is less than 1.