Ambiguous database-to-derivation mapping in variables.csv

[DougManuel]

Summary

The current variables.csv schema allows variableStart to contain both [VAR] defaults and DerivedVar:: entries without explicit association to specific databases. This creates ambiguity about which mapping applies to which database/cycle.

Example 1: active_transport

Location: inst/extdata/variables.csv line 2

Current entry:

Field	Value
variable	active_transport
label	Daily active transportation
variableType	Continuous
databaseStart	cchs2001_p, cchs2003_p, cchs2005_p, cchs2007_2008_p, cchs2009_2010_p, cchs2010_p, cchs2011_2012_p, cchs2012_p, cchs2013_2014_p, cchs2014_p, cchs2015_2016_p, cchs2017_2018_p, cchs2009_m, cchs2010_m, cchs2012_m
variableStart	DerivedVar::[PAC_4A_cont, PAC_4B_cont], DerivedVar::[PAC_7, PAC_7A, PAC_7B_cont, PAC_8, PAC_8A, PAC_8B_cont], DerivedVar::[PAYDVTTR, PAADVTRV]

The problem: There are 15 databases listed, but three different DerivedVar:: formulas with no indication of which formula applies to which databases.

Example 2: energy_exp

Location: inst/extdata/variables.csv line 114

Current entry:

Field	Value
variable	energy_exp
label	Daily energy expenditure
variableType	Continuous
databaseStart	cchs2001_p, cchs2003_p, cchs2005_p, cchs2007_2008_p, cchs2009_2010_p, cchs2010_p, cchs2011_2012_p, cchs2012_p, cchs2013_2014_p, cchs2014_p, cchs2015_2016_p, cchs2017_2018_p, cchs2009_s, cchs2010_s, cchs2012_s
variableStart	cchs2001_p::PACADEE, cchs2003_p::PACCDEE, cchs2005_p::PACEDEE, [PACDEE], DerivedVar::[DHHGAGE_cont, PAA_045, PAA_050, PAA_075, PAA_080, PAADVDYS, PAADVVIG, PAYDVTOA, PAYDVADL, PAYDVDYS]

The problem: There are 15 databases listed, with three different mapping types:

1. Explicit mappings for 2001-2005: cchs2001_p::PACADEE, cchs2003_p::PACCDEE, cchs2005_p::PACEDEE
2. Default pass-through: [PACDEE] - applies to which remaining cycles?
3. Derivation formula: DerivedVar::[DHHGAGE_cont, PAA_045, ...] - applies to which cycles?

After subtracting the 3 explicit mappings, there are 12 remaining databases. But there's no way to know which of the 12 use [PACDEE] (pass-through) vs the DerivedVar:: formula.

Why this matters

1. Validation: We cannot programmatically validate that source variables exist in the correct databases
2. Documentation: Users must guess or consult external documentation to understand the mapping
3. Maintenance: When updating or debugging, the implicit mapping is error-prone

Contrast with pass-through variables

For pass-through mappings like ADL_01, the pattern works well:

variableStart: cchs2001_p::RACA_6A, cchs2003_p::RACC_6A, ..., [ADL_01]

Here, explicit db::VAR mappings cover specific cycles, and [ADL_01] is the default for remaining databases. The semantics are clear because there's only one default pattern.

Possible solutions

Option A: Explicit database prefixes for DerivedVar

variableStart: cchs2001_p::DerivedVar::[PAC_4A_cont, PAC_4B_cont],
               cchs2003_p::DerivedVar::[PAC_4A_cont, PAC_4B_cont],
               cchs2015_2016_p::DerivedVar::[PAYDVTTR, PAADVTRV], ...

Pros: Fully explicit, validates easily Cons: Verbose, repetitive

Option B: New syntax with database grouping

variableStart: [cchs2001_p, cchs2003_p]::DerivedVar::[PAC_4A_cont, PAC_4B_cont],
               [cchs2015_2016_p, cchs2017_2018_p]::DerivedVar::[PAYDVTTR, PAADVTRV]

Pros: Compact, explicit Cons: Requires parser changes

Option C: Remove variableStart from variables.csv

The database-to-source-variable mapping already exists in variable_details.csv with full explicit db::VAR syntax. The variableStart column in variables.csv is:

• Redundant (duplicates information in variable_details.csv)
• Complex (multiple syntax patterns: db::VAR, [DEFAULT], DerivedVar::[])
• Error-prone (hard to maintain by humans, hard to validate programmatically)

Pros: Eliminates ambiguity entirely, single source of truth Cons: Loss of quick overview in variables.csv, migration effort

Recommendation

I lean toward Option B (database grouping syntax) for these reasons:

1. Quick reference value: variables.csv serves as a human-readable index. Seeing [cchs2001_p, cchs2003_p]::DerivedVar::[...] at a glance tells you the variable's derivation pattern without opening variable_details.csv

2. Structural clarity: The grouping syntax makes the database-to-mapping relationship explicit while keeping variables.csv as the canonical list of harmonized variables

3. Incremental migration: Option B can be adopted incrementally for ambiguous cases, whereas Option C requires a larger schema change

That said, Option C has a compelling simplicity argument—one source of truth is easier to maintain than two that must stay synchronized. The deciding factor may be: how often do people consult variableStart directly vs using tooling that reads from variable_details.csv?

Questions for discussion

1. Is this ambiguity causing issues in practice, or is the implicit ordering sufficient?
2. Which solution best balances clarity, maintainability, and backward compatibility?
3. Are there other variables with this pattern we should audit?

Related

This issue was discovered while validating smoking variable harmonization worksheets. The smoking variables use explicit db::VAR mappings throughout, which made validation straightforward.

[yulric]

The details about which variables go with which database in the case where there are multiple derived variables (DerivedVar entires) are in the variable details sheet. Here are the relevant entries for active_transport and energy_exp. For example for active_transport, DerivedVar::[PAC_4A_cont, PAC_4B_cont] is for databases cchs2001_p, cchs2003_p, cchs2005_p, DerivedVar::[PAC_7, PAC_7A, PAC_7B_cont, PAC_8, PAC_8A, PAC_8B_cont] is for databases cchs2007_2008_p, cchs2009_2010_p, cchs2010_p, cchs2011_2012_p, cchs2012_p, cchs2013_2014_p, cchs2014_p, cchs2009_s, cchs2010_s, cchs2012_s and DerivedVar::[PAYDVTTR, PAADVTRV] is for databases cchs2015_2016_p, cchs2017_2018_p.

That being said, I actually wanted to change that so that it all of this information is in the variables sheet using one of your options. Option C would be great but Option B is a good in between. One issue with Option B though is that it makes it harder programmatically to parse out this information as in the variable start strings cchs2001::db_col1, [cchs2001, cchs2003]::db_col2, cchs2001::DerivedVar::[col1, col2], [cchs2001, cchs2003]::DerivedVar::[col1, col2], [db_col] and cchs2001::db_col, [cchs2001, cchs2003]::db_col, cchs2001::DerivedVar::[col1, col2], [cchs2001, cchs2003]::DerivedVar::[col1, col2], [DerivedVar::[col1, col2]] since we can no longer split on commas as derived variables break that rule.

[DougManuel]

Agree. variable_details is correct and has the needed information. I believe variables.csv variableStart isn't actually used by rec_with_table, it is variable_details that is used.

There are potential versions of Option B, but I think all are fairly complex. If we go that route, we'd want to ensure we have validation and fixing functions.

[yulric]

Yes, the variableStart column in the variables sheet is mainly there to make things easier.

If we agree with this approach then I can start writing a proposal for it.