Skip to content

Joins

Match_Datasets brings a second dataset into reach, so a check can compare across the two.

Match_Datasets:
- Name: "DM"
  Keys: ["STUDYID", "USUBJID"]
Check:
  expression: 'AESTDTC < DM.RFSTDTC'

Columns of the joined dataset are referenced dot-qualified — DM.RFSTDTC. Unqualified names always mean the primary dataset, and a bare name that only the join carries fails the build.

The ordinary join

Match_Datasets:
- Name: "DM"
  Keys: ["STUDYID", "USUBJID"]
  Join_Type: "left"

Name is the dataset to join.

Keys are the columns matched on. The usual form is a list of names shared by both sides:

Keys: ["STUDYID", "USUBJID"]

When the column is named differently on each side, declare both:

Keys:
- "STUDYID"
- { left: "AEREFID", right: "EGREFID" }

left is the primary dataset, right is the joined one. The two forms mix freely in one list.

When the value to join on lives in a dataset the rule already joins, not in the primary, write it qualified: Keys: ["DM.RPATHCD", "RPHASE"].

Join_Type is inner or left, lowercase.

  • left keeps every primary row, whether or not it found a partner. Unmatched rows see missing values on the joined side.
  • inner keeps only primary rows that matched.

The default is not a shrug. With no Join_Type the join behaves as the rule's shape implies, and for a rule about "every AE must have a matching DM record" that is the wrong question to leave to a default — an inner join silently removes exactly the rows the rule exists to find. Say which you mean whenever the absence of a partner is part of the question.

⛔ Declare the key — required for every keyed join

A rule that joins on a key MUST declare every key column in Requirements.Variables. Undeclared, a study that lacks a key column does not skip the rule — it runs it: a key absent on one side contributes an empty value, no row matches, and every row is reported (a flood); a key set absent on both sides is an execution error. Neither is an answer about the data. Declared, the rule skips, and the skip names the column.

The declaration, per key column:

Requirements:
  Variables:
    All: ["VISITNUM"]                 # the FIRST key, bare
    All_Or_None:                      # every key: bare side + joined side, one group each
    - ["VISITNUM", "TV.VISITNUM"]
    - ["VISIT", "TV.VISIT"]
    - ["VISITDY", "TV.VISITDY"]
Match_Datasets:
- Name: "TV"
  Keys: ["VISITNUM", "VISIT", "VISITDY"]
  • One All_Or_None group per key column, [KEY, DS.KEY] — for a sided key [LEFT, DS.RIGHT]. A key present on one side only skips the rule; a key absent on both sides is dropped from the join and the rule runs on the remaining keys (a Perm key such as VISITDY may legitimately be missing from both).
  • The first key also bare in All. That is what turns "every key absent on both sides" into a skip instead of an error, and it guarantees the primary side of the key the rule is mostly about. Where the check already requires it in All, nothing is added.
  • Several joins on the same key share ONE group — [USUBJID, DM.USUBJID, AE.USUBJID] for a rule joining DM and AE on USUBJID. Two groups repeating USUBJID mean the same thing but draw a load warning.
  • Child: true entries declare their keys bare in All only (USUBJID, IDVAR, IDVARVAL on the SUPP--/CO/RELREC primary). The parent is resolved per row, so it has no name to declare — and no All_Or_None group may name a Child key, unless an ordinary entry of the same rule also keys on it (a DM join sharing USUBJID carries [USUBJID, DM.USUBJID]).
  • A joined dataset absent from the study skips the rule of an ordinary keyed entry (one carrying a key group); Child entries, &DOM& templates and keyless joins are not affected. The group's qualified half (DM.USUBJID) is then absent while the primary's bare key is present, so the group is only partly present — the rule cannot be answered, and non-executability is not a finding. If the joined dataset's absence must itself be reported, that is a separate dataset-presence rule (e.g. not ds_exists("TA")), not something the joining rule can say.
  • A qualified key (DM.RPATHCD on the TP entry) is declared under its qualified name: the group is [DM.RPATHCD, TP.RPATHCD], and DM.RPATHCD goes in All as well — whichever position the key has. The gate demands All only for a first key, but a group alone lets a study that lacks RPATHCD on both sides run, and there the rule errors instead of skipping.
  • Expansion-template keys (&DOM&, &DOM&SEQ) cannot be declared — loader gate R6 bars a token from Requirements — so only a literal first key goes in All.

