Hypothyroidism

Documentation

eMERGE Documentation
- 📄 EMERGE_supplementary_project_hypothyroidism_definition_09_12_21b.doc (Downloaded: 03/19/19)
Code Set
GitHub Issue #18

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:

dx_case_inclusion_criteria_1: Presence of hypothyroidism (ICD9 codes - criteria # 0)
mx_case_inclusion_criteria_1: Presence of Abnormal TSH (>5.0)/FT4 (<0.5) - lab results (measurement strings - criteria 1 and 2)
mxrx_case_inclusion_criteria_1: Require:
- At least 2 instances of medication, with >= 3 months between the first and last instance of the medication occurrence (drug strings - criteria # 3)
- At least 2 instances of lab, with >= 3 months between the first and last instance of the lab occurrence (measurement strings - criteria # 4)
- At least 1 medication (drug strings - criteria # 3) and at least 1 lab (measurement strings - criteria # 4), with >= 3 months between the first and last instance of the medication or lab occurrence
dx_case_exclusion_criteria_1: NOT presence of secondary causes of hypothyroidism (ICD9 codes - criteria # 5)
dx_case_exclusion_criteria_2: NOT presence of other thyroid diseases (ICD9 codes; condition strings - criteria # 6)
px_case_exclusion_criteria_1: NOT presence of ANY radiation therapy codes that proceeds hypothyroidism dx (CPT codes - criteria # 11)
px_case_exclusion_criteria_2: NOT presence of ANY Thyroidectomy codes (CPT codes - criteria # 12)
rx_case_exclusion_criteria_2: NOT presence of ANY thyroid-altering medications (drug strings - criteria # 13)
dxmx_case_exclusion_criteria_1: NOT presence of abnormal TSH/FT4 within 6 months before pregnancy to one 1 year after pregnancy:
- Pregnancy exclusion ICD 9 codes (ICD9 codes - criteria # 7)
- Abnormally high b-HCG in urine or serum (measurement strings - criteria # 8)
rxmx_case_exclusion_criteria_1: NOT presence of ALL Abnormal Lab or Medication References Occur Within 6 Weeks of a Contrast Study:
- Contrast exposure keyword search (drug strings - criteria # 9)
- Contrast exposure keyword search (measurement strings - criteria # 10)

Control Criteria:

mx_control_inclusion_criteria_1: Have a normal TSH (0.5 – 5) and a normal FT4 (0.5-1.2; if checked) (measurement strings - criteria # 14 and criteria # 15)
visit_criteria_1: Presence of >= 2 past medical history sections (could substitute two non-acute clinic visits or requirement for annual physical; visit_admitting_source_concept_id == Ambulatory visit (44814672))
mx_control_inclusion_criteria_2: Presence of >= 2 medication lists (could substitute two non-acute clinic visits or requirement for annual physical; visit_admitting_source_concept_id == Ambulatory visit (44814672))
dx_control_exclusion_criteria_1: NOT presence of ANY billing codes for hypothyroidism (ICD9 codes - criteria # 16)
mx_control_exclusion_criteria_1: NOT presence of ANY evidence of thyroid altering medications (drug strings - criteria # 17)
all_control_exclusion_criteria_1: NOT presence of ANY cause of hypothyroidism or hyperthyroidism (condition strings; drug strings; measurement strings; observation strings; procedure strings - criteria # 18)
px_control_exclusion_criteria_1: NOT presence of ANY radiation therapy codes (CPT codes - criteria # 19)
px_control_exclusion_criteria_2: NOT presence of ANY thyroidectomy codes (CPT codes - criteria # 20)
dx_control_exclusion_criteria_2: NOT presence of other thyroid diseases (ICD9 codes; condition strings - criteria # 21)

Cohort Logic Table

COHORT	CHUNK	LOGICAL OPERATOR
CASE	dx_case_inclusion_criteria_1	OR
CASE	mx_case_inclusion_criteria_1	AND
CASE	mxrx_case_inclusion_criteria_1	AND
CASE	dx_case_exclusion_criteria_1	AND
CASE	dx_case_exclusion_criteria_2	AND
CASE	px_case_exclusion_criteria_1	AND
CASE	px_case_exclusion_criteria_2	AND
CASE	rx_case_exclusion_criteria_2	AND
CASE	dxmx_case_exclusion_criteria_1	AND
CASE	rxmx_case_exclusion_criteria_1	---

CONTROL	mx_control_inclusion_criteria_1	AND
CONTROL	visit_criteria_1	AND
CONTROL	mx_control_inclusion_criteria_2	AND
CONTROL	dx_control_exclusion_criteria_1	AND
CONTROL	mx_control_exclusion_criteria_1	AND
CONTROL	all_control_exclusion_criteria_1	AND
CONTROL	px_control_exclusion_criteria_1	AND
CONTROL	px_control_exclusion_criteria_2	AND
CONTROL	dx_control_exclusion_criteria_2	---

Covariates

Demographics
- Sex of the participant
- Race of the participant
- Ethnicity of the participant
- Year of birth
- Age at first hypothyroidism indication
- Age when clinical event recorded in record (medication)
- Subject's weight at enrollment
- Subject's height at enrollment
- Subject's BMI over time

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}.HYPOTHYROIDISM_COHORT_VARS cohort
  WHERE 
    co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
    AND cohort.phenotype_definition_number = 0
    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
),

mx_case_inclusion_criteria_1 AS (
  SELECT * FROM (
    SELECT m.person_id, cohort.standard_code_set AS code_set 
    FROM 
      {database}.measurement m, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort  
    WHERE 
      m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
      AND cohort.phenotype_definition_number = 1 
      AND cohort.standard_code_set = {code_set_group}
      AND m.value_as_number > m.range_high
    GROUP BY 
      m.person_id, cohort.standard_code_set
    HAVING 
      COUNT(DISTINCT m.measurement_concept_id) >= 1
   UNION ALL
    SELECT m.person_id, cohort.standard_code_set AS code_set 
    FROM 
      {database}.measurement m, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort  
    WHERE 
      m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
      AND cohort.phenotype_definition_number = 2 
      AND cohort.standard_code_set = {code_set_group}
      AND m.value_as_number < m.range_high
    GROUP BY 
      m.person_id, cohort.standard_code_set
    HAVING 
      COUNT(DISTINCT m.measurement_concept_id) >= 1   
)),

mxrx_case_inclusion_criteria_1 AS (
SELECT * FROM 
(SELECT person_id, code_set
    FROM
    (SELECT v.person_id,
      v.visit_occurrence_id,
      v.visit_start_datetime,
      LAG(visit_start_datetime) OVER (ORDER BY v.person_id, visit_start_datetime) as lag,
      cohort.standard_code_set AS code_set
     FROM 
      {database}.visit_occurrence v,
      {database}.drug_exposure de, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
    WHERE de.visit_occurrence_id = v.visit_occurrence_id  
      AND de.drug_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
      v.person_id, v.visit_occurrence_id, v.visit_start_datetime, code_set
    HAVING
      COUNT(DISTINCT de.drug_concept_id) >= 1)
  WHERE
    DATETIME_DIFF(DATETIME(visit_start_datetime),
    DATETIME(lag), MONTH) >= 3
  GROUP BY person_id, code_set
  HAVING 
    COUNT(DISTINCT visit_occurrence_id) >= 2
  UNION ALL
  SELECT person_id, code_set
    FROM
    (SELECT v.person_id,
      v.visit_occurrence_id,
      v.visit_start_datetime,
      LAG(visit_start_datetime) OVER (ORDER BY v.person_id, visit_start_datetime) as lag,
      cohort.standard_code_set AS code_set
     FROM 
      {database}.visit_occurrence v,
      {database}.measurement m, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
    WHERE m.visit_occurrence_id = v.visit_occurrence_id  
      AND m.measurement_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
      v.person_id, v.visit_occurrence_id, v.visit_start_datetime, code_set
    HAVING
      COUNT(DISTINCT m.measurement_concept_id) >= 1)
  WHERE
    DATETIME_DIFF(DATETIME(visit_start_datetime),
    DATETIME(lag), MONTH) >= 3
  GROUP BY person_id, code_set
  HAVING 
    COUNT(DISTINCT visit_occurrence_id) >= 2)
UNION ALL
(SELECT person_id, code_set
    FROM
    (SELECT v.person_id,
      v.visit_occurrence_id,
      v.visit_start_datetime,
      LAG(visit_start_datetime) OVER (ORDER BY v.person_id, visit_start_datetime) as lag,
      cohort.standard_code_set AS code_set
     FROM 
      {database}.visit_occurrence v,
      {database}.drug_exposure de, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
    WHERE de.visit_occurrence_id = v.visit_occurrence_id  
      AND de.drug_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
      v.person_id, v.visit_occurrence_id, v.visit_start_datetime, code_set
    HAVING
      COUNT(DISTINCT de.drug_concept_id) >= 1)
  WHERE
    DATETIME_DIFF(DATETIME(visit_start_datetime),
    DATETIME(lag), MONTH) >= 3
  GROUP BY person_id, code_set
  HAVING 
    COUNT(DISTINCT visit_occurrence_id) >= 1
  INTERSECT DISTINCT
  SELECT person_id, code_set
    FROM
    (SELECT v.person_id,
      v.visit_occurrence_id,
      v.visit_start_datetime,
      LAG(visit_start_datetime) OVER (ORDER BY v.person_id, visit_start_datetime) as lag,
      cohort.standard_code_set AS code_set
     FROM 
      {database}.visit_occurrence v,
      {database}.measurement m, 
      {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
    WHERE m.visit_occurrence_id = v.visit_occurrence_id  
      AND m.measurement_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
      v.person_id, v.visit_occurrence_id, v.visit_start_datetime, code_set
    HAVING
      COUNT(DISTINCT m.measurement_concept_id) >= 1)
  WHERE
    DATETIME_DIFF(DATETIME(visit_start_datetime),
    DATETIME(lag), MONTH) >= 3
  GROUP BY person_id, code_set
  HAVING 
    COUNT(DISTINCT visit_occurrence_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}.HYPOTHYROIDISM_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) > 0
),

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

px_case_exclusion_criteria_1 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set FROM
(SELECT pr.person_id, v.visit_end_date as procedure_date, cohort.standard_code_set AS code_set
  FROM 
    {database}.procedure_occurrence pr,
    {database}.visit_occurrence v,
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort
  WHERE 
    pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64)
    AND v.visit_occurrence_id = pr.visit_occurrence_id
    AND cohort.phenotype_definition_number = 11
    AND cohort.standard_code_set = {code_set_group}
  GROUP BY pr.person_id, cohort.standard_code_set, v.visit_end_date
  HAVING
    COUNT(DISTINCT pr.procedure_concept_id) > 0) cont1,
  {database}.condition_occurrence co,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = co.person_id
  AND co.visit_occurrence_id = v.visit_occurrence_id
  AND co.condition_concept_id = CAST(cohort.standard_concept_id AS int64)
  AND cohort.phenotype_definition_number = 0
  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
  AND max(cont1.procedure_date) < max(v.visit_end_date)
),

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

rx_case_exclusion_criteria_2 AS (
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  de.drug_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 de.person_id, cohort.standard_code_set
HAVING
  COUNT(DISTINCT de.drug_concept_id) >= 1
),

dxmx_case_exclusion_criteria_1 AS (
SELECT * FROM
(SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT m.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = m.visit_occurrence_id
    AND m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.phenotype_definition_number = 1 
    AND cohort.standard_code_set = {code_set_group}
    AND m.value_as_number > m.range_high
  GROUP BY 
    m.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1) cont1,
  {database}.condition_occurrence co,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = co.person_id
  AND co.visit_occurrence_id = v.visit_occurrence_id
  AND 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, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT co.condition_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), MONTH) <= 6
  AND DATETIME_DIFF(DATETIME(cont1.visit_end_datetime), DATETIME(v.visit_end_datetime), MONTH) >= 12
  UNION ALL
  SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT m.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = m.visit_occurrence_id
    AND m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.phenotype_definition_number = 2 
    AND cohort.standard_code_set = {code_set_group}
    AND m.value_as_number < m.range_low
  GROUP BY 
    m.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1) cont1,
  {database}.condition_occurrence co,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = co.person_id
  AND co.visit_occurrence_id = v.visit_occurrence_id
  AND 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, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT co.condition_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), MONTH) <= 6
  AND DATETIME_DIFF(DATETIME(cont1.visit_end_datetime), DATETIME(v.visit_end_datetime), MONTH) >= 12)  
  INTERSECT DISTINCT  
  (SELECT m.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT m.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = m.visit_occurrence_id
    AND m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.phenotype_definition_number = 1 
   AND cohort.standard_code_set = {code_set_group}
    AND m.value_as_number > m.range_high
  GROUP BY 
    m.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1) cont1,
  {database}.measurement m,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = m.person_id
  AND m.visit_occurrence_id = v.visit_occurrence_id
  AND m.measurement_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 m.person_id, cohort.standard_code_set, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT m.measurement_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), MONTH) <= 6
  AND DATETIME_DIFF(DATETIME(cont1.visit_end_datetime), DATETIME(v.visit_end_datetime), MONTH) >= 12  
  UNION ALL 
  SELECT m.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT m.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = m.visit_occurrence_id
    AND m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.phenotype_definition_number = 2 
  AND cohort.standard_code_set = {code_set_group}
    AND m.value_as_number < m.range_low
  GROUP BY 
    m.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1) cont1,
  {database}.measurement m,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = m.person_id
  AND m.visit_occurrence_id = v.visit_occurrence_id
  AND m.measurement_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 m.person_id, cohort.standard_code_set, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT m.measurement_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), MONTH) <= 6
  AND DATETIME_DIFF(DATETIME(cont1.visit_end_datetime), DATETIME(v.visit_end_datetime), MONTH) >= 12
)),

rxmx_case_exclusion_criteria_1 AS (
SELECT * FROM
(SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT de.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.drug_exposure de, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = de.visit_occurrence_id
    AND de.drug_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 
    de.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT de.drug_concept_id) >= 1) cont1,
  {database}.drug_exposure de,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = de.person_id
  AND de.visit_occurrence_id = v.visit_occurrence_id
  AND 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, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT de.drug_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), WEEK) <= 6
 UNION ALL
SELECT de.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT m.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = m.visit_occurrence_id
    AND m.measurement_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 
    m.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1) cont1,
  {database}.drug_exposure de,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = de.person_id
  AND de.visit_occurrence_id = v.visit_occurrence_id
  AND 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, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT de.drug_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), WEEK) <= 6)
