← SASnRFilter Using a Subquery

Introduction

A common clinical programming task is to keep records from one dataset only when they match a condition in another — for example, keeping AE records only for subjects in the safety population.
In SAS, this is done with a WHERE ... IN (SELECT ...) subquery inside PROC SQL. In R, we use semi_join() in dplyr or %in% subsetting in base R.
The subquery pattern avoids bringing extra columns into the result — we only want to filter, not merge.
This lesson uses a small ADSL with a SAFFL flag and an ADAE dataset, keeping only AE records for safety-population subjects.
After running, we confirm that AE records for subjects outside the safety population are excluded from the result.

Filter Using a Subquery

SAS (Base SAS)+

*==============================================================================;
* Create sample ADSL with safety flag;
*==============================================================================;

data adsl;
    input usubjid $ trt01p $ saffl $;
    datalines;
SUBJ001 Placebo Y
SUBJ002 DrugA   Y
SUBJ003 Placebo N
SUBJ004 DrugA   Y
SUBJ005 Placebo Y
;
run;

*==============================================================================;
* Create sample ADAE;
*==============================================================================;

data adae;
    input usubjid $ aedecod $12. aesev $;
    datalines;
SUBJ001 Headache    MILD
SUBJ002 Nausea      MODERATE
SUBJ003 Fatigue     MILD
SUBJ003 Dizziness   SEVERE
SUBJ004 Rash        MILD
SUBJ005 Cough       MILD
;
run;

*==============================================================================;
* Subquery: keep AEs only for safety-population subjects;
*==============================================================================;

proc sql;
    create table ae_saf as
    select *
    from adae
    where usubjid in
        (select usubjid from adsl where saffl = 'Y');
quit;

proc print data=ae_saf; title "AEs in Safety Population"; run;

*==============================================================================;
* NOT IN subquery: AEs for subjects NOT in safety population;
*==============================================================================;

proc sql;
    create table ae_nonsaf as
    select *
    from adae
    where usubjid not in
        (select usubjid from adsl where saffl = 'Y');
quit;

proc print data=ae_nonsaf; title "AEs NOT in Safety Population"; run;

The subquery (SELECT usubjid FROM adsl WHERE saffl = 'Y') builds a list of subject IDs in the safety population.
The outer query uses WHERE usubjid IN (...) to keep only ADAE rows whose USUBJID appears in that list.
This is a filter-only operation — no columns from ADSL are added to the result, unlike a join.
SUBJ003 has saffl = 'N', so both of their AE records (Fatigue and Dizziness) are excluded from ae_saf.
The NOT IN variant reverses the logic — it keeps AEs for subjects outside the safety population, which is useful for data-quality checks.
Subqueries are more efficient than a full join when we only need to filter and do not need columns from the lookup table.
After running, we confirm ae_saf has 4 rows (SUBJ001, SUBJ002, SUBJ004, SUBJ005) and ae_nonsaf has 2 rows (SUBJ003).

R (tidyverse)+

library(tidyverse)

#==============================================================================
# Create sample ADSL and ADAE
#==============================================================================

adsl <- tribble(
  ~usubjid,  ~trt01p,    ~saffl,
  "SUBJ001", "Placebo",  "Y",
  "SUBJ002", "DrugA",    "Y",
  "SUBJ003", "Placebo",  "N",
  "SUBJ004", "DrugA",    "Y",
  "SUBJ005", "Placebo",  "Y"
)

adae <- tribble(
  ~usubjid,  ~aedecod,     ~aesev,
  "SUBJ001", "Headache",   "MILD",
  "SUBJ002", "Nausea",     "MODERATE",
  "SUBJ003", "Fatigue",    "MILD",
  "SUBJ003", "Dizziness",  "SEVERE",
  "SUBJ004", "Rash",       "MILD",
  "SUBJ005", "Cough",      "MILD"
)

#==============================================================================
# semi_join: keep AEs where subject is in safety population
#==============================================================================

ae_saf <- adae %>%
  semi_join(
    adsl %>% filter(saffl == "Y"),
    by = "usubjid"
  )
ae_saf

#==============================================================================
# anti_join: AEs for subjects NOT in safety population
#==============================================================================

ae_nonsaf <- adae %>%
  anti_join(
    adsl %>% filter(saffl == "Y"),
    by = "usubjid"
  )
ae_nonsaf

#==============================================================================
# Alternative: filter with %in% and pull()
#==============================================================================

saf_ids <- adsl %>% filter(saffl == "Y") %>% pull(usubjid)

ae_saf_v2 <- adae %>% filter(usubjid %in% saf_ids)
ae_saf_v2

semi_join() is the dplyr equivalent of WHERE ... IN (SELECT ...) — it keeps rows from the left table that have a match in the right table, without adding any columns.
We first filter ADSL to the safety population with filter(saffl == "Y"), then pass it as the right side of semi_join().
anti_join() is the opposite — it keeps rows that do not match, equivalent to the NOT IN subquery.
The alternative approach uses pull() to extract a vector of safe subject IDs, then filter(usubjid %in% saf_ids) to subset.
Both approaches give the same result. semi_join() is cleaner when the lookup condition is complex; %in% is simpler for a straightforward ID list.
Neither approach adds columns from ADSL — the output has only the original ADAE columns, just like the SAS subquery.
After running, we confirm ae_saf has 4 rows and ae_nonsaf has 2 rows.

Happy learning!

Search lessons

Introduction

Filter Using a Subquery