Missing Value

[SASNE]

Hi Gurus,

When i connect to Database server like Oracle, i know that it treats Null value differently from SAS, Do you think it fixs the problem if i assign every variable (which is in where clause) is  missing  ?

Thanks

[LinusH]

It depends on what the problem is that you have, what is your desired outcome?
If you set:
Options sastrace = ',,,d' sastraceloc = saslog nostsuffix;
You can see what Oracle is doing with your query.

[SASNE]

Hi LinusH,

I want to include those Null value, but when i set

select ColA  ColB from table where ColA!=1,

then the result exclude all null value, in case i want it

[Tom · Accepted solution]

Unlike SAS most database system will use a tri-level logic with respect to missing values.  In SAS a boolean test is either true or false. In DBMS the result can be unknown or null which is treated as neither true nor false.

For example in SAS a numeric missing value is less than any actual number and you can test for equality between missing values.  So missing values would be included in tests like these:

age < 10
age ne 10
age = baseline_age

But in most DBMS you need to explicitely include the NULL values in your testing.

age < 10 or age is null
age ne 10 or age is null
age = baseline_age or (age is null and baseline_age is null)