Treat missing values as zero in SAS where clause

7
votes

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?

3
sas
Note that it's only in non-SAS data that rows with myVar=. won't be included. The reason is that missing values are NULL, and comparisons with NULL will always evaluate to FALSE. In native SAS data, missing values are treated as a non-negative number, which is less than zero. So "where myVar > -2;" will actually work with missing values when operating on native SAS data. - Martin Bøgelund

3 Answers

13
votes

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.

7
votes

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.

-1
votes

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