emis schema🔗
Available on backends: EMIS
This schema defines the data (both primary care and externally linked) available in the OpenSAFELY-EMIS backend. For more information about this backend, see "EMIS Primary Care".
from ehrql.tables.emis import (
clinical_events,
medications,
ons_deaths,
patients,
practice_registrations,
vaccinations,
)
many rows per patient
clinical_events🔗
Each record corresponds to a single clinical or consultation event for a patient.
Note that event codes do not change in this table. If an event code in the coding system becomes inactive, the event will still be coded to the inactive code. As such, codelists should include all relevant inactive codes.
many rows per patient
medications🔗
The medications table provides data about prescribed medications in primary care.
Prescribing data, including the contents of the medications table are standardised across clinical information systems such as SystmOne (TPP). This is a requirement for data transfer through the Electronic Prescription Service in which data passes from the prescriber to the pharmacy for dispensing.
Medications are coded using dm+d codes. The medications table is structured similarly to the clinical_events table, and each row in the table is made up of a patient identifier, an event (dm+d) code, and an event date. For this table, the event refers to the issue of a medication (coded as a dm+d code), and the event date, the date the prescription was issued.
Factors to consider when using medications data🔗
Depending on the specific area of research, you may wish to exclude medications in particular periods. For example, in order to ensure medication data is stable following a change of practice, you may want to exclude patients for a period after the start of their practice registration . You may also want to exclude medications for patients for a period prior to their leaving a practice. Alternatively, for research looking at a specific period of interest, you may simply want to ensure that all included patients were registered at a single practice for a minimum time prior to the study period, and were registered at the same practice for the duration of the study period.
Examples of using ehrQL to calculation such periods can be found in the documentation on how to use ehrQL to answer specific questions.
one row per patient
ons_deaths🔗
Registered deaths
Date and cause of death based on information recorded when deaths are certified and registered in England and Wales from February 2019 onwards. The data provider is the Office for National Statistics (ONS).
This table includes the underlying cause of death and up to 15 medical conditions mentioned on the death certificate.
These codes (cause_of_death_01
to cause_of_death_15
) are not ordered meaningfully.
More information about this table can be found in following documents provided by the ONS:
- Information collected at death registration
- User guide to mortality statistics
- How death registrations are recorded and stored by ONS
In the associated database table ONS_Deaths,
a small number of patients have multiple registered deaths.
This table contains the earliest registered death.
The ehrql.tables.raw.core.ons_deaths
table contains all registered deaths.
Tip
If you need to query for place of death, please note that
this is only available in the tpp
backend
-
date
🔗
date
-
Patient's date of death.
-
underlying_cause_of_death
🔗
ICD-10 code
-
Patient's underlying cause of death of death.
-
cause_of_death_01
🔗
ICD-10 code
-
Medical condition mentioned on the death certificate.
-
cause_of_death_02
🔗
ICD-10 code
-
Medical condition mentioned on the death certificate.
-
cause_of_death_03
🔗
ICD-10 code
-
Medical condition mentioned on the death certificate.
-
cause_of_death_04
🔗
ICD-10 code
-
Medical condition mentioned on the death certificate.
-
cause_of_death_05
🔗
ICD-10 code
-
Medical condition mentioned on the death certificate.
-
cause_of_death_06
🔗
ICD-10 code
-
Medical condition mentioned on the death certificate.
-
cause_of_death_07
🔗
ICD-10 code
-
Medical condition mentioned on the death certificate.
-
cause_of_death_08
🔗
ICD-10 code
-
Medical condition mentioned on the death certificate.
-
cause_of_death_09
🔗
ICD-10 code
-
Medical condition mentioned on the death certificate.
-
cause_of_death_10
🔗
ICD-10 code
-
Medical condition mentioned on the death certificate.
-
cause_of_death_11
🔗
ICD-10 code
-
Medical condition mentioned on the death certificate.
-
cause_of_death_12
🔗
ICD-10 code
-
Medical condition mentioned on the death certificate.
-
cause_of_death_13
🔗
ICD-10 code
-
Medical condition mentioned on the death certificate.
-
cause_of_death_14
🔗
ICD-10 code
-
Medical condition mentioned on the death certificate.
-
cause_of_death_15
🔗
ICD-10 code
-
Medical condition mentioned on the death certificate.
one row per patient
patients🔗
Patients in primary care.
In the EMIS backend, this table also includes information about the patient's current practice registration. Historical practice registration data is not currently available.
Recording of death in primary care🔗
In England, it is the statutory duty of the doctor who had attended in the last illness to complete a medical certificate of cause of death (MCCD). ONS death data are considered the gold standard for identifying patient deaths because they are based on these MCCDs.
There is generally a lag between the death being recorded in ONS data and it
appearing in the primary care record, but the coverage or recorded death is almost
complete and the date of death is usually reliable when it appears. There is
also a lag in ONS death recording (see ons_deaths
for more detail). You can find out more about the accuracy of date of death
recording in primary care in:
Gallagher, A. M., Dedman, D., Padmanabhan, S., Leufkens, H. G. M. & de Vries, F 2019. The accuracy of date of death recording in the Clinical Practice Research Datalink GOLD database in England compared with the Office for National Statistics death registrations. Pharmacoepidemiol. Drug Saf. 28, 563–569. https://doi.org/10.1002/pds.4747
-
date_of_birth
🔗
date
-
Patient's date of birth.
- Always the first day of a month
- Never
NULL
-
sex
🔗
string
-
Patient's sex.
- Possible values:
female
,male
,unknown
- Never
NULL
- Possible values:
-
date_of_death
🔗
date
-
Patient's date of death.
-
registration_start_date
🔗
date
-
Date patient joined practice.
- Never
NULL
- Never
-
registration_end_date
🔗
date
-
Date patient left practice.
-
practice_pseudo_id
🔗
string
-
Pseudonymised practice identifier.
- Never
NULL
- Never
-
rural_urban_classification
🔗
integer
-
Rural urban classification:
- 1 - Urban major conurbation
- 2 - Urban minor conurbation
- 3 - Urban city and town
- 4 - Urban city and town in a sparse setting
- 5 - Rural town and fringe
- 6 - Rural town and fringe in a sparse setting
- 7 - Rural village and dispersed
-
8 - Rural village and dispersed in a sparse setting
-
Always >= 1 and <= 8
-
imd_rounded
🔗
integer
-
Index of Multiple Deprivation (IMD) rounded to the nearest 100, where lower values represent more deprived areas.
- Always >= 0, <= 32800, and a multiple of 100
-
age_on(date)
🔗
-
Patient's age as an integer, in whole elapsed calendar years, as it would be on the given date.
This method takes no account of whether the patient is alive on the given date. In particular, it may return negative values if the given date is before the patient's date of birth.
View method definition
return (date - patients.date_of_birth).years
-
has_practice_registration_spanning(start_date, end_date)
🔗
-
Whether a patient's registration spans the entire period between
start_date
andend_date
.View method definition
return patients.registration_start_date.is_on_or_before(start_date) & ( patients.registration_end_date.is_after(end_date) | patients.registration_end_date.is_null() )
many rows per patient
practice_registrations🔗
Each record corresponds to a patient's registration with a practice.
Warning
At present, the EMIS database contains only the patient's current practice registration and does not include their full registration history.
-
for_patient_on(date)
🔗
-
Return each patient's practice registration as it was on the supplied date.
Where a patient is registered with multiple practices we prefer the most recent registration and then, if there are multiple of these, the one with the longest duration. If there's still an exact tie we choose arbitrarily based on the practice ID.
View method definition
spanning_regs = practice_registrations.where(practice_registrations.start_date <= date).except_where( practice_registrations.end_date < date ) ordered_regs = spanning_regs.sort_by( practice_registrations.start_date, practice_registrations.end_date, practice_registrations.practice_pseudo_id, ) return ordered_regs.last_for_patient()
-
spanning(start_date, end_date)
🔗
-
Filter registrations to just those spanning the entire period between
start_date
andend_date
.View method definition
return practice_registrations.where( practice_registrations.start_date.is_on_or_before(start_date) & (practice_registrations.end_date.is_after(end_date) | practice_registrations.end_date.is_null()) )
many rows per patient
vaccinations🔗
This table contains information on administered vaccinations, identified using SNOMED-CT codes for the vaccination procedure.
Vaccinations may also be queried by product code using the medications table.
Vaccinations that were administered at work or in a pharmacy might not be included in this table.