ADSL Derivation & Merge Traps

A Subject-Level Analysis Dataset, or ADSL, should hold exactly one row per subject. But a silent break can leave more rows than subjects, with no error and no warning. The first symptom appears downstream, when a demographic table and a survival figure report different Ns from the same data. The root cause is upstream: a merge in ADSL against a source that still had multiple records per subject, fanning out duplicate Unique Subject Identifier, or USUBJID, rows. Today we derive the treatment start date, TRT01SDT, and population flags like the Safety Population Flag, or SAFFL, while enforcing one row per subject. We add Quality Control, or QC, checks at the merge itself, so fan-out is caught where it happens.

ADSL, the Subject-Level Analysis Dataset, is the analysis backbone: exactly one record per subject for demographics, treatment variables, key dates, and population flags. Every downstream dataset—Vital Signs Analysis (ADVS), Laboratory Analysis (ADLB), Adverse Events Analysis (ADAE), and Time-to-Event Analysis (ADTTE)—merges ADSL in to inherit TRT01P, TRT01A, and SAFFL. That means a subject-level error repeats on every row and still looks perfectly consistent. So for any source domain, ask how many rows per subject first, then the variable list. Demographics (DM) is one row per subject, so merge directly; but Subject Visits (SV), Exposure (EX), Disposition (DS), and Supplemental Demographics (SUPPDM) have many rows per subject, so aggregate, subset, or transpose wide before merging.

TRT01SDT is a cascade, not a simple lookup. First, qualify exposure records from the Exposure domain, or EX, using EXDOSE > 0 or EXOCCUR = Y, per your Statistical Analysis Plan, or SAP. Then take the earliest EXSTDTC among qualifying records as TRT01SDT, and mirror that logic with the latest qualifying date for TRT01EDT. If no qualifying record exists, fall back only to what the SAP specifies — often Demographics (DM) RFXSTDTC or a Disposition (DS) recorded first-dose date — never to habit. Partial dates like 2026-01 or 2026 are imputed per the SAP, with the imputation flagged, never dropped silently. Exposure dates arrive as International Organization for Standardization (ISO) 8601 character strings, so check length before INPUT — 10 for dates, 16 for datetimes — to avoid silent missing values.

On this slide, planned treatment (TRT01P) comes from randomization: the Disposition (DS) randomization record, or the Demographics (DM) ARM variable when the Statistical Analysis Plan (SAP) accepts it. Actual treatment (TRT01A) comes from what the subject received, usually Actual Arm (ACTARM) or the Exposure (EX) records. When TRT01P and TRT01A disagree, that disagreement is data, not a bug: a subject randomized to A who received B is analyzed as randomized for efficacy and as treated for safety. Never rewrite TRT01A to match TRT01P; keep both variables and list discrepancies for medical review. The table covers common scenarios: planned A and dosed A is normal; planned A and first dose B means keep both and list; planned A but never dosed means Intent-to-Treat (ITT) yes, safety no; dosed without randomization means data review, and the SAP governs.

Every population flag ships with three things: the Statistical Analysis Plan citation it implements, its derivation code, and a quality control listing of subjects where the flag disagrees with its basis. The usual bases are Intent-to-Treat flag ITT01FL for randomized into the study, Safety Population Flag SAFFL for received at least one dose, which is EXDOSE greater than zero, and Completed through Week 24 Flag COMP24FL against Disposition, DS. Aggregate before the merge, then verify the row count after every merge, not once at the end. Rows must equal distinct Unique Subject Identifiers, USUBJIDs, at every step; divergence pinpoints the fan-out. The Structured Query Language, SQL, query on the slide compares counts from dm_sdtm and the Subject-Level Analysis Dataset, ADSL, to show that difference.

This checkpoint confirms your mastery of Subject-Level Analysis Dataset (ADSL) invariants, treatment date cascades, and planned-versus-actual treatment logic. Question one asks how the one-row-per-subject invariant of ADSL should affect source-domain handling when Exposure (EX) contains multiple records for a Unique Subject Identifier (USUBJID). The correct answer is C: before merging into ADSL, derive each subject-level value from EX, such as the earliest Exposure Start Date/Time (EXSTDTC) per USUBJID, so the dataset keeps exactly one row per subject. Question two asks which statements correctly describe a defensible cascade for populating the Date of First Exposure to Treatment (TRT01SDT). The correct answers are A, D, and E: take the earliest valid EXSTDTC per USUBJID as the preferred actual-treatment start date; if no actual date exists, apply the Statistical Analysis Plan (SAP) fallback and document any imputation; and apply the same rule to every subject while preserving traceability, leaving the value missing if no rule is defined. Question three asks why a difference between Planned Treatment for Period 01 (TRT01P) and Actual Treatment for Period 01 (TRT01A) is an analysis finding rather than an ADSL coding error. The correct answer is A: the difference is a substantive clinical and operational fact that may affect analysis populations or estimand choices, so both values must remain visible and analyzed.

