Standardize Observational Datasets into a Unified Data Dictionary

Transforms messy variable lists from multiple research sources into a standardized, ready-to-use tabular data dictionary with harmonized names and types.

You getA Markdown table with these exact columns, under the table and end with a single line titled Limitations & Risks…

Prompt

Prompt
Act as a research data manager. I will paste raw variable lists, codebook snippets, or schema extracts from two or more observational studies below.

Your job is to reconcile them into a single, cohesive data dictionary.

Rules to follow:
1. Consolidate overlapping concepts under clean snake_case standard variable names.
2. Assign strict data types (integer, float, categorical, boolean, date, or text).
3. Define permissible ranges or accepted categorical levels for each variable.
4. Specify standard null codes (use NA for system missing, -99 for unknown, -98 for refused).
5. Map every standard variable back to its raw source field name.

Output format:
- A Markdown table with these exact columns: Standard Name, Data Type, Valid Range / Allowed Values, Missing Code, Source Field Mapping, Clinical/Operational Definition.
- Under the table, provide a short numbered list titled Harmonization Decisions explaining any unit conversions or conceptual merges.
- End with a single line titled Limitations & Risks detailing potential bias or measurement mismatch between sources.

Paste your raw variable lists or codebook text below this line:

Example output

Example for: harmonizing two cohort surveys tracking patient hypertension and medication adherence.

Standard NameData TypeValid Range / Allowed ValuesMissing CodeSource Field MappingClinical/Operational Definition
subject_idtextalphanumeric (e.g., SITE-1001)NAStudyA: pt_id; StudyB: SUBJNUMUnique study participant identifier assigned at enrollment.
systolic_bp_mmhginteger60 to 260-99StudyA: sbp; StudyB: sys_pressResting systolic blood pressure measured in millimeters of mercury.
diastolic_bp_mmhginteger40 to 140-99StudyA: dbp; StudyB: dias_pressResting diastolic blood pressure measured in millimeters of mercury.
antihypertensive_usebooleantrue, false-98, -99StudyA: meds_bp (1/0); StudyB: rx_hyper (Y/N)Self-reported current use of prescription blood pressure medication.
survey_datedateYYYY-MM-DD (2020-01-01 to 2025-12-31)NAStudyA: visit_dt; StudyB: date_recordedCalendar date when clinical measurements and survey answers were recorded.
Harmonization Decisions
  1. Harmonized meds_bp (integer 1/0) and rx_hyper (text Y/N) into a standardized boolean field antihypertensive_use.
  2. Normalized blood pressure field names to include explicit unit tags (_mmhg) to prevent downstream calculation errors.
  3. Standardized missing flags across both cohorts: missing records default to NA, while unspecified non-responses map to -99.

Limitations & Risks: Study A measured seated blood pressure using automated cuffs, while Study B recorded manual sphygmomanometer readings, which may introduce systematic measurement bias during pooled analysis.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Strict table column definitions prevent missing schema details like units, data types, and missing codes.
  • Requiring backward mapping to raw field names makes the dictionary immediately actionable for ETL and data wrangling.
  • The mandatory harmonization notes force transparency regarding how incompatible categorical scales were merged.

Paste messy variable lists, codebooks, or table schemas from multiple datasets right after the prompt. Works directly in Claude, ChatGPT, Gemini, or Perplexity.

Common edits

  • Change the missing code scheme to match specific pipeline standards like IEEE NaN or custom database integer flags.
  • Add a column for longitudinal study visits or wave numbers if working with multi-year panel datasets.

Make it repeatable

Keep using this prompt without pasting it in every time.

Keep exploring

More in AI Research