jaimeyan.com / interactive explainer

From Raw Data to TLF: The Clinical Data Journey

How a handful of messy EDC extracts becomes a submission package: eight animated scenes covering SDTM mapping, SUPPQUAL, ADSL, BDS baseline and windowing, TLF production and QC, and Define-XML. Press play, or step through one beat at a time.

Honest label: teaching schematic — illustrative data only (fictional Study XYZ, Subjects 001–004) — not measured results. Rules summarized per SDTMIG/ADaMIG; the sourced posts are the authority.

scene — beat 0/0

Space play/pause  → next beat  ← previous beat  R reset

Static mode: animation disabled (reduced motion or no JavaScript) — every scene is shown in its complete final state.

SCENE 1 / 8

One journey, four stations

Every clinical study moves its data through the same four stations, each with its own contract.

Raw EDC 40+ extract tables SDTM tabulation domains ADaM analysis datasets TLF tables · listings · figures contract: mapping spec contract: ADaM spec contract: mock shell contract: define-XML traceable in both directions — any number walks back to a collected value fictional Study XYZ · 4 subjects · this walkthrough: 8 scenes
  1. Data enters as raw EDC extracts and leaves as TLF deliverables, passing through SDTM tabulation domains and ADaM analysis datasets.
  2. Every arrow is a governed transformation: validated programs, reviewed specifications, no hand edits.
  3. Each station has a written contract — mapping spec, ADaM spec, mock shell, define-XML — and code that produces anything the contract does not name is a finding.
  4. The contracts govern the stations; you can be checked against every row in them.
  5. Traceability runs both directions: any number in any table can be walked back to the value a site collected.
  6. This page walks the whole road with one fictional study: Study XYZ, subjects 001–004.
  • data station
  • contract document
  • governed flow

Teaching schematic — not measured. Illustrative structure only; rules per SDTMIG/ADaMIG.

Sources: series roadmap · Part 4, SDTM domain basics.

SCENE 2 / 8

Raw data as it actually arrives

A mock adverse-event extract: four subjects, eleven raw columns, and every classic mapping problem in one screen.

SUBJID AETERM_RAW AESTDAT_RAW AESEV_RAW AESER_RAW MDRPT 001 Nausea and vomiting 2023-03-04 Grade 1 (Mild) No Nausea 002 pneumonia Yes Pneumonia 003 head ache 2023-01-20 N Headache 004 COVID-19 . 2023-02 2023-01-20 end: UNK UNK Grade 3 (Severe) MODERATE (pending) also in the extract: AEREL_RAW free text ("possible", "unlikely related") · three different date formats
  1. The AE extract for Study XYZ arrives on a Tuesday: eleven columns, no standard names, no standard formats.
  2. Four subjects, four events — and nothing about the table hints at the target shape. The SDTM model does.
  3. Subject 002's onset is a partial date, month precision only: 2023-02. That is valid, collected data — not a bug.
  4. Subjects 003 and 004 carry unknown dates (UNK). Unknown is stored as missing and raised as a data query — never invented.
  5. Severity mixes two scales: CT-assigned Grade 1 (Mild) and Grade 3 (Severe) beside bare MODERATE. Relatedness is free text.
  6. Subject 004's MedDRA coding is still pending. The row ships with AEDECOD blank, tracked by an open query.
  7. None of this is unusual. The distance from this table to a submission-grade domain is about forty decisions — and every one belongs in the spec before the code.
  • raw column header
  • collected row
  • mapping decision needed

Teaching schematic — not measured. Table reproduced from the Part 5 mock extract (itself synthetic, fictional Study XYZ).

Source: Part 5, SDTM AE domain mapping.

SCENE 3 / 8

Mapping into an SDTM domain

Spec rows turn the raw extract into the AE domain: identifiers, verbatim topic, controlled terminology, coded terms, dates, sequence.

