A structured approach to registry data integration
Health and social registries hold data in many different structures. Research questions and operational definitions also change during a project. This vignette describes a modular framework that makes analysis-ready datasets simpler to build and to reproduce. It works for cross-sectional summaries and for high-frequency longitudinal analyses.
The challenge
Most epidemiological analyses involve more than two variables. A typical research question is:
“Estimate the effect of X on Y, adjusted for education, income, sex, birth country, comorbidity, and time since diagnosis.”
The question is simple to state. Each adjustment variable needs its own data work:
- Education is recorded yearly in administrative databases, but you want the level “on the date of diagnosis.”
- Comorbidity can need a scan of all inpatient and outpatient ICD codes across 10 or more years to build a Charlson index.
- Time since diagnosis needs the first occurrence of a disease, and all other data aligned to that date.
- Income needs adjustment for inflation and weighting by household.
Each variable needs several joins, filters, date comparisons and aggregations.
A common approach builds a wide person-level dataset with pre-calculated variables (one record per person). This works for static analyses. It fails when the research question changes, for example to stratify by calendar year, add time-varying covariates or lag an exposure.
Each such change often means that you rebuild much of the dataset.
The skeleton framework
This approach starts with a structural skeleton, not with the complete dataset.
The skeleton is a long-format table that defines the unit of analysis. It has one row per person per time period, for example one row per person per week. All later data attach to this time structure. For example:
| id | isoyearweek | isoyear |
|---|---|---|
| 100001 | 2010-01 | 2010 |
| 100001 | 2010-02 | 2010 |
| 100001 | 2010-03 | 2010 |
| 100002 | 2009-52 | 2009 |
| 100002 | 2010-01 | 2010 |
The skeleton contains:
- One row per person and time period within the analysis window
- No exposure, outcome, or covariate data at the start
- Weekly rows (isoyearweek) in the analysis window, and yearly rows (isoyear) for the years before it
create_skeleton() builds weekly rows from week 01 of
isoyear_min to isoyearweek_max. Each ISO year
from 1900 to isoyear_min - 1 gets one yearly row. swereg
has no monthly or daily skeleton.
After you build the skeleton, you add the data to it one step at a time. The data are:
- Outcomes: binary indicators for events of interest (e.g., myocardial infarction occurrence)
- Exposures: treatment or intervention status (e.g., vaccination, hospitalization, benefit receipt)
- Covariates: time-fixed (e.g., sex), semi-time-varying (e.g., annual income), or high-resolution (e.g., new diagnoses)
Each step is a separate operation with standard joins and transformations. This gives three advantages:
- You can run, debug, or change one step without the others
- The source of each variable stays visible
- The original time structure stays intact
When an analysis needs one row per person (e.g., logistic regression, baseline tables), you collapse the skeleton at the last stage.
Temporal data classification
Registry data change over time in three ways, and the skeleton handles each of them:
1. Time-invariant data (demographics, baseline characteristics)
A variable that does not change gets the same value on all rows for the person:
- Sex assigned at birth, birth country, genetic markers
2. Periodically updated data (socioeconomic status, family structure)
A variable with a regular update cycle gets its value on all rows within the period it covers:
- Annual income from tax records
- Family composition, marital status
- Education level, which can change from year to year
- Employment status
Methodological advantages
The skeleton gives these practical benefits:
- Data quality checks: missing rows show where follow-up stops.
- Changes to the analysis: to redefine an exposure or add a lag, you change only the step that makes that variable.
- Time-varying covariates: variables with different time resolutions (annual income, daily prescriptions) join on the matching time unit.
- Multiple outcomes: one skeleton supports the analysis of several endpoints at the same time.
Example: a model of sickness absence after COVID-19 infection. The skeleton spans person-weeks from March 2020 to December 2022. You add these data in sequence:
- Positive COVID-19 test dates from laboratory databases
- Inpatient diagnoses from hospital registers
- Sickness absence benefits from social insurance records
- Income and education from administrative data
- Age, calculated for each follow-up date
- Outcome variable: weekly sickness absence status (binary)
The result supports time-to-event models, generalized estimating equations, fixed-effects regressions, or conditional logistic regression. The choice depends on how you aggregate time and how you encode the variables.
The two-step swereg workflow
swereg implements this skeleton concept through two steps:
Step 1: create the skeleton
- Create the time-structured skeleton
- Add raw registry data in sequence (demographics, diagnoses, prescriptions, etc.)
- Derive analysis-ready variables (composites, first-occurrence markers, quality filters)
- Result: a complete skeleton with all data and derived variables
Step 2: analyse the skeleton
- Collapse weekly data to yearly (or other time units) as needed
- Create analysis datasets for specific research questions
- Run descriptive statistics, regression models, or survival analyses
- Result: final analysis outputs
You can debug, change, or rerun each step on its own.
Software implementation
The swereg package has functions to:
- Define skeletons for populations with given follow-up periods
- Add and aggregate registry data in the skeleton
- Calculate exposures and outcomes from ICD, ATC, and other medical classification codes
- Align data in time (e.g., find the income or education closest to a diagnosis date)
- Handle time relationships and links to calendar years
The package gives:
- Consistent time handling (ISO week standards, rules for partial overlap)
- Operational definitions that you can read in the code (e.g., the criteria for “hospital admission”)
- Reproducible methods across research projects and teams
Getting started
To learn swereg, read the vignettes in this order:
- “Creating the skeleton”: build the time grid, add registry data, and derive variables
- “Analysing the skeleton”: collapse to the right granularity and run analyses
For production-scale pipelines with incremental rebuilds, see “The skeleton pipeline”.