Query Verification: Steroid Induced Osteonecrosis Cohort

callahantiff commented 5 years ago

@mgkahn - Can you please help me verify the query to select Steroid Induced Osteonecrosis patients?

COHORT CRITERIA Case Criteria:

rx_case_inclusion_criteria_1: Presence of ANY corticosteroid (no minimum dose, IV (de.route_concept_id==4171047), IM (de.route_concept_id==4302612) or PO (de.route_concept_id==4132161) ≥ 14 days cumulative) (drug strings - criteria # 1)
dx_case_inclusion_criteria_1: Presence of aseptic necrosis of bone; query window from steroid initiation to termination + 1 yr (ICD9 codes - criteria # 8)
all_case_inclusion_criteria_1: Presence of specific keywords suggestive of osteonecrosis ( condition strings; measurement strings; observation strings; procedure strings - criteria # 9)
dx_case_exclusion_criteria_1: NOT presence of Alcohol Abuse (ICD9 codes; condition strings - criteria # 2)
dx_case_exclusion_criteria_2: NOT presence of Sickle Cell (ICD9 codes; condition strings - criteria # 3)
dx_case_exclusion_criteria_3: NOT presence of Gaucher Disease (ICD9 codes; condition strings - criteria # 4)
all_case_exclusion_criteria_1: NOT presence of Legge-Calve-Perthes (ICD9 codes; condition strings; drug strings; measurement strings; observation strings; procedure strings - criteria # 5)
all_case_exclusion_criteria_2: NOT HIV+ (Exclude if value ≠ “undetectable” or is NULL) (ICD9 codes; condition strings; drug strings; measurement strings - criteria # 6)
all_case_exclusion_criteria_3: NOT presence of Organ Transplant (ICD9 codes; CPT codes; condition strings - criteria # 7)

Control Criteria:

Not a case
rx_control_inclusion_criteria_1: Presence of ANY corticosteroid (no minimum dose, IV (de.route_concept_id==4171047), IM (de.route_concept_id==4302612) or PO (de.route_concept_id==4132161) ≥ 14 days cumulative) (medication strings - criteria # 10)
rx_control_exclusion_criteria_1: NOT presence of bisphosphonates (medication strings - criteria # 11)
dx_control_exclusion_criteria_1: NOT presence of Alcohol Abuse (ICD9 codes; condition strings - criteria # 12)
dx_control_exclusion_criteria_2: NOT presence of Sickle Cell (ICD9 codes; condition strings - criteria # 13)
dx_control_exclusion_criteria_3: NOT presence of Gaucher Disease (ICD9 codes; condition strings - criteria # 14)
all_control_exclusion_criteria_1: NOT presence of Legge-Calve-Perthes (ICD9 codes; condition strings; drug strings; measurement strings; observation strings; procedure strings - criteria # 15)
all_control_exclusion_criteria_2: NOT HIV+ (Exclude if value ≠ “undetectable” or is NULL) (ICD9 codes; condition strings; drug strings; measurement strings - criteria # 16)
all_control_exclusion_criteria_3: NOT presence of Organ Transplant (ICD9 codes; CPT codes; condition strings - criteria # 17)

Cohort Logic Table

COHORT	CHUNK	LOGICAL OPERATOR
CASE	rx_case_inclusion_criteria_1	AND
CASE	dx_case_inclusion_criteria_1	OR
CASE	all_case_inclusion_criteria_1	AND
CASE	dx_case_exclusion_criteria_1	AND
CASE	dx_case_exclusion_criteria_2	AND
CASE	dx_case_exclusion_criteria_3	AND
CASE	all_case_exclusion_criteria_1	AND
CASE	all_case_exclusion_criteria_2	AND
CASE	all_case_exclusion_criteria_3	---

CONTROL	rx_control_inclusion_criteria_1	AND
CONTROL	rx_control_exclusion_criteria_1	AND
CONTROL	dx_control_exclusion_criteria_1	AND
CONTROL	dx_control_exclusion_criteria_2	AND
CONTROL	dx_control_exclusion_criteria_3	AND
CONTROL	all_control_exclusion_criteria_1	AND
CONTROL	all_control_exclusion_criteria_2	AND
CONTROL	all_control_exclusion_criteria_3	---

NOTE.

Replace all instances of {database} with CHCO_DeID_Oct2018
Comment out all instances of {code_set_group}

Query can be found here and is also included below:


WITH rx_case_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.drug_exposure de, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE de.route_concept_id IN (4132161, 4171047, 4302612)
  AND de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 1
  AND cohort.standard_code_set = {code_set_group}
GROUP BY
  de.person_id, code_set, de.drug_exposure_start_datetime, de.drug_exposure_end_datetime
HAVING
  COUNT(DISTINCT de.drug_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(de.drug_exposure_end_datetime), DATETIME(de.drug_exposure_start_datetime), day) >= 14
),

dx_case_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM
  {database}.drug_exposure de,
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort,
(SELECT co.person_id, min(DATETIME(co.condition_start_datetime)) AS cond_start_date
FROM 
  {database}.condition_occurrence co, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 8
  AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, co.condition_concept_id
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1) cont
WHERE
  cont.person_id = de.person_id
  AND de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 1
  AND cohort.standard_code_set = {code_set_group}
GROUP BY de.person_id, code_set, de.drug_exposure_start_datetime, de.drug_exposure_end_datetime, cont.cond_start_date
HAVING
  COUNT(DISTINCT de.drug_concept_id) >= 1
  AND cont.cond_start_date >= min(DATETIME(de.drug_exposure_start_datetime))
  AND cont.cond_start_date <= DATETIME_ADD(DATETIME(max(de.drug_exposure_end_datetime)), INTERVAL 1 YEAR)
),

all_case_inclusion_criteria_1 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 9 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 9 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.measurement m, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 9 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  m.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.observation o, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  o.observation_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 9 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  o.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 9 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),

dx_case_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 2
  AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
),

dx_case_exclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 3
  AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
),

dx_case_exclusion_criteria_3 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 4
  AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
),

all_case_exclusion_criteria_1 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 5 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 5 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.measurement m, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 5 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  m.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.observation o, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  o.observation_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 5 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  o.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 5 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),

all_case_exclusion_criteria_2 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 6 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 6 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.measurement m, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 6 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  m.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.observation o, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  o.observation_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 6 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  o.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 6 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),

all_case_exclusion_criteria_3 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 7 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 7 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.measurement m, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 7 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  m.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.observation o, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  o.observation_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 7 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  o.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 7 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),

rx_control_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.drug_exposure de, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE de.route_concept_id IN (4132161, 4171047, 4302612)
  AND de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 10
  AND cohort.standard_code_set = {code_set_group}
GROUP BY
  de.person_id, code_set, de.drug_exposure_start_datetime, de.drug_exposure_end_datetime
HAVING
  COUNT(DISTINCT de.drug_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(de.drug_exposure_end_datetime), DATETIME(de.drug_exposure_start_datetime), day) >= 14
),

rx_control_exclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de,
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 11
  AND cohort.standard_code_set = {code_set_group}
GROUP BY de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
),

dx_control_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 12
  AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
),

dx_control_exclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 13
  AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
),

dx_control_exclusion_criteria_3 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 14
  AND cohort.standard_code_set = {code_set_group}
GROUP BY co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
),

all_control_exclusion_criteria_1 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 15 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 15 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.measurement m, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 15 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  m.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.observation o, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  o.observation_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 15 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  o.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 15
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),

all_control_exclusion_criteria_2 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 16 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 16 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.measurement m, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 16 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  m.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.observation o, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  o.observation_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 16 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  o.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 16 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
)),

