BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_zaras
Pyrite | Level 9

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

4 REPLIES 4
LinusH
Tourmaline | Level 20

OR

Data never sleeps
DBailey
Lapis Lazuli | Level 10

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

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

LinusH
Tourmaline | Level 20

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
andreas_zaras
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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