Crohn's Disease

Documentation

• eMERGE Documentation
  • 📄 Demonstration Project_phenotype criteria_Crohns_08_04_07_clean.docx (Downloaded: 03/17/19)
• Code Set
• GitHub Issue #20

---

See the SQL query to identify the cohort below and note that the mapping between the bolded words at the beginning of each of the case and control criteria and the named query chunks

Cohort Criteria

Case Criteria:
CASE TYPE 1: Crohn’s and medications only

• dx_case_inclusion_criteria_1: Presence of >2 occurrences of relevant diagnosis codes (ICD9 codes - criteria # 1)
• rx_case_inclusion_criteria_1: Presence of >= 1 medications (drug strings - criteria # 2)
• dx_case_exclusion_criteria_1: NOT presence of any ulcerative colitis diagnosis codes (ICD9 codes - criteria # 3)

CASE TYPE 2: Crohn’s and medications with Ulcerative Colitis Diagnosis

• dx_case_inclusion_criteria_1: Presence of >2 occurrences of relevant diagnosis codes (ICD9 codes - criteria # 1)
• rx_case_inclusion_criteria_1: Presence of >=1 medications (drug strings - criteria # 2)
• dx_case_inclusion_criteria_2: Presence of >2 ulcerative colitis diagnosis codes (ICD9 codes - criteria # 4)

Control Criteria:

• dx_control_exclusion_criteria_1: NOT presence of >2 occurrences of relevant codes (ICD9 codes - criteria # 5)
• rx_control_exclusion_criteria_1: NOT presence of >=1 medications (drug strings - criteria # 6)
• dx_control_exclusion_criteria_2: NOT presence of >2 ulcerative colitis diagnosis codes (ICD9 codes - criteria # 7)
• dx_control_exclusion_criteria_3: NOT presence of ANY exclusionary diagnosis code (ICD9 codes - criteria # 8)
• dx_control_exclusion_criteria_4: NOT presence of ANY diagnosis keywords (condition strings - criteria # 9)

---

Cohort Logic Table

COHORT	CHUNK	LOGICAL OPERATOR
CASE TYPE 1	dx_case_inclusion_criteria_1	AND
CASE TYPE 1	rx_case_inclusion_criteria_1	AND
CASE TYPE 1	dx_case_exclusion_criteria_1	---
CASE TYPE 2	dx_case_inclusion_criteria_1	AND
CASE TYPE 2	rx_case_inclusion_criteria_1	AND
CASE TYPE 2	dx_case_inclusion_criteria_2	---
CONTROL	dx_control_exclusion_criteria_1	AND
CONTROL	rx_control_exclusion_criteria_1	AND
CONTROL	dx_control_exclusion_criteria_2	AND
CONTROL	dx_control_exclusion_criteria_3	AND
CONTROL	dx_control_exclusion_criteria_4	---

---

Covariates

• None provided

---

SQL Query

WITH dx_case_inclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.condition_occurrence co, 
  {database}.CROHNSDISEASE_COHORT_VARS cohort 
WHERE 
  co.condition_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 
  co.person_id, cohort.standard_code_set 
HAVING 
  COUNT(DISTINCT co.condition_concept_id) > 2
),

rx_case_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.drug_exposure de, 
  {database}.CROHNSDISEASE_COHORT_VARS cohort 
WHERE 
  de.drug_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 
  de.person_id, cohort.standard_code_set 
HAVING 
  COUNT(DISTINCT de.drug_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}.CROHNSDISEASE_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_inclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.condition_occurrence co, 
  {database}.CROHNSDISEASE_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) > 2
),

dx_control_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.condition_occurrence co, 
  {database}.CROHNSDISEASE_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) > 2
),

rx_control_exclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.drug_exposure de, 
  {database}.CROHNSDISEASE_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
),

dx_control_exclusion_criteria_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.condition_occurrence co, 
  {database}.CROHNSDISEASE_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) > 2
),

dx_control_exclusion_criteria_3 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.condition_occurrence co, 
  {database}.CROHNSDISEASE_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, cohort.standard_code_set 
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
),

dx_control_exclusion_criteria_4 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.condition_occurrence co, 
  {database}.CROHNSDISEASE_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
)

SELECT * FROM
  (SELECT person_id, code_set, 'CROHNSDISEASE_CASE_TYPE1' AS cohort_type
    FROM (
    (SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
      INTERSECT DISTINCT 
     SELECT person_id, code_set FROM rx_case_inclusion_criteria_1)
      EXCEPT DISTINCT
     SELECT person_id, code_set FROM dx_case_exclusion_criteria_1)
   GROUP BY person_id, code_set, cohort_type)

  UNION ALL

  (SELECT person_id, code_set, 'CROHNSDISEASE_CASE_TYPE2' AS cohort_type
    FROM (
     SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
      INTERSECT DISTINCT 
     SELECT person_id, code_set FROM rx_case_inclusion_criteria_1
      INTERSECT DISTINCT 
     SELECT person_id, code_set FROM dx_case_inclusion_criteria_2)
   GROUP BY person_id, code_set, cohort_type)

  UNION ALL
  (SELECT p.person_id, {code_set_group} AS code_set, 'CROHNSDISEASE_CONTROL' AS cohort_type
    FROM {database}.person p
    WHERE p.person_id NOT IN (
       SELECT person_id ROM dx_control_exclusion_criteria_1
        INTERSECT DISTINCT
       SELECT person_id FROM rx_control_exclusion_criteria_1
        INTERSECT DISTINCT
       SELECT person_id FROM dx_control_exclusion_criteria_2
        INTERSECT DISTINCT
       SELECT person_id FROM dx_control_exclusion_criteria_3
        INTERSECT DISTINCT
       SELECT person_id FROM dx_control_exclusion_criteria_4)
  GROUP BY p.person_id, code_set, cohort_type)
;

This query can can also be accessed via a GitHub Gist here