UNION ALL
(SELECT m.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT de.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.drug_exposure de, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = de.visit_occurrence_id
    AND de.drug_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 
    de.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT de.drug_concept_id) >= 1) cont1,
  {database}.measurement m,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = m.person_id
  AND m.visit_occurrence_id = v.visit_occurrence_id
  AND m.measurement_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 m.person_id, cohort.standard_code_set, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT m.measurement_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), WEEK) <= 6
  UNION ALL
 SELECT m.person_id, cohort.standard_code_set AS code_set 
FROM
(SELECT m.person_id, v.visit_end_datetime, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort,
    {database}.visit_occurrence v
  WHERE 
    v.visit_occurrence_id = m.visit_occurrence_id
    AND m.measurement_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 
    m.person_id, cohort.standard_code_set, v.visit_end_datetime
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1) cont1,
  {database}.measurement m,
  {database}.visit_occurrence v,
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort
WHERE 
  cont1.person_id = m.person_id
  AND m.visit_occurrence_id = v.visit_occurrence_id
  AND m.measurement_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 m.person_id, cohort.standard_code_set, v.visit_end_datetime, cont1.visit_end_datetime
HAVING
  COUNT(DISTINCT m.measurement_concept_id) >= 1
  AND DATETIME_DIFF(DATETIME(v.visit_end_datetime), DATETIME(cont1.visit_end_datetime), WEEK) <= 6)
),

