Deriving the ADSL Subject-Level Analysis Dataset

The failure mode is quiet and expensive: an ADSL that carries two rows for a single subject, or a SAFFL population count that does not match the safety table denominator, and suddenly every downstream analysis dataset that merges to the spine inherits the defect. ADSL — the Subject-Level Analysis Dataset — is the one-record-per-subject backbone that every other ADaM dataset joins to, so getting its grain, its treatment variables, and its population flags exactly right is the single highest-leverage derivation in a submission. This page is the step-by-step companion to ADaM Dataset Derivation for Statistical Analysis and sits within the wider Clinical Data Architecture & EDC Standards reference where the EDC is an immutable, read-only source of truth. It is written for the Python ETL engineers and biostatistical programmers who have to turn SDTM DM, EX, and DS into a spine that a statistician can trust and a reviewer can trace.

The ADSL Derivation Sequence #

ADSL merges three SDTM domains onto the demographics grain, collapsing many-per-subject exposure and disposition records into single derived values before deriving treatment, populations, and groupings.

Root Cause: Why ADSL Grain Is Fragile #

ADSL breaks because its three source domains disagree on cardinality. SDTM DM is already one row per subject, but EX (exposure) carries many rows — one per dosing interval — and DS (disposition) carries one row per disposition event. A naive merge against either fans the subject out into multiple rows, and because pandas does not complain about a one-to-many join by default, the inflation is silent until a population count comes out wrong. On top of that, screening throws a population question the grain cannot answer by itself: subjects who consented but were never randomized (screen failures) appear in DM yet must be excluded from every analysis population. The derivation is therefore a controlled collapse — aggregate the many-per-subject domains to single values, then merge onto the demographics grain and derive flags — rather than a chain of joins.

Step-by-Step Implementation #

Step 1: Establish the DM Subject Grain #

Start from DM, which defines the population of subjects. Keep only the columns ADSL needs and assert the grain immediately, so any duplicate USUBJID fails at the boundary rather than propagating into treatment and population derivations.

# ALCOA+ requirement: Complete — DM defines the one-row-per-subject universe;
# assert uniqueness up front so no duplicate subject reaches the spine.
import pandas as pd


def dm_grain(dm: pd.DataFrame) -> pd.DataFrame:
    keep = ["STUDYID", "USUBJID", "SUBJID", "SITEID", "ARM", "ARMCD",
            "AGE", "AGEU", "SEX", "RACE", "COUNTRY", "RFSTDTC"]
    adsl = dm[keep].copy()
    if adsl["USUBJID"].duplicated().any():
        raise ValueError("DM is not unique on USUBJID; ADSL grain is unsafe")
    return adsl.reset_index(drop=True)

Step 2: Aggregate EX to First and Last Exposure #

Exposure is many-per-subject, so collapse it before it touches the spine. The first dosing date becomes TRTSDT (treatment start), the last becomes TRTEDT (treatment end), and the treatment actually administered becomes TRT01A. A stable mergesort on the date makes “first” and “last” reproducible when timestamps tie.

# ALCOA+ requirement: Accurate — actual treatment and treatment dates come from EX,
# what the subject really received, aggregated to one deterministic row per subject.
import pandas as pd


def aggregate_exposure(ex: pd.DataFrame) -> pd.DataFrame:
    dosed = ex[ex["EXDOSE"].fillna(0) > 0].copy()          # exclude zero-dose records
    dosed = dosed.sort_values(["USUBJID", "EXSTDTC"], kind="mergesort")
    return dosed.groupby("USUBJID", as_index=False).agg(
        TRTSDT=("EXSTDTC", "first"),
        TRTEDT=("EXENDTC", "last"),
        TRT01A=("EXTRT", "first"),
    )

Step 3: Derive Planned vs Actual Treatment #

Planned treatment (TRT01P) comes from the randomized ARM; actual treatment (TRT01A) came from EX in the previous step. Keeping them as separate columns is what lets a safety analysis run on actual treatment and an efficacy analysis on planned, and the numeric companions (TRT01PN) give statistical procedures a sortable code.

# ALCOA+ requirement: Accurate — planned and actual treatment are distinct columns;
# conflating them mis-assigns any subject dosed off their randomized arm.
import pandas as pd

TRT_DECODE = {"Placebo": 0, "Drug 50mg": 1, "Drug 100mg": 2}


def derive_treatment(adsl: pd.DataFrame, exposure: pd.DataFrame) -> pd.DataFrame:
    adsl = adsl.merge(exposure, on="USUBJID", how="left", validate="one_to_one")
    adsl["TRT01P"] = adsl["ARM"]                         # planned, from randomization
    adsl["TRT01PN"] = adsl["TRT01P"].map(TRT_DECODE)
    adsl["TRT01AN"] = adsl["TRT01A"].map(TRT_DECODE)     # actual may differ from planned
    return adsl

Step 4: Derive Population Flags and Exclude Screen Failures #

