Derivation: keys, rows, columns¶

Summary¶

One specification produces one dataset:

output_dataset = derive(input_datasets, spec)

yamaa first constructs the output rows and then derives columns onto those rows. Column derivation never changes the row count.

Spec field	Question	Answer
`keys:`	What identifies a row?	The ordered columns whose combined values must be present and unique.
`rows:`	Which rows exist?	When present, row templates construct rows from retained input records or groups.
`columns:`	What does each row carry?	Each derivation produces exactly one value per constructed row, in declaration order.
`input:` with `key`	How does another dataset reach a row?	A read of that input joins on its declared `key`, each column matched against the row's column of the same name.

When rows is absent, the distinct key combinations in the default input construct the output rows. When rows is present, its templates construct the rows and keys validate their identity.

Keys identify rows¶

keys: is always required. It names output columns that together identify one row:

`domain`	`keys`	One row per
ADSL	`[STUDYID, USUBJID]`	subject
ADLB	`[STUDYID, USUBJID, PARAMCD, ASEQ]`	analysis record
ADAE	`[STUDYID, USUBJID, AESEQ]`	event

Every completed row must have non-missing key values, and no two rows may have the same key combination. A key therefore names only columns every row carries: an analysis date such as ADT, missing on a record collected without one, would fail the build on that record.

A sponsor's key variables often include one that may be missing, such as the end date of an ongoing medication. Keep it out of keys. Declare a non-missing identity there instead, such as --SEQ or ASEQ, and list the sponsor's key variables in output.order_by with a unique verification over them (OUTPUT-3). The rows then come out in the sponsor's order, a missing end date stays missing, and two records that repeat the sponsor's key fail the verification.

When the source carries no identity, derive one. A key computed by a window numbers each base record, so every record builds its own row (ROW-8):

yamaa: "1.0"
type: spec
model: ADaM
domain: ADCM
keys: [USUBJID, ASEQ]
input:
  CM: source/cm.csv

output:
  path: adcm.csv
  columns: [USUBJID, ASEQ, CMTRT, ASTDT, AENDT]
  order_by: [USUBJID, CMTRT, ASTDT, AENDT]

columns:
  - {name: USUBJID, type: str, derivation: CM.USUBJID}
  - {name: CMTRT, type: str, derivation: CM.CMTRT}
  - {name: ASTDT, type: date, derivation: {to_date: {source: CM.CMSTDTC}}}
  - {name: AENDT, type: date, derivation: {to_date: {source: CM.CMENDTC}}}
  # Each subject's records numbered by treatment and start date.
  - name: ASEQ
    type: int
    derivation:
      row_number:
        window: {group_by: [USUBJID], order_by: [CM.CMTRT, CM.CMSTDTC]}

verifications:
  - unique: {columns: [USUBJID, CMTRT, ASTDT, AENDT], id: sponsor-keys}

A key's window orders by fields of the base record, CM.CMSTDTC here, not by columns derived from them: ordering by ASTDT fails validation as key_dependency (COLUMN-7), because a key is computed from one base record and the keys before it.

Without `rows`: distinct keys construct the rows¶

Omit rows when the desired output has one row per distinct key combination already represented by the input. The input need not be physically one-to-one: several input records may contribute values to the same output row.

yamaa: "1.0"
type: spec
model: ADaM
domain: ADSL
keys: [STUDYID, USUBJID]
input:
  DM: source/dm.csv

output:
  path: adsl.csv
  columns: [STUDYID, USUBJID, AGE]

columns:
  - name: STUDYID
    type: str
    label: Study Identifier
    derivation: DM.STUDYID

  - name: USUBJID
    type: str
    label: Unique Subject Identifier
    derivation: DM.USUBJID

  - name: AGE
    type: int
    label: Age
    derivation: DM.AGE

A key table containing one row per distinct key combination, with columns derived onto it without changing its row count.

Read the diagram from left to right. The Key Table contains each distinct key combination once, in first-appearance order. Column 1 and Column 2 attach values to those fixed rows.

Each column derivation must resolve to exactly one value for a row. Repeated copies of the same present value still count as one value; competing present values fail instead of forcing yamaa to choose. A missing result remains the row's single missing value.

With `rows`: templates construct sections¶

Use rows when the specification must explicitly construct different kinds of output rows. Each entry is a row template and builds one section. Sections concatenate in specification order.

Two row-template sections concatenated vertically, with shared and section-specific columns derived onto the resulting rows.

This specification builds separate HEIGHT and WEIGHT sections from one input dataset:

yamaa: "1.0"
type: spec
model: ADaM
domain: ADVS
keys: [STUDYID, USUBJID, PARAMCD]
input:
  VS: source/vs.csv

output:
  path: advs.csv
  columns: [STUDYID, USUBJID, PARAMCD, AVAL]

columns:
  - name: STUDYID
    type: str
    label: Study Identifier
    derivation: VS.STUDYID

  - name: USUBJID
    type: str
    label: Unique Subject Identifier
    derivation: VS.USUBJID

  - name: PARAMCD
    type: str
    label: Parameter Code

  - name: AVAL
    type: float
    label: Analysis Value
    derivation: VS.VSSTRESN