The corpus is held to this by JoinKeyDeclarationLintTest, and every package by the loader (RulePackageLoader.validateJoinKeyDeclarations, the same shape rules): a keyed entry whose key is not declared is a per-rule load error naming the rule, the entry and the key — in a user package too, which the lint cannot see. In the corpus the lint fires first: a keyed entry without its declarations fails the build, naming the rule and the key.

⚠ The key is not the only column to declare. A column the check reads is declared bare in All too when its presence is a precondition of the check — never a column that is one disjunct among several, and never the column whose absence is the finding (the one rule).

⛔ A bare name only the join carries fails the build

A bare X is the primary dataset's column, whatever the rule joins. So when no dataset in the rule's scope defines X but a joined dataset does, a bare X — in the Check, a binding or Output_Variables — does not read the join: it reads the primary's absent-column value ("" or missing on every row), and the rule reports too few findings, or wrong ones, with nothing to say so.

A rule scoped to EX that joins DM on USUBJID:

date(EXSTDTC) < date(RFXSTDTC)       ⛔ EX defines no RFXSTDTC, so this is never DM's
date(EXSTDTC) < date(DM.RFXSTDTC)    ✅

The same holds for an ADaM template in Output_Variables when the Check does not itself read that template: a bare APxxSDT names a column of exactly that name on the primary, which no dataset has, so the finding reports nothing for it. The joined period dates are reported as ADSL.AP${*}SDT (every ADSL column the template matches). A template the Check does read is different: the rule is expanded once per matching column, and the Output_Variables entry is renamed with it — TRTxxP in both reports the TRT01P the check just read.

The corpus build rejects such a rule (UnqualifiedJoinedReadLintTest), naming the rule, the name and the join. There are two ways out:

  • Qualify it — DM.RFXSTDTC. This is almost always what was meant.
  • Declare it, when the primary really is meant. A broadly scoped rule rarely needs to: if any dataset in its scope defines the name (a rule over every domain reading VISIT is judged against SV too), it is not flagged at all. Declaring is for a narrowly scoped rule whose primary may carry a column the standard's list for it omits — a Permissible variable such as EPOCH or TAETORD on SV, or a variable an ADaM dataset copies from ADSL. A bare entry in All (the rule skips where the primary lacks it), a bare entry in an Any group, or an All_Or_None group pairing the bare and the joined side — [X, DM.X] — each says which side the bare name means. They differ where the primary lacks X: the All entry skips the rule; the pair skips only when the join carries X and runs when neither side does; an Any group skips only when none of its members is present. So with the last two the check can still run on the absent column's constant, and that verdict must be the one you want. None, and a qualified DM.X on its own, do not declare it.

"Defined by the scope" is judged against the standard's own dataset definitions: a name any dataset in the rule's scope defines is not flagged, even when the join carries it too. Child: true entries are exempt — their parent's columns are read bare by design.

Key types must agree — and what to do when they do not

A join key is matched by type. A character AESEQ and a numeric AESEQ are not the same key, and a rule that meets such a pair stops with an error naming the column and both types:

Match_Datasets AE: join key AESEQ is Character in the primary dataset and Numeric in AE.

That is deliberate. The alternative — matching them anyway — only works for the canonical spelling: "5" would join 5, while "05", "5.0" and " 5" would not. Whether a subject's records joined would then depend on the spelling of a value whose type is already wrong, which is not something a rule should decide by accident.

This is a data defect, not an authoring one. In CDISC a variable of a given name has one type — measured across the whole metadata library, every join key used anywhere in this corpus is single-typed, AESEQ included. A divergence means the delivered study disagrees with the standard, and CDISC-AD0199 is the rule that reports exactly that. Your rule erroring is the correct outcome: it says "I cannot answer this question on this data" instead of inventing an answer.

You have two ways to say what should happen instead.

Declare the types you need, and the rule skips cleanly. This is the usual choice — the rule states its precondition and steps aside when the study does not meet it:

Requirements:
  Variables:
    All:
    - "AESEQ:N"        # the primary dataset
    - "AE.AESEQ:N"     # the joined dataset

⚠⚠ Declare BOTH sides. A qualified entry like AE.AESEQ:N is checked against the joined dataset only, and an unqualified one against the primary only. Declaring just the joined side is the common mistake: on a study where the primary is the wrong type, that entry is satisfied, the rule does not skip, and it errors anyway.

Or compare the keys as text, on purpose:

Match_Datasets:
- Name: "AE"
  Keys: ["STUDYID", "USUBJID", "AESEQ"]
  Join_Type: "left"
  Join_As_String: true

Join_As_String applies to every key of that entry, not only the mismatched one, so an entry's behaviour cannot change from study to study. It does not make "05" join 5 — it makes the text comparison explicit rather than accidental.

⚠ Two things to know before reaching for it. Numeric keys under Join_As_String compare at 12 significant digits rather than exactly, because that is the precision of the rendered form. And it must be the unquoted boolean true — "true" in quotes is rejected as a load error rather than silently accepted.

There is no numeric counterpart, and none is planned: parsing text into a number is partial ("ABC" has no value) and it invents identity, collapsing "05", "5.0" and " 5" onto the same key. A join may fail to find a match; it must never manufacture one.

Three cases are exempt, because they match a text-carried key against a typed column by design and have their own rules: Child: true, Name: "RELREC", and SUPP-- / SQ* entries. A SUPPAE.IDVARVAL of "1" is meant to find AE.AESEQ of 1. Join_As_String has no effect on these, and writing it on one is a load error rather than a silent no-op — as it is on an entry with no Keys at all, which builds no key comparison to apply it to.

Sided keys must declare both sides

If you use the { left, right } form, every such element needs both halves as strings:

Keys:
- "STUDYID"
- { left: "AEREFID", right: "EGREFID" }   # ✅
- { left: "VISITNUM" }                    # ⛔ load error

The two sides are read as parallel lists and paired by position, so an element only one side can read does not make the pair "partly declared" — it shifts everything after it. A half-declared element, an empty object, or a non-string name is rejected at load.

⛔ Sided keys and the Child / RELREC / SUPP-- family do not combine. That family pivots on the standard IDVAR / IDVARVAL names, which are the same on both sides by construction, and its merge reads the left names only — so a sided declaration there would be silently ignored. It is a load error instead.

A qualified key — joining through an earlier entry

Some keys are not in the primary dataset at all. A BW record reaches its trial path through its subject: TP has no USUBJID, and the subject's path RPATHCD lives in DM. Join DM first, then key TP on the qualified DM.RPATHCD:

Requirements:
  Variables:
    All: ["USUBJID", "DM.RPATHCD", "RPHASE"]
    All_Or_None:
    - ["USUBJID", "DM.USUBJID"]
    - ["DM.RPATHCD", "TP.RPATHCD"]
    - ["RPHASE", "TP.RPHASE"]
Match_Datasets:
- Name: "DM"
  Keys: ["USUBJID"]
- Name: "TP"
  Keys: ["DM.RPATHCD", "RPHASE"]
Check:
  expression: 'TP.RPRFDY == 1'

The qualified name says where the record's side of the key comes from: DM.RPATHCD is the RPATHCD of the row's bound DM record. The joined dataset is matched on the same variable, unqualified — TP.RPATHCD. There is no {left, right} to write: DM.RPATHCD joins on RPATHCD.

  • The qualifier names an EARLIER entry of the same rule — never the entry itself, never a later one: the source must be bound before this join runs. That entry must be an ordinary keyed join (not Child: true, not RELREC, not SUPP-- / SQ, no -- template name, with Keys) and inner — Join_Type: "inner" or none. A left source is refused: an unmatched row has no DM record to read the key from, and an empty key is not an answer. Anything else is a load error naming the rule and the key.
  • The entry carrying the key is an ordinary one too — a qualified key on a Child, RELREC or SUPP-- entry, or a template, is a load error.
  • A bare string only. A dotted name inside a sided element ({ left: "DM.RPATHCD", right: "RPATHCD" }) is a load error naming the bare spelling. Neither half may carry a -- or & wildcard: the two sides resolve against different datasets, so name the column.
  • It expands rows like any other join. The join is n-to-m: a record whose phase has three TP stages becomes three rows. And each row reads its own bound DM record — if DM carries a subject twice, on two paths, the record is copied by the DM join and each copy joins its own path's stages.
  • Everything else is as on any entry: Join_Type, Filter, TP._matched_, keep_missings for a blank source value (a missing DM.RPATHCD never pairs with an empty TP.RPATHCD), and Join_As_String. The key-type check compares the source column with the joined one — a character DM.RPATHCD against a numeric TP.RPATHCD stops the rule with an error naming both.

