topic Re: Missing Value in SAS Enterprise Guide

Missing Value

SASNE — Sat, 18 Feb 2017 16:55:10 GMT

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

Re: Missing Value

LinusH — Sat, 18 Feb 2017 21:39:27 GMT

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.

Re: Missing Value

SASNE — Sun, 19 Feb 2017 10:12:41 GMT

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

Re: Missing Value

Tom — Sun, 19 Feb 2017 15:27:57 GMT

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)