all_control_exclusion_criteria_3 AS (
SELECT person_id, code_set FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 17 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
UNION DISTINCT
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 17 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  de.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT de.drug_concept_id) >= 1
UNION DISTINCT
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.measurement m, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 17 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  m.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT m.measurement_concept_id) >= 1
UNION DISTINCT
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.observation o, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  o.observation_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 17 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  o.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT o.observation_concept_id) >= 1
UNION DISTINCT
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 17 
  AND cohort.standard_code_set = {code_set_group}
GROUP BY 
  pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
))

SELECT * FROM
  (SELECT person_id, code_set, 'STEROIDINDUCEDOSTEONECROSIS_CASE' AS cohort_type
    FROM (
    (SELECT person_id, code_set FROM rx_case_inclusion_criteria_1
      INTERSECT DISTINCT 
     (SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
      UNION DISTINCT 
     SELECT person_id, code_set FROM all_case_inclusion_criteria_1))
       EXCEPT DISTINCT
     (SELECT person_id, code_set FROM dx_case_exclusion_criteria_1
       UNION DISTINCT
      SELECT person_id, code_set FROM dx_case_exclusion_criteria_2
       UNION DISTINCT
      SELECT person_id, code_set FROM dx_case_exclusion_criteria_3
       UNION DISTINCT
      SELECT person_id, code_set FROM all_case_exclusion_criteria_1
       UNION DISTINCT
      SELECT person_id, code_set FROM all_case_exclusion_criteria_2
       UNION DISTINCT
      SELECT person_id, code_set FROM all_case_exclusion_criteria_3))
   GROUP BY person_id, code_set, cohort_type)

  UNION ALL

  (SELECT person_id, code_set, 'STEROIDINDUCEDOSTEONECROSIS_CONTROL' AS cohort_type
    FROM (
    (SELECT person_id, code_set FROM rx_control_inclusion_criteria_1)
       EXCEPT DISTINCT
     (SELECT person_id, code_set FROM rx_control_exclusion_criteria_1
       UNION DISTINCT
      SELECT person_id, code_set FROM dx_control_exclusion_criteria_1
       UNION DISTINCT
      SELECT person_id, code_set FROM dx_control_exclusion_criteria_2
       UNION DISTINCT
      SELECT person_id, code_set FROM dx_control_exclusion_criteria_3
       UNION DISTINCT
      SELECT person_id, code_set FROM all_control_exclusion_criteria_1
       UNION DISTINCT
      SELECT person_id, code_set FROM all_control_exclusion_criteria_2
      UNION DISTINCT
      SELECT person_id, code_set FROM all_control_exclusion_criteria_3))

-- making sure no control patient is also a case
WHERE person_id NOT IN (
    (SELECT person_id FROM rx_case_inclusion_criteria_1
      INTERSECT DISTINCT 
     (SELECT person_id FROM dx_case_inclusion_criteria_1
      UNION DISTINCT 
     SELECT person_id FROM all_case_inclusion_criteria_1))
       EXCEPT DISTINCT
     (SELECT person_id FROM dx_case_exclusion_criteria_1
       UNION DISTINCT
      SELECT person_id FROM dx_case_exclusion_criteria_2
       UNION DISTINCT
      SELECT person_id FROM dx_case_exclusion_criteria_3
       UNION DISTINCT
      SELECT person_id FROM all_case_exclusion_criteria_1
       UNION DISTINCT
      SELECT person_id FROM all_case_exclusion_criteria_2
       UNION DISTINCT
      SELECT person_id FROM all_case_exclusion_criteria_3))
   GROUP BY person_id, code_set, cohort_type)