raw AE extract AETERM_RAW AESTDAT_RAW AESEV_RAW AESER_RAW MDRPT + 6 more mapping spec (the contract) 1 STUDYID = "XYZ-001" (assigned) 2 USUBJID = cats(STUDYID,SUBJID) 3 AETERM = strip(raw) verbatim 4 AESEV: "Grade 1 (Mild)"→MILD 5 AESER: "Yes"/"No" → Y/N 6 AEDECOD = join MDRPT only 7 AESTDTC = string as collected 8 AESEQ = count after sort every variable traces to a row; formulas, not prose SDTM AE domain USUBJID XYZ-001-001 XYZ-001-002 XYZ-001-003 XYZ-001-004 built once, reused ← same string ← in every ← domain AETERM (verbatim) "head ache" stays AEDECOD (MedDRA PT) Headache · 004: blank AESTDTC (ISO 8601) 2023-02 ships as 2023-02 never completed AESEV: MILD/SEV∗... decision rows → CT values AESER: Y/N · criteria blank AESEQ = count within subject after sort by STUDYID USUBJID AEDECOD AESTDTC AETERM (deterministic) ▲ submission-grade AE: 4 rows, 40 decisions, all sourced
  1. Mapping starts from the raw table: which columns move directly, which need a decision row, which need a data query.
  2. The mapping specification is the contract between data management, programming, and inspection — target, source, transformation, CT, notes.
  3. The AE domain takes shape: one row per observation per subject, standard variables, standard names.
  4. USUBJID is STUDYID + '-' + SUBJID, built in one program and reused verbatim everywhere — two programs building it is how trailing-blank ghosts are born.
  5. AETERM keeps the verbatim string ("head ache" stays "head ache"); AEDECOD comes only from the version-pinned MedDRA coding deliverable — 004 stays blank with an open query.
  6. Dates ship as collected ISO 8601 strings: 2023-02 keeps month precision. Completing a partial in SDTM changes clinical meaning.
  7. Severity collapses to MILD/MODERATE/SEVERE through decision rows; AESER becomes Y/N; the six seriousness criteria stay blank unless checked, never defaulted to N.
  8. AESEQ numbers records within subject from a deterministic sort — never EDC row numbers, which reshuffle on every re-extract.
  9. Result: a submission-grade AE domain where every variable traces to a spec row — the code contains no judgment, only transcription.
  • raw extract
  • mapping spec rows
  • SDTM AE

Teaching schematic — not measured. Variables and rules per SDTMIG; fictional Study XYZ data.

Sources: Part 5, AE mapping · Part 6, mapping specification.

SCENE 4 / 8

SUPPQUAL: the pressure valve

CRF values with no IG variable go vertical: one row per stored value, linked back through the parent's sequence number.

parent: AE record 001/1 AETERM = "Nausea and vomiting" AEREL = "POSSIBLY RELATED" (CT) AESEV = "MILD" (CT) but the CRF also collected: "possible" (verbatim) · "Grade 1 (Mild)" SUPPAE (vertical) RDOMAIN IDVAR IDVARVAL QNAM QVAL AE AESEQ 1 AERELVER possible AE AESEQ 1 AETOXLOC Grade 1 (Mild) + QLABEL · QORIG = "CRF" · QEVAL = "INVESTIGATOR" QNAM max 8 characters link why AESEQ must be deterministic: a re-run that reshuffles sequence numbers silently orphans every supplemental qualifier attached to those records
  1. Real CRFs collect things the Implementation Guide has no variable for — verbatim relatedness, a local toxicity grade.
  2. Those values go to a supplemental qualifier dataset, vertical: one row per stored value instead of one extra column.
  3. Each response becomes a SUPPAE row: QNAM = AERELVER, QLABEL = "Causality, Verbatim", QVAL = the collected string.
  4. The local grade string rides along as AETOXLOC because safety review uses the raw scale — with QORIG = CRF and QEVAL = INVESTIGATOR.
  5. The link back to the parent record runs through IDVAR = AESEQ and the sequence number in IDVARVAL.
  6. That link is exactly why AESEQ must be derived deterministically — reshuffled sequences silently orphan every attached qualifier.
  • parent domain record
  • SUPP-- row (vertical)
  • IDVAR/IDVARVAL link

Teaching schematic — not measured. SUPP-- structure per SDTMIG; values from the fictional Study XYZ extract.

Source: Part 4, SDTM domain basics.

SCENE 5 / 8

ADSL: one row per subject is the whole job

