CODEWITHSUNDEEP
sqlsasproc-sql
amestrian
amestrian

Reputation: 602

proc sql code in SAS not filtering properly

I hope this is not a very very obvious, but if it is please don't kill me, I'm really new at this and struggle to find much info.

This code should do the following:

-grab columns from 2 different tables -only select rows that are not on a third table on a particular date

proc sql;
create table DiallerExtra as
SELECT a.AGREEMENT_NUMBER,
    b.CURRENT_FIRST_NAME,
    b.TELEPHONE_NUMBER
FROM TABLE1 a, TABLE2 b
WHERE a.AGREEMENT_NUMBER
NOT IN (SELECT AgreementNumber
FROM TABLE3 (WHERE =(DeleteDate >= today()-1)))
;
quit;

I first tried this (below) and it worked fine to filter the results (I ended up with 15 rows only).

proc sql;
create table DiallerExtra as
SELECT a.AGREEMENT_NUMBER
FROM TABLE1 a
WHERE a.AGREEMENT_NUMBER
NOT IN (SELECT AgreementNumber
FROM TABLE3 (WHERE =(DeleteDate >= today()-1)))
;
quit;

But when I tried the first code, it doesn't seem to be filtering correctly cause it spits out all the agreements on TABLE2, which is a lot.

Upvotes: 0

Views: 429

Answers (1)

Tom
Tom

Reputation: 51621

The "filtering" of your NOT IN logic is not the problem.

Add something to tell SQL how to combine TABLE1 and TABLE2.

If you want to combine two tables with SQL you need to tell it how to match the observations. Otherwise every observation in TABLE1 is matched to every observation in TABLE2. In your first example even if there is only one observation in TABLE1 with a value of A.AGREEMENT_NUMBER that is in the TABLE3 observations that match your WHERE= dataset option then it will be matched with every observation in TABLE2. So if TABLE2 had 100 customers the result set will have 100 observations.

So add another condition to your WHERE statement. For example if both TABLE1 and TABLE2 have AGREEMENT_NUMBER then perhaps you want to match on that.

create table DiallerExtra as
SELECT a.AGREEMENT_NUMBER
     , b.CURRENT_FIRST_NAME
     , b.TELEPHONE_NUMBER
FROM TABLE1 a
   , TABLE2 b
WHERE a.AGREEMENT_NUMBER = b.AGREEMENT_NUMBER
  and a.AGREEMENT_NUMBER NOT IN 
     (SELECT AgreementNumber FROM TABLE3 (WHERE =(DeleteDate >= today()-1)))
;

Upvotes: 2

Related Questions