;
;

mgkahn commented 5 years ago

rx_case_inclusion_criteria_1:

You don't need "de.route_concept_id = c.concept_id" because you never use anything from the concept table for the route. You've already decoded the routes that you want into concept_ids.
While it doesn't change anything analytically, it probably would be easier to debug if you did your group by order_date, end_date (rather than currently end_date, order_date)so that the list would be sorted first by order date.
You are using "raw" drug orders. If you created and used drug_eras instead, you may get more intervals that are longer than 14 days because eras "merge" shorter intervals together. So, while an individual order may not meet 14 days, a drug era might. You would need to run the OHDSI provided algorithm for drug era. Jonathan Derkermajian can help you navigate this code if you wish to try it out.

mgkahn commented 5 years ago

dx_case_inclusion_criteria_1: Probably correct but checking -- your query only looks at the FIRST Dx [min(condition_starttime)], which may have occurred before the steroid time interval. You would not pick up instances of the condition that did occur within the steroid time interval. Thinking clinically, I think this is right -- if there is a Dx of necrosis BEFORE the steroid, then it is hard to call this steroid induced necrosis. So I think the logic you have here is right but just wanted to be explicit that this is what your logic is doing.

mgkahn commented 5 years ago

all_case_inclusion_criteria_1

Does this run successfully? Your UNIONS return only person_ids but your outer SELECT returns person_id and code_set_group. I just don't know if this runs. If not, you may need to add {code_set_group} to each of your subqueries so they return person_id, {code_set_group}.
You use cohort.phenotype_definition_number =9 for all subqueries, which use concept_codes from many different OMOP tables/domains. This should be fine since you will only match on concept_codes for the domain that matches the table domain. In other queries, you used different cohort.phenotype_definition_numbers for each data domain. I think what you have here is fine since the right domain codes will be found in the right tables (and you'll pick up any codes that are being stored in the wrong table!).
BTW: you do the same in all_case_exclusion_criteria_1 and many others. Just check that the query runs correctly or if it needs to be adjusted as I mention in the first bullet. It's an easy fix if needed.

mgkahn commented 5 years ago

Remaining all_case_inclusion -- I see you used the same "shell" that looks at conditions, medications, observations, procedures even when the code set probably is "looking" only at a single domain (e.g. conditions). This is fine even if inefficient and more expensive. I suspect folks not using GBQ would object to such brute force queries. But it does work since the concept codes will only match in the right domains.

mgkahn commented 5 years ago

rx_control_inclusion_criteria_1

same comment as previously about possibly using drug_era rather than drug_exposure events for 14d exposure duration.
same comment about not needs de.route_concept_id = c.concept_id JOIN since you don't use anything from the concept table for this field.

mgkahn commented 5 years ago

Overall logic: I think you have the logic for exclusions wrong for both cases and controls. Any exclusion removes a patient. So you need to "OR" (= UNION DISTINCT) all of the exclusions so that a patient_id that appears in ANY exclusion is included in your "EXCEPT DISTINCT" subquery. As currently written, a patient would only be excluded if they met ALL exclusion criteria.

mgkahn commented 5 years ago

End of mgk comments on steroid induced osteonecrosis

callahantiff commented 5 years ago

rx_case_inclusion_criteria_1:

You don't need "de.route_concept_id = c.concept_id" because you never use anything from the concept table for the route. You've already decoded the routes that you want into concept_ids.

Good point, I will remove that.

While it doesn't change anything analytically, it probably would be easier to debug if you did your group by order_date, end_date (rather than currently end_date, order_date)so that the list would be sorted first by order date.

OK, I will update accordingly.

You are using "raw" drug orders. If you created and used drug_eras instead, you may get more intervals that are longer than 14 days because eras "merge" shorter intervals together. So, while an individual order may not meet 14 days, a drug era might. You would need to run the OHDSI provided algorithm for drug era. Jonathan Derkermajian can help you navigate this code if you wish to try it out.

That's a great idea! I wonder if it also makes sense to do that for conditions too? I won't have that in time for our AMIA Informatics Summit, but I can adjust the queries and implement this afterwards. I created issue #114

For rx_case_inclusion_criteria_1 and rx_control_inclusion_criteria_1:

WITH rx_case_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.drug_exposure de, 
  {database}.concept c, 
  {database}.STEROIDINDUCEDOSTEONECROSIS_COHORT_VARS cohort 
WHERE  de.route_concept_id IN (4132161, 4171047, 4302612)
  AND de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 1
  AND cohort.standard_code_set = {code_set_group}
GROUP BY
  de.person_id, code_set, de.drug_exposure_order_datetime, de.drug_exposure_end_datetime
HAVING
  COUNT(DISTINCT de.drug_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(de.drug_exposure_end_datetime), DATETIME(de.drug_exposure_order_datetime), day) >= 14
),