Population flags subset the subject universe for each analysis. RANDFL marks randomized subjects; SAFFL marks any subject who took at least one dose; ITTFL follows the randomized set; EFFFL is the efficacy population, typically the safety set with a post-baseline efficacy measurement. Screen failures — consented but never randomized — resolve to N on every flag and are thereby excluded from analysis without being deleted from the dataset.

# ALCOA+ requirement: Consistent — one Y/N flag per population, mutually coherent;
# screen failures (never randomized, never dosed) fall out as N on every flag.
import pandas as pd


def derive_populations(adsl: pd.DataFrame, has_post_baseline: pd.Series) -> pd.DataFrame:
    yn = {True: "Y", False: "N"}
    adsl["RANDFL"] = adsl["ARMCD"].notna().map(yn)               # randomized
    adsl["SAFFL"] = adsl["TRTSDT"].notna().map(yn)               # took >= 1 dose
    adsl["ITTFL"] = adsl["RANDFL"]                               # intent-to-treat
    adsl["EFFFL"] = (adsl["SAFFL"].eq("Y") & has_post_baseline).map(yn)
    return adsl

Step 5: Derive Demographic Groupings, Strata, and Disposition #

Analyses group subjects by age band, so derive AGEGR1 (character label) alongside AGEGR1N (its sort order), pin the stratification factors used at randomization, and merge the single relevant DS disposition record to record completion. DS is many-per-subject, so filter to the disposition event before merging.

# ALCOA+ requirement: Attributable — groupings and disposition are reproducible from
# source; AGEGR1N gives a stable sort, and completion comes from the DS term.
import numpy as np
import pandas as pd


def derive_groupings(adsl: pd.DataFrame, ds: pd.DataFrame) -> pd.DataFrame:
    bins, labels, codes = [0, 18, 65, np.inf], ["<18", "18-64", ">=65"], [1, 2, 3]
    adsl["AGEGR1"] = pd.cut(adsl["AGE"], bins=bins, labels=labels, right=False)
    adsl["AGEGR1N"] = adsl["AGEGR1"].map(dict(zip(labels, codes))).astype("Int64")
    adsl["STRAT1"] = adsl["COUNTRY"]                    # a randomization stratum
    # One disposition record per subject: the study completion event.
    disp = ds[ds["DSCAT"].eq("DISPOSITION EVENT")].drop_duplicates("USUBJID")
    adsl = adsl.merge(disp[["USUBJID", "DSDECOD"]], on="USUBJID", how="left")
    adsl["COMPLFL"] = adsl["DSDECOD"].eq("COMPLETED").map({True: "Y", False: "N"})
    return adsl

Verification and Audit Trail #

ADSL correctness is a regulated activity: the spine feeds every table, so its checks are archived as OQ evidence. Capture the following on every run.

Run the population reconciliation as an explicit test rather than a manual eyeball, because a flag that silently miscounts by one subject is exactly the kind of defect that survives to inspection.

# GxP test artifact: population counts and grain are asserted, not inspected;
# the pass/fail report is retained as OQ evidence for the derivation.
def test_adsl_integrity(adsl):
    assert adsl["USUBJID"].is_unique, "ADSL grain broken"
    assert set(adsl["SAFFL"].unique()) <= {"Y", "N"}
    # Every dosed subject must be safety-population; contrapositive catches gaps.
    dosed_not_saf = adsl[adsl["TRTSDT"].notna() & adsl["SAFFL"].ne("Y")]
    assert dosed_not_saf.empty, "dosed subject missing from SAFFL"

Edge Cases and Vendor-Specific Gotchas #

Planned-vs-actual treatment mismatch. A subject randomized to Placebo but dosed with active drug has TRT01P = "Placebo" and TRT01A = "Drug 50mg". Do not “correct” either value — both are true. Carry both columns, log the mismatch for medical review, and let each analysis pick the population-appropriate one; safety runs on TRTA, efficacy on TRTP.

Screen failures leaking into populations. Subjects who signed consent but failed screening exist in DM with a null ARMCD. If a flag derivation keys off DM presence rather than randomization, they contaminate the ITT denominator. Anchor RANDFL on ARMCD (or the randomization date), never on mere DM existence.

Partial exposure-date imputation. EX start dates sometimes arrive as partials (2026-03), which break TRTSDT-dependent flags and downstream relative-day math. Impute to a documented convention — first of the month for a start date — record the imputation in a TRTSDTF imputation flag, and never silently coerce a partial to a full date without leaving that flag behind.

Frequently Asked Questions #

Related #

• ADaM Dataset Derivation for Statistical Analysis — the parent guide covering ADSL, BDS, and OCCDS structures together.
• Deriving the ADAE Adverse-Event Analysis Dataset — the OCCDS dataset that merges to this ADSL spine.
• Clinical Data Architecture & EDC Standards — the reference architecture this analysis layer sits within.
• CDISC SDTM Transformation Pipelines — the tabulation stage that produces the DM, EX, and DS inputs.