rows:
  - id: height
    filter: "VS.VSTESTCD = 'HEIGHT'"
    derivations:
      PARAMCD: "'HEIGHT'"

  - id: weight
    filter: "VS.VSTESTCD = 'WEIGHT'"
    derivations:
      PARAMCD: "'WEIGHT'"

Each template keeps the records of one test and derives what differs between the sections, here PARAMCD as quoted text. STUDYID, USUBJID and AVAL are the same in both sections, so they are derived once, at column level.

The constructed rows must still satisfy keys: every key value must be present and every key combination unique. Section order is construction order; an optional output.order_by may independently reorder the finished artifact.

Columns fill the constructed rows¶

Columns resolve in declaration order, so a later column may read an earlier one. A column-level derivation is the column's default: every rows entry inherits it, and a rows entry may name the column in its own derivations to override the default for that entry's rows only. A column with no column-level derivation must be derived in every rows entry; write null, the missing value, when its value is deliberately missing in one section.

One restriction: a column-level derivation that is not row-local -- one that holds an aggregate or a window operation, or reads a SELF intermediate or a column that is not row-local -- is not a default, so a rows entry that derives the same column fails validation instead of deriving the column twice. A read through a join, or through a named intermediate over an input, is row-local and may be such a default.

Reading another dataset: declared join keys¶

A derivation reads another input by qualifying a column with the input's name, such as EX.EXDOSE. Unless that input built the row, the read is a join, and yamaa never infers the join from the columns two datasets happen to share: the input declares the columns it joins on in key, and each is matched against the current row's column of the same name.

yamaa: "1.0"
type: spec
model: ADaM
domain: ADSL
keys: [STUDYID, USUBJID]
input:
  DM: source/dm.csv
  EX:
    path: source/ex.csv
    types: {EXDOSE: float}
    key: [STUDYID, USUBJID]
base: DM

output:
  path: adsl.csv
  columns: [STUDYID, USUBJID, TRTSDT, CUMDOSE]

intermediates:
  # Each subject's first exposure record, by the date its start carries.
  - id: FIRSTEX
    dataset: EX
    key: [STUDYID, USUBJID]
    filter: "EX.EXSTDT IS NOT NULL"
    order_by: [EX.EXSTDT]
    keep: first
    derivations:
      EXSTDT: {to_date: {source: EXSTDTC}}

columns:
  - name: STUDYID
    type: str
    label: Study Identifier
    derivation: DM.STUDYID

  - name: USUBJID
    type: str
    label: Unique Subject Identifier
    derivation: DM.USUBJID

  - name: TRTSDT
    type: date
    label: Date of First Exposure to Treatment
    derivation: FIRSTEX.EXSTDT

  - name: CUMDOSE
    type: float
    label: Cumulative Dose
    derivation:
      aggregate: "SUM(EX.EXDOSE)"

DM is the base that built the rows, so DM.STUDYID reads the records each row was built from, without a join. A subject can have several EX records, and each kind of read treats them in its own way:

A scalar read, such as EX.EXDOSE as a whole derivation, joins on the input's key and gives one value per row. For a subject with two exposure records it fails with multiple_matches: the join finds more than one record and has no rule to choose one.
An aggregate reduces every record it matches. CUMDOSE declares no key of its own, so it matches on the input's key.
A named intermediate chooses one record per row and always declares its own key, here the same columns, with order_by and keep choosing the first exposure. The exceptions are an intermediate that drives a row template and one that is a side of a pair relation, which declare no key. Its id shares one list of names with the inputs, the other intermediates and the domain, so it cannot repeat the name of the input it reads: an intermediate EX over the input EX fails with duplicate_identifier (LOOKUP-3). Name it for the record it chooses, as FIRSTEX names the first exposure, and FIRSTEX.EXSTDT and EX.EXDOSE then read as what they are. An intermediate that would only repeat its input under another name is not needed: declare the key on the input and read it directly (LOOKUP-4). An intermediate over a table of one record that shares no column with the rows, such as the study's settings, writes key: none: every row reads that record (LOOKUP-5). An intermediate over a supplemental qualifier dataset writes pivot, so each listed qualifier name is a field of the record it selects for the row (LOOKUP-28). Its derivations add a field to each EX record: EXSTDT, the date to_date reads from EXSTDTC, missing for a partial start, which filter and order_by read as EX.EXSTDT. EXSTDTC itself stays text, as collected: an SDTM --DTC may be partial, and typing it date at ingest would fail the whole input on the first partial value (INPUT-7).
A pair relation, a named intermediate that writes inner_join, keeps every matching pair of two relations as a record of its own, matched on its key, so a row template driving on it builds one row for each pair, such as each adverse event with each medication given for it (LOOKUP-27). A lookup never builds more rows: choosing one of several records with keep drops the others.