mx_control_inclusion_criteria_1 AS (
SELECT * FROM (
SELECT m.person_id, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_COHORT_VARS cohort  
  WHERE 
    m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
    AND cohort.phenotype_definition_number = 14 
    AND cohort.standard_code_set = {code_set_group}
    AND m.value_as_number < m.range_high
    AND m.value_as_number > m.range_low
  GROUP BY 
    m.person_id, cohort.standard_code_set
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1
 INTERSECT DISTINCT
 SELECT m.person_id, cohort.standard_code_set AS code_set 
  FROM 
    {database}.measurement m, 
    {database}.HYPOTHYROIDISM_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}
    AND m.value_as_number < m.range_high
    AND m.value_as_number > m.range_low
  GROUP BY 
    m.person_id, cohort.standard_code_set
  HAVING 
    COUNT(DISTINCT m.measurement_concept_id) >= 1)
),

visit_criteria_1 AS (
  SELECT v.person_id
  FROM
    {database}.visit_occurrence v
  WHERE
    v.admitting_source_concept_id = 44814672
  GROUP BY 
    v.person_id
  HAVING
    COUNT(DISTINCT v.visit_occurrence_id) >= 2
),

mx_control_inclusion_criteria_2 AS (
  SELECT de.person_id
  FROM
    {database}.drug_exposure de
  GROUP BY 
    de.person_id
  HAVING
    COUNT(DISTINCT de.drug_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}.HYPOTHYROIDISM_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
),

