How to find participant death reports from EHR data

Participant death is recorded in Electronic Health Records (EHRs) and is stored in the death table. Both the Registered and Controlled Tiers contain death data, however the Registered Tier has a date shift and the cause is suppressed. See the table below for a summary and review the How All of Us protects participant privacy and Data Dictionary support articles for more detail. 

To extract death data you have two options, outlined below:

1. Utilize the Dataset Builder Tool:

This method is recommended for those unfamiliar with SQL and are using the Controlled Tier dataset. Within the tool you can search the keyword 'death' and find all related EHR concepts, including but not limited to, 'Death of unknown cause' 'Death in hospital' etc. The benefit of this method is it allows you to be specific in the types of death. However, this method is not an efficient way to collect death in the Registered Tier because of field suppressions. See this article for details on how to use the Dataset Builder Tool.  

2. Query the Death table using SQL:

This is the recommended method and will extract all death data most efficiently. Within a desired notebook, you will write a SQL query extracting the Death table. Within the SQL you will choose which fields you would like to include. See the lists below for available fields based off access tier:

Registered Tier - person_id, death_date, death_datetime, death_type_concept_id, cause_concept_id, cause_source_value & cause_source_concept_id.

Controlled Tier - person_id, death_date, death_datetime, death_type_concept_id, cause_concept_id, cause_source_value & cause_source_concept_id.

Example of using SQL to query the Death table:

## Using Registered Tier dataset

SELECT DISTINCT person_id, death_date, death_datetime, death_type_concept_id, cause_concept_id, cause_source_value, cause_source_concept_id
FROM `fc-aou-cdr-prod.R2022Q4R9.death` 

## Using Controlled Tier dataset

SELECT DISTINCT person_id, death_date, death_datetime, death_type_concept_id, cause_concept_id, cause_source_value, cause_source_concept_id
FROM `fc-aou-cdr-prod-ct.C2022Q4R11.death`