7

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?

Adnan
  • 2,949
  • 4
  • 29
  • 45
  • 1
    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 Jun 26 '09 at 13:46

3 Answers3

13

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.

Ville Koskinen
  • 1,266
  • 2
  • 15
  • 20
7

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.

Martin Bøgelund
  • 1,681
  • 1
  • 17
  • 26
  • 1
    +1: That's an awesome suggestion! We no longer support SAS 8 for our product so I really dont care if coalesce was available then or not but if I did, this would be an ingenious workaround. – Adnan Jun 26 '09 at 23:36
-1

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

ajit
  • 1