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;
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;
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;
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;
Thanks so much to the both of you. The NOT IN was a lifesaver!!!
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.