PROC SQL: Exclude values from one table based on another table

[Dsquared]

Hi,

I am using EG to run code and have two separate datasets: one with my subset-population of interest and a second with the entire dataset of people with exclusion criteria codes.  How can I exclude any patient IDs that are in the exclusion criteria?  I've been trying different ways to no avail.  Here was my most recent attempt but I can't use the WHERE in PROC SQL.  What is the best code to do this?

Thanks!!

proc sql;

create table mart.NOex

as select a.*, b.patid

from mart.validoutcome a, mart.exclusions1 b

where a.patid NE b.patid

order by a.patid, a.index_dt;

quit;

[ChrisBrooks]

You can do this with a sub select. You don't give data so here's an example using SASHELP.CARS where I want all makes by manufacturers who DON'T make SUVs (the suv table is your exclusion list)

proc sql;
	create table suv
	as select distinct make
	from sashelp.cars
	where type="SUV";
quit;

proc sql;
	create table others as 
	select * from sashelp.cars
	where make not in
		(select make
		 from suv);
quit;

[PGStats · Accepted solution]

Use the NOT IN() condition in your WHERE clause:

proc sql;
create table mart.NOex as 
select *
from mart.validoutcome
where patid not in (select patid from mart.exclusions1)
order by patid, index_dt;
quit;

[Dsquared]

Thanks so much to the both of you.  The NOT IN was a lifesaver!!!

[ProcWes]

I got here late, but I would do it this like:

proc sql;
create table mart.NOex
as select a.*
from mart.validoutcome a
left join mart.exclusions1 b
on a.patid = b.patid
having missing(b.patid)
order by a.patid, a.index_dt;
quit;