Is there a more concise version of the CASE WHEN foo.bar IS NULL THEN 0 ELSE 1 END paradigm?

The goal is simply to check whether a field is NULL or not, yielding a value of 1 when the field is not NULL, and 0 otherwise. I can’t use COALESCE here, because if the field isn’t NULL, I just want to return 1, not the value of the field.

I have a gut feeling that there’s already a function that does this – something like NULL_STATUS_INTEGER(foo.bar), for example – instead of needing to write out the full CASE statement of CASE WHEN foo.bar IS NULL THEN 0 ELSE 1 END. I’m not saying that the CASE version is absurdly long, but I just find it strange that there’s not a shorter way to do this.

I’m primarily interested in solutions that aren’t confined to one SQL vendor, but I happen to be using SAS, if there happens to be a SAS-specific way to do this in PROC SQL.

Answer

Comparisons are also boolean, return a true/false

proc sql;
create table want as
select *, name = 'Alfred' as flag
from sashelp.class;
quit;

data want;
set sashelp.class;
flag = (name = 'Alfred');

run;

EDIT:

Another option is to use IFN()/IFC(), which factor in three conditions, FALSE, TRUE, MISSING. IFN()/IFC() can be used in SQL or a data step in SAS.

x = ifc(name="Alfred", 1, 0, .);