Now let's trace the demo subject through the derivation cascade. In the Exposure (EX) extract, we sort by Unique Subject Identifier (USUBJID) and Exposure Start Date/Time (EXSTDTC), then keep FIRST.USUBJID. That selects the earliest qualifying row, here AIRIS-101 at 2019-05-08T09:23, not the later NAB-PACLITAXEL record. The derived TRT01SDT maps to the final Subject-Level Analysis Dataset (ADSL) row, which carries Treatment Start Date (TRTSDT) = 2019-05-08 and Treatment Start Time (TRTSTM) = 09:23:00. For Disposition (DS), multiple records on 2019-05-22 exist; LAST.USUBJID picks WITHDRAWAL BY PATIENT FROM STUDY, matching End of Study Date (EOSDT) = 2019-05-22.

This segment is hands-on, so you will do it on the website at jaimeyan.com/learn rather than in the video. There you will complete the SAS, Statistical Analysis System, DATA step that sorts the Exposure, or EX, domain by USUBJID, the Unique Subject Identifier, and by EXSTDTC, the Exposure Start Date/Time in character form, so that FIRST.USUBJID keeps the earliest qualifying dose and INPUT(EXSTDTC, YYMMDD10.) gives you a numeric TRT01SDT, the Treatment Start Date; applied to the displayed exposure rows that date comes out as 08MAY2019, and the exercise also shows you why merging EX into ADSL, the Subject-Level Analysis Dataset, without that pre-aggregation would produce a silent duplicate. So give it a try yourself after the video, and check that your first-dose merge matches.

This final checkpoint quiz tests merge discipline and derivation rules using realistic subject-level analysis dataset (ADSL) failure scenarios. After merging a subject-level analysis dataset (ADSL) with the exposure domain (EX), a structured query language (SQL) check returns N = 210 total observations and NSUBJ = 174 distinct unique subject identifier (USUBJID) values. The correct answer is B: a merge fan-out occurred, because more total rows than distinct USUBJID values violates the one-row-per-subject invariant and must be investigated before subject-level analysis. When deriving ADSL.TRT01SDT (date of first exposure to treatment) from EX rows, the rule is that the statistical analysis plan (SAP), not programmer judgment, governs fallbacks and partial-date imputation. The correct answers are A and C: use only displayed EX values and apply the SAP convention without silently replacing a partial or missing exposure start date/time (EXSTDTC), and if the SAP specifies partial-date imputation, apply it consistently across all subjects and document it in the Analysis Data Model (ADaM) metadata or derivation comment. For the demo subject, the short-answer model is: derive TRT01SDT as the earliest non-missing EXSTDTC among that subject's displayed EX rows; any partial-date imputation must follow the SAP, and if no EX row is displayed, state the missing evidence and apply the SAP rule without inventing a date.

The Subject-Level Analysis Dataset, or ADSL, is one row per subject, so assert rows equal distinct Unique Subject Identifiers, or USUBJIDs, after every merge. Treatment Start Date, or TRT01SDT, is a cascade: qualifying Exposure, or EX, records, earliest date, Statistical Analysis Plan or SAP fallback, SAP-defined imputation for partial dates. Planned Treatment, or TRT01P, versus Actual Treatment, or TRT01A, discrepancies are findings for medical review, never code fixes. Every population flag ships with a SAP citation, its derivation code, and a Quality Control, or QC, listing of disagreements. Before merging, collapse many-rows source; for Supplemental Qualifiers, or SUPPQUAL, transposes, convert Identifier Variable Value, or IDVARVAL, with INPUT before the numeric BY merge. Next, continue the paired bootcamp: ADSL feeds every downstream Analysis Data Model, or ADaM, dataset, so structural QC on ADSL protects the entire analysis line.