The SAS Output Delivery System and reporting techniques

Access to Oracle and Missing Values

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

Access to Oracle and Missing Values

Hello,

I connect to an Oracle datase through Access to Oracle. I write a proc sql on a single table and i ask sas to bring all the columns of the data souce and only the rows for which the valeus of the variable X is difrferent than 'GR', 'LP', 'GE'. Variable X contains missing values. The where clause that i use is:

where x not in ('GR','LP','GE')

Since there are missing values in X then rows with missing values in the variable X should appear. But what sas brings back is the rows for which the value of X is everything else then 'GR','LP','Ge', except missing values. Though if i write

where x is null

the missing values are brought back

ANy ideas how to resole this so that the missing vlaues apprear?

Regards,

ANdreas


Accepted Solutions
Solution
‎06-13-2013 04:38 AM
Frequent Contributor
Posts: 75

Re: Access to Oracle and Missing Values

Posted in reply to andreas_zaras

Hello,

Thanks for your answer. I also found a useful link about my question:

http://ebookbrowse.com/chapman-tasha-and-lori-carlton-using-sas-with-oracle-pdf-d394357638

Null values In SAS, any selection criteria that includes “not equals” (^=, <>, or ne), “less than” (< or lt), or “less than or equal to” (<= or le) will include null or missing values. For example, the criteria Where Language ne “English” will pull records where the language is Spanish, French, Chinese, or blank. However, this is not the case in Oracle SQL. In Oracle, a null value is non-existent. It doesn‟t equal or not equal anything. For this reason, comparison operators will not apply for null records. Where Language ne “English” in an Oracle SQL statement will only pull records where the language is Spanish, French, or Chinese. Blank or null records will be excluded. To include null records in the selection criteria, this must be stated explicitly, i.e., Where Language ne “English” or Language is null

Regards,

Andreas

View solution in original post


All Replies
Super User
Posts: 5,434

Re: Access to Oracle and Missing Values

Posted in reply to andreas_zaras

OR

Data never sleeps
Super Contributor
Posts: 578

Re: Access to Oracle and Missing Values

Posted in reply to andreas_zaras

If you want the rows with missing values to be returned, then use this:

where coalesce(x,'XX') not in ('GR','LP','GE')

Super User
Posts: 5,434

Re: Access to Oracle and Missing Values

Just curious, do you think the Oracle optimizer can handle these kind of logic (if x was either indexed or partitioned by)?

Data never sleeps
Solution
‎06-13-2013 04:38 AM
Frequent Contributor
Posts: 75

Re: Access to Oracle and Missing Values

Posted in reply to andreas_zaras

Hello,

Thanks for your answer. I also found a useful link about my question:

http://ebookbrowse.com/chapman-tasha-and-lori-carlton-using-sas-with-oracle-pdf-d394357638

Null values In SAS, any selection criteria that includes “not equals” (^=, <>, or ne), “less than” (< or lt), or “less than or equal to” (<= or le) will include null or missing values. For example, the criteria Where Language ne “English” will pull records where the language is Spanish, French, Chinese, or blank. However, this is not the case in Oracle SQL. In Oracle, a null value is non-existent. It doesn‟t equal or not equal anything. For this reason, comparison operators will not apply for null records. Where Language ne “English” in an Oracle SQL statement will only pull records where the language is Spanish, French, or Chinese. Blank or null records will be excluded. To include null records in the selection criteria, this must be stated explicitly, i.e., Where Language ne “English” or Language is null

Regards,

Andreas

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 511 views
  • 5 likes
  • 3 in conversation