proc sql code in SAS not filtering properly

Question

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.

I don't understand this syntax: (WHERE =(DeleteDate >= today()-1)). What does this do in proc sql? It is not valid SQL syntax and I haven't come across it before. — Gordon Linoff
@GordonLinoff it filters for that date. My manager use it, I've used it and I've also seen it used in tutorials and such :) — amestrian
@jarlh where would that go? I tried including one but wasn't able to find where to put it exactly... can you make an answer please? — amestrian
@Gordon Linoff SAS SQL allows you to add dataset options, one of which is a where condition. It prevents you from needing to create a subquery and instead will perform the filtering while reading the data. — Stu Sztukowski

Tom Tom · Accepted Answer · 2021-02-15T22:05:32

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)))
;

proc sql code in SAS not filtering properly

1 Answers