When a qualified key cannot be resolved

A plain key absent on one side contributes an empty value, and absent on both sides it is dropped (see Declare the key). Neither applies to a qualified key. Dropping it would join TP on RPHASE alone — every path's stages, the wrong answer with nothing to say so. So the rule stops with an error when:

  • the study has no dataset for the source entry (no DM),
  • the source dataset lacks the column (DM without RPATHCD),
  • the joined dataset lacks it (TP without RPATHCD — it needs the same variable, unqualified).

Declared as above — DM.RPATHCD in All and the pair [DM.RPATHCD, TP.RPATHCD] — each of these skips the rule instead. The error is what an undeclared or group-only declaration gets: a group alone is satisfied by a study lacking RPATHCD on both sides, and that study reaches the join.

⚑ The same qualified spelling works in a grouped function's group= and in the rule-level Grouping, under the same convention.

Did it match? — _matched_

A rule that asks "is there a related record at all?" does not need a column from the other side. It needs to know whether the join found one:

Match_Datasets:
- Name: "AE"
  Keys: ["STUDYID", "USUBJID"]
  Join_Type: "left"
  Filter: 'AEOUT == "FATAL"'
Check:
  expression: 'not AE._matched_'

<DATASET>._matched_ is true on a primary row that found a partner. With a left join and a Filter, it is the natural way to ask "does this subject have a fatal AE?"

⭐ The second use: saying what you conclude when the join found nothing

_matched_ is not only for rules whose whole question is "does a partner exist". It is also how a rule that reads a joined column says what it concludes when there is no partner — and without it, that case is decided by accident.

The trap is that var_exists(SV.SVPRESP) looks like it answers "can I read this", but it is a schema fact, broadcast to every row: does SV have this column at all. It says nothing about whether this row matched. So a cascade written as

   var_exists(SV.SVPRESP) and SV.SVPRESP == "Y"          -- "SV says planned"
or not var_exists(SV.SVPRESP) and <fallback>             -- "SV cannot say"

silently drops every unmatched row on a study whose SV does carry the column: the first arm is selected because the column exists, SV.SVPRESP on an unmatched row is the type default ("", not "Y"), and the second arm can never be reached. No finding, no SKIPPED, nothing in the report.

Adding an explicit arm fixes it, and states the intent:

   SV._matched_ and SV.SVPRESP == "Y"
or SV._matched_ and not var_exists(SV.SVPRESP) and <fallback>
or not SV._matched_ and <what to conclude with no SV record>

