BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dsquared
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

4 REPLIES 4
ChrisBrooks
Ammonite | Level 13

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
Opal | Level 21

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
Dsquared
Fluorite | Level 6

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

 

ProcWes
Quartz | Level 8

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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