Skip to main content

Link your extracts

Joins, bind_rows() and calculating follow-up time - assemble your extracts into one dataset

Join register extracts on pnr in R with left_join() and filter joins, validate the join, use bind_rows(), and calculate follow-up time and the event variable.
Published

November 19, 2026

Your extracts from Phase 6, Extract from LPR and Phase 11 are stored as separate RDS files. Once each extract is down to one row per person (Long ↔︎ wide format), you join them on pnr into one analysis-ready dataset. Finally, you calculate follow-up time and the event variable.

Paths and variable names in the examples are generic - replace them with your own.

First get each extract down to one row per person. left_join() only behaves predictably when both tables have one row per pnr. Many register extracts have multiple rows per person (long format). Reduce them to one row per person with group_by() + slice() or pivot_wider() - explained in Long ↔︎ wide format. This page assumes your extracts are already in that shape.

Validate your join

Joins rarely fail with an error message - but they can produce silently wrong results. Always check these three things after a left_join():

# Before join: what is nrow?
nrow(cohort) # e.g. 4,823

# Join:
cohort2 <- cohort %>% left_join(outcome, by = "pnr")

# Check 1: did the row count increase? Duplicated keys in outcome give extra rows.
nrow(cohort2) # should still be 4,823

# Check 2: how many got a match?
sum(!is.na(cohort2$event_date)) # number with outcome
sum(is.na(cohort2$event_date)) # number without - expected censored?

# Check 3: who did NOT match? (diagnostic - not necessarily an error)
missing <- anti_join(cohort, outcome, by = "pnr") # pnr's not found in outcome
nrow(missing) # 0 = all matched; > 0 = investigate

anti_join() as diagnostics anti_join(cohort, outcome, by = "pnr") returns the cohort members that are not found in the outcome table. Use it for “who in my cohort has no death date record?” (all alive - expected) or “who is missing from register X?” (unexpected - investigate).

Stack tables vertically - bind_rows()

bind_rows() and left_join() solve two fundamentally different problems:

bind_rows() left_join()
What it does Stacks tables vertically - adds rows Combines tables horizontally - adds columns
Requires That the columns have the same names That the tables share one common key column
Result More rows, same number of columns Same number of rows, more columns
Matches on key? No - all rows from both tables Yes - match on pnr or other key
Typical use LPR2 + LPR3 → one combined register Link outcome or covariate to cohort table

bind_rows() is not a join. It does not look at pnr and does not match. It simply places table 2’s rows below table 1’s rows - like pasting two Excel sheets together vertically.

# bind_rows: LPR2 and LPR3 have the same columns (pnr, date_contact, icd3)
# → stack them vertically into one combined diagnosis register
lpr2_dx # 45,000 rows - diagnoses up to March 2019
lpr3_dx # 32,000 rows - diagnoses from March 2019 onwards

all_dx <- bind_rows(lpr2_dx, lpr3_dx) # 77,000 rows - both periods combined

Columns missing in one table (e.g. a column that only exists in LPR3) are automatically filled with NA for rows from the other table.

Counting diagnoses across this combined all_dx? Expect a jump around 2019-2020 that isn’t a real change in disease occurrence - see why.

When do you use which? bind_rows() - when you have two versions of the same register (LPR2 + LPR3, exposed + comparators) and want to merge them into one. left_join() - when you want to attach a new variable (outcome, date of death, age) to your cohort table.

Practical examples: join date of death and emigration to the cohort

read_register() requires fastreg set up with the path to your registers - see Parquet and fastreg if you did not convert them from SAS yourself.

Date of death (DOD)

library(fastreg) # read_register()
library(dplyr) # %>%, semi_join(), left_join(), collect()
library(tibble) # tibble()

# Attach date of death (DOD) to the cohort -------------------------------------
# Cohort data from Phase 10. Keep only the columns that identify a row and the
# ones Phase 10 owns. The file also holds working columns from the matching
# (death_date, first_outcome_date ...); attach those fresh here, so no column
# ends up twice (death_date.x / death_date.y).
cohort <- readRDS("path/to/full_cohort.rds") %>%
  select(pnr, index_date, exposed, sex, foed_dag,
         any_of(c("match_id", "crossover_date"))) # any_of(): kept if the column exists

# Death dates from DOD:
dod <- read_register("dod") %>% # without fastreg: open_dataset("E:/workdata/[projectnumber]/cleaned-data/parquet-registers/dod/")
  rename_with(tolower) %>%               # standardise column names
  semi_join(tibble(pnr = cohort$pnr), by = "pnr", copy = TRUE) %>%   # only the cohort's pnr's
  select(pnr, death_date = doddato) %>%   # rename doddato to death_date
  collect()                              # fetch into R

# Join: all cohort members retained; the living get death_date = NA
cohort <- cohort %>%
  left_join(dod, by = "pnr")       # attach death date - NA = still alive

Emigration date (VNDS)

Emigration censors just like death - the person leaves the study on the day they emigrate. VNDS contains one row per migration event. Take the first exit after the index date, not simply the first ever: a person may have left and come back, and an exit before index censors nothing. Whether the person was resident at index at all is decided in Phase 10.

# Attach emigration date (VNDS) to the cohort ----------------------------------
# Emigration dates from VNDS:
vnds <- read_register("vnds") %>%              # without fastreg: open_dataset("E:/workdata/[projectnumber]/cleaned-data/parquet-registers/vnds/")
  rename_with(tolower) %>%                       # standardise column names
  semi_join(tibble(pnr = cohort$pnr), by = "pnr", copy = TRUE) %>%   # only the cohort's pnr's
  filter(indud_kode == "U") %>%                  # "U" = udvandring/emigration (VNDS holds both in- and out-migration)
  select(pnr, haend_dato) %>%
  collect()

emigration <- cohort %>%                         # the cohort has pnr + index_date
  distinct(pnr, index_date) %>%                  # one row per person and index date
  left_join(vnds, by = "pnr", relationship = "many-to-many") %>% # several index dates x several exits per person is expected here
  filter(haend_dato > index_date) %>%            # only exits AFTER index - those are the ones that censor
  group_by(pnr, index_date) %>%                  # per person AND index date: the answer depends on the index date
  arrange(haend_dato) %>%                        # oldest date first
  slice(1) %>%                                   # first exit after that index date
  ungroup() %>%                                  # release grouping
  select(pnr, index_date, emigration_date = haend_dato) # rename haend_dato to emigration_date

# Join: all cohort members retained; non-emigrants get emigration_date = NA
cohort <- cohort %>%
  left_join(emigration, by = c("pnr", "index_date")) # NA = no exit after index

The date of death is one date per person, so pnr alone is the right key. The emigration date depends on the index date, so it is keyed on pnr and index_date: a person who was drawn as a comparison person on two different dates then gets the right exit for each of them.

Derived variables: create new columns

Once the extracts are joined, you almost always need to compute new variables from the columns you now have. That is mutate(): it adds a column calculated from existing ones.

library(dplyr) # mutate, if_else, case_when

cohort <- cohort %>%
  mutate(
    # Age at index: days between two dates / 365.25 (a year is 365.25 days, leap years)
    age_at_index = as.numeric(index_date - foed_dag) / 365.25,

    # BMI from height (m) and weight (kg)
    bmi = weight_kg / (height_m^2),

    # Categorise a continuous variable into groups
    age_group = case_when(
      age_at_index < 50 ~ "<50",
      age_at_index < 65 ~ "50-64",
      TRUE ~ "65+" # everything else (here: 65 and over)
    )
  )

Two habits worth keeping:

  • Compute a variable once and reuse it, so age at index is the same number in the matching and in Table 1.
  • if_else() for yes/no, case_when() for several categories - and use the same type in every branch (1L/0L, or text in all of them). Both are in the Function guide.

The censoring date and the event indicator are derived variables too, but they need dates from several extracts at once, so they come next.

Calculate follow-up time and event variable

Before you can analyse, each cohort member needs a censoring date (when follow-up ends) and an event variable (did they experience the outcome?).

The censoring date is the earliest of: event date, date of death, emigration date, the date a comparison person becomes exposed themselves (crossover) and end of study period. death_date and emigration_date were joined on in the practical examples above; the outcome date is joined on first here.

# Calculate follow-up time and event variable ----------------------------------
# The outcome extract from Phase 9/11: pnr + event_date, one row per person
hip_fracture <- readRDS("path/to/extract_hip_fracture.rds") %>%
  distinct(pnr, event_date)
cohort <- cohort %>% left_join(hip_fracture, by = "pnr") # NA = no event

study_end <- as.Date("2024-12-31") # replace with your actual study end date (the same date as STUDY_END if you matched a comparison cohort in Phase 10)
# format: "yyyy-mm-dd" (ISO 8601 - R's standard)

cohort <- cohort %>%
  mutate(
    # Censoring date = the earliest of all possible stopping reasons
    censor_date = pmin(
      event_date,
      death_date,
      emigration_date,
      crossover_date, # comparison person becomes exposed (NA for everyone else)
      study_end,
      na.rm = TRUE
    ),

    # Follow-up time in days (Cox) and in years (rates, Poisson)
    time_days = as.numeric(censor_date - index_date),
    followup_years = time_days / 365.25,

    # Outcome variable: 1 = outcome occurred before censoring, 0 = censored
    outcome = as.integer(!is.na(event_date) & event_date <= censor_date)
  ) %>%
  filter(time_days > 0) # drop zero/negative follow-up (see below)

pmin() compares vectors position by position and returns the smallest value per person - it is the vectorised version of min(). na.rm = TRUE ensures that a missing death date (= alive) does not make the censoring date NA.

Crossover. A comparison person who later becomes exposed stops being a comparison person on that day, so follow-up is censored at crossover_date (made in Phase 10, NA for the exposed and for comparison persons who never become exposed). Without that line, time after their own exposure is counted as unexposed time. If your cohort has no crossover_date column (no comparison group, or one that can never become exposed), leave the line out. The alternative to censoring is to let the person switch group at crossover and contribute exposed time from then on. That needs a dataset with several rows per person, see Time-varying exposure.

Zero or negative follow-up. Someone whose outcome, death or emigration is recorded on the index date itself gets time_days = 0 and was never at risk; an event before index (a prevalent case that slipped through) gives a negative time. Both break the analysis (log(0) in Poisson, meaningless Cox time), so the filter() above drops them - count how many, and if same-day events are common in your data, decide deliberately whether they belong in the study.

Competing risks? If a competing event (typically death) must be handled separately (see Time-to-event), then instead of a binary outcome (0/1) build a status with three values - what happened first:

# Competing risks: a status with three values ----------------------------------
cohort <- cohort %>%
  mutate(
    # status_code: which date "won" the pmin() above?
    status_code = case_when(
      !is.na(event_date) & event_date <= censor_date ~ 1, # outcome first
      !is.na(death_date) & death_date <= censor_date ~ 2, # death first (competing risk)
      TRUE ~ 0 # otherwise censored (emigration/study end)
    )
  )

Assemble the final analysis dataset

By now your cohort is built in Phase 10: a table with pnr and index_date (and exposed/case if you have a comparison group - renamed to exposure in the final select()) - one row per person, unless your comparison group was matched with replacement, in which case the same pnr can carry several rows, one per index_date it was matched on. The recipe below works for both: everything measured relative to the index date is joined on pnr + index_date, see comparison cohort’s callout on it. Your outcomes, covariates and censoring variables are extracted as separate RDS files (Phase 9, Phase 11). This last step joins them onto the cohort with left_join(), calculates follow-up time, and keeps only the columns the analysis needs.

Cohort construction - including the matching - belongs to Phase 10. Here we assume the cohort already exists; this page only assembles the analysis dataset around it. Design choices (comparator, immortal time, matching ratio) are decided back in Phase 1.

End with select() - keep only the columns the analysis requires.

cohort_final <- cohort %>%
  select(
    pnr,
    index_date,
    exposure = exposed, # renamed: the analysis pages call it `exposure`
    censor_date,
    time_days,
    followup_years,
    outcome,
    status_code, # from the competing-risks block above
    age, # age, nmi and the three _cat columns are added in the
    sex, # complete recipe below (steps 4 and 5)
    nmi,
    occupation_cat,
    education_cat,
    income_cat
  )

saveRDS(cohort_final, "path/to/analysis.rds") # save the final dataset
Complete recipe - from cohort to analysis-ready dataset
# Complete recipe: cohort -> analysis-ready dataset ----------------------------
library(fastreg) # read_register()
library(dplyr)   # left_join, semi_join, mutate, group_by, slice
library(tibble)  # tibble()

# 0. Load your cohort ----------------------------------------------------------
# One row per person and index date. Keep only the columns that identify a row
# and the ones Phase 10 owns; everything else is attached fresh below, so no
# column ends up twice (death_date.x / death_date.y).
cohort <- readRDS("path/to/full_cohort.rds") %>%
  select(pnr, index_date, exposed, sex, foed_dag,
         any_of(c("match_id", "crossover_date"))) # any_of(): kept if the column exists
n_start <- nrow(cohort)   # to check below that no join adds or loses rows

# THE JOIN KEY. A date that belongs to the person (outcome, death) is joined on
# pnr. Anything measured RELATIVE TO THE INDEX DATE (emigration after index, SES,
# NMI) is joined on pnr + index_date: with a comparison group matched with
# replacement the same pnr has several rows, one per index date, and a join on
# pnr alone would then silently multiply the rows.

# 1. Prevalent cases are already excluded --------------------------------------
# People who already had the outcome before index are not at risk of getting it.
# They were removed in Phase 10 (build-study-population.qmd, Step 3: the
# `prevalent` object and filter(!pnr %in% prevalent)), so full_cohort.rds holds
# only people at risk at index. The filter in step 4 catches any that slipped through.

# 2. Link the outcome ----------------------------------------------------------
# One row per person with the FIRST event date after index, or no row at all.
# left_join keeps everyone, so people with no event get NA - which is exactly
# what "no event" means here, and what the outcome indicator in step 4 reads.
hip_fracture <- readRDS("path/to/extract_hip_fracture.rds") %>%   # pnr + event_date
  distinct(pnr, event_date)                                       # one row per person
cohort <- cohort %>% left_join(hip_fracture, by = "pnr")

# 3. Link censoring: death date and emigration ---------------------------------
deaths <- read_register("dod") %>%   # without fastreg: open_dataset("E:/workdata/[projectnumber]/cleaned-data/parquet-registers/dod/")
  rename_with(tolower) %>%
  semi_join(tibble(pnr = cohort$pnr), by = "pnr", copy = TRUE) %>%
  select(pnr, death_date = doddato) %>% collect()

vnds_data <- read_register("vnds") %>%    # without fastreg: open_dataset("E:/workdata/[projectnumber]/cleaned-data/parquet-registers/vnds/")
  rename_with(tolower) %>%
  semi_join(tibble(pnr = cohort$pnr), by = "pnr", copy = TRUE) %>%
  filter(indud_kode == "U") %>%
  select(pnr, haend_dato) %>% collect()

# Emigration needs the first exit AFTER index, not the first ever: someone who
# lived abroad as a child and moved back is not censored at that childhood exit.
# So attach index_date first, filter, and only then take the earliest remaining,
# per person AND index date.
vnds_data <- cohort %>%
  distinct(pnr, index_date) %>%
  left_join(vnds_data, by = "pnr", relationship = "many-to-many") %>%   # several index dates x several exits per person is expected
  filter(haend_dato > index_date) %>%
  group_by(pnr, index_date) %>% arrange(haend_dato) %>% slice(1) %>% ungroup() %>%
  select(pnr, index_date, emigration_date = haend_dato)

cohort <- cohort %>%
  left_join(deaths,    by = "pnr") %>%
  left_join(vnds_data, by = c("pnr", "index_date"))

stopifnot(nrow(cohort) == n_start)   # a join that multiplied rows stops here

# 4. Follow-up time and the outcome indicator ----------------------------------
# censor_date = the FIRST of the ways follow-up can end. pmin() with
# na.rm = TRUE takes the earliest date that actually exists, so someone who never
# died, never emigrated and never had the event simply ends at study_end.
# crossover_date = the day a comparison person becomes exposed themselves (from
# Phase 10; NA for everyone else). Leave that line out if your cohort has no such column.
# outcome = 1 only if the outcome happened at or before that date: a diagnosis
# recorded after the person emigrated does not count. status_code adds death as
# a competing event (0 = censored, 1 = outcome, 2 = death; outcome wins a tie).
study_end <- as.Date("2024-12-31")   # replace with your actual study end date (the last date your data cover; the same date as STUDY_END in Phase 10)
cohort <- cohort %>%
  mutate(
    censor_date    = pmin(event_date, death_date, emigration_date, crossover_date,
                          study_end, na.rm = TRUE),
    time_days      = as.numeric(censor_date - index_date),
    followup_years = time_days / 365.25,   # 365.25 = leap years
    outcome        = as.integer(!is.na(event_date) & event_date <= censor_date),
    status_code    = case_when(
      outcome == 1 ~ 1,
      !is.na(death_date) & death_date <= censor_date ~ 2,
      TRUE ~ 0
    ),
    age            = as.numeric(index_date - foed_dag) / 365.25   # age at index, for every row
  ) %>%
  filter(time_days > 0)   # no follow-up = never at risk (event/death/emigration on index day)

# 5. Link covariates (SES, comorbidity) ----------------------------------------
# sex and age are already on the cohort (step 0 and step 4). The extracts below
# are measured relative to the index date, so they are joined on pnr + index_date.
# distinct() makes sure each extract has ONE row per key: the same person can be
# drawn into two matched sets on the same date, and two identical rows on each
# side would otherwise become four.
ses_data <- readRDS("path/to/extract_ses.rds") %>%    # education_cat, occupation_cat, income_cat
  distinct(pnr, index_date, .keep_all = TRUE)
nmi_data <- readRDS("path/to/extract_nmi.rds") %>%    # NMI score - see nmi.qmd
  distinct(pnr, index_date, .keep_all = TRUE)

n_before <- nrow(cohort)
cohort <- cohort %>%
  left_join(ses_data, by = c("pnr", "index_date")) %>%
  left_join(nmi_data, by = c("pnr", "index_date"))
stopifnot(nrow(cohort) == n_before)   # same number of rows as before the joins

# 6. Keep ONLY the columns the analysis requires -------------------------------
cohort_final <- cohort %>%
  select(pnr, index_date, exposure = exposed, censor_date, time_days, followup_years,
         outcome, status_code, age, sex, nmi, occupation_cat, education_cat, income_cat)

saveRDS(cohort_final, "path/to/analysis.rds")  # save the final dataset
nrow(cohort_final)   # n_start minus the rows dropped for zero follow-up in step 4
names(cohort_final)  # did every join actually add the columns you expected?

Next steps

You now have one analysis-ready dataset with one row per person (one row per person and index date, if your comparison group was matched with replacement). The next step is the analysis:

→ Phase 13 - Analysis

See also

TipFurther information

Further depth (in English):

  • Joining data in The Epidemiologist R Handbook.
  • Joins in R for Data Science: keys, mutating vs. filtering joins and what happens when a key has duplicates.
Back to top