Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

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

[ Edited ]

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;


Accepted Solutions
Solution
‎10-25-2017 11:54 AM
Esteemed Advisor
Posts: 5,624

Re: Proc SQL with 2 databases

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

View solution in original post


All Replies
Valued Guide
Posts: 596

Re: Proc SQL with 2 databases

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;
Solution
‎10-25-2017 11:54 AM
Esteemed Advisor
Posts: 5,624

Re: Proc SQL with 2 databases

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;
PG
Occasional Contributor
Posts: 7

Re: Proc SQL with 2 databases

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

 

Contributor
Posts: 38

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

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 674 views
  • 2 likes
  • 4 in conversation