mx_control_exclusion_criteria_1 AS (
SELECT m.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.measurement m, 
  {database}.HYPOTHYROIDISM_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
),

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}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  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 ALL
SELECT de.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.drug_exposure de, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  de.drug_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  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 ALL
SELECT m.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.measurement m, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  m.measurement_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  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 ALL
SELECT o.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.observation o, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  o.observation_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  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 ALL
SELECT pr.person_id, cohort.standard_code_set AS code_set
FROM 
  {database}.procedure_occurrence pr, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 18 
  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_control_exclusion_criteria_1 AS (
SELECT pr.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.procedure_occurrence pr, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 19
  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_control_exclusion_criteria_2 AS (
SELECT pr.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.procedure_occurrence pr, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  pr.procedure_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 20
  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_2 AS (
SELECT co.person_id, cohort.standard_code_set AS code_set 
FROM 
  {database}.condition_occurrence co, 
  {database}.HYPOTHYROIDISM_COHORT_VARS cohort 
WHERE 
  co.condition_concept_id = CAST(cohort.standard_concept_id AS int64) 
  AND cohort.phenotype_definition_number = 21
  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, 'HYPOTHYROIDISM_CASE' AS cohort_type
    FROM (    
    ((SELECT person_id, code_set FROM dx_case_inclusion_criteria_1
      UNION DISTINCT 
    (SELECT person_id, code_set FROM mx_case_inclusion_criteria_1)) 
      INTERSECT DISTINCT
    SELECT person_id, code_set FROM mxrx_case_inclusion_criteria_1)         
      EXCEPT DISTINCT      
    (SELECT person_id, code_set FROM dx_case_exclusion_criteria_1
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM dx_case_exclusion_criteria_2
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM px_case_exclusion_criteria_1
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM px_case_exclusion_criteria_2
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM rx_case_exclusion_criteria_2
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM dxmx_case_exclusion_criteria_1
      INTERSECT DISTINCT
     SELECT person_id, code_set FROM rxmx_case_exclusion_criteria_1))
    GROUP BY person_id, code_set, cohort_type)
    
  UNION ALL

  (SELECT p.person_id, {code_set_group} AS code_set, 'HYPOTHYROIDISM_CONTROL' AS cohort_type
    FROM {database}.person p
    WHERE p.person_id NOT IN (
      SELECT person_id FROM dx_control_exclusion_criteria_1
          INTERSECT DISTINCT
        SELECT person_id FROM mx_control_exclusion_criteria_1
          INTERSECT DISTINCT
        SELECT person_id FROM all_control_exclusion_criteria_1
          INTERSECT DISTINCT
        SELECT person_id FROM px_control_exclusion_criteria_1
          INTERSECT DISTINCT
        SELECT person_id FROM px_control_exclusion_criteria_2
          INTERSECT DISTINCT
        SELECT person_id FROM dx_control_exclusion_criteria_2)
    AND p.person_id IN (
        (SELECT person_id FROM visit_criteria_1
         UNION DISTINCT
        SELECT person_id FROM mx_control_inclusion_criteria_1)
         INTERSECT DISTINCT
        SELECT person_id FROM mx_control_inclusion_criteria_2)
  GROUP BY p.person_id, code_set, cohort_type)
;

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Hypothyroidism

Documentation

Cohort Criteria

Covariates

SQL Query

Project Information

Analyses

Findings

Enabling Reproducible Research

Discussions

Clone this wiki locally