Providing software solutions since 1976

• Log in
• Create profile
• Welcome
  [Log out]
• Edit profile
• My SAS

• Print  |
• Feedback  |
• Share

  • E-mail
  

  E-mail This Page

  Your Name:*
  Your E-mail:*
  Recipient E-mail:*
  Send a copy to me
  Personal Message:
  
  

  * indicates a required field

A query that contains an outer join of two (2) DBMS tables and further
subsets the query with a WHERE clause may generate incorrect results.

The problem seems to occur when the variable being subset on is not a
date variable. SAS builds the query and passes the outer join to the
DBMS.  The SAS engine will also include a WHERE clause (or add to it in
Oracle's case) if the original WHERE clause references a date variable.
If the variable is not a date the condition is not passed over to the
DBMS to be processed nor it is processed by SAS.  So any further
subsetting criteria that was intended is not applied and the results
will contain rows that should have been removed as a result of the WHERE
clause.

For example, the following WHERE clause will cause the data to be
appropriately subset based on B_DAY but will still contain any males
that existed originally.

   where b_day < '01jan1995'd and gender='F'

This only occurs with outer joins.  Inner joins are not affected.


A Technical Support hot fix for Release 8.2 (TS2M0) for this
issue is available at:

http://www.sas.com/techsup/download/hotfix/82_sbcs_prod_list.html#007222

For customers running SAS with Asian Language Support (DBCS), this
hot fix should be downloaded from:

http://www.sas.com/techsup/download/hotfix/82_dbcs_prod_list.html#007222

Operating System and Release Information

A fix for Release 8.2 (TS2M0) for this issue is available at:

A fix for Release 8.2 (TS2M0) with Asian Language Support (DBCS) for this issue is available at: