Data Manipulation with dplyr for Clinical Data

Introduction

Clinical trial programming involves transforming raw and standardized observations into analysis-ready datasets, summary tables, listings, and figures.

Although many clinical programmers historically perform these transformations using SAS DATA steps and PROC SQL, R provides a powerful alternative through the tidyverse ecosystem.

Within that ecosystem, dplyr is the primary package for data manipulation.

The important point for a clinical programmer is that dplyr is not simply a collection of convenient commands.

Used correctly, it provides a structured language for expressing operations such as:

• selecting analysis populations;
• deriving analysis variables;
• identifying baseline observations;
• selecting records by visit;
• joining subject-level and observation-level data;
• creating treatment variables;
• calculating within-subject changes;
• identifying first and last observations;
• detecting duplicates;
• creating analysis flags; and
• validating intermediate datasets.

The Core dplyr Verbs

Most clinical data manipulation tasks can be expressed using a relatively small set of dplyr verbs.

A Clinical Dataset Example

Consider a simplified longitudinal laboratory dataset.

lab <- tibble::tribble(
  ~USUBJID, ~TRT01P, ~AVISIT, ~ADY, ~PARAMCD, ~AVAL,
  "01-001", "Drug A", "Baseline",   1, "ALT",  32,
  "01-001", "Drug A", "Week 4",    29, "ALT",  41,
  "01-001", "Drug A", "Week 8",    57, "ALT",  58,
  "01-002", "Drug A", "Baseline",   1, "ALT",  27,
  "01-002", "Drug A", "Week 4",    29, "ALT",  25,
  "01-002", "Drug A", "Week 8",    57, "ALT",  21,
  "01-003", "Placebo", "Baseline", 1, "ALT",  35,
  "01-003", "Placebo", "Week 4",  29, "ALT",  37,
  "01-003", "Placebo", "Week 8",  57, "ALT",  39
)

The data are in long format: each row represents one patient, parameter, and assessment.

This structure is particularly convenient for dplyr because grouped operations naturally map onto the hierarchy of clinical observations.

Filtering Clinical Data

The filter() function selects rows satisfying logical conditions.

lab %>%
  filter(PARAMCD == "ALT")

Multiple conditions can be combined.

lab %>%
  filter(
    PARAMCD == "ALT",
    ADY > 0,
    AVAL > 40
  )

This retains ALT observations after baseline where the value exceeds 40.

OR Conditions

The logical operator | represents OR.

lab %>%
  filter(
    PARAMCD == "ALT" | PARAMCD == "AST"
  )

The equivalent membership expression is usually cleaner.

lab %>%
  filter(
    PARAMCD %in% c("ALT", "AST")
  )

Excluding Values

lab %>%
  filter(
    !PARAMCD %in% c("ALP", "BILI")
  )

Negation should be used carefully when missing values are possible.

Missing Values in Clinical Data

R represents missing values using NA.

A common mistake is writing:

filter(AVAL == NA)

This does not correctly identify missing values.

Instead use:

filter(is.na(AVAL))

or:

filter(!is.na(AVAL))

This distinction becomes particularly important when deriving analysis populations and selecting records.

Selecting Variables

The select() function controls which columns are retained.

lab %>%
  select(
    USUBJID,
    TRT01P,
    AVISIT,
    ADY,
    PARAMCD,
    AVAL
  )

Variables can also be removed.

lab %>%
  select(
    -PARAMCD
  )

A useful clinical-programming pattern is to keep the variables required for traceability while dropping unnecessary source columns only at a deliberate dataset boundary.

Renaming Variables

lab %>%
  rename(
    SUBJECT = USUBJID,
    VALUE = AVAL
  )

In clinical programming, however, renaming should be done intentionally.

For CDISC-oriented workflows, standardized variable names such as USUBJID, AVAL, BASE, CHG, and PCHG often carry important semantic meaning.

Sorting Data

Use arrange() to control row order.

lab %>%
  arrange(
    USUBJID,
    ADY
  )

Descending order can be specified using desc().

lab %>%
  arrange(
    USUBJID,
    desc(ADY)
  )

Sorting is particularly important before position-based operations such as selecting the first or last assessment.

Creating Variables With mutate()

The mutate() function creates derived variables.

For example, suppose a laboratory value has a reference upper limit of 40.

lab %>%
  mutate(
    ABOVE_ULN = AVAL > 40
  )

A categorical interpretation can then be created.

lab %>%
  mutate(
    ALT_STATUS = case_when(
      is.na(AVAL) ~ NA_character_,
      AVAL > 40 ~ "High",
      TRUE ~ "Not High"
    )
  )

case_when() for Clinical Derivations

Complex clinical derivations often require several conditions.

lab %>%
  mutate(
    TOX_GRADE = case_when(
      is.na(AVAL) ~ NA_integer_,
      AVAL < 40 ~ 0L,
      AVAL < 80 ~ 1L,
      AVAL < 120 ~ 2L,
      AVAL < 200 ~ 3L,
      TRUE ~ 4L
    )
  )

The example is illustrative rather than a substitute for an actual toxicity grading algorithm.

The TRUE Condition

A final TRUE condition acts as a catch-all.

mutate(
  CATEGORY = case_when(
    AVAL < 10 ~ "Low",
    AVAL < 20 ~ "Moderate",
    TRUE ~ "High"
  )
)

This pattern is useful when every nonmissing observation should receive a category.

Deriving Change From Baseline

One of the most common clinical-analysis derivations is change from baseline.

If:

and:

then:

In dplyr:

analysis <- analysis %>%
  mutate(
    CHG = AVAL - BASE
  )

Deriving Percent Change From Baseline

Percentage change is:

The corresponding dplyr expression is:

analysis <- analysis %>%
  mutate(
    PCHG = 100 * (AVAL - BASE) / BASE
  )

In production programming, the denominator should be explicitly protected against missing or zero baseline values.

analysis <- analysis %>%
  mutate(
    PCHG = case_when(
      is.na(AVAL) | is.na(BASE) ~ NA_real_,
      BASE == 0 ~ NA_real_,
      TRUE ~ 100 * (AVAL - BASE) / BASE
    )
  )

Why Explicit Missing-Value Logic Matters

Consider a baseline value of zero.

The percentage change is undefined.

A robust clinical derivation should not allow an invalid denominator to silently propagate through the analysis dataset.

group_by(): The Core of Subject-Level Clinical Programming

Clinical datasets are frequently hierarchical.

For example:

• study;
• treatment;
• subject;
• parameter;
• visit;
• assessment.

The group_by() function allows calculations to be performed within these groups.

lab %>%
  group_by(USUBJID) %>%
  summarise(
    N = n(),
    MIN_VALUE = min(AVAL, na.rm = TRUE),
    MAX_VALUE = max(AVAL, na.rm = TRUE)
  )

This produces one row per subject.

Grouping by Multiple Variables

Often the correct unit is more specific than subject.

For laboratory data, a useful grouping may be:

lab %>%
  group_by(
    USUBJID,
    PARAMCD
  ) %>%
  summarise(
    N = sum(!is.na(AVAL)),
    MIN_VALUE = min(AVAL, na.rm = TRUE),
    MAX_VALUE = max(AVAL, na.rm = TRUE),
    .groups = "drop"
  )

This creates one summary per subject and parameter.

The Importance of .groups

After summarise(), grouping behavior matters.

For explicit clinical programming, it is often useful to write:

summarise(
  ...,
  .groups = "drop"
)

This makes it clear that the resulting dataset is intentionally ungrouped.

Counting Records

The n() function counts rows within each group.

lab %>%
  group_by(USUBJID) %>%
  summarise(
    N_RECORDS = n(),
    N_NONMISSING = sum(!is.na(AVAL)),
    .groups = "drop"
  )

The distinction between total records and nonmissing records can be important in clinical data review.

Counting Distinct Patients

lab %>%
  summarise(
    N_SUBJECTS = n_distinct(USUBJID)
  )

This is preferable to counting rows when multiple records can exist per patient.

distinct() and Duplicate Detection

The distinct() function can identify unique combinations.

lab %>%
  distinct(
    USUBJID,
    TRT01P
  )

This can be useful for validating that each subject maps to a single planned treatment.

However, distinct() should not be used blindly to remove duplicates.

Two apparently duplicate records may actually represent different assessments or different collection events.

Finding Potential Duplicates

A better QC pattern is to count records at the expected uniqueness level.

lab %>%
  count(
    USUBJID,
    PARAMCD,
    ADY,
    name = "N"
  ) %>%
  filter(N > 1)

This identifies subject-parameter-day combinations occurring more than once.

Filtering After Grouping

The behavior of filter() changes when data are grouped.

For example, to retain subjects whose maximum ALT exceeds 100:

lab %>%
  group_by(USUBJID) %>%
  filter(
    max(AVAL, na.rm = TRUE) > 100
  ) %>%
  ungroup()

Every record for qualifying subjects is retained.

This is different from filtering individual observations with:

filter(AVAL > 100)

The first asks:

The second asks:

This distinction is fundamental in clinical data programming.

slice_min() and slice_max()

Modern dplyr provides useful functions for selecting extreme observations.

For example, identify the lowest ALT measurement for each subject:

lab %>%
  group_by(USUBJID) %>%
  slice_min(
    order_by = AVAL,
    n = 1,
    with_ties = TRUE
  ) %>%
  ungroup()

The corresponding maximum is:

lab %>%
  group_by(USUBJID) %>%
  slice_max(
    order_by = AVAL,
    n = 1,
    with_ties = TRUE
  ) %>%
  ungroup()

Why with_ties Matters

Suppose two observations have the same maximum value.

With:

with_ties = TRUE

both records can be returned.

With:

with_ties = FALSE

only one record is retained.

For clinical programming, arbitrary tie-breaking should generally be avoided unless the specification explicitly defines how the tie is resolved.

Selecting the First Record

After sorting:

data %>%
  arrange(
    USUBJID,
    ADY
  ) %>%
  group_by(USUBJID) %>%
  slice_head(n = 1) %>%
  ungroup()

This selects the earliest record according to the current sort order.

Selecting the Last Record

data %>%
  arrange(
    USUBJID,
    ADY
  ) %>%
  group_by(USUBJID) %>%
  slice_tail(n = 1) %>%
  ungroup()

Again, the definition of "last" depends entirely on the ordering variables.

Baseline Selection

Baseline derivation is one of the most important uses of grouped dplyr operations.

A simplified rule might be:

Suppose:

lab %>%
  filter(
    !is.na(AVAL),
    ADY <= 1
  ) %>%
  group_by(
    USUBJID,
    PARAMCD
  ) %>%
  slice_max(
    order_by = ADY,
    n = 1,
    with_ties = FALSE
  ) %>%
  ungroup()

This is only an illustrative baseline rule.

A real study might define baseline using:

• date/time relative to first dose;
• nominal versus actual assessment date;
• multiple assessments on the same date;
• screening measurements;
• unscheduled assessments;
• duplicate or replicate results;
• eligibility criteria; or
• a parameter-specific derivation.

Creating BASE and Merging It Back

One robust pattern is to derive baseline separately and then join it back.

baseline <- lab %>%
  filter(
    !is.na(AVAL),
    ADY <= 1
  ) %>%
  group_by(
    USUBJID,
    PARAMCD
  ) %>%
  slice_max(
    order_by = ADY,
    n = 1,
    with_ties = FALSE
  ) %>%
  ungroup() %>%
  select(
    USUBJID,
    PARAMCD,
    BASE = AVAL
  )

analysis <- lab %>%
  left_join(
    baseline,
    by = c("USUBJID", "PARAMCD")
  ) %>%
  mutate(
    CHG = AVAL - BASE
  )

This pattern has a major advantage: the baseline derivation is isolated and can be independently validated.

Joining Clinical Data

Clinical programming frequently requires combining datasets.

Typical examples include:

• subject-level treatment data with laboratory data;
• demographics with adverse events;
• exposure with laboratory observations;
• baseline values with post-baseline records;
• analysis population flags with parameter-level data.

dplyr provides several join functions.

left_join(): The Most Common Clinical Join

Suppose a subject-level dataset contains treatment assignment.

adsl <- tibble::tribble(
  ~USUBJID, ~TRT01P, ~SAFFL,
  "01-001", "Drug A", "Y",
  "01-002", "Drug A", "Y",
  "01-003", "Placebo", "Y",
  "01-004", "Placebo", "N"
)

Treatment can be attached to laboratory data:

lab_analysis <- lab %>%
  left_join(
    adsl %>%
      select(
        USUBJID,
        TRT01P,
        SAFFL
      ),
    by = "USUBJID"
  )

This is conceptually similar to merging subject-level attributes onto observation-level data.

The Most Important Join Question

Before performing a join, ask:

For example:

• One subject in ADSL → many observations in ADLB
• One subject in ADSL → many adverse events in ADAE
• One subject + parameter → many longitudinal observations
• One subject + parameter → one baseline record

Understanding this relationship is essential because an incorrect join can silently multiply records.

Join Multiplication

Suppose:

a

contains two rows for subject 001 and:

b

also contains two rows for subject 001.

A simple join on USUBJID can produce four combinations.

Checking Join Keys Before Joining

Suppose a baseline dataset is expected to contain one row per subject and parameter.

baseline %>%
  count(
    USUBJID,
    PARAMCD
  ) %>%
  filter(n > 1)

If this returns records, the baseline dataset does not satisfy the expected one-row-per-subject-parameter structure.

semi_join(): Population Filtering

Suppose adsl defines the safety population.

safety_subjects <- adsl %>%
  filter(SAFFL == "Y")

ae_safety <- ae %>%
  semi_join(
    safety_subjects,
    by = "USUBJID"
  )

The advantage is that semi_join() filters the rows without bringing treatment variables into the adverse-event dataset.

anti_join(): A Powerful QC Tool

Suppose every subject in an analysis dataset should exist in ADSL.

analysis %>%
  distinct(USUBJID) %>%
  anti_join(
    adsl %>% distinct(USUBJID),
    by = "USUBJID"
  )

Any returned subjects are not present in ADSL.

This is an excellent way to identify unexpected subject mismatches.

Join Validation

For important joins, dplyr supports explicit relationship specifications in modern versions.

analysis %>%
  left_join(
    baseline,
    by = c("USUBJID", "PARAMCD"),
    relationship = "many-to-one"
  )

The statement communicates an important assumption:

If the data violate that expected relationship, the join can be flagged rather than silently producing an unintended result.

Joining With Multiple Keys

Clinical datasets often require composite keys.

analysis %>%
  left_join(
    baseline,
    by = c(
      "USUBJID",
      "PARAMCD"
    )
  )

Other possible keys include:

• USUBJID + PARAMCD
• USUBJID + AVISIT
• USUBJID + PARAMCD + AVISIT
• USUBJID + ADY + PARAMCD

The correct key is determined by the business and analysis definition of the records, not by whichever variables happen to produce a successful join.

Window Functions

Some of the most powerful dplyr functionality comes from window functions.

Unlike summarise(), which reduces multiple rows to fewer rows, window functions can calculate information across rows while retaining the original observations.

Examples include:

• lag()
• lead()
• row_number()
• min_rank()
• dense_rank()
• percent_rank()
• cumsum()

lag(): Previous Clinical Observation

Suppose laboratory observations are ordered chronologically.

lab %>%
  arrange(
    USUBJID,
    PARAMCD,
    ADY
  ) %>%
  group_by(
    USUBJID,
    PARAMCD
  ) %>%
  mutate(
    PREV_AVAL = lag(AVAL)
  ) %>%
  ungroup()

The previous observation can then be used to calculate within-patient change.

lab %>%
  arrange(
    USUBJID,
    PARAMCD,
    ADY
  ) %>%
  group_by(
    USUBJID,
    PARAMCD
  ) %>%
  mutate(
    PREV_AVAL = lag(AVAL),
    CHANGE_FROM_PREVIOUS = AVAL - PREV_AVAL
  ) %>%
  ungroup()

lead(): The Next Observation

lab %>%
  arrange(
    USUBJID,
    PARAMCD,
    ADY
  ) %>%
  group_by(
    USUBJID,
    PARAMCD
  ) %>%
  mutate(
    NEXT_ADY = lead(ADY)
  ) %>%
  ungroup()

This can be useful for identifying the interval until the next assessment.

Calculating Time Between Assessments

lab %>%
  arrange(
    USUBJID,
    PARAMCD,
    ADY
  ) %>%
  group_by(
    USUBJID,
    PARAMCD
  ) %>%
  mutate(
    DAYS_TO_NEXT = lead(ADY) - ADY
  ) %>%
  ungroup()

This can help identify unusually long gaps between assessments.

row_number()

Within each patient, observations can be numbered.

lab %>%
  arrange(
    USUBJID,
    ADY
  ) %>%
  group_by(USUBJID) %>%
  mutate(
    RECORD_NUMBER = row_number()
  ) %>%
  ungroup()

This can be useful for QC and exploratory review.

Identifying the First Post-Baseline Assessment

lab %>%
  filter(
    ADY > 1,
    !is.na(AVAL)
  ) %>%
  arrange(
    USUBJID,
    ADY
  ) %>%
  group_by(
    USUBJID,
    PARAMCD
  ) %>%
  slice_head(n = 1) %>%
  ungroup()

This selects the earliest qualifying post-baseline assessment.

Creating Analysis Flags

Clinical analysis datasets often contain flags such as:

• SAFFL
• ITTFL
• EFFFL
• ANL01FL
• ONTRTFL
• ABLFL

A simplified example:

analysis <- analysis %>%
  mutate(
    ANL01FL = if_else(
      !is.na(AVAL) & AVISIT != "Baseline",
      "Y",
      NA_character_
    )
  )

The actual derivation must follow the study-specific analysis specification.

if_else() vs. ifelse()

dplyr's if_else() is generally preferable in typed clinical programming because it is stricter about data types.

mutate(
  FLAG = if_else(
    AVAL > 100,
    "Y",
    "N"
  )
)

The stricter type behavior can help expose unexpected derivation problems.

Creating Subject-Level Flags From Longitudinal Data

Suppose a subject should receive an indicator if any ALT measurement exceeds 100.

alt_flags <- lab %>%
  group_by(USUBJID) %>%
  summarise(
    ALT_HIGH_FL = if_else(
      any(AVAL > 100, na.rm = TRUE),
      "Y",
      "N"
    ),
    .groups = "drop"
  )

The resulting dataset has one row per subject.

Any vs. All

Clinical derivations frequently ask whether:

or:

dplyr can express both:

group_by(USUBJID) %>%
summarise(
  ANY_HIGH = any(AVAL > 100, na.rm = TRUE),
  ALL_HIGH = all(AVAL > 100, na.rm = TRUE),
  .groups = "drop"
)

Be particularly careful with missing values when using these functions.

Handling Dates

Clinical datasets contain multiple date concepts:

• date of randomization;
• date of first dose;
• assessment date;
• adverse-event start date;
• adverse-event end date.

Dates should be represented using appropriate date classes rather than treated as arbitrary character strings.

analysis <- analysis %>%
  mutate(
    ADT = as.Date(ADT)
  )

Once dates are represented appropriately, date arithmetic becomes much safer.

Deriving Study Day

A simplified study-day derivation is:

for dates on or after treatment start, depending on the study's day-numbering convention.

analysis <- analysis %>%
  mutate(
    ADY = as.integer(ADT - TRTSDT) + 1
  )

Working With Visit Windows

Clinical assessments are frequently collected near, but not exactly on, their nominal visit days.

Suppose Week 4 is nominally Day 29 with an allowed window of ±7 days.

analysis <- analysis %>%
  mutate(
    WEEK4_FL = ADY >= 22 & ADY <= 36
  )

A more scalable approach is to maintain a visit-window lookup table.

visit_windows <- tibble::tribble(
  ~AVISIT, ~TARGET_DAY, ~LOWER_DAY, ~UPPER_DAY,
  "Week 4",  29, 22, 36,
  "Week 8",  57, 50, 64,
  "Week 12", 85, 78, 92
)

The assessment can then be compared against the appropriate window.

Why Visit Assignment Is More Complicated Than It Looks

Suppose an assessment occurs on Day 57.

It might map exactly to Week 8.

But if a patient has assessments on Days 55 and 60, the analysis specification must determine:

• whether both are eligible;
• which is closest to target;
• whether earlier or later values are preferred in a tie;
• whether the visit is nominal or analysis-derived; and
• whether unscheduled assessments can contribute.

These decisions should not be hidden inside an arbitrary dplyr pipeline.

Selecting the Observation Closest to Target Day

A common derivation is to calculate the absolute deviation from the target.

analysis %>%
  mutate(
    DIST_FROM_TARGET = abs(ADY - 57)
  ) %>%
  filter(
    ADY >= 50,
    ADY <= 64
  ) %>%
  group_by(USUBJID, PARAMCD) %>%
  slice_min(
    order_by = DIST_FROM_TARGET,
    n = 1,
    with_ties = FALSE
  ) %>%
  ungroup()

If ties matter, the tie-breaker should be explicitly encoded.

Explicit Tie-Breaking

Suppose two observations are equally close to the target day.

You might specify preference for the earlier observation:

analysis %>%
  filter(
    ADY >= 50,
    ADY <= 64
  ) %>%
  mutate(
    DIST_FROM_TARGET = abs(ADY - 57)
  ) %>%
  arrange(
    USUBJID,
    PARAMCD,
    DIST_FROM_TARGET,
    ADY
  ) %>%
  group_by(
    USUBJID,
    PARAMCD
  ) %>%
  slice_head(n = 1) %>%
  ungroup()

The important point is not the exact tie-breaker; it is that the tie-breaker should be deliberate.

The Pipe Operator

The pipe:

%>%

allows a sequence of operations to be expressed as a readable workflow.

analysis %>%
  filter(SAFFL == "Y") %>%
  arrange(USUBJID, ADY) %>%
  mutate(
    CHG = AVAL - BASE
  )

The equivalent sequence of intermediate assignments would be considerably more verbose.

The Native Pipe

Modern R also supports the native pipe:

analysis |>
  filter(SAFFL == "Y") |>
  arrange(USUBJID, ADY) |>
  mutate(
    CHG = AVAL - BASE
  )

Both styles are valid.

A clinical programming team should generally establish a consistent coding standard rather than mixing styles arbitrarily.

Building a Complete Clinical Pipeline

Consider the following conceptual workflow:

Complete Example: Laboratory Analysis Dataset

The following example demonstrates a more realistic sequence.

library(dplyr)

analysis <- lb %>%

  filter(
    PARAMCD == "ALT",
    !is.na(AVAL)
  ) %>%

  left_join(
    adsl %>%
      select(
        USUBJID,
        TRT01P,
        SAFFL,
        TRTSDT
      ),
    by = "USUBJID",
    relationship = "many-to-one"
  ) %>%

  mutate(
    ADY = as.integer(ADT - TRTSDT) + 1
  ) %>%

  group_by(
    USUBJID,
    PARAMCD
  ) %>%

  arrange(
    ADY,
    .by_group = TRUE
  ) %>%

  mutate(
    BASE = if_else(
      ADY <= 1,
      AVAL,
      NA_real_
    )
  ) %>%

  ungroup()

The example illustrates an important concept: clinical derivations are often best understood as a series of explicit transformations rather than one enormous expression.

A Better Baseline Pattern

For production work, isolate the baseline derivation.

baseline <- lb %>%

  filter(
    PARAMCD == "ALT",
    ADY <= 1,
    !is.na(AVAL)
  ) %>%

  arrange(
    USUBJID,
    PARAMCD,
    desc(ADY)
  ) %>%

  group_by(
    USUBJID,
    PARAMCD
  ) %>%

  slice_head(n = 1) %>%

  ungroup() %>%

  select(
    USUBJID,
    PARAMCD,
    BASE = AVAL,
    ABLFL = "Y"
  )

Then attach the baseline:

analysis <- lb %>%

  filter(
    PARAMCD == "ALT"
  ) %>%

  left_join(
    baseline,
    by = c(
      "USUBJID",
      "PARAMCD"
    ),
    relationship = "many-to-one"
  ) %>%

  mutate(
    CHG = AVAL - BASE,
    PCHG = case_when(
      is.na(AVAL) | is.na(BASE) ~ NA_real_,
      BASE == 0 ~ NA_real_,
      TRUE ~ 100 * CHG / BASE
    )
  )

Why This Is More Robust

The pipeline separates three logically distinct operations:

1. Identify baseline.
2. Attach baseline to observations.
3. Calculate derived values.

This makes each component easier to test independently.

Grouped mutate() vs. summarise()

This distinction is essential.

Suppose there are five laboratory observations per subject.

Using:

group_by(USUBJID) %>%
summarise(
  MAX_AVAL = max(AVAL, na.rm = TRUE)
)

produces one row per subject.

Using:

group_by(USUBJID) %>%
mutate(
  MAX_AVAL = max(AVAL, na.rm = TRUE)
)

retains all five observations and attaches the subject-level maximum to each one.

Subject-Level Maximum With mutate()

analysis %>%
  group_by(USUBJID) %>%
  mutate(
    MAX_PCHG = max(PCHG, na.rm = TRUE)
  ) %>%
  ungroup()

Every observation now carries the patient's maximum percentage change.

Subject-Level Nadir

For tumor-response data, the minimum percentage change can represent the observed maximum tumor shrinkage.

analysis %>%
  group_by(USUBJID) %>%
  mutate(
    NADIR_PCHG = min(
      PCHG,
      na.rm = TRUE
    )
  ) %>%
  ungroup()

Production code should explicitly handle subjects for whom all values are missing.

Handling All-Missing Groups

A safer pattern is:

analysis %>%
  group_by(USUBJID) %>%
  mutate(
    NADIR_PCHG = if_else(
      all(is.na(PCHG)),
      NA_real_,
      min(PCHG, na.rm = TRUE)
    )
  ) %>%
  ungroup()

This avoids generating misleading values when no usable observations exist.

Creating a Best-Response Record

Suppose the analysis requires the observation corresponding to the maximum tumor shrinkage.

best_change <- analysis %>%
  filter(
    !is.na(PCHG)
  ) %>%
  group_by(
    USUBJID
  ) %>%
  slice_min(
    order_by = PCHG,
    n = 1,
    with_ties = FALSE
  ) %>%
  ungroup()

The important distinction is that this is selecting an observation, whereas the subject-level minimum could be calculated with min().

Finding the Date of Maximum Toxicity

Suppose the analysis dataset contains a toxicity grade.

max_tox <- ae_analysis %>%
  group_by(USUBJID) %>%
  slice_max(
    order_by = ATOXGR,
    n = 1,
    with_ties = TRUE
  ) %>%
  ungroup()

If several events share the same maximum grade, multiple records may be returned.

Whether that is appropriate depends on the downstream analysis.

When You Need Exactly One Record

If the specification requires exactly one record, a deterministic tie-breaker should be defined.

max_tox <- ae_analysis %>%
  arrange(
    USUBJID,
    desc(ATOXGR),
    AESTDT,
    AESEQ
  ) %>%
  group_by(USUBJID) %>%
  slice_head(n = 1) %>%
  ungroup()

Here the ordering hierarchy is:

1. highest toxicity grade;
2. earliest start date;
3. lowest sequence number.

This is substantially safer than relying on arbitrary row order.

Working With Treatment Groups

Treatment variables often need to be transformed for reporting.

analysis %>%
  mutate(
    TRTGRP = case_when(
      TRT01P == "Placebo" ~ "Control",
      TRT01P == "Drug A" ~ "Active",
      TRT01P == "Drug B" ~ "Active",
      TRUE ~ NA_character_
    )
  )

For controlled clinical reporting, treatment labels should normally come from a controlled specification or metadata source rather than being repeatedly hard-coded throughout programs.

Factor Ordering

The display order of treatment groups can matter in tables and figures.

analysis <- analysis %>%
  mutate(
    TRT01P = factor(
      TRT01P,
      levels = c(
        "Placebo",
        "Drug A",
        "Drug B"
      )
    )
  )

This can make downstream reporting deterministic.

Across-Patient Summaries

Once patient-level values have been derived, dplyr can summarize them by treatment.

analysis %>%
  group_by(TRT01P) %>%
  summarise(
    N = n_distinct(USUBJID),
    MEAN = mean(AVAL, na.rm = TRUE),
    SD = sd(AVAL, na.rm = TRUE),
    MEDIAN = median(AVAL, na.rm = TRUE),
    MIN = min(AVAL, na.rm = TRUE),
    MAX = max(AVAL, na.rm = TRUE),
    .groups = "drop"
  )

This is a useful starting point for exploratory analysis, although production clinical tables usually require more sophisticated handling of denominators, missingness, precision, and analysis populations.

Conditional Summaries

Suppose we want the number of subjects with an ALT value greater than 100.

lab %>%
  group_by(TRT01P) %>%
  summarise(
    N_SUBJECTS = n_distinct(USUBJID),
    N_HIGH = n_distinct(
      USUBJID[AVAL > 100]
    ),
    .groups = "drop"
  )

This distinguishes the number of qualifying subjects from the number of qualifying observations.

Observation Counts vs. Subject Counts

This distinction is one of the most common sources of clinical-programming errors.

If a patient has five abnormal ALT measurements:

but:

The correct denominator depends on the analysis.

Counting Subjects With dplyr

analysis %>%
  filter(
    AVAL > 100
  ) %>%
  summarise(
    N = n_distinct(USUBJID)
  )

The use of n_distinct() is often critical when multiple records can exist per subject.

A Common Anti-Pattern

Avoid assuming:

n()

means number of patients.

It means number of rows in the current group.

For patient-level counts, use:

n_distinct(USUBJID)

when appropriate.

Clinical Population Selection

Suppose ADSL contains:

• SAFFL — safety population;
• ITTFL — intent-to-treat population;
• EFFFL — efficacy population.

A safety analysis can begin with:

adsl_safety <- adsl %>%
  filter(
    SAFFL == "Y"
  )

An observation-level dataset can then be restricted using:

analysis <- observations %>%
  semi_join(
    adsl_safety,
    by = "USUBJID"
  )

This provides a clear separation between defining the population and manipulating observations.

Filtering by Treatment and Population

adsl %>%
  filter(
    SAFFL == "Y",
    TRT01P == "Drug A"
  )

Multiple conditions inside filter() are evaluated as AND conditions.

Using Between()

For numeric ranges:

filter(
  ADY %in% 1:7
)

or:

filter(
  between(ADY, 1, 7)
)

The latter can improve readability.

String Operations

Clinical data frequently contain character variables requiring classification.

Useful functions include:

• str_detect()
• str_starts()
• str_ends()
• str_to_upper()
• str_trim()

For example:

analysis %>%
  filter(
    stringr::str_detect(
      PARAM,
      "Alanine"
    )
  )

Character normalization can be useful when integrating data from multiple sources.

Missing Character Values

Do not assume an empty string and NA mean the same thing.

analysis %>%
  mutate(
    PARAM = na_if(
      stringr::str_trim(PARAM),
      ""
    )
  )

This converts empty strings into missing values.

Coalesce()

The coalesce() function returns the first nonmissing value.

analysis %>%
  mutate(
    FINAL_VALUE = coalesce(
      AVAL,
      AVALC
    )
  )

This can be useful when integrating data sources, but it should not be used to arbitrarily substitute one clinical measurement for another.

Across() for Multiple Variables

dplyr can apply the same operation across multiple columns.

data %>%
  mutate(
    across(
      c(AVAL, BASE, CHG),
      as.numeric
    )
  )

For descriptive summaries:

data %>%
  summarise(
    across(
      c(AVAL, BASE, CHG),
      ~ mean(.x, na.rm = TRUE)
    )
  )

This is useful for exploratory data processing but should be used cautiously when variables have different semantic meanings.

Using across() With Naming

data %>%
  summarise(
    across(
      c(AVAL, CHG),
      list(
        N = ~ sum(!is.na(.x)),
        MEAN = ~ mean(.x, na.rm = TRUE),
        SD = ~ sd(.x, na.rm = TRUE)
      ),
      .names = "{.col}_{.fn}"
    )
  )

This creates variables such as:

• AVAL_N
• AVAL_MEAN
• AVAL_SD
• CHG_N
• CHG_MEAN
• CHG_SD

Reusable Functions

If the same derivation appears repeatedly, write a function rather than copying code.

derive_pchg <- function(aval, base) {

  case_when(
    is.na(aval) | is.na(base) ~ NA_real_,
    base == 0 ~ NA_real_,
    TRUE ~ 100 * (aval - base) / base
  )

}

Then:

analysis <- analysis %>%
  mutate(
    PCHG = derive_pchg(
      AVAL,
      BASE
    )
  )

This can improve consistency across analysis programs.

Custom Functions and Traceability

Reusable functions are particularly useful when they encapsulate stable, well-defined rules.

However, avoid hiding study-specific logic inside opaque functions that make the derivation difficult for independent programmers or reviewers to inspect.

Programming With Temporary Variables

Temporary variables can make complex derivations easier to understand.

analysis <- analysis %>%
  mutate(
    DIST = abs(ADY - TARGET_DAY),
    IN_WINDOW = between(
      ADY,
      LOWER_DAY,
      UPPER_DAY
    )
  ) %>%
  filter(
    IN_WINDOW
  ) %>%
  group_by(
    USUBJID,
    PARAMCD
  ) %>%
  slice_min(
    DIST,
    n = 1,
    with_ties = FALSE
  ) %>%
  ungroup() %>%
  select(
    -DIST,
    -IN_WINDOW
  )

Temporary variables make the logic inspectable and can then be removed once they have served their purpose.

Clinical Data Reshaping

dplyr itself primarily manipulates data in their existing structure.

For reshaping between long and wide formats, the companion tidyr package is commonly used.

For example, converting selected laboratory visits into columns:

analysis %>%
  tidyr::pivot_wider(
    id_cols = USUBJID,
    names_from = AVISIT,
    values_from = AVAL
  )

This can be useful for exploratory work.

However, long format is often preferable for analysis programming because it supports grouped operations and longitudinal procedures naturally.

Long vs. Wide Clinical Data

Programmatic QC With count()

One of the simplest and most powerful QC techniques is to count expected keys.

analysis %>%
  count(
    USUBJID,
    PARAMCD
  ) %>%
  summarise(
    MAX_RECORDS = max(n),
    MIN_RECORDS = min(n),
    N_GROUPS = n()
  )

Unexpected counts can reveal duplicate or missing observations.

Checking One Treatment Per Subject

adsl %>%
  group_by(USUBJID) %>%
  summarise(
    N_TREATMENTS = n_distinct(TRT01P),
    .groups = "drop"
  ) %>%
  filter(
    N_TREATMENTS > 1
  )

This is a simple but valuable validation check.

Checking One Baseline Per Subject and Parameter

baseline %>%
  count(
    USUBJID,
    PARAMCD
  ) %>%
  filter(
    n != 1
  )

A zero-row result is expected if every subject-parameter combination should have exactly one baseline record.

Checking Missing Baselines

analysis %>%
  distinct(
    USUBJID,
    PARAMCD,
    BASE
  ) %>%
  filter(
    is.na(BASE)
  )

Missing baseline values should be investigated rather than automatically replaced.

Checking Impossible Values

Clinical programmers should create domain-specific QC checks.

For example:

analysis %>%
  filter(
    AVAL < 0
  ) %>%
  select(
    USUBJID,
    PARAMCD,
    AVISIT,
    ADY,
    AVAL
  )

Whether a negative value is impossible depends on the parameter.

The important principle is to encode scientifically meaningful expectations where possible.

Checking Chronological Ordering

analysis %>%
  arrange(
    USUBJID,
    PARAMCD,
    ADY
  ) %>%
  group_by(
    USUBJID,
    PARAMCD
  ) %>%
  mutate(
    PREVIOUS_ADY = lag(ADY),
    ORDER_ERROR = !is.na(PREVIOUS_ADY) &&
      ADY < PREVIOUS_ADY
  ) %>%
  ungroup()

The exact implementation can be simplified after sorting, but the concept is important: longitudinal calculations require deliberate ordering.

Ungrouping Is a Defensive Practice

Suppose:

data %>%
  group_by(USUBJID) %>%
  mutate(
    MAX_AVAL = max(AVAL, na.rm = TRUE)
  ) %>%
  mutate(
    GRAND_MEAN = mean(AVAL, na.rm = TRUE)
  )

The second calculation is still grouped by subject.

If the intention was a study-wide mean, the grouping must be removed.

data %>%
  group_by(USUBJID) %>%
  mutate(
    MAX_AVAL = max(AVAL, na.rm = TRUE)
  ) %>%
  ungroup() %>%
  mutate(
    GRAND_MEAN = mean(AVAL, na.rm = TRUE)
  )

Grouped Sorting With .by_group

When working with grouped data:

data %>%
  group_by(
    USUBJID,
    PARAMCD
  ) %>%
  arrange(
    ADY,
    .by_group = TRUE
  )

This ensures that rows are ordered within each group.

The .by Argument

Modern dplyr also supports temporary grouping through the .by argument.

data %>%
  mutate(
    MAX_AVAL = max(AVAL, na.rm = TRUE),
    .by = USUBJID
  )

This can be convenient when the grouping applies to only one operation.

For long, complex clinical derivations, explicit group_by() and ungroup() may remain easier to read because the grouping state is visible.

When .by Is Useful

analysis %>%
  mutate(
    BASE = first(AVAL),
    .by = c(USUBJID, PARAMCD)
  )

This avoids changing the persistent grouping state of the data frame.

Building ADaM-Style Variables

A simplified analysis dataset might contain:

dplyr is particularly effective for implementing these row-level and subject-level derivations.

ADSL + BDS-Style Workflow

A common pattern is:

Traceability in dplyr Pipelines

A clinical-programming pipeline should make it possible to answer:

• Where did this variable come from?
• Which source records contributed?
• What rule selected this observation?
• Which population flag was applied?
• How was baseline determined?
• How was the derived value calculated?

For example:

analysis <- lb %>%

  # Restrict to the parameter
  filter(
    PARAMCD == "ALT"
  ) %>%

  # Attach subject-level attributes
  left_join(
    adsl %>%
      select(
        USUBJID,
        TRT01P,
        SAFFL,
        TRTSDT
      ),
    by = "USUBJID",
    relationship = "many-to-one"
  ) %>%

  # Derive analysis day
  mutate(
    ADY = as.integer(ADT - TRTSDT) + 1
  ) %>%

  # Attach baseline
  left_join(
    baseline,
    by = c(
      "USUBJID",
      "PARAMCD"
    ),
    relationship = "many-to-one"
  ) %>%

  # Derive change variables
  mutate(
    CHG = AVAL - BASE,
    PCHG = derive_pchg(
      AVAL,
      BASE
    )
  )

Comments should explain the clinical purpose of a transformation rather than merely restating the function name.

Good Comment

# Select the last nonmissing assessment
# before first dose as baseline

Weak Comment

# Use slice_max()

The first comment explains the analysis rule.

Modular Clinical Programming

Large dplyr programs should generally be divided into logical stages.

raw_data
    ↓
population_data
    ↓
baseline_data
    ↓
analysis_data
    ↓
analysis_flags
    ↓
reporting_data

Each stage should have a clear purpose.

Example of Modular Code

population <- adsl %>%
  filter(
    SAFFL == "Y"
  )