The Subject-Level Analysis Dataset is the spine every other analysis dataset inherits — and merge discipline is what keeps it one row per subject.

DM 1 row / subject the spine EX many rows / subject aggregate first: earliest EXDOSE>0 TRT01SDT cascade: qualifying EX → earliest date → SAP fallback SAFFL · ITT01FL each flag ships with its SAP citation + QC listing ADSL — 1 row per subject USUBJID TRT01P TRT01A SAFFL -001 Study Drug Study Drug Y -002 Study Drug Placebo! Y -003 Placebo Placebo Y -004 Study Drug (not dosed) N 002 randomized A, dosed B → keep both, list for medical review invariant check after EVERY merge: rows = subjects, or the merge fanned out the classic defect: 204 rows, 200 subjects a DS extract with two records per subject broke the invariant
  1. ADSL carries demographics, treatment variables, key dates, and population flags at exactly one record per subject.
  2. Sources with many rows per subject — EX, SV, DS — are aggregated before the merge, never after.
  3. TRT01SDT is a cascade: restrict EX to qualifying records, take the earliest date, then the SAP-defined fallback — not a lookup.
  4. Every population flag ships with three things: the SAP citation it implements, the derivation, and a QC listing of disagreements.
  5. Merges land on the spine: rows must equal distinct subjects at every step.
  6. Subject 002 randomized to A but dosed B: TRT01P and TRT01A disagree — that is data for medical review, never a code fix.
  7. The running check catches fan-out the moment it happens — a mystery N=204 becomes a five-minute fix.
  8. Get ADSL right and every downstream dataset inherits the right answer on every row; get it wrong and they inherit the wrong answer just as consistently.
  • ADSL row (the spine)
  • derivation cascade
  • invariant check

Teaching schematic — not measured. Treatment scenarios per the Part 2 discrepancy table (illustrative).

Source: Part 2, ADSL derivation walkthrough.

SCENE 6 / 8

BDS: parameters, baseline, windows

One subject's systolic blood pressure shows how windowing, the baseline flag, and change from baseline decide whether a BDS dataset is right.

Day -21 Day -2 Day 5 Day 71 Day 95 Day 130 PARAMCD = SYSBP · PARAM = "Systolic Blood Pressure (mmHg)" · ADY anchored to TRT01SDT (no Day 0) 124 128 122 118 120 116 collected VS records, subject 001 (mmHg, illustrative) BASELINE (-30..-1) WEEK 1 (1..7) WEEK 12 (64..98) WK24 (141..) gap: Day 99-140 NOT IN WINDOW stays in data, ANL01FL off ABLFL = Y last value on/before first dose — not the first record (124!) BASE = 128 on every record CHG = AVAL - BASE 118-128 = -10 ANL01FL=Y (SAP: last record wins in-window) loser: flag off, kept
  1. BDS grain: one record per subject per parameter per analysis timepoint, with ADY anchored to first dose and no Day 0.
  2. Subject 001's collected SYSBP records: screening 124, Day -2 value 128, Week 1 value 122, a Week 12 visit 118, an unscheduled recheck 120, and a late visit 116.
  3. Windows are day ranges transcribed from the SAP: Baseline -30..-1, Week 1 1..7, Week 12 64..98, Week 24 141..189.
  4. The Day 130 visit lands in the gap between windows: it stays in the dataset flagged off — dropping it would destroy the query trail.
  5. Baseline is the last non-missing value on or before first dose — 128 at Day -2, not the first record (124). Picking the wrong record here corrupts every change-from-baseline table at once.
  6. BASE copies onto every record; CHG = AVAL - BASE, so the Week 12 visit reads -10 mmHg.
  7. Two records sit inside Week 12: the SAP names the winner — last record, so the recheck carries ANL01FL = Y and the loser stays, flag off.
  • collected record (illustrative mmHg)
  • flagged: ABLFL / ANL01FL
  • SAP window band

Teaching schematic — not measured. Values invented for teaching; window boundaries per the Part 17 worked example.

Sources: Part 7, ADaM BDS · Part 17, windowing & baseline.

SCENE 7 / 8

TLF: the shell is a contract