A qualified read of an input that declares no key fails validation with no_applicable_keys at the read. An empty or repeated key, or a named intermediate without one, fails before any input is read, and each declared key column is checked against the input wherever a read uses it. The output keys and an input's key answer different questions: keys identify the rows yamaa builds, and key states how another dataset reaches them.

Summarize a group: a value on each row, or a row per group¶

A group summary takes one of two shapes, and the shape decides where the aggregate goes:

You want	Write	Rows
The group's summary beside each row of the group, such as a treatment group's mean dose on every subject	an unqualified aggregate at column level, its `group_by` naming output columns	unchanged: the value is broadcast to every row of the partition
One new row for each group, such as a visit total	a `rows` entry with `group_by` naming fields of its driver, its derivations reducing the group	one candidate row per group, kept when its `filter` holds

The first shape, from schema-aggregation, adds a column and no row:

  - name: AVGNDOSE
    type: float
    label: Mean Dose Records per Treatment Group
    derivation:
      aggregate:
        expr: "MEAN(NDOSE)"
        group_by: [TRTGRP]

The second, from schema-row-templates, adds one row per subject and visit. A grouped template reads a driver field only as one of its group_by fields or through an aggregate, such as SUM(LB.LBSTRESN) over the group's records:

  - id: visit_total
    group_by: [LB.USUBJID, LB.VISIT]
    filter: "AVAL > 100"
    derivations:
      PARAMCD: "'TOTAL'"
      PARAM: "'Total of Components'"
      AVAL:
        aggregate: "SUM(LB.LBSTRESN)"
      DTYPE: "'CALCULATION'"

LOOKUP-18 defines the aggregate contexts, and ROW-4 and ROW-5 the grouped template.

Group by a field you derive¶

When the grouping field is not stored in the input, derive it in a named intermediate over that input and drive the grouped template from the intermediate (ROW-2). The intermediate declares no key: it does not look up one record for a row but hands the template every record it keeps, each carrying the derived fields, and group_by names them through the intermediate's qualifier. In adam-adpp-accumulation-ratio each PK parameter record takes its subject's arm and the arm's steady-state visit, and one ratio row is built for each subject, arm and visit:

intermediates:
  - id: SUBJARM
    dataset: ADSL
    key: [STUDYID, USUBJID]
    columns: [ARMCD]
    require_match: true

  - id: PPARM
    dataset: PP
    derivations:
      ARMCD: SUBJARM.ARMCD
      SSVISIT:
        case:
          - when: "ARMCD = 'A'"
            then: "'DAY 7'"
          - when: "ARMCD = 'B'"
            then: "'DAY 5'"

rows:
  - id: arauc
    dataset: PPARM
    group_by: [PPARM.STUDYID, PPARM.USUBJID, PPARM.ARMCD, PPARM.SSVISIT]
    filter: "AVAL IS NOT NULL"
    derivations:
      STUDYID: PPARM.STUDYID
      USUBJID: PPARM.USUBJID
      PARAMCD: "'ARAUC'"
      PARAM: "'Accumulation Ratio AUCTAU'"
      AVISIT: PPARM.SSVISIT
      ARMCD: PPARM.ARMCD
      SSVAL:
        aggregate:
          filter: "PPARM.PPTESTCD = 'AUCTAU' AND PPARM.VISIT = PPARM.SSVISIT"
          expr: "ONLY(PPARM.PPSTRESN)"
      D1VAL:
        aggregate:
          filter: "PPARM.PPTESTCD = 'AUCTAU' AND PPARM.VISIT = 'DAY 1'"
          expr: "ONLY(PPARM.PPSTRESN)"
      AVAL: "SSVAL / NULLIF(D1VAL, 0)"
      AVALU: "'RATIO'"

The benchmark's full specification also declares the output columns, which the template fills, and a record-driven template that keeps every collected record.

One record, or one value¶

ONLY(x) counts records, not values: a group with two records fails with aggregate_multiple_records even when both carry the same value (FORMULA-20). Use it where a second record is an error, as for the steady-state and day 1 values above. MIN(x) or MAX(x) instead selects one value and checks nothing about the others; when the group's records must all agree, select with one of them and verify the agreement. COUNT(x) with distinct: true in a column, and an assert that it is at most 1, shows that the present values agree. COUNT(x) skips missing values, so where a missing value also disagrees, also assert that COUNT(x) is 0 or equals the record count, COUNT(D.*) (FORMULA-24). A group with no record makes every count missing, so give each assert a when, such as the record count being present, that exempts it (CHECK-12).

Choosing one record by order is a window's work. A baseline_flag that finds two eligible rows on the latest date fails with ambiguous_baseline; declare window.order_by with the baseline date first and the terms that tell same-date rows apart after it, such as a collection time and a sequence number (WINDOW-9, schema-baseline-tie-break). Rows still tied on every declared term fail again: yamaa adds no term of its own.

More examples¶

Derive age groups in ADSL: adam-adsl-age-group.
Derive BMI in ADVS: adam-advs-bmi.