Appendicitis

Documentation

eMERGE Documentation
- 📄 Appendicitis-algorithm-2016_5-5.pdf (Downloaded: 03/17/19)
Code Set
GitHub Issue #22

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 1: Pathology report positive for appendicitis~~ (We don’t have this data)

CASE TYPE 2A: No pathology report, treatment is systemic antibiotics

dx_case_inclusion_criteria_1: Presence of an appendicitis diagnosis (ICD9 codes - criteria # 1)
rx_case_inclusion_criteria_1: Presence of systemic antibiotics (>2 days treatment, starting on encounter of appendicitis diagnosis) (drug strings - criteria # 2)
dx_case_exclusion_criteria_1: NOT presence of an appendicitis comorbidity in same encounter as appendicitis diagnosis (ICD9 codes - criteria # 3)
px_case_exclusion_criteria_1: NOT presence of an interventional Radiology code (CPT codes - criteria # 4)

CASE TYPE 2B: No pathology report, treatment is interventional radiology

dx_case_inclusion_criteria_1: Presence of an appendicitis diagnosis (ICD9 codes - criteria # 1)
px_case_inclusion_criteria_1: Presence of an interventional Radiology code (CPT codes - criteria # 5)
dx_case_exclusion_criteria_1: NOT presence of an appendicitis comorbidity in same encounter as appendicitis diagnosis (ICD9 codes - criteria # 3)
rx_case_exclusion_criteria_1: NOT Presence of systemic antibiotics (>2 days treatment, starting on encounter of appendicitis diagnosis) (drug strings - criteria # 6)

CASE TYPE 3: Reported history of appendicitis/appendectomy, without history of incidental appendectomy

all_case_inclusion_criteria_1: Presence of history of appendicitis reported (condition strings; drug strings;measurement strings; observation strings;procedure strings - criteria # 7)
px_case_exclusion_criteria_2: NOT presence of an incidental appendectomy (CPT codes/ICD9 codes - criteria # 8)

Control Criteria:
CONTROL TYPE 1: Pediatric patients

all_control_exclusion_criteria_1: NOT presence of history of appendicitis reported (condition strings; drug strings; measurement strings; observation strings; procedure strings - criteria # 9)
dx_control_exclusion_criteria_1: NOT presence of exclusionary codes (ICD9 codes - criteria # 10)

CONTROL TYPE 2: Adult patients

visit_criteria_1: Presence of at least 2 primary care/medical home visits between ages 20-40 years (inclusive)
- Visit_occurrence (visit_admitting_type)
  - Home/Self-Care (44814675)
  - Ambulatory visit (44814672)
  - Other (44814649)
all_control_exclusion_criteria_1: NOT presence of history of appendicitis reported (condition strings; drug strings; measurement strings; observation strings; procedure strings - criteria # 9)
dx_control_exclusion_criteria_1: NOT presence of exclusionary codes (ICD9 codes - criteria # 10)

Cohort Logic Table

COHORT	CHUNK	LOGICAL OPERATOR
CASE TYPE 2A	dx_case_inclusion_criteria_1	AND
CASE TYPE 2A	rx_case_inclusion_criteria_1	AND
CASE TYPE 2A	dx_case_exclusion_criteria_1	AND
CASE TYPE 2A	px_case_exclusion_criteria_1	---

CASE TYPE 2B	dx_case_inclusion_criteria_1	AND
CASE TYPE 2B	px_case_inclusion_criteria_1	AND
CASE TYPE 2B	dx_case_exclusion_criteria_1	AND
CASE TYPE 2B	rx_case_exclusion_criteria_1	---

CASE TYPE 3	all_case_inclusion_criteria_1	AND
CASE TYPE 3	px_case_exclusion_criteria_2	---

CONTROL_TYPE 1	all_control_exclusion_criteria_1	AND
CONTROL_TYPE 1	px_control_exclusion_criteria_1	---

CONTROL_TYPE 2	visit_criteria_1	AND
CONTROL_TYPE 2	all_control_exclusion_criteria_1	AND
CONTROL_TYPE 2	dx_control_exclusion_criteria_1	---

Covariates

History of Disease (ICD9, CPT)
- CPT code used to record procedure such as appendectomy
  - 77261; 77285; 77417; 76003; 77002; 77003; 76360; 77012; 76393; 76942
- ICD 9 code used to record disease
  - 540; 047.19; 540.0; 540.1; 540.9
  - Age at first instance and each instance of ICD9 code
History of Disease (Medications)
- Cefotetan; Cefotan; Cefotaxime; Claforan; Mefotoxin; Piperacillin; Tazobactam; Zosyn; Ampicillin; Sulbactam; Unasyn; Ticarcillin; Clavulanate; Timentin; Cefepime; Maxipime; Gentamicin; Gentacidin; Garamycin; Meropenem; Merrem; Ertapenem; Invanz; Metronidazole; Flagyl; Clindamycin; Cleocin; Levofloxacin; Levaquin
- Subject age in days at date associated with Systemic Antibiotics prescription
Demographics
- Sex of the participant
- Race of the participant
- Ethnicity of the participant
- Year of birth
- Subject age in days at date of ANY in-person visit
Features of Disease (CUIs) -- skipping; the requirement is only for clinical notes

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}.APPENDICITIS_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) >= 1
),

rx_case_inclusion_criteria_1 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.drug_exposure de, 
  {database}.APPENDICITIS_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}
  AND de.visit_occurrence_id IN (
      SELECT visit_occurrence_id FROM
      (SELECT co.visit_occurrence_id, min(co.condition_start_date)
    FROM 
      {database}.condition_occurrence co, 
      {database}.APPENDICITIS_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.visit_occurrence_id
    HAVING 
      COUNT(DISTINCT co.condition_concept_id) >= 1))
GROUP BY 
  de.person_id, cohort.standard_code_set, de.drug_exposure_end_datetime, de.drug_exposure_order_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) > 2
),

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

px_case_exclusion_criteria_1 AS (
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr,
  {database}.APPENDICITIS_COHORT_VARS cohort
WHERE 
  pr.procedure_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 pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
),

px_case_inclusion_criteria_1 AS (
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr,
  {database}.APPENDICITIS_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
),

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

all_case_inclusion_criteria_1 AS (
SELECT person_id, {code_set_group} AS code_set FROM
(SELECT co.person_id
FROM 
  {database}.condition_occurrence co, 
  {database}.APPENDICITIS_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
FROM 
  {database}.drug_exposure de, 
  {database}.APPENDICITIS_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
FROM 
  {database}.measurement m, 
  {database}.APPENDICITIS_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
FROM 
  {database}.observation o, 
  {database}.APPENDICITIS_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
FROM 
  {database}.procedure_occurrence pr, 
  {database}.APPENDICITIS_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
)),

px_case_exclusion_criteria_2 AS (
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr,
  {database}.APPENDICITIS_COHORT_VARS cohort
WHERE 
  pr.procedure_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 pr.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT pr.procedure_concept_id) >= 1
),

all_control_exclusion_criteria_1 AS (
SELECT person_id, {code_set_group} AS code_set FROM
(SELECT co.person_id
FROM 
  {database}.condition_occurrence co, 
  {database}.APPENDICITIS_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
FROM 
  {database}.drug_exposure de, 
  {database}.APPENDICITIS_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
FROM 
  {database}.measurement m, 
  {database}.APPENDICITIS_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
FROM 
  {database}.observation o, 
  {database}.APPENDICITIS_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
FROM 
  {database}.procedure_occurrence pr, 
  {database}.APPENDICITIS_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_control_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.condition_occurrence co,
  {database}.APPENDICITIS_COHORT_VARS cohort
WHERE 
  co.condition_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 co.person_id, cohort.standard_code_set
HAVING 
  COUNT(DISTINCT co.condition_concept_id) >= 1
),

visit_criteria_1 AS (
SELECT v.person_id, {code_set_group} AS code_set 
FROM 
  {database}.visit_occurrence v, 
  {database}.person p 
WHERE 
  v.person_id = p.person_id 
  AND v.admitting_source_concept_id IN (44814675, 44814672, 44814649) 
GROUP BY 
  v.person_id, v.visit_start_datetime, p.birth_datetime
HAVING 
  COUNT(DISTINCT v.visit_start_date) >= 2
  AND DATETIME_DIFF(DATETIME(v.visit_start_datetime), DATETIME(p.birth_datetime), YEAR) >= 20
  AND DATETIME_DIFF(DATETIME(v.visit_start_datetime), DATETIME(p.birth_datetime), YEAR) <= 40
)

SELECT * FROM
  (SELECT person_id, code_set, 'APPENDICITIS_CASE_TYPE2A' 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
      UNION DISTINCT
      SELECT person_id, code_set FROM px_case_exclusion_criteria_1))
   GROUP BY person_id, code_set, cohort_type)

  UNION ALL

  (SELECT person_id, code_set, 'APPENDICITIS_CASE_TYPE2B' AS cohort_type
    FROM (
    (SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM px_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 rx_case_exclusion_criteria_1))
   GROUP BY person_id, code_set, cohort_type)

  UNION ALL

  (SELECT person_id, code_set, 'APPENDICITIS_CASE_TYPE3' AS cohort_type
    FROM (
    (SELECT person_id, code_set FROM all_case_inclusion_criteria_1)
      EXCEPT DISTINCT
     (SELECT person_id, code_set FROM px_case_exclusion_criteria_2))
   GROUP BY person_id, code_set, cohort_type)

  UNION ALL

  (SELECT p.person_id, {code_set_group} AS code_set, 'APPENDICITIS_CONTROL_TYPE1' AS cohort_type
      FROM {database}.person p
      WHERE p.person_id NOT IN (
        SELECT person_id FROM all_control_exclusion_criteria_1
          INTERSECT DISTINCT
        SELECT person_id FROM dx_control_exclusion_criteria_1)
    GROUP BY p.person_id, code_set, cohort_type)
  
  UNION ALL

  (SELECT p.person_id, {code_set_group} AS code_set, 'APPENDICITIS_CONTROL_TYPE2' AS cohort_type
    FROM {database}.person p
    WHERE p.person_id NOT IN (
         SELECT person_id FROM all_control_exclusion_criteria_1
          INTERSECT DISTINCT
         SELECT person_id FROM dx_control_exclusion_criteria_1)
    AND p.person_id IN (SELECT person_id FROM visit_criteria_1)
  GROUP BY p.person_id, code_set, cohort_type)
;

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Appendicitis

Documentation

Cohort Criteria

Covariates

SQL Query

Project Information

Analyses

Findings

Enabling Reproducible Research

Discussions

Clone this wiki locally