From mock shell to shipped RTF — and the four-pass QC order that catches the N=24/N=26 class of defect at its cheapest point.

mock shell 14.1.1 Title: AEs, Safety Population Pop: first dose + 30-min obs headers: N=24 / N=24 / N=48 rows: SOC / PT, n (%) footnotes: MedDRA vX.Y page x of y · (Continued) every element is a checkable commitment produced output counts from ADAE joined to ADSL denominator: ADSL where SAFFL = "Y", by TRT01P header reads: N=26 program counted every subject with any exposure — defensible, but not the contract Pass 2 catch: data definitions — the N=24/N=26 class of defect, before any recompute four passes, cheapest first 1 Format: titles, footnotes, pages 2 Definitions: pop, denominators 3 Numbers: independent recompute 4 Consistency: same N everywhere each pass gates the next discrepancy record: output 14.1.1 observed N=26 · expected N=24 (SAP 6.2) disposition: query to statistician — never silently fixed shipped RTF conventions titles/footnotes inside the file · style template page x of y · n (100) without decimals · program ID read the shell into decisions in order: population → data source → rows → columns → statistics → footnotes
  1. The mock shell is a contract: output ID, titles, population statement, column blocks, denominators, footnotes — every element checkable.
  2. The program builds the table with denominators pulled from ADSL under the population flag — never from the event dataset.
  3. The output says N=26 where the shell says N=24: both defensible, only one is the deliverable — caught at Pass 2, data definitions.
  4. QC runs four passes cheapest-first: format, definitions, numbers, consistency — and never recomputes an output that fails an earlier pass.
  5. Every discrepancy gets a record — observed, expected, disposition, owner — because a silently fixed discrepancy resurfaces at the next data cut.
  6. The shipped RTF carries titles inside the file, one style template, page x of y, precision exactly per shell — including the n (100) convention.
  7. Read the shell into decisions in order, population first — skipping step one is the single most common serious discrepancy on a first QC cycle.
  • shell / output
  • QC catch
  • discrepancy record

Teaching schematic — not measured. The N=24/N=26 scene is the Part 3 worked example (illustrative).

Source: Part 3, mock shell to RTF.

SCENE 8 / 8

Define-XML and the package that ships

The spec becomes machine-readable metadata, the guide explains what machines cannot, and the whole package travels together.

spec columns Target · Source Transformation CT / Origin Notes / Query e.g. row 9: TEMP in °F → (x-32)×5/9 rule define-XML 2.1 ItemGroupDef = one dataset ItemDef = one variable + origin CodeList = CT binding + version MethodDef = derivation text generates ValueListDef + WhereClauseDef one variable, two rules: WHERE VSTESTCD = TEMP generated from the spec — never hand-typed drift between data and metadata = build failure, not a discovery ADRG orientation · special derivations · population filters · known issues eCTD module 5 package transport datasets (XPT) define.xml + define.pdf reviewer's guide annotated CRF arrives as one unit the journey, end to end: raw EDC → SDTM → ADaM → TLF → package every value traceable to collection · every rule traceable to a spec row · every claim checkable
  1. The mapping spec's columns are the source: target, source, transformation, CT/origin, notes.
  2. They generate define-XML almost one-to-one: origin column → ItemDef origin attribute, CT column → CodeList reference, transformation → MethodDef text.
  3. A rule that varies by parameter — TEMP converted from Fahrenheit, weight passed through — becomes ValueListDef plus WhereClauseDef: value-level metadata.
  4. Define-XML is generated, never hand-typed — generation is what makes data/metadata disagreement structurally impossible.
  5. The ADRG covers what machines cannot: orientation, special derivations, population filters, and known issues — disclosed rough edges earn trust.
  6. The package ships as one unit in eCTD module 5: datasets, define.xml, define.pdf, the guide, and the annotated CRF.
  7. That closes the journey: every value traceable to collection, every rule to a spec row, every claim checkable — the whole point of the road you just walked.
  • spec columns
  • define-XML element
  • submission package

Teaching schematic — not measured. Element-to-column mapping per the Part 16 table (summarized).

Sources: Part 16, define-XML & the reviewer's guide · Part 6, mapping specification.