Why Variable Mapping Requires More Than Matching Names
A cross-cohort validation analysis can fail before any model is fitted if a field in one cohort is treated as equivalent to a field in another merely because the labels look alike. “Baseline age,” for example, could mean age at enrollment in one dictionary and age at diagnosis in another. A shared label does not make those quantities interchangeable. Mapping variables is the documented process of deciding which cohort-specific fields represent a precisely defined shared quantity, and what transformations, if any, are needed to express them consistently.
The aim is not to make every cohort’s dictionary look identical. It is to make each analytical input traceable from the validation protocol to its original source, with enough evidence and detail for another analyst to reproduce the decision. The locked validation protocol specifies what the analysis requires; the mapping translates that specification into cohort-specific fields without quietly changing the intended quantity.
A mapping is supported by semantic evidence, not just matching strings. That evidence can include a dictionary definition, a data collection form, a codebook, or a documented derivation. Before mapping, specify what the target quantity means. For a clinical measurement this may include the analyte, specimen, unit, and collection time. For an outcome it may include the event definition, ascertainment window, and coding. For a covariate it may include the reference date and allowed categories. The exact details depend on the locked analysis.
A Reproducible Crosswalk
A useful crosswalk has one row per cohort and target quantity, with enough fields to show both the source and the decision. At minimum, record the target definition; cohort and source-field name; source definition and data type; source unit or category codes; target unit or coding; transformation; reference time; missing-value conventions; evidence and source version; mapping status; and reviewer or decision date. Preserve the original field and values. Apply a documented transformation to create an analysis variable rather than overwriting source data.
| Crosswalk element | Question it answers | Example entry |
|---|---|---|
| Target quantity | What exact variable does the analysis require? | Fasting plasma glucose at enrollment, in mmol/L |
| Source field and definition | Which field supplies the information, and what was recorded? | GLU0: fasting plasma glucose at baseline visit |
| Unit or coding | How are source values represented? | mg/dL; numeric |
| Transformation | How are source values expressed on the target scale? | Multiply by 0.0555 |
| Timing and missingness | When was it measured, and how are absent or special values represented? | Enrollment visit; code 999 means missing |
| Evidence and status | What supports the decision, and is it resolved? | Dictionary v2.1 and form; mapped, reviewer initials and date |
Use explicit statuses rather than a single yes/no flag. A direct mapping has the same intended meaning and representation as the target, requiring no substantive transformation. A deterministic transformation uses a documented rule, such as a unit conversion or recoding, to represent the same quantity. A conditional mapping is valid only for a stated subset or under a stated condition; record that condition so the analysis can enforce it. A field is unresolved when the available documentation does not establish what it measures. Mark it not mappable when its definition is substantively different or the required information is unavailable. Do not invent a transformation to force a match.
For numeric values, a conversion should preserve the measured quantity and state the units on both sides. If a source value \(x_s\) is in source units and \(x_t\) is the target-unit value, a linear conversion may be written \(x_t = a + b x_s\), where the constants follow from the unit definitions. For categorical values, write the mapping as an explicit code-to-code rule. Keep “unknown,” “not assessed,” “not applicable,” and ordinary missingness distinct when the source system distinguishes them; collapsing them can change the meaning of the analysis input.
Worked Example: Converting a Laboratory Unit
Suppose the target quantity is fasting plasma glucose in mmol/L. A hypothetical cohort dictionary defines field GLU0 as fasting plasma glucose at enrollment, recorded in mg/dL. Its documentation specifies the conventional conversion \(1\text{ mg/dL}=0.0555\text{ mmol/L}\). A participant’s source value is \(108\text{ mg/dL}\). Determine the target-scale value and what belongs in the crosswalk.
The definitions and timing match the target as stated, and the documented unit conversion is deterministic. The transformation is:
Substituting the source value gives \(x_t = 0.0555(108)=5.994\text{ mmol/L}\), or \(5.994\text{ mmol/L}\) to four significant figures. The crosswalk records GLU0, the source unit mg/dL, the target unit mmol/L, the multiplier 0.0555, the enrollment timing, and the dictionary evidence. A check should confirm that the transformed values are numeric and that applying the rule to the source values reproduces the stored target values, if both versions are retained.
This mapping establishes a unit conversion for the same defined measurement; it does not establish that collection procedures or laboratory platforms are comparable across cohorts. Those are separate measurement questions.
Justifying Mappings and Checking Transformations
A transformation is defensible only if its inputs, assumptions, and scope are explicit. For a unit conversion, confirm that the source and target refer to the same quantity, that the stated units are correct, and that the conversion rule is appropriate. For recoding, confirm that source categories have the same interpretation as target categories. If one source category combines several target categories, the target cannot be reconstructed without additional information.
The arithmetic behind a linear unit conversion is straightforward but useful to make explicit. Suppose one source unit equals \(c\) target units. A measurement of \(x_s\) source units represents \(c\) target units per source unit, so multiplying the measured amount by that ratio gives:
The source-unit labels cancel, leaving the target unit. This reasoning also exposes a common error: multiplying or dividing by the conversion factor in the wrong direction. Check the direction by converting a simple reference value or by writing the units through the calculation.
Worked Example: Recoding Disease Status
A validation target requires disease status coded as 1 for diagnosed by the enrollment date and 0 for no diagnosis by that date. In a hypothetical cohort, the dictionary for field DXBASE states: 1 = diagnosis documented before or at enrollment, 2 = no diagnosis documented by enrollment, and 9 = not assessed. Map the source codes to the target.
The event definition and reference time align with the target. The source code 1 maps to target 1, and source code 2 maps to target 0. Source code 9 does not mean “no diagnosis”; it means the status was not assessed. Therefore, record it as missing or not assessed according to the analysis’s specified missing-data handling, not as target 0. The explicit mapping is \(1\mapsto1\), \(2\mapsto0\), and \(9\mapsto\text{not assessed}\).
For example, a hypothetical source record with DXBASE = 2 becomes target disease status 0. One with DXBASE = 9 remains unobserved for this target. The crosswalk must preserve the code meanings and the handling of 9, rather than recording only a generic “recode 1/2 to 1/0.”
Worked Example: Rejecting a Superficial Match
The target quantity is age in years at enrollment. A hypothetical cohort dictionary contains AGE, defined as age at diagnosis in completed years, and ENROLL_DATE, the enrollment date. No date of birth or enrollment-age field is available. Can AGE be mapped directly?
No. Age at diagnosis and age at enrollment refer to different reference times. The difference is not a unit conversion or code recoding. Without enough information to calculate age at enrollment, AGE cannot represent the target as specified. Mark the mapping as not mappable with the reason “reference time differs; required information unavailable.” Do not label AGE as enrollment age simply because it is the cohort’s only age field.
If a documented date of birth were available, enrollment age might be derivable under a specified rule, but that rule would need to handle dates and the definition of age consistently. It cannot be inferred from the field name alone.
Auditing, Diagnostics, and Failure Modes
An audit should let a reviewer travel in both directions: from a target quantity to each cohort’s source field and transformation, and from a transformed analysis variable back to its source and evidence. Retain dictionary versions, codebooks, transformation code, and decision history. If a definition changes between data releases, record the version used; otherwise the same crosswalk may silently point to a different variable later.
After implementing the map, run checks that correspond to its rules. Confirm that all expected source codes are accounted for; flag unexpected codes rather than treating them as valid by default. Check numeric transformations against hand calculations and, where available, independently stored values. Verify that missing and special codes were handled as specified. Inspect ranges and units for implausible results, but treat range checks as diagnostics, not as evidence that the meaning is correct. A plausible range cannot resolve an ambiguous definition.
A map can fail in several ways: confusing a label with a definition, ignoring different time points, reversing a conversion factor, treating an “unknown” category as absence of disease, or discarding a special missing code. Another failure is forcing one-to-one mappings where source and target categories differ. If a source category merges target categories, a deterministic recode cannot recover the lost distinction. Document that limitation rather than fabricating detail.
A common alternative is informal analyst judgment during data cleaning. It may be quick, but it is difficult to reproduce and tends to hide assumptions in code. A structured crosswalk takes more initial effort, but makes decisions reviewable and reusable. Automated name matching can help identify candidate fields; it cannot establish semantic equivalence. A shared harmonized dataset can simplify later analysis, but it still needs a documented mapping back to source definitions and must not erase cohort-specific coding or provenance.
Variable mapping is also distinct from deciding whether cohorts are eligible or comparable for a particular validation claim. A mapped field can be consistently named and transformed while important differences remain in who was enrolled or how a measurement was obtained. Conversely, a cohort may be suitable for a validation question even when a particular requested field is unavailable. Keep mapping decisions transparent so those broader judgments can be made on their own evidence.
Check Your Understanding
Answer each question, then compare your reasoning with the solutions.
- A target is potassium in mmol/L. A hypothetical source field records potassium in mmol/L at the same visit, with the same specimen definition. What mapping status is appropriate, and what should still be documented?
- A source field uses 0 = no, 1 = yes, and 8 = unknown. The target uses 1 = yes and 0 = no. How should code 8 be handled?
- A value of 2.0 is recorded in source units, and 1 source unit equals 4 target units. Calculate the target value and show the units logic.
- Why is a field called “baseline age” insufficient evidence that it matches age at enrollment?
- Name one check that can detect an implementation error and one limitation that such a check cannot resolve.
Solutions. (1) A direct mapping is appropriate based on the stated matching definition, units, and visit. Record the source field, dictionary evidence and version, timing, missing-value conventions, and status. (2) Map 0 to 0 and 1 to 1; preserve 8 as unknown or missing according to the analysis specification, not as no. (3) \(2.0\text{ source units}\times(4\text{ target units}/1\text{ source unit})=8.0\text{ target units}\). The source units cancel. (4) The label does not establish the reference date or operational definition; the dictionary or collection documentation must confirm those details. (5) Comparing transformed values with hand calculations can detect a wrong conversion factor. It cannot establish that the source field’s documented meaning is semantically equivalent to the target.