⛔ Two things this obliges you to do:

  • Declare the dataset. _matched_ is unresolvable without it — Requirements.Datasets: ["SV"] — and the rule is a hard ERROR otherwise, not a skip (D89b: declare ⇒ skip, don't declare ⇒ error, with no third answer).
  • Then cover the population that declaration excludes. The rule now skips when the dataset is absent. If that absence is legitimate, the study needs a sibling rule gated not ds_exists("SV").

⚠ Join on the other dataset's own key. A key component present on one side and absent on the other makes the row unequal unconditionally, so joining on a Perm column makes _matched_ false for the whole study and routes every row to the unmatched arm — the failure looks exactly like "no partner ever existed". For SV that key is USUBJID + VISITNUM, never VISIT.

⭐ The worked instance is the canonical unplanned(visit) predicate; the full doctrine, both polarities and the four-case table are house rules rather than guide material.

Filter — narrow the other side before matching

Filter: 'AEOUT == "FATAL"'

A boolean expression evaluated against the joined dataset's own rows, before the key index is built. Rows that fail it can never become a partner.

That ordering is the point. Filtering after the join would depend on which partner row survived; filtering before it makes the question — does a fatal AE exist for this subject? — independent of the collapse.

Two constraints:

  • Right-side columns only. A reference to the primary dataset, or to a $-binding, is a load error. A filter that correlates the two sides is a different feature and is not this one.
  • An unresolvable filter column is a bind error — unless it is declared in Requirements.Variables, in which case the rule skips cleanly instead.

Child: true — resolving a parent row

For a primary dataset that points at another — SUPP--, CO, RELREC — whose rows carry RDOMAIN, IDVAR and IDVARVAL naming the parent row:

Match_Datasets:
- Name: "AE"
  Keys: ["STUDYID", "USUBJID", "IDVAR", "IDVARVAL"]
  Child: true

The engine resolves the parent domain (from RDOMAIN, or by stripping the SUPP prefix), finds the parent row where parent[IDVAR] == IDVARVAL, and augments the primary row with the parent's columns. The check can then read those columns directly.

The remaining keys — Keys minus IDVAR and IDVARVAL — narrow which parent rows are considered.

⛔ A Child entry is joined only through its pointer — it has no direct join. The engine builds no keyed lookup for it, so AE._matched_ and a dotted read such as AE.AESMIE are load errors on a Child entry — in the Check, in a Bindings expression, in Output_Variables, and as a qualified wildcard (AE.**SMIE, AE.${X}) — and so are their template spellings, SUPPAE._matched_, SUPPAE.QVAL or SUPP--.QVAL against a SUPP-- entry. Read the parent's columns bare: they arrive through the merge above, under their own names. Two more load errors on a Child entry: its Name must be a concrete dataset or a template with a trailing -- (SUPP--, SQ--), and keep_missings: false has no effect on it (the pointer merge keeps blank keys unconditionally). ⚠ One shape no check sees: a read whose qualifier is itself substituted (${X}.COL) is bound per row and, on a Child entry, reads the not-supplied default silently — do not write it.

⛔ Declare the child-side keys bare in All — see Declare the key.

⚠ STUDYID participates only if you declare it. It is not added implicitly. Omitting it from Keys means rows are matched across studies, which matters for a pooled submission.

⚠ On a column-name collision the child wins. If the primary already has a column of that name, the parent's value does not overwrite it.

Forward RELREC — Name: RELREC with no Keys

Match_Datasets:
- Name: "RELREC"

A keyless RELREC entry is what selects this shape. It builds one evaluation row per (primary record, related record) pair via RELREC, with the related columns reachable as RELREC.*.

Three behaviours worth knowing before you use it:

  • It is inner — a primary record with no related record produces no row, so a rule written this way cannot report "nothing is related".
  • A declared forward join with no matching pairs yields zero rows, and therefore no findings.
  • It pairs the primary record with every related record, whatever its domain. To mean one related domain, read its columns by their literal names and guard them: on a pairing with a record of another domain RELREC.PCRFTDTC is absent and reads empty, so not empty(RELREC.PCRFTDTC) keeps that row silent. RELREC.**<suffix> instead resolves per related record against that record's own prefix.

⛔ Wildcard is retired and is a load error. It was accepted on this entry and read by nothing; it never filtered the related domain. Any other key an entry does not model is a load error too.

Which shape do you need?

the question the shape
compare a value here with a value there ordinary join, Keys
does a related record exist? left join + _matched_, usually with Filter
does a related record of a particular kind exist? Filter on the joined side
my primary dataset points at a parent row Child: true
the two sides type the key differently fix the data, or Requirements.Variables on both sides, or Join_As_String: true
walk RELREC relationships Name: "RELREC", no Keys
the key lives in a dataset the rule already joins a qualified key on a later entry

⚠ A join changes what an unqualified name means in only one respect: nothing. Unqualified names keep referring to the primary dataset even after a join. If you mean the joined side, qualify it — see Values and missing values for what an unmatched row looks like.


Next: Grouping — evaluating per group instead of per row.