CPRD Aurum Cohort scripts

Introduction

This repository contains the R scripts used by the Exeter Diabetes team to produce three cohorts and their associated biomarker/comorbidity/sociodemographic data from a CPRD Aurum dataset:

An 'at-diagnosis' cohort
A prevalent cohort (registered at 01/01/2024)
A treatment response (MASTERMIND) cohort (those initiating diabetes medications)

The below diagram outlines the data processing steps involved in creating these cohorts.

graph TD;
    A["<b>CPRD Aurum June 2024 release</b>"] --> |"Unique patients with a diabetes-related medcode between 01/01/2004-30/06/2024"| B["<b>Our extract</b>: n=2,727,999"]
    B -->|"Patient from one of 44 practices which may have merged (recommended to remove in CPRD Aurum Data Specification v3.4)"|C["n=30,759"]
    B -->|"Patients with gender==3 (indeterminate)"|D["n=43"]
    B --> E["n=2,697,197"]
    E -->|"With a diabetes QOF code with a valid date* (quality check to remove those without diabetes)"|F["n=2,110,415"]
    F -->|"Patients with a code for diabetes insipidus (excluded because they may mistakenly have diabetes mellitus codes)"|G["n=1,300"]
    F -->|"Patients with a code for gestational diabetes (excluded because algorithms for determining diagnosis date will not work in this group)"|H["n=28,053"]
    F --> I["n=2,081,081"]
    I --> |"Inconsistencies in diabetes type suggesting <br> coding errors or unclassifiable"|J["n=36"]
    I --> K["<b>Diabetes cohort</b>: n=2,081,045"]
    K --> L["<b>01 At-diagnosis cohort</b>: <br> n= <br> Index date=diagnosis date"]
    K --> M["<b>02 Prevalent cohort</b>: <br> n= <br> Actively registered on 01/01/2024 <br> Index date=diagnosis date"]
    K --> N["<b>03 Treatment response (MASTERMIND) cohort</b>: <br> n= with  unique drug periods <br> For T2D 1st instance dataset excluding drug starts within 91 days <br> of registration: n= with  unique drug periods <br> With script for diabetes medication <br> Index date=drug start date"]

* A valid date is an obsdate (for medcodes) which is no earlier than the patient's date of birth (no earlier than the month of birth if date of birth is not available; no earlier than full date of birth if this is available), no later than deregistration where this is present, and no later than the last collection date from the Practice. NB: QOF codes include codes for some non-Type 1/Type 2 diabetes types but not for gestational diabetes, so people with gestational diabetes codes only may be removed at this stage.

Note that as we are waiting for new HES data to be available before applying for linkage data (including ONS death data), the current scripts do not exclude codes (including diabetes diagnosis dates) which are after the patient's date of death. In addition, the current all_patid_ethnicity script uses GP-coded ethnicity only.

Extract details

Patients with a diabetes-related medcode (full list here) in the Observation table were extracted from the June 2024 CPRD Aurum release. See the log file for defining the patient list here.

Script overview

The below diagram shows the R scripts (in grey boxes) used to create the final cohorts (at-diagnosis, prevalent, and treatment response).

graph TD;
    A["<b>Our extract</b> <br> with linked HES APC, patient IMD, and ONS death data"] --> |"all_diabetes_cohort <br> & all_patid_ethnicity"|B["<b>Diabetes cohort</b> with static <br> patient data including <br> ethnicity and IMD*"]
    A-->|"all_patid_ckd_stages"|C["<b>Longitudinal CKD stages</b> <br> for all patients"]
    A-->|"baseline_biomarkers <br> (requires index date)"|E["<b>Biomarkers</b> <br> at index date"]
    A-->|"comorbidities <br> (requires index date)"|F["<b>Comorbidities</b> <br> at index date"]
    A-->|"smoking <br> (requires index date)"|G["<b>Smoking status</b> <br> at index date"]
    A-->|"alcohol <br> (requires index date)"|H["<b>Alcohol status</b> <br> at index date"]
    C-->|"ckd_stages <br> (requires index date)"|I["<b>CKD stage</b <br> at index date"]
    B-->|"final_merge"|J["<b>Final cohort dataset</b>"]
    E-->|"final_merge"|J
    F-->|"final_merge"|J
    G-->|"final_merge"|J
    H-->|"final_merge"|J
    I-->|"final_merge"|J

*IMD=Index of Multiple Deprivation; 'static' because we only have data from 2015.

Each of the three final cohorts (at-diagnosis, prevalent, and treatment response) contains static patient data e.g. ethnicity, IMD and diabetes type from the diabetes cohort dataset, plus biomarker, comorbidity, and sociodemographic (smoking/alcohol) data at the (cohort-specific) index date.