callahantiff commented 5 years ago

dx_case_inclusion_criteria_1: Probably correct but checking -- your query only looks at the FIRST Dx [min(condition_starttime)], which may have occurred before the steroid time interval. You would not pick up instances of the condition that did occur within the steroid time interval. Thinking clinically, I think this is right -- if there is a Dx of necrosis BEFORE the steroid, then it is hard to call this steroid induced necrosis. So I think the logic you have here is right but just wanted to be explicit that this is what your logic is doing.

Thanks for thinking about this so critically, it sounds like we are in the same page about this. I was intending for the query to run this way 😄.

callahantiff commented 5 years ago

all_case_inclusion_criteria_1

Does this run successfully? Your UNIONS return only person_ids but your outer SELECT returns person_id and code_set_group. I just don't know if this runs. If not, you may need to add {code_set_group} to each of your subqueries so they return person_id, {code_set_group}.

You use cohort.phenotype_definition_number =9 for all subqueries, which use concept_codes from many different OMOP tables/domains. This should be fine since you will only match on concept_codes for the domain that matches the table domain. In other queries, you used different cohort.phenotype_definition_numbers for each data domain. I think what you have here is fine since the right domain codes will be found in the right tables (and you'll pick up any codes that are being stored in the wrong table!).

BTW: you do the same in all_case_exclusion_criteria_1 and many others. Just check that the query runs correctly or if it needs to be adjusted as I mention in the first bullet. It's an easy fix if needed.

Bullets 1 and 3: Thanks for pointing this out. I did run the query confirm that it returns the same rows as when adding in the missing argument to the subqueries. That said, I agree that this seems sketchy so I updated all all_... queries that use the same template to be the same. This should be good to go!

Bullet 2: I agree that this is a brute-force approach and not the most elegant, but it works 😄! I am happy to change if there is a different way that you would like me to write this. Just let me know.

callahantiff commented 5 years ago

Remaining all_case_inclusion -- I see you used the same "shell" that looks at conditions, medications, observations, procedures even when the code set probably is "looking" only at a single domain (e.g. conditions). This is fine even if inefficient and more expensive. I suspect folks not using GBQ would object to such brute force queries. But it does work since the concept codes will only match in the right domains.

Consistent with response above, I agree that this is a brute-force approach and not the most elegant, but it works 😄! I am happy to change if there is a different way that you would like me to write this. Just let me know.

callahantiff commented 5 years ago

Overall logic: I think you have the logic for exclusions wrong for both cases and controls. Any exclusion removes a patient. So you need to "OR" (= UNION DISTINCT) all of the exclusions so that a patient_id that appears in ANY exclusion is included in your "EXCEPT DISTINCT" subquery. As currently written, a patient would only be excluded if they met ALL exclusion criteria.

This might be a bigger question on my end then. I take it that unless you commented, the other queries looked OK? I am only asking so I know whether or not I need to go back the other queries.

So, if the logical definitions says: "NOT presence of ANY A AND NOT presence of ANY B AND NOT presence of ANY C"

You would write this like?

EXCEPT DISTINCT
(A UNION DISTINCT B UNION DISTINCT C)

callahantiff commented 5 years ago

It looks like MIMIC-OMOP does not have de.drug_exposure_order_datetime in the drug_exposure table. Should I substitute it for de.drug_exposure_start_datetime for both CHCO and MIMIC-OMOP?

mgkahn commented 5 years ago

Yes. Next best alternative.

NOTE: NEW EMAIL ADDRESS

Michael G. Kahn MD, PhD Professor with Tenure, Section of Clinical Informatics, Department of Pediatrics Co-Director & Translational Informatics Core Director, Colorado Clinical and Translational Sciences Institute Director, Health Data Compass Associate Director, Colorado Center for Personalized Medicine University of Colorado Denver Anschutz Medical Campus Aurora, Colorado 80045 USA

Medical Director, Research Informatics Children's Hospital Colorado Research Institute Aurora, Colorado

E: Michael.Kahn@cuanschutz.edumailto:Michael.Kahn@cuanschutz.edu | P: 303-724-8334 www.cuanschutz.eduhttp://www.cuanschutz.edu

[CU Anschutz Website]https://www.cuanschutz.edu/

[CU Anschutz Facebook]https://www.facebook.com/CUAnschutzMed/ [CU Anschutz Instagram] https://www.instagram.com/cuanschutz/ [CU Anschutz Linkedin] https://www.linkedin.com/school/university-of-colorado-anschutz-medical-campus/ [CU Anschutz Twitter] https://twitter.com/CUAnschutz

From: "Tiffany J. Callahan" notifications@github.com Reply-To: callahantiff/PheKnowVec reply@reply.github.com Date: Monday, August 12, 2019 at 1:39 PM To: callahantiff/PheKnowVec PheKnowVec@noreply.github.com Cc: "Kahn, Michael" MICHAEL.KAHN@CUANSCHUTZ.EDU, Mention mention@noreply.github.com Subject: Re: [callahantiff/PheKnowVec] Query Verification: Steroid Induced Osteonecrosis Cohort (#107)

It looks like MIMIC-OMOP does not have de.drug_exposure_order_datetime in the drug_exposure table. Should I substitute it for de.drug_exposure_start_datetime for both CHCO and MIMIC-OMOP?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/callahantiff/PheKnowVec/issues/107?email_source=notifications&email_token=AA557TRZOW6O6W6HOPVIR5DQEG347A5CNFSM4IF3SZS2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD4DTH2I#issuecomment-520565737, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AA557TR3R5XUR2TI3RTQ3V3QEG347ANCNFSM4IF3SZSQ.

callahantiff commented 5 years ago

TODO FOR ME:

[ ] Note to update the logic table here to make it clear where you are using OR with exclusion criteria! No need to re-check all of the other phenotypes.
- Here the query is right, but the truth table needs to be fixed!

callahantiff / PheKnowVec

Query Verification: Steroid Induced Osteonecrosis Cohort #107