Accessing externally sourced socioeconomic status data

Accessing Externally Sourced Socioeconomic Status Data

A selection of socioeconomic status (SES) summary statistics, sourced from the U.S. Census American Community Survey via a three digit zip code linkage, are made available within the Controlled Tier dataset. These data are stored in an appended table and cover a selection of domains on a per Census block basis. Please see the Controlled Tier CDR Data Dictionary for details about the specific data elements available. 

These data can be accessed via both the Researcher Workbench tools and through direct query/import from a workspace notebook. Please see details about each method below.

1. Import via the Researcher Workbench Workspace Concept Set Selector Tool

The Concept Set Selector tool can be used to import and join a pre-packaged set of available SES concepts with a cohort within a dataset. 

1. Direct Query/Import from CDR

• The appended table can be directly queried/imported from the CDR via the table aou-res-curation-output-prod:C2022Q4R11.zip3_ses_map, which is only available in the Controlled Tier. 
• Each row in the Controlled Tier Data Dictionary "Socioeconomic Status" tab" describes a column in this table.
• Data can be joined on the Controlled Tier observation table for a participant by linking the participant’s generalized zip code with the zip3_as_string field in this table.

This can be done by joining with the observation table with the corrected_table as (

/* the table improperly prefixes all zip3 strings with 0, so the case statement here is fixing that for the join to work */

select zip3,

CASE WHEN LENGTH(zip3_as_string) >  5 THEN SUBSTR(zip3_as_string, 2) ELSE zip3_as_string END as zip3_as_string,

fraction_assisted_income,

fraction_high_school_edu,

median_income,

fraction_no_health_ins,

fraction_poverty,

fraction_vacant_housing,

deprivation_index,

acs

from `aou-res-curation-output-prod.C2022Q2R6.zip3_ses_map`

)

select observation_concept_id,

observation_source_concept_id,

observation_source_value,

value_source_concept_id,

value_source_value,

o.value_as_string,

count(*) as n

from `aou-res-curation-output-prod.C2022Q2R6.observation` o

join `corrected_table` z

on o.value_as_string = z.zip3_as_string

group by 1, 2, 3, 4, 5, 6