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.
leftkeeps every primary row, whether or not it found a partner. Unmatched rows see missing values on the joined side.innerkeeps only primary rows that matched.
The default is not a shrug. With no
Join_Typethe 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 — aninnerjoin 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_Nonegroup 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 (aPermkey such asVISITDYmay 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 inAll, nothing is added. - Several joins on the same key share ONE group —
[USUBJID, DM.USUBJID, AE.USUBJID]for a rule joining DM and AE onUSUBJID. Two groups repeatingUSUBJIDmean the same thing but draw a load warning. Child: trueentries declare their keys bare inAllonly (USUBJID,IDVAR,IDVARVALon the SUPP--/CO/RELREC primary). The parent is resolved per row, so it has no name to declare — and noAll_Or_Nonegroup may name aChildkey, unless an ordinary entry of the same rule also keys on it (aDMjoin sharingUSUBJIDcarries[USUBJID, DM.USUBJID]).- A joined dataset absent from the study skips the rule of an ordinary keyed entry (one
carrying a key group);
Childentries,&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.RPATHCDon theTPentry) is declared under its qualified name: the group is[DM.RPATHCD, TP.RPATHCD], andDM.RPATHCDgoes inAllas well — whichever position the key has. The gate demandsAllonly for a first key, but a group alone lets a study that lacksRPATHCDon 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 fromRequirements— so only a literal first key goes inAll.
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
Alltoo 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
VISITis judged againstSVtoo), 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 asEPOCHorTAETORDonSV, or a variable an ADaM dataset copies fromADSL. A bare entry inAll(the rule skips where the primary lacks it), a bare entry in anAnygroup, or anAll_Or_Nonegroup pairing the bare and the joined side —[X, DM.X]— each says which side the bare name means. They differ where the primary lacksX: theAllentry skips the rule; the pair skips only when the join carriesXand runs when neither side does; anAnygroup 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 qualifiedDM.Xon 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,
AESEQincluded. A divergence means the delivered study disagrees with the standard, andCDISC-AD0199is 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:Nis 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_Stringcompare at 12 significant digits rather than exactly, because that is the precision of the rendered form. And it must be the unquoted booleantrue—"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, notRELREC, notSUPP--/SQ, no--template name, withKeys) andinner—Join_Type: "inner"or none. Aleftsource is refused: an unmatched row has noDMrecord 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,RELRECorSUPP--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
TPstages becomes three rows. And each row reads its own boundDMrecord — ifDMcarries a subject twice, on two paths, the record is copied by theDMjoin and each copy joins its own path's stages. - Everything else is as on any entry:
Join_Type,Filter,TP._matched_,keep_missingsfor a blank source value (a missingDM.RPATHCDnever pairs with an emptyTP.RPATHCD), andJoin_As_String. The key-type check compares the source column with the joined one — a characterDM.RPATHCDagainst a numericTP.RPATHCDstops 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 (
DMwithoutRPATHCD), - the joined dataset lacks it (
TPwithoutRPATHCD— 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
Childentry is joined only through its pointer — it has no direct join. The engine builds no keyed lookup for it, soAE._matched_and a dotted read such asAE.AESMIEare load errors on aChildentry — in theCheck, in aBindingsexpression, inOutput_Variables, and as a qualified wildcard (AE.**SMIE,AE.${X}) — and so are their template spellings,SUPPAE._matched_,SUPPAE.QVALorSUPP--.QVALagainst aSUPP--entry. Read the parent's columns bare: they arrive through the merge above, under their own names. Two more load errors on aChildentry: itsNamemust be a concrete dataset or a template with a trailing--(SUPP--,SQ--), andkeep_missings: falsehas 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 aChildentry, reads the not-supplied default silently — do not write it.⛔ Declare the child-side keys bare in
All— see Declare the key.⚠
STUDYIDparticipates only if you declare it. It is not added implicitly. Omitting it fromKeysmeans 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.PCRFTDTCis absent and reads empty, sonot empty(RELREC.PCRFTDTC)keeps that row silent.RELREC.**<suffix>instead resolves per related record against that record's own prefix.
⛔
Wildcardis 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.