This directory contains the scripts which are common to all three cohorts: 'all_diabetes_cohort', 'all_patid_ckd_stages', and 'all_patid_ethnicity'. These pull out static patient characteristics or features based on longitudinal data which may go beyond the index date of the cohorts (e.g. all_patid_ethnicity uses ethnicity codes from all time for each patient, which may occur later than the index date for a cohort).

In addition, this directory contains templates for the scripts which pull out data relative to the cohort index dates ('baseline_biomarkers', 'comorbidities', 'smoking', 'alcohol', 'ckd_stages' and 'final_merge'). The final cohorts each use tailored versions of these to account for the different index dates, the different biomarkers/comorbidities required for the different cohorts, and different additional inclusion/exclusion criteria which are applied in the 'final_merge' script. In addition to these differences, the cohorts have different additional scripts which pull in additional information e.g. the treatment response cohort has a 'drug_sorting_and_combos' script which defines the drug start dates which are used as the index dates, as well as scripts for biomarker responses (6/12 month post-index), which are used to evaluate treatment response.

The exact 'tailored' and additional scripts used to create each cohort dataset can be found in the relevant subdirectory: 01-At-diagnosis, 02-Prevalent, 03-Treatment-response-(MASTERMIND), along with a data dictionary of all variables in the final cohort dataset.

Script details

Data from CPRD was provided as raw text files which were imported into a MySQL database using a custom-built package (aurum) built by Dr Robert Challen. This package also includes functions to allow easy querying of the MySQL tables from R, using the 'dbplyr' tidyverse package. Codelists used for querying the data (denoted as 'codes${codelist_name}' in scripts) can be found in our CPRD-Codelists repository.

Our CPRD-Codelists repository also contains more details on the algorithms used to define variables such as ethnicity, diabetes diagnosis date, and diabetes type - see individual scripts for links to the appropriate part of the CPRD-Codelists repository.

Script description	Outputs
all_patid_ckd_stages: uses eGFR calculated from serum creatinine to define longitudinal CKD stages for all patids as per our algorithm	all_patid_ckd_stages_from_algorithm: 1 row per patid, with onset of different CKD stages in wide format
all_patid_ethnicity: uses GP and linked HES data to define ethnicity as per our algorithm	all_patid_ethnicity: 1 row per patid, with 5-category, 16-category and QRISK2-category ethnicity (where available)
all_diabetes_cohort: table of patids meeting the criteria for our mixed Type 1/Type 2/'other' diabetes cohort plus additional patient variables	all_diabetes_cohort: 1 row per patid of those in the diabetes cohort, with diabetes diagnosis dates, DOB, gender, ethnicity etc.
template_baseline_biomarkers: pulls biomarkers value at cohort index dates	{cohort_prefix}_baseline_biomarkers: 1 row per patid-index date combination with all biomarker values at index date where available (including HbA1c and height)
template_comorbidities: finds onset of comorbidities relative to cohort index dates	{cohort_prefix}_comorbidities: 1 row per patid-index date combination, with earliest pre-index date code occurrence, latest pre-index date code occurrence, and earliest post-index date code occurrence
template_smoking: finds smoking status at cohort index dates	{cohort_prefix}_smoking: 1 row per patid-index date combination, with smoking status and QRISK2 smoking category at index date where available
template_alcohol: finds alcohol status at cohort index dates	{cohort_prefix}_alcohol: 1 row per patid-index date combination, with alcohol status at index date where available
template_ckd_stages: finds onset of CKD stages relative to cohort index dates	{cohort_prefix}_ckd_stages: 1 row per patid-index date combination, with baseline CKD stage at index date where available
template_final_merge: pulls together variables from all of the above tables and adds age and diabetes duration at index date (not for at-diagnosis cohort as age at diagnosis variable already present from all_diabetes_cohort script)	{cohort_prefix}_final_merge: 1 row per patid-index date combination with relevant biomarker/comorbidity/smoking/alcohol variables

Data dictionary of variables in 'final_merge' table

Biomarkers included: HbA1c (mmol/mol), weight (kg), height (m), BMI (kg/m2), HDL (mmol/L), triglycerides (mmol/L), blood creatinine (umol/L), LDL (mmol/L), ALT (U/L), AST (U/L), total cholesterol (mmol/L), DBP (mmHg), SBP (mmHg), ACR (mg/mmol / g/mol).

Comorbidities included: atrial fibrillation, angina, asthma, bronchiectasis, CKD stage 5/ESRD, CLD, COPD, cystic fibrosis, dementia, diabetic nephropathy, haematological cancers, heart failure, hypertension, IHD, myocardial infarction, neuropathy, other neurological conditions, PAD, pulmonary fibrosis, pulmonary hypertension, retinopathy, (coronary artery) revascularisation, rhematoid arthritis, solid cancer, solid organ transplant, stroke, TIA.