Lesson 5: Joins #

Why you care #

MERGE with IN= flags is the workhorse of SAS data management. In real work, you'll answer questions like "which patients have continuous coverage?" or "which dispensings don't match an enrollment record?" by joining tables and filtering the result. Polars gives you the same power — each IN= combination gets a name (how=) instead of flag arithmetic. This lesson ends in a real Sentinel-style coverage QC: dispensings with no drug-covered enrollment span.

Concept #

What a join is #

A join combines rows from two tables based on matching values in one or more columns:

df_left.join(df_right, on="PatID", how="inner")

The on= parameter specifies the matching column(s). The how= parameter specifies which rows to keep:

• how="inner": keep only rows where the key matches in both tables
• how="left": keep all rows from the left table; nulls or multiplication if the right side has no match or multiple matches
• how="anti": keep rows from the left table that have no match on the right

Inner join: keep only matches in both tables #

Input:

patients (one row per patient):
┌───────┬─────┐
│ PatID ┆ Sex │
│ 101   ┆ F   │
│ 102   ┆ M   │
│ 103   ┆ F   │
└───────┴─────┘

fills (one row per dispensing):
┌───────┬─────┐
│ PatID ┆ Rx  │
│ 102   ┆ A   │
│ 103   ┆ B   │
│ 103   ┆ C   │
│ 104   ┆ D   │
└───────┴─────┘

Code:

fills.join(patients, on="PatID", how="inner")

Output:

shape: (3, 3)
┌───────┬─────┬─────┐
│ PatID ┆ Rx  ┆ Sex │
│ 102   ┆ A   ┆ M   │
│ 103   ┆ B   ┆ F   │
│ 103   ┆ C   ┆ F   │
└───────┴─────┴─────┘

PatID 101 is dropped (has no fill). PatID 104's fill D is dropped (no patient record). Only matching rows survive. This is MERGE a b; BY patid; IF a AND b in SAS.

Left join: keep all left rows, multiply if matched, null if unmatched #

Input: same as above.

Code:

patients.join(fills, on="PatID", how="left")

Output:

shape: (4, 3)
┌───────┬─────┬──────┐
│ PatID ┆ Sex ┆ Rx   │
│ 101   ┆ F   ┆ null │
│ 102   ┆ M   ┆ A    │
│ 103   ┆ F   ┆ B    │
│ 103   ┆ F   ┆ C    │
└───────┴─────┴──────┘

PatID 101 stays (but its Rx is null — no fill). PatID 103 multiplies: one patient row matches two fill rows, so it appears twice in the output. This row multiplication is why exercise part (b) avoids a naive left join — it would count coverage incorrectly.

Anti join: keep left rows with no match on the right #

Input: same as above.

Code:

fills.join(patients, on="PatID", how="anti")

Output:

shape: (1, 2)
┌───────┬─────┐
│ PatID ┆ Rx  │
│ 104   ┆ D   │
└───────┴─────┘

Only PatID 104 (fill D) survives: it has no matching patient record. This answers the QC question "which fills are orphans?" — exactly what you'll do in part (c).

Multi-key joins #

When the grain (the row-identifying combination) has more than one column, the on= parameter takes a list:

a.join(b, on=["PatID", "RxDate", "Rx"], how="anti")

matches all three columns. Example from demo05 step 5:

Input:

a (two fills for the same patient and Rx, different dates):
┌───────┬────────────┬─────┐
│ PatID ┆ RxDate     ┆ Rx  │
│ 101   ┆ 2021-01-01 ┆ A   │
│ 101   ┆ 2021-02-01 ┆ A   │
└───────┴────────────┴─────┘

b (one fill):
┌───────┬────────────┬─────┐
│ PatID ┆ RxDate     ┆ Rx  │
│ 101   ┆ 2021-02-01 ┆ A   │
└───────┴────────────┴─────┘

Code:

a.join(b, on=["PatID", "RxDate", "Rx"], how="anti")

Output:

shape: (1, 3)
┌───────┬────────────┬─────┐
│ PatID ┆ RxDate     ┆ Rx  │
│ 101   ┆ 2021-01-01 ┆ A   │
└───────┴────────────┴─────┘

Only the January fill survives: the February fill matches on all three columns, so it's dropped by the anti-join. The grain matters.

Data-model facts #

Before you start the exercise, know these:

Dispensing grain: A dispensing is uniquely identified by PatID + RxDate + Rx. No two rows in dispensing.parquet have the same combination of all three.

Enrollment is one row per span: A patient can have multiple enrollment records, each representing a coverage span (start and end date). If you join on PatID alone, a single dispensing can match multiple spans, multiplying the result rows. You must filter by date to reduce it back to one row per unique (PatID, RxDate, Rx).

