How to find participant death reports

Participant death is recorded in Electronic Health Records (EHRs) and from HealthPro reporting. This information is now stored in the aou_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. 

All death records are now provided in the aou_death table. Deceased status information is now available from 2 sources: EHR (src_id = EHR sites) and HealthPro, a program portal for collecting participant data by program staff (src_id = Staff Portal : HealthPro). Deceased status information has historically been sourced from EHR (if available). In September 2020, All of Us Research Program launched deceased status reporting in HealthPro. Starting in the CDRv8, this additional source of participant deceased status is made available to researchers. Currently, program reported cause of death from HealthPro is NOT provided in the CDR as it is collected as free text.

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

1. Use Data Explorer:

Note: Data Explorer provides one death record per participant in an exported data snapshot.

This method is recommended for researchers who are unfamiliar with SQL and use the Controlled Tier dataset:

• Open the workspace Resources tab and create a cohort.
• In Data Explorer, define the cohort criteria by selecting “Deceased” under “Demographics”, and then save the cohort.
• Create a data snapshot for the saved cohort, select the death data you need, and export the snapshot to the workspace for analysis.

Data Explorer lets you select participants who are deceased. It is less efficient for collecting death data in the Registered Tier because some fields are suppressed. See How to use Data Explorer for step-by-step guidance.
 

2.1. Query the Death table using SQL:

This is the recommended method and will extract all death data most efficiently. In a Jupyter application within your workspace, write a SQL query against the aou_death table to extract the fields you need. See the lists below for available fields based on access tier:

Registered Tier - aou_death_id, person_id, death_date, death_datetime, death_type_concept_id, cause_concept_id, cause_source_value, src_id, primary_death_record.

Controlled Tier - aou_death_id, person_id, death_date, death_datetime, death_type_concept_id, cause_concept_id, cause_source_value, src_id, primary_death_record.

Example of using Python to query the aou_death table:

import pandas_gbq
import os

dataset = os.environ['WORKSPACE_CDR']
death_data = pandas_gbq.read_gbq(f'”””SELECT * FROM `{dataset}.aou_death` “””)
death_data.head()

Example of using R to query the aou_death table:

library(bigrquery)
library(tidyverse)
download_data <- function(query){
    tb <- bq_project_query(Sys.getenv('GOOGLE_PROJECT'), query = str_glue(query))
    bq_table_download(tb,bigint = "integer64")
}
dataset <- Sys.getenv('WORKSPACE_CDR')
death_data = download_data("SELECT distinct person_id, death_date
                              , death_type_concept_id, cause_concept_id, primary_death_record, src_id
                            FROM `{dataset}.aou_death`")
head(death_data)

2.2. Get a person-level death table with readable names:

The aou_death table contains all available death records, so it may include multiple records for one participant. The primary_death_record field identifies the primary record selected for each participant. Filter this field to TRUE to create a table with one death record per participant. Use death_type_concept_id and cause_concept_id to look up readable death type and cause information in the concept table, as shown in the examples below.

Example of using Python to get one death record per participant with readable names:

import pandas_gbq
import os

dataset = os.environ['WORKSPACE_CDR']

all_death_data= pandas_gbq.read_gbq(f'''
SELECT 
  person_id, 
  death_date, 
  t.concept_name as death_type, 
  c.concept_name as death_cause, 
  primary_death_record
FROM `{dataset}.aou_death`
LEFT JOIN `{dataset}.concept` t 
  on t.concept_id = death_type_concept_id
LEFT JOIN `{dataset}.concept` c 
  on c.concept_id = cause_concept_id
WHERE primary_death_record = TRUE''')

all_death_data.head()

Example of using R to get one death record per participant with readable names:

library(bigrquery)
library(tidyverse)
download_data <- function(query){
tb <- bq_project_query(Sys.getenv('GOOGLE_PROJECT'), query = str_glue(query))
bq_table_download(tb,bigint = "integer64")
}
dataset <- Sys.getenv('WORKSPACE_CDR')
all_death_data = download_data("
SELECT person_id, death_date, t.concept_name as death_type, c.concept_name as death_cause, primary_death_record
FROM `{dataset}.aou_death`
LEFT JOIN `{dataset}.concept` t on t.concept_id = death_type_concept_id
LEFT JOIN `{dataset}.concept` c on c.concept_id = cause_concept_id
WHERE primary_death_record = TRUE
")
head(all_death_data)