baseline <- derive_baseline(
  lb,
  population
)

analysis <- derive_analysis(
  lb,
  population,
  baseline
)

qc <- validate_analysis(
  analysis
)

This architecture can be easier to maintain than one very long pipeline.

Common dplyr Mistake: Filtering Too Early

Consider a baseline rule requiring the last measurement before treatment.

If post-baseline observations are filtered out before the baseline derivation, that may be harmless.

But if the filter accidentally removes a valid baseline candidate, the derived baseline can change.

Always identify which records are needed for each derivation before applying population or timing filters.

Common Mistake: Joining Before Establishing Uniqueness

Suppose baseline contains duplicate subject-parameter records.

Joining it onto the longitudinal dataset can multiply records.

The safer sequence is:

1. derive baseline;
2. validate baseline uniqueness;
3. join baseline;
4. validate post-join counts.

Common Mistake: Using distinct() to Hide a Join Problem

A programmer may notice that a join created extra rows and respond with:

distinct()

This can make the dataset appear correct while silently discarding records.

Common Mistake: Implicit Row Order

Code such as:

group_by(USUBJID) %>%
slice_head(n = 1)

does not have a meaningful clinical interpretation unless the data have already been sorted according to the intended definition.

Instead:

arrange(
  USUBJID,
  ADY
) %>%
group_by(
  USUBJID
) %>%
slice_head(n = 1)

makes the ordering explicit.

Common Mistake: Ignoring Ties

Suppose two laboratory results occur at the same time.

Using:

slice_min(
  AVAL,
  n = 1,
  with_ties = FALSE
)

arbitrarily chooses one if they tie.

If the study specifies a rule for resolving replicate observations, that rule must be encoded explicitly.

Common Mistake: Forgetting Missingness

Code such as:

min(AVAL)

returns NA when missing values are present.

Using:

min(
  AVAL,
  na.rm = TRUE
)

ignores missing values.

But this does not mean the result is always clinically valid.

If every value is missing, additional handling may be required.

Common Mistake: Confusing Observation and Subject-Level Logic

This:

filter(AVAL > 100)

selects abnormal observations.

This:

group_by(USUBJID) %>%
filter(
  any(AVAL > 100, na.rm = TRUE)
)

selects all observations belonging to subjects who ever had an abnormal value.

Those are fundamentally different operations.

Performance Considerations

Clinical datasets can become large, particularly with:

• laboratory data;
• ECG data;
• vital signs;
• pharmacokinetic observations;
• exposure records;
• high-frequency device data.

For many ordinary clinical-trial datasets, dplyr performs well.

For very large datasets, consider:

• reducing columns early when safe;
• filtering populations deliberately;
• avoiding unnecessary repeated joins;
• using database-backed workflows when appropriate;
• profiling rather than prematurely optimizing.

Keep Only What You Need — But Not Too Early

Selecting relevant variables can reduce memory usage:

lb_small <- lb %>%
  select(
    USUBJID,
    PARAMCD,
    ADT,
    AVAL
  )

However, do not remove variables needed for traceability or downstream derivations merely to make the dataset smaller.

Database-Backed dplyr

One of dplyr's important architectural strengths is that many operations can be translated to SQL for database-backed data.

Conceptually:

This can become important when clinical data are stored in large relational systems rather than local R data frames.

Testing a Pipeline With Small Known Data

Before applying complex derivations to a large clinical dataset, construct a small dataset with known expected results.

test_data <- tibble::tribble(
  ~USUBJID, ~ADY, ~AVAL,
  "001",  -1, 100,
  "001",   1,  90,
  "001",  29,  80,
  "002",  -2, 200,
  "002",   1, 180,
  "002",  29, 160
)

The expected percentage changes are easy to calculate manually.

Such small deterministic examples are extremely useful for unit testing.

Assertions for Clinical QC

Simple checks can be written directly in R.

stopifnot(
  all(
    analysis$BASE >= 0 |
    is.na(analysis$BASE)
  )
)

Another example:

stopifnot(
  nrow(
    baseline %>%
      count(USUBJID, PARAMCD) %>%
      filter(n != 1)
  ) == 0
)

This causes the program to stop if the expected baseline uniqueness rule is violated.

Comparing Program Outputs

Independent validation can compare datasets generated by different programs.

For example, a reference implementation might produce:

reference <- ...

programmer_output <- ...

dplyr::anti_join(
  reference,
  programmer_output,
  by = c(
    "USUBJID",
    "PARAMCD",
    "AVISIT"
  )
)

A reciprocal comparison can identify records present only in the programmer's output.

dplyr::anti_join(
  programmer_output,
  reference,
  by = c(
    "USUBJID",
    "PARAMCD",
    "AVISIT"
  )
)

This is conceptually similar to a dataset-level comparison.

Comparing Derived Values

For numeric variables, exact equality may be inappropriate when different numeric representations or rounding conventions are involved.

A tolerance-based comparison can be implemented explicitly.

comparison <- reference %>%
  inner_join(
    programmer_output,
    by = "USUBJID",
    suffix = c("_REF", "_OUT")
  ) %>%
  mutate(
    MATCH = abs(
      AVAL_REF - AVAL_OUT
    ) < 1e-8
  )

The tolerance should reflect the scientific and computational requirements of the analysis.

A Production-Oriented dplyr Checklist

A More Advanced Example: Selecting the Analysis Record

Suppose an analysis requires one record per subject and parameter at a nominal visit.

The specification states:

• Only observations inside the visit window are eligible.
• The observation closest to the target day is selected.
• If tied, the earlier observation is preferred.
• Missing values are not eligible.

The implementation can be expressed as:

analysis_visit <- data %>%

  filter(
    !is.na(AVAL),
    between(
      ADY,
      LOWER_DAY,
      UPPER_DAY
    )
  ) %>%

  mutate(
    DIST_FROM_TARGET =
      abs(ADY - TARGET_DAY)
  ) %>%

  arrange(
    USUBJID,
    PARAMCD,
    DIST_FROM_TARGET,
    ADY
  ) %>%

  group_by(
    USUBJID,
    PARAMCD
  ) %>%

  slice_head(
    n = 1
  ) %>%

  ungroup() %>%

  mutate(
    ANL01FL = "Y"
  )

This is a good example of translating a written statistical-programming specification into a sequence of explicit transformations.

Why Explicit Pipelines Are Valuable in Clinical Programming

A clinical programmer should be able to read the code and reconstruct the derivation.

For example:

filter(!is.na(AVAL))
    ↓
filter(visit window)
    ↓
calculate distance to target
    ↓
sort by distance
    ↓
sort by tie-breaker
    ↓
select first record
    ↓
set ANL01FL = "Y"

This is much easier to review than a compressed expression whose intent is difficult to determine.

