topic Re: PROC SQL: Exclude values from one table based on another table in SAS Enterprise Guide

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

Dsquared — Wed, 25 Oct 2017 15:59:54 GMT

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;

Re: Proc SQL with 2 databases

ChrisBrooks — Mon, 23 Oct 2017 14:51:16 GMT

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;

Re: Proc SQL with 2 databases

PGStats — Tue, 24 Oct 2017 20:40:36 GMT

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;

Re: Proc SQL with 2 databases

Dsquared — Wed, 25 Oct 2017 15:55:14 GMT

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

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

ProcWes — Wed, 25 Oct 2017 22:15:02 GMT

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;