How should NHANES data files be merged?

A concrete task with RigorMed

1. Write merge rules as a reviewable file-handoff brief

   These are suggested task inputs and outputs to request, not a new execution, a customer outcome or a guarantee of completion.

   Materials to provide: Confirmed cycles, an official file list, SEQN and record-granularity documentation, and existing matching summaries.

   Requested outputs / delivery goal: Merge-rule documentation, a component-matching checking table and a sample-flow record draft.

2. An example request

   Using the components and actual summaries I provide, organise the merge order, SEQN relationships, eligible populations and matching checks. Flag one-to-many cases requiring tests. Without execution records, do not claim that merging succeeded.

3. What still needs review

   This page's actual counts come from the listed official files. Do not copy them as your own data or call component ineligibility loss to follow-up.

Fix the sources and record granularity first

Both files are publicly released NHANES 2017–2018 components. Do not apply this example directly to different cycles or components with multiple rows per person.

BPQ_J eligibility comes from its Eligible Sample section and BPQ020 Codebook. The demographics-file count is not the research denominator for BPQ020. Check component eligibility and the join key before defining eligibility rules for the specific question.

Five checks before and after merging by SEQN

1. Use only official files from the same cycle

   Obtain DEMO_J.XPT and BPQ_J.XPT through the CDC/NCHS links below and open their Codebooks. Record cycle, component, access date, eligible population and variable definitions. Similar filenames or third-party mirrors do not replace source verification.

2. Check keys rather than concatenating columns immediately

   Count rows, non-missing SEQN values and duplicate SEQN values separately. Here DEMO_J has 9,254 unique keys and BPQ_J has 6,161, with no missing or duplicate keys on either side. A participant-level one-to-one merge can therefore be checked in this example. For one-to-many matches or duplicates, revisit component documentation to establish record granularity instead of arbitrarily deduplicating.

3. Audit matches with an outer join, then select the join required for analysis

   Outer-join by SEQN while retaining source indicators: 6,161 are present on both sides, 3,093 only in DEMO_J and 0 only in BPQ_J. With DEMO_J as the left table, a left join retains 9,254 rows, but 3,093 have no BPQ fields. An inner join or BPQ_J-based join has 6,161 rows. The join type follows the question and component eligibility, not a desire for a larger number.

4. Explain the 3,093 participants rather than calling them lost to follow-up

   Inspect the DEMO_J-only records: all 3,093 are younger than 16, while all 6,161 participants aged 16 or older match BPQ_J. This agrees with BPQ_J's official age eligibility. The difference of 3,093 reflects component eligibility in this example; it is not longitudinal loss to follow-up and cannot be generalized to other cycles or components.

5. Then check field codes and the research sample

   BPQ020 asks whether a doctor or other health professional has ever told the participant they have hypertension; it is not measured blood pressure at this examination. Both Codebook and file show 2,137 'yes', 4,014 'no' and 10 'don't know' responses. If a prespecified analysis retains only yes/no responses, 6,151 answers are available. Do not treat code 9 as a numeric measurement or attribute these 10 responses to file merging. Further study rules must separately define age, missingness in other fields, applicable weights, strata and PSUs.

Record the two kinds of count changes separately

The table checks unweighted participant counts in these official files. It is neither a population estimate nor the eligibility flow for a formal analysis.

Reproducible procedure: read each official XPT, retaining SEQN plus RIDAGEYR from demographics and BPQ020 from the questionnaire. Check SEQN uniqueness on each side, outer-join the keys and count both, left_only and right_only. Within left_only, count RIDAGEYR < 16. Finally, count BPQ020 codes 1, 2 and 9 according to its Codebook. R, SAS or Python can reproduce these counts; they do not depend on one program's automatic filtering.

Frequently asked questions