Skip to content
Advertisement

Why ‘where’ statement seems to filter expected rows in SAS proc SQL?

I full joined 2 tables first and then full joined a 3rd table, now I got 1000 more rows in the result. But when I added a where statement following the join process I can only get 200 more rows and it seems that some expected rows were filtered. I don’t know what I’ve done wrong.

Advertisement

Answer

Your where clause is causing empty rows to be filtered. Consider a simplified schema:

TableA

TableB

And a simple full join with no filter:

Which will return

Now, if you apply a filter to anything from A, e.g. WHERE A.Col1 = 1, you’ll get rid of the 2nd Row (probably as intended) since 2 <> 1, but you’ll also remove the 3rd row, since A.Col is NULL, and NULL <> 1. As you have removed all rows with no matching record in TableA you have effectively turned your full join into a left join. If you then apply a further predicate on TableB, your left join becomes an inner join.

With Full joins, I find the easiest solution is to apply your filters before the join by using subqueries, e.g.:

Which removes the 2nd row, but still retains the 3rd row from the previous results:

You can also use OR, but the more predicates you have the more convoluted this can get, e.g.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement