Translating an Excel specification¶

1. A spec.yaml is one Dataset-sheet row plus a slice of the Variable sheet¶

Here is the same specification written twice. It is adam-adsl-bmi, which reads a subject-level source and adds two derived variables: BMI as an inline expression, and BMI_FN via a routine the project supplies.

As you would write it today. A Dataset sheet row:

Dataset	Description	Class	Structure	Key Variables
ADSL	Subject-Level Analysis Dataset	ADSL	One record per subject	STUDYID, USUBJID

and five Variable sheet rows:

Variable Name	Variable Label	Type	Length	Origin	Core	Conversion Definition	Variable Type	Variable Order
STUDYID	Study Identifier	Char	20	Predecessor	Req	SOURCE.STUDYID	ADSL	1
USUBJID	Unique Subject Identifier	Char	30	Predecessor	Req	SOURCE.USUBJID	ADSL	2
HEIGHTCM	Height (cm)	Num	8	Predecessor	Perm	SOURCE.HEIGHTCM	ADSL	3
WEIGHTKG	Weight (kg)	Num	8	Predecessor	Perm	SOURCE.WEIGHTKG	ADSL	4
BMI	Body Mass Index (kg/m2)	Num	8	Derived	Perm	`BMI = WEIGHTKG / (HEIGHTCM/100)**2`	ADSL	5

As yamaa writes it, annotated with the cell each line replaces:

schema_version: "1.0"          # which schema version this spec targets (exact match)
domain: ADSL                   # Dataset sheet -> Dataset
keys: [STUDYID, USUBJID]       # Dataset sheet -> Key Variables, and actually checked
input:                      # <- no cell for this; usually a Comment or a separate sheet
  SOURCE: input/adsl.csv
base: SOURCE                   # Dataset sheet -> Structure, but as the driver of the row count

output:
  path: adsl.csv             # <- no cell for this; the file, and its format
  columns: [STUDYID, USUBJID, HEIGHTCM, WEIGHTKG, BMI, BMI_FN]   # what ships, in which order

columns:                       # this section (below) is the Variable sheet
  # rows 1-4 each read one source variable:
  #   - name: STUDYID
  #     type: str
  #     label: Study Identifier
  #     derivation: SOURCE.STUDYID
  - name: BMI                  #   Variable
    type: float                #   Type
    label: Body Mass Index (kg/m2)   # Label
    derivation:                #   Conversion Definition -- but executable
      compute:
        expr: "WEIGHTKG / POWER(NULLIF(HEIGHTCM, 0) / 100, 2)"

verifications:                 # <- no cell for this either
  - unique:
      columns: [STUDYID, USUBJID]
  - assert:
      id: bmi-missing-only-without-usable-inputs
      when: "BMI IS NULL"
      require: "HEIGHTCM IS NULL OR HEIGHTCM = 0 OR WEIGHTKG IS NULL"

What the two tables above have no cell for¶

Line up the two versions and the interesting part is not what moved -- it is what has no Excel counterpart at all. Four things:

Which file the data comes from. Origin says Predecessor and Conversion Definition names a variable; neither names a file.
NULLIF(HEIGHTCM, 0). There is no cell that says what happens when height is zero -- missing, an error, or Inf are all plausible, and each programmer picks differently.
Dependency order vs delivery order. One Variable Order column does both jobs. yamaa splits them into columns order and output.columns.
The two verifications. Key Variables looks like it asserts uniqueness, but nothing executes it. The assert rule -- "BMI is empty only when height or weight is unusable" -- normally survives as a sentence in a review email.

Going the other way, every one of the eleven columns has a yamaa field -- two live in governed submission metadata rather than in the derivation:

Core is a conformance classification against a CDISC implementation guide, not a statement about derivation. It travels in submission.core (Req / Exp / Perm for SDTM; Req / Cond / Perm for ADaM).
Comments for Define is documentation by definition -- submission.comment. The template already separates it from Conversion Definition; yamaa just makes the split executable-vs-governed-metadata.

The free-form column.metadata map is uninterpreted annotation only: it is never merged with submission metadata and must not carry a governed key such as core, codelist, length, or comment.

2. The full mapping table¶

2.1 Dataset level¶

Excel spec	yamaa	Notes
Dataset Name	`domain`	One dataset per spec
Dataset Label / Class / Structure	`submission.label` / `submission.class` / `submission.structure`	Governed dataset metadata, written under `submission:`; reserved in `metadata:`, which rejects them with `reserved_metadata_key` (REQ-0910)
Key Variables	`keys`	Non-missing and unique are enforced
Sort Order (submission sort)	`output.order_by`	A presentation order applied after every check
Input datasets (usually only in a Comment)	`input:`	Every input is declared and named
Structure: "one record per subject per visit"	`base` plus `rows` templates	Row count comes from these, not from a sentence
Copy the corporate template and edit	`parents:`	Real layering; a change to the parent flows down (Specification composition)
Dataset-level review checks	`verifications:`	`unique`, `row_count`, `all_or_none`, `assert`

2.2 Variable level¶

Excel column	yamaa	Notes
`Variable Name`	`column.name`
`Variable Label`	`column.label`
`Type` (Char / Num)	`column.type`	Closed set of five: `str` `int` `float` `date` `datetime`
`Length`	a `max_length` verification plus `submission.length`	It is a constraint, so it becomes an executed one. `submission.length` is what define.xml shows; on a `str` column with a `max_length` verification it is derived from `max` and need not be declared separately
Significant digits / display format	`submission.significant_digits` / `submission.display_format`	Presentation metadata, not the stored value; emitted as SignificantDigits / def:DisplayFormat in define.xml (Types and conversion)
`Controlled Terms or Format`	`mapping` / `intermediates` / `allowed_values`, plus `submission.codelist`	Translation and enforcement separate here too; `submission.codelist` names a codelist the study document declares
`Origin` = Assigned	`literal: DM`
`Origin` = Collected (CRF / eDT)	`odm: ODM.IT.DM.AGE`	Reads the one record of the item among the ODM records the row was built from; `event`, `form`, `item_group`, and `filter` narrow it (Name binding)
`Origin` = Predecessor	`source: ADSL.TRTSDT`	A qualified cross-dataset name reads that dataset through the implicit join on the output keys (Lookup and joins)
`Origin` = Derived	a specific expression	See the derivation vocabulary
`Core` (Req / Exp / Perm for SDTM; Req / Cond / Perm for ADaM)	`submission.core`	Conformance classification; it says nothing about derivation
`Conversion Definition`	`derivation:`	From a sentence a person reads to an expression a machine runs
`Variable Order`	`columns` order and `output.columns`	One Excel column doing two jobs
`Comments for Define`	`submission.comment`	Sponsor note carried with the column definition, for define generation
Variable-level review checks	`column.verifications`	`not_missing`, `allowed_values`, `range`, `max_length`, `matches`
"if not collected then U"	the `missing:` handler
"if not in codelist then 99"	the `unmapped:` handler	Without `unmapped:`, an unlisted value stops the run
"subject X was corrected to 99"	a `case` branch

2.3 Codelist splits into three constructs¶

Excel has one Codelist column. yamaa separates by where the vocabulary lives:

Situation	yamaa	Example
Short vocabulary, written in the spec	`mapping`	`M -> M, F -> F`
Vocabulary is an external file (MedDRA, WHODrug, a reference-range table)	a named intermediate	`sdtm-ae-coding`
No translation, only a check that the value is one of these	`allowed_values`	`values: [M, F, U]`
Numeric banding (AGEGR1, BMI categories)	`cut`	Example 1

Where the codelist name lives¶

One Excel Controlled Terms or Format cell collapses two declarations. yamaa keeps them apart: the spec names the codelist, and the study document declares it. From sdtm-dm-metadata, spec.yaml first:

  - name: SEX
    type: str
    label: Sex
    derivation: DM_RAW.SEX
    submission:
      core: Req
      role: Record Qualifier
      length: 1
      codelist: SEX
      origin:
        type: Collected
        source: Investigator

then the codelists: entry in define.yaml that the binding names:

codelists:
  - id: SEX
    name: Sex
    standard: CT_SDTM
    items:
      - value: F
        decode: Female
      - value: M
        decode: Male
      - value: U
        decode: Unknown

submission.codelist carries the id, not the values. The document carries the values once, and every column naming that id shares the one statement. When the bound codelist declares items, an allowed_values verification on the same column must list the same values -- the check the run executes and the terminology the document publishes stay in agreement.

2.4 Value-level metadata¶

Value-level metadata (VLM) is what a Variable sheet cannot express: when a variable's meaning depends on another variable's value, each value needs its own derivation. AVAL is the standard case -- alanine aminotransferase where PARAMCD is ALT, systolic blood pressure where it is SYSBP.

Excel spec	yamaa
One VLM row (how AVAL is derived when PARAMCD = "ALT")	One row template under `rows:`
The VLM Where Clause	`row.filter` (an SQL predicate)
"this PARAM is derived from another PARAM"	Another row template with its own `literal` PARAMCD
"one collected record yields several analysis records"	Several row templates, appended in order

See adam-adlb-bds in More examples below.

3. Worked equivalences¶

Three full walkthroughs. Each shows the Excel rows, then the YAML, then what actually differs. All are real directories under benchmarks/ with fixed expected output.

Example 1: direct mapping, a codelist, and numeric banding¶

Source: adam-adsl-demographics

Excel:

Variable	Label	Type	Length	Origin	Codelist	Comment
SEX	Sex	Char	1	Predecessor: DM.SEX	SEX	Carry through as collected; if not collected -> U
SEXN	Sex (N)	Num	8	Derived		M=1, F=2, U=0
AGEGR1	Pooled Age Group 1	Char	5	Derived	AGEGR1	<18 / 18-64 / >=65; UNKNOWN if AGE missing

yamaa:

  - name: SEX
    type: str
    label: Sex
    derivation:
      first_available:
        sources: [DM.SEX]
        missing: U
    verifications:
      - not_missing: {}
      - allowed_values:
          values: [M, F, U]
      - matches:
          pattern: '^[MFU]$'

  - name: SEXN
    type: int
    label: Sex (N)
    derivation:
      mapping:
        source: SEX
        dict:
          M: 1
          F: 2
          U: 0
        missing: 0

  - name: AGEGR1
    type: str
    label: Pooled Age Group 1
    derivation:
      cut:
        source: AGE
        breaks: [18, 65]
        labels: ['<18', '18-64', '>=65']
        missing: UNKNOWN

What changed:

SEX needs no translation at all: first_available carries the collected value through unchanged, and only the uncollected case needs a handler (missing: U). The M=1/F=2/U=0 coding is a separate mapping on SEXN, where the missing case repeats (missing: 0) and there is no "unrecognised" branch to answer -- SEX is already constrained to three values. Where a wording genuinely can surprise you, the same benchmark's RACEN shows both handlers at once: unmapped: 99 for a race the dictionary does not know, missing: null for an empty one. A condition with no handler stops the run instead of turning into a blank.
The codelist name (SEX, AGEGR1) has no single home. The translation lives in mapping.dict, the check lives in allowed_values, and the name itself goes in submission.codelist if you generate define.xml -- see Where the codelist name lives below.

Example 2: a Comment sentence becomes `compute`¶

Source: adam-adsl-bmi

Variable	Type	Origin	Comment
BMI	Num	Derived	BMI = WEIGHTKG / (HEIGHTCM/100)**2, rounded to 1 decimal

columns:
  - name: BMI
    type: float
    label: Body Mass Index (kg/m2)
    derivation:
      compute:
        expr: "WEIGHTKG / POWER(NULLIF(HEIGHTCM, 0) / 100, 2)"
    # The Excel row's "rounded to 1 decimal" is display-only here: the source
    # benchmark stores the unrounded computed value, so the rounding becomes
    # a submission.display_format instead of round_half_away_from_zero.
    submission:
      display_format: "8.1"

verifications:
  - assert:
      id: bmi-missing-only-without-usable-inputs
      when: "BMI IS NULL"
      require: "HEIGHTCM IS NULL OR HEIGHTCM = 0 OR WEIGHTKG IS NULL"

What changed:

NULLIF(HEIGHTCM, 0) writes the "what if height is zero" case -- almost never stated in an Excel spec -- into the formula. Division by zero is a failure, so it has to be stated.
"rounded to 1 decimal" translates to round_half_away_from_zero (with digits: 1) -- the one rounding the language provides (REQ-1172). Use it when the rounded value is what you store and validate; when only the display is rounded (a TFL or listing), leave the stored value unrounded and declare submission.display_format instead.
The assert verification, limited by when, turns "BMI is empty exactly when height or weight is unusable" -- normally a note to the reviewer -- into an executable assertion.

Example 3: Predecessor and the declared-key lookup¶

Source: adam-adae-onset-emergence

Excel:

Variable	Origin	Comment
TRTSDTM	Predecessor: ADSL.TRTSDTM	merge by STUDYID USUBJID
TRTEMFL	Derived	Y if ASTDTM >= TRTSDTM, else blank

keys: [STUDYID, USUBJID, AESEQ]

columns:
  - name: TRTSDTM
    type: datetime
    derivation: ADSL.TRTSDTM         # <- no merge statement anywhere

  - name: TRTEMFL
    type: str
    derivation:
      flag: "ASTDTM >= TRTSDTM"     # <- Y when true; a comparison with a
                                    #    missing moment is unknown, never true
    verifications:
      allowed_values:
        values: [Y]

What changed:

source: ADSL.TRTSDTM reads across datasets through the Lookup and joins contract's declared-key join: the join keys are the applicable keys -- the output keys that also exist on the right side. So "merge by STUDYID USUBJID" is not written: it is a consequence of keys. Multiple matches fail by default; relaxing it requires explicit order_by and keep.
flag derives Y when the predicate holds and leaves the flag empty otherwise. A comparison with a missing moment is unknown, never true -- so "else blank" needs no statement, and the allowed_values check pins the flag to the one value it may carry.

More examples¶

The same treatment for the remaining constructs, one line each -- open the benchmark for the full side-by-side:

Example	What it shows
`adam-adlb-bds`	VLM and BDS: one row template per PARAMCD, then `baseline_flag` / `aggregate`-with-`filter` / `row_number` as columns
`adam-adex-cumulative-dose`	`aggregate:` with `expr: "SUM(EX.EXDOSE)"`, reducing by the applicable keys; a CSV field entering arithmetic must declare its type
`adam-adae-partial-dates`	`date_impute` beside `date_precision` reading the same source; `missing` and `invalid` are separate defects
`sdtm-dm-metadata`	`submission` vs `verifications`: Length becomes both `submission.length` (for define.xml) and a `max_length` check
`sdtm-ae-coding`	Coding against MedDRA with a named intermediate; several columns read the one record it selects
`schema-inheritance`	Corporate, compound and study layers via `parents:` -- real layering instead of copying the template

Translating an Excel specification¶

1. A spec.yaml is one Dataset-sheet row plus a slice of the Variable sheet¶

What the two tables above have no cell for¶

2. The full mapping table¶

2.1 Dataset level¶

2.2 Variable level¶

2.3 Codelist splits into three constructs¶

Where the codelist name lives¶

2.4 Value-level metadata¶

3. Worked equivalences¶

Example 1: direct mapping, a codelist, and numeric banding¶

Example 2: a Comment sentence becomes compute¶

Example 3: Predecessor and the declared-key lookup¶

More examples¶

Example 2: a Comment sentence becomes `compute`¶