topic Re: proc sql; equivalent of Oracle is Null(A, 'use this when null') in SAS Programming

proc sql; equivalent of Oracle is Null(A, 'use this when null')

DavidPhillips2 — Fri, 17 Jul 2020 20:42:17 GMT

What is the SAS proc sql equivalent of the oracle function is null that accepts two parameters the column you are checking and the value to use when null. I found a reference to ifnull() function but when I tried it I got an error. Maybe it's a simple syntax thing.

Re: proc sql; equivalent of Oracle is Null(A, 'use this when null')

mklangley — Fri, 17 Jul 2020 20:49:27 GMT

COALESCE() for numeric variables. (e.g. coalesce(numeric_var, 0) )

COALESCEC() for character variables. (e.g. coalescec(char_var, 'abc') )

Re: proc sql; equivalent of Oracle is Null(A, 'use this when null')

ballardw — Fri, 17 Jul 2020 21:01:58 GMT

IFNULL in SAS as far as I know is restricted to FEDSQL use and not for Proc SQL.

The Coalesce functions as @mklangley references will return the left most non-missing value of a list of variables or values (3,4,5 or more) so may be more flexible.

Coalescec (var1, var2,var3,'result') would 'result' if all of var1 through 3 were missing (or null for those who haven't quite mastered the SAS jargon yet).