Temporal coverage rule: A dispensing is covered by a span if Enr_Start <= RxDate <= Enr_End (both inclusive). You filter the joined rows by this date window.

Drug coverage: The DrugCov column in enrollment is "Y" for spans that cover prescription drugs, "N" for spans that don't. Answered question: "Which dispensings fall within a drug-covered span?"

Collapse the grain with .unique(): After joining and filtering, calling .unique() on (PatID, RxDate, Rx) reduces span-multiplied rows back to one row per unique dispensing, letting you count correctly.

Supporting methods #

.select(column_names): Keep only specified columns before a join to avoid carrying unnecessary data and make results cleaner.

enrollment.select("PatID", "Enr_Start", "Enr_End")

.unique(): Keep only one row per unique combination of the selected columns.

result.select("PatID", "RxDate", "Rx").unique()

.height: The number of rows in the dataframe. Use it to count results.

df.height  # same as len(df), but clearer intent

pl.read_parquet(path): Load a Parquet file into a DataFrame. You'll use this at the start of most exercises to load the tables you'll join.

df_demographic = pl.read_parquet("data/demographic.parquet")
df_enrollment = pl.read_parquet("data/enrollment.parquet")
df_dispensing = pl.read_parquet("data/dispensing.parquet")

SAS reflex check: IN= flags become how=. MERGE + IF a AND b ↔ how="inner"; IF a ↔ how="left"; IF a AND NOT b ↔ how="anti". But MERGE BY does a one-to-one walk within BY groups — join() always produces the full match combination, so expect row multiplication where SAS would have quietly aligned rows.

Worked example #

Open demos/demo05.py and run it:

python demos/demo05.py

Step 1 creates two tiny 3–4 row tables so you can see every row in every join.

Steps 2–4 show each join type side-by-side with its output. Read the narration carefully — especially step 3's warning about row multiplication.

Step 5 shows a multi-key join: even though PatID and Rx are the same, the date matters, so the January fill doesn't match the February span.

Step 6 is the real data: enrollment.parquet has thousands of spans, dispensing.parquet has hundreds of thousands of fills. The join multiplies rows (join output 268k+ rows from 199k dispensings), then .unique() on the grain brings it back down. The covered count is around 189k (about 95% of dispensings fall within a covered span — by design, ~5% are deliberately unmatched).

Modify and rerun:

1. In step 1, change patients to add PatID 105 with Sex "M". Re-run and predict: which joins' row counts stay the same, which change? (inner: drops 105 since no fills; left from patients: gains a null; anti on fills: stays the same.)

2. In step 6, change the filter from DrugCov == "Y" to DrugCov == "N". Predict: does the covered count go up or down? (Down — fewer drug-covered spans.) Run it and confirm.

SAS ↔ Polars #

Your turn #

Run the exercise:

python exercises/ex05_joins.py

You'll see NotImplementedError: TODO 1: read demographic, enrollment, dispensing. That's expected — each TODO is a task statement. Read it, then use the hints below to code the solution. Lesson 05 data-model facts (above) are the ground rules; demo05 showed you the join semantics.

Hints for TODO 1 #

df_demographic = pl.read_parquet(DATA_DIR / "demographic.parquet")
df_enrollment = pl.read_parquet(DATA_DIR / "enrollment.parquet")
df_dispensing = pl.read_parquet(DATA_DIR / "dispensing.parquet")

Hints for TODO 2(a) #

result = df_dispensing.join(df_demographic, on="PatID", how="inner")
inner_demo_count = result.height

Hints for TODO 2(b) #

enr_drug = df_enrollment.filter(pl.col("DrugCov") == "Y")
joined = df_dispensing.join(
    enr_drug.select("PatID", "Enr_Start", "Enr_End"),
    on="PatID",
    how="inner",
)
covered = joined.filter(...)  # the date-window condition goes here

Hints for TODO 2(c) #

# covered_disp is the unique (PatID, RxDate, Rx) frame from TODO 2(b)
uncovered = df_dispensing.join(
    covered_disp,
    on=["PatID", "RxDate", "Rx"],
    how="anti",
)
n_uncovered = uncovered.height

Predict-the-output checks #

Check 1: A 4-row fills table (PatID 102, 103, 103, 104) inner-joined to a 3-row patients table (PatID 101, 102, 103) on PatID. How many rows in the result?

Check 2: Which how= answers the question "which dispensings have no covered span"?

Check 3: A patient with 2 enrollment spans joined to 1 dispensing on PatID (left join). How many rows in the result?

Solution: When you're done or stuck, compare your code to solutions/ex05_joins.py. Read its comments and assertions to see the full picture.

Next #

Proceed to Lesson 6: Write, Partition, Lazy.