Desktop productivity for business analysts and programmers

Missing Value

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

Missing Value

[ Edited ]

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

 

 

Attachment

Accepted Solutions
Solution
‎02-19-2017 10:07 AM
Super User
Super User
Posts: 6,315

Re: Missing Value

[ Edited ]

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)

 

View solution in original post


All Replies
Esteemed Advisor
Posts: 5,194

Re: Missing Value

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.
Data never sleeps
Contributor
Posts: 37

Re: Missing Value

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

 

 

 

Solution
‎02-19-2017 10:07 AM
Super User
Super User
Posts: 6,315

Re: Missing Value

[ Edited ]

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)

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 173 views
  • 0 likes
  • 3 in conversation