Treat missing values as zero in SAS where clause

Question

Is there an equivalent of the Oracle NVL function in SAS?

For example, if I have a where clause in SAS that says:

where myVar > -2;

it is not going to include any rows that have myVar = .

If I want to treat missing values as zero, i have to say:

where myVar > -2 or missing( myVar )

I'd like to be able to do something like:

where NVL( myVar, 0 ) > -2 // or some SAS equivalent

Is there something like this in SAS?

Answer 1

The coalesce function should do the job.

where coalesce(myVar,0) > -2

I'm not sure if the function became available in SAS 9, so if you have a really old SAS version this might not work.

Answer 2

Using the coalesce function is the right way to do this.

But if you have an old version of SAS where coalesce isn't implemented, you can use this trick:

where sum(myVar,0) > -2

If you use the sum function in SAS for adding, any non-missing number in the summation will force the result to be non-missing.

Thus adding 0 with the sum function will transform a missing value to 0, and non-missing values will remain unaltered.

Answer 3

One thing that y can do is like array varlistname var1 var2 var3 varn; if array <>. then output;

It will ouput data sets having non missing values