When a Single Pipeline Becomes Too Long

A pipeline containing dozens of transformations may become difficult to debug.

Break it into meaningful intermediate objects:

eligible <- data %>%
  filter(
    !is.na(AVAL)
  )

windowed <- eligible %>%
  filter(
    between(
      ADY,
      LOWER_DAY,
      UPPER_DAY
    )
  )

ranked <- windowed %>%
  mutate(
    DIST = abs(
      ADY - TARGET_DAY
    )
  ) %>%
  arrange(
    USUBJID,
    PARAMCD,
    DIST,
    ADY
  )

selected <- ranked %>%
  group_by(
    USUBJID,
    PARAMCD
  ) %>%
  slice_head(n = 1) %>%
  ungroup()

This may be longer, but it can be substantially easier to validate.

dplyr and SAS Conceptual Mapping

For programmers transitioning from SAS, the following conceptual mapping can be useful.

The mapping is conceptual rather than one-to-one.

dplyr Is Not a Replacement for Clinical Specifications

One of the most important principles is that dplyr implements a derivation; it does not define the derivation.

The specification should determine:

• population;
• baseline;
• visit windows;
• analysis records;
• missing-data handling;
• treatment assignment;
• endpoint definitions;
• censoring rules; and
• other analysis conventions.

dplyr is the programming language used to translate those decisions into data transformations.

Recommended Clinical dplyr Workflow

A robust workflow can be summarized as:

Each stage should have a clear input, output, and validation strategy.

Final Example: End-to-End Analysis Pipeline

The following example combines many of the concepts discussed in this tutorial.

library(dplyr)

#--------------------------------------------------
# 1. Define analysis population
#--------------------------------------------------

population <- adsl %>%
  filter(
    SAFFL == "Y"
  ) %>%
  select(
    USUBJID,
    TRT01P,
    TRTSDT
  )

#--------------------------------------------------
# 2. Identify eligible observations
#--------------------------------------------------

eligible <- lb %>%
  filter(
    PARAMCD == "ALT",
    !is.na(AVAL)
  ) %>%

  semi_join(
    population,
    by = "USUBJID"
  )

#--------------------------------------------------
# 3. Attach treatment variables
#--------------------------------------------------

analysis_base <- eligible %>%

  left_join(
    population,
    by = "USUBJID",
    relationship = "many-to-one"
  ) %>%

  mutate(
    ADY = as.integer(
      ADT - TRTSDT
    ) + 1
  )

#--------------------------------------------------
# 4. Derive baseline
#--------------------------------------------------

baseline <- analysis_base %>%

  filter(
    ADY <= 1
  ) %>%

  arrange(
    USUBJID,
    PARAMCD,
    desc(ADY)
  ) %>%

  group_by(
    USUBJID,
    PARAMCD
  ) %>%

  slice_head(
    n = 1
  ) %>%

  ungroup() %>%

  select(
    USUBJID,
    PARAMCD,
    BASE = AVAL
  )

#--------------------------------------------------
# 5. Attach baseline
#--------------------------------------------------

analysis <- analysis_base %>%

  left_join(
    baseline,
    by = c(
      "USUBJID",
      "PARAMCD"
    ),
    relationship = "many-to-one"
  )

#--------------------------------------------------
# 6. Derive change variables
#--------------------------------------------------

analysis <- analysis %>%

  mutate(
    CHG = AVAL - BASE,

    PCHG = case_when(
      is.na(AVAL) |
        is.na(BASE) |
        BASE == 0 ~ NA_real_,

      TRUE ~
        100 * (AVAL - BASE) / BASE
    )
  )

#--------------------------------------------------
# 7. Identify analysis records
#--------------------------------------------------

analysis <- analysis %>%

  arrange(
    USUBJID,
    ADY
  ) %>%

  group_by(
    USUBJID,
    PARAMCD
  ) %>%

  mutate(
    ANL01FL = if_else(
      !is.na(AVAL) &
        ADY > 1,
      "Y",
      NA_character_
    )
  ) %>%

  ungroup()

#--------------------------------------------------
# 8. QC baseline uniqueness
#--------------------------------------------------

baseline_qc <- baseline %>%

  count(
    USUBJID,
    PARAMCD
  ) %>%

  filter(
    n != 1
  )

#--------------------------------------------------
# 9. QC subject membership
#--------------------------------------------------

subject_qc <- analysis %>%

  distinct(
    USUBJID
  ) %>%

  anti_join(
    population %>%
      distinct(USUBJID),
    by = "USUBJID"
  )

#--------------------------------------------------
# 10. QC duplicate analysis records
#--------------------------------------------------

duplicate_qc <- analysis %>%

  filter(
    ANL01FL == "Y"
  ) %>%

  count(
    USUBJID,
    PARAMCD,
    AVISIT
  ) %>%

  filter(
    n > 1
  )

What Makes This Pipeline Production-Friendly?

Several features are worth emphasizing.

• The analysis population is explicitly defined.
• Population membership is applied using semi_join().
• Treatment information comes from the subject-level dataset.
• Baseline is derived separately.
• The baseline join explicitly declares the expected relationship.
• Change and percentage change are derived with explicit missing-value logic.
• Analysis flags are created separately.
• QC datasets are generated alongside the analysis dataset.

This is much closer to how dplyr should be used in a serious clinical programming environment than simply chaining a few filtering commands together.

Final Clinical Programming Checklist

The Most Important Concepts

If you remember only a few ideas from this tutorial, remember these:

1. Know the grain of your data.
2. Understand the expected cardinality before performing a join.
3. Use group_by() deliberately for within-subject or within-parameter calculations.
4. Use summarise() when reducing records and mutate() when retaining them.
5. Sort explicitly before selecting first, last, closest, or best records.
6. Define tie-breaking rules instead of relying on arbitrary row order.
7. Handle missing values explicitly.
8. Separate baseline derivation from downstream calculations when possible.
9. Use semi_join() and anti_join() as clinical QC tools.
10. Validate the data after every major transformation.

References

Wickham, H., François, R., Henry, L., Müller, K., & Vaughan, D. dplyr: A Grammar of Data Manipulation. R package documentation and reference materials.

Wickham, H., Averick, M., Bryan, J., et al. (2019). Welcome to the Tidyverse. Journal of Open Source Software, 4(43), 1686.

Wickham, H. (2014). Tidy Data. Journal of Statistical Software, 59(10), 1–23.

R Core Team. R: A Language and Environment for Statistical Computing. R Foundation for Statistical Computing, Vienna, Austria.

CDISC. Analysis Data Model (ADaM). Clinical Data Interchange Standards Consortium.

CDISC. Study Data Tabulation Model (SDTM). Clinical Data Interchange Standards Consortium.