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