Skip to content

Instantly share code, notes, and snippets.

@callahantiff
Last active November 27, 2019 00:32
Show Gist options
  • Save callahantiff/e279f0a80db52e423a316f0a08a17d7c to your computer and use it in GitHub Desktop.
Save callahantiff/e279f0a80db52e423a316f0a08a17d7c to your computer and use it in GitHub Desktop.
Med2Mech Patient Cohorts
-- Query 1: Search for specific patients using specific vocabulary codes
SELECT DISTINCT
CONCAT('"', CAST(c1.concept_id AS STRING), '"') AS OMOP_ID,
c1.concept_name AS OMOP_LABEL,
CONCAT('"', CAST(c.concept_code AS STRING), '"') AS CLINICAL_CODE,
CONCAT('"', CAST(c1.concept_code AS STRING), '"') AS SNOMED_CODE,
c.vocabulary_id AS VOCABULARY
FROM CHCO_DeID_Oct2018.concept c
JOIN CHCO_DeID_Oct2018.concept_relationship r ON r.concept_id_1 = c.concept_id
JOIN CHCO_DeID_Oct2018.concept c1 ON r.concept_id_2 = c1.concept_id
WHERE r.relationship_id IN ("Maps to")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ("E00.0", "E00.1", "E00.2", "E00.9", "E03.0", "E03.1", "243")
AND c.vocabulary_id IN ("ICD9", "ICD9CM", "ICD10", "ICD10CM")
AND c.domain_id = "Condition"
AND c1.vocabulary_id = "SNOMED"
AND c1.domain_id = "Condition"
ORDER BY CLINICAL_CODE, OMOP_ID;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- Query 2: Search for all relevant ICD-9CM and ICD-10 codes
SELECT DISTINCT
search_string AS SEARCH_STRING,
CONCAT('"', concept_id, '"') AS OMOP_ID,
concept_name AS OMOP_LABEL,
CONCAT('"', concept_code, '"') AS CLINICAL_CODE,
vocabulary_id AS VOCABULARY
FROM
(
SELECT
DISTINCT c.concept_name,
CAST(c.concept_id AS STRING) AS concept_id,
c.concept_code,
c.domain_id,
c.vocabulary_id,
CASE
WHEN LOWER(c.concept_name) LIKE "%congenital hypothyroidism%" THEN "congenital hypothyroidism"
ELSE "NOT A MATCH TO SOURCE STRING" END AS search_string
FROM
CHCO_DeID_Oct2018.concept c
WHERE c.domain_id = "Condition"
)
WHERE search_string != "NOT A MATCH TO SOURCE STRING"
ORDER BY vocabulary_id, OMOP_ID, search_string, CLINICAL_CODE;
-- Query 1: Search for specific patients using specific vocabulary codes
SELECT DISTINCT
CONCAT('"', CAST(c1.concept_id AS STRING), '"') AS OMOP_ID,
c1.concept_name AS OMOP_LABEL,
CONCAT('"', CAST(c.concept_code AS STRING), '"') AS CLINICAL_CODE,
CONCAT('"', CAST(c1.concept_code AS STRING), '"') AS SNOMED_CODE,
c.vocabulary_id AS VOCABULARY
FROM CHCO_DeID_Oct2018.concept c
JOIN CHCO_DeID_Oct2018.concept_relationship r ON r.concept_id_1 = c.concept_id
JOIN CHCO_DeID_Oct2018.concept c1 ON r.concept_id_2 = c1.concept_id
WHERE r.relationship_id IN ("Maps to")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ("277.0", "277.00", "E84", "E84.0", "E84.1", "E84.8", "E84.9")
AND c.vocabulary_id IN ("ICD9", "ICD9CM", "ICD10", "ICD10CM")
AND c.domain_id = "Condition"
AND c1.vocabulary_id = "SNOMED"
AND c1.domain_id = "Condition"
ORDER BY CLINICAL_CODE, OMOP_ID;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- Query 2: Search for all relevant ICD-9CM and ICD-10 codes
SELECT DISTINCT
search_string AS SEARCH_STRING,
CONCAT('"', concept_id, '"') AS OMOP_ID,
concept_name AS OMOP_LABEL,
CONCAT('"', concept_code, '"') AS CLINICAL_CODE,
vocabulary_id AS VOCABULARY
FROM
(
SELECT
DISTINCT c.concept_name,
CAST(c.concept_id AS STRING) AS concept_id,
c.concept_code,
c.domain_id,
c.vocabulary_id,
CASE
WHEN LOWER(c.concept_name) LIKE "%cystic fibrosis%" THEN "cystic fibrosis"
ELSE "NOT A MATCH TO SOURCE STRING" END AS search_string
FROM
CHCO_DeID_Oct2018.concept c
WHERE c.domain_id = "Condition"
)
WHERE search_string != "NOT A MATCH TO SOURCE STRING"
ORDER BY vocabulary_id, OMOP_ID, search_string, CLINICAL_CODE;
-- Query 1: Search for specific patients using specific vocabulary codes
SELECT DISTINCT
CONCAT('"', CAST(c1.concept_id AS STRING), '"') AS OMOP_ID,
c1.concept_name AS OMOP_LABEL,
CONCAT('"', CAST(c1.concept_code AS STRING), '"') AS SNOMED_CODE,
CONCAT('"', CAST(c.concept_code AS STRING), '"') AS CLINICAL_CODE,
c.vocabulary_id AS VOCABULARY
FROM CHCO_DeID_Oct2018.concept c
JOIN CHCO_DeID_Oct2018.concept_relationship r ON r.concept_id_1 = c.concept_id
JOIN CHCO_DeID_Oct2018.concept c1 ON r.concept_id_2 = c1.concept_id
WHERE r.relationship_id IN ("Maps to")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ("121230", "F391800", "387732009", "111507009", "121228", "G552.11", "195016002", "10059117", "129619006", "F391B00", "315608004", "193234005", "677C300", "758921000000101", "66731000119103", "677C200", "758911000000107", "137511000119103", "141683", "10013801", "F391000", "76670001", "267712004", "155095006", "138743", "359.1", "10019897", "F391.00", "193225000", "44292004", "F391z00", "193236007", "240050008", "C562928", "133622", "G71.0", "10028356", "3303", "F39B.00", "73297009", "193257004", "10028357", "240046001", "D020388", "F39z.00", "193261005", "240071003", "240048000", "240047005")
AND c.vocabulary_id IN ("CIEL", "Read", "SNOMED","SNOMED", "CIEL", "Read", "SNOMED", "MedDRA", "SNOMED", "Read", "SNOMED", "SNOMED", "Read", "SNOMED","SNOMED", "Read", "SNOMED", "SNOMED", "CIEL", "MedDRA","Read", "SNOMED", "SNOMED", "SNOMED", "CIEL", "ICD9CM", "MedDRA", "Read", "SNOMED", "SNOMED", "Read","SNOMED", "SNOMED", "MeSH", "CIEL","ICD10", "MedDRA", "OXMIS", "Read", "SNOMED", "SNOMED", "MedDRA","SNOMED", "MeSH", "Read", "SNOMED", "SNOMED", "SNOMED", "SNOMED")
AND c.domain_id = "Condition"
AND c1.vocabulary_id = "SNOMED"
AND c1.domain_id = "Condition"
ORDER BY SNOMED_CODE, VOCABULARY;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- Query 2: Search for all relevant ICD-9CM and ICD-10 codes
SELECT DISTINCT
search_string AS SEARCH_STRING,
CONCAT('"', concept_id, '"') AS OMOP_ID,
concept_name AS OMOP_LABEL,
CONCAT('"', concept_code, '"') AS CLINICAL_CODE,
vocabulary_id AS VOCABULARY
FROM
(
SELECT
DISTINCT c.concept_name,
CAST(c.concept_id AS STRING) AS concept_id,
c.concept_code,
c.domain_id,
c.vocabulary_id,
CASE
WHEN LOWER(c.concept_name) LIKE "%muscular dystrophy%" THEN "muscular dystrophy"
WHEN LOWER(c.concept_name) LIKE "%duchenne%" THEN "duchenne muscular dystrophy"
WHEN LOWER(c.concept_name) LIKE "%becker%" THEN "becker muscular dystrophy"
WHEN LOWER(c.concept_name) LIKE "%severe%dystrophinopathy%" THEN "severe dystrophinopathy"
ELSE "NOT A MATCH TO SOURCE STRING" END AS search_string
FROM
CHCO_DeID_Oct2018.concept c
WHERE c.domain_id = "Condition"
)
WHERE search_string != "NOT A MATCH TO SOURCE STRING"
ORDER BY vocabulary_id, OMOP_ID, search_string, CLINICAL_CODE;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
- Query 2: Get age in yrs at first diagnosis
SELECT DISTINCT co.person_id,'muscular dystrophy' AS cond, 3 AS cond_num, 'MD' AS cond_short, p.birth_datetime,
min(co.condition_start_datetime) AS cond_start,
TIMESTAMP_DIFF(min(co.condition_start_datetime), p.birth_datetime, DAY)/365.25 AS age_at_first_dx
FROM CHCO_DeID_Oct2018.condition_occurrence co
JOIN CHCO_DeID_Oct2018.person p
ON p.person_id = co.person_id
WHERE co.condition_concept_id IN (4296473, 4154093, 4308668)
GROUP BY co.person_id, cond, cond_num, cond_short, p.birth_datetime
ORDER BY co.person_id
-- Query 3: Final code set to identify MD patients (Nov2019 Update)
SELECT DISTINCT
CONCAT('"', CAST(c1.concept_id AS STRING), '"') AS OMOP_ID,
c1.concept_name AS OMOP_LABEL,
CONCAT('"', CAST(c1.concept_code AS STRING), '"') AS SNOMED_CODE,
CONCAT('"', CAST(c.concept_code AS STRING), '"') AS CLINICAL_CODE,
c.vocabulary_id AS VOCABULARY
FROM CHCO_DeID_Oct2018.concept c
JOIN CHCO_DeID_Oct2018.concept_relationship r ON r.concept_id_1 = c.concept_id
JOIN CHCO_DeID_Oct2018.concept c1 ON r.concept_id_2 = c1.concept_id
WHERE r.relationship_id IN ("Maps to")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ("121230", "F391800", "387732009", "111507009", "121228", "G552.11", "195016002", "10059117", "129619006", "F391B00", "315608004", "193234005", "677C300", "758921000000101", "66731000119103", "677C200", "758911000000107", "137511000119103", "141683", "10013801", "F391000", "76670001", "267712004", "155095006", "138743", "359.1", "10019897", "F391.00", "193225000", "44292004", "F391z00", "193236007", "240050008", "C562928", "133622", "G71.0", "10028356", "3303", "F39B.00", "73297009", "193257004", "10028357", "240046001", "D020388", "F39z.00", "193261005", "240071003", "240048000", "240047005")
AND c.vocabulary_id IN ("CIEL", "Read", "SNOMED","SNOMED", "CIEL", "Read", "SNOMED", "MedDRA", "SNOMED", "Read", "SNOMED", "SNOMED", "Read", "SNOMED","SNOMED", "Read", "SNOMED", "SNOMED", "CIEL", "MedDRA","Read", "SNOMED", "SNOMED", "SNOMED", "CIEL", "ICD9CM", "MedDRA", "Read", "SNOMED", "SNOMED", "Read","SNOMED", "SNOMED", "MeSH", "CIEL","ICD10", "MedDRA", "OXMIS", "Read", "SNOMED", "SNOMED", "MedDRA","SNOMED", "MeSH", "Read", "SNOMED", "SNOMED", "SNOMED", "SNOMED")
# adding exclusion criteria from MD clinic meeting
AND c1.concept_id NOT IN (80399, 4247802, 72618)
AND c.domain_id = "Condition"
AND c1.vocabulary_id = "SNOMED"
AND c1.domain_id = "Condition"
ORDER BY SNOMED_CODE, VOCABULARY;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- Look at counts of exclusion codes for eligible patients
SELECT DISTINCT co.person_id,
IFNULL(sq1.HPMD_COUNT, 0) AS HPMD_COUNT,
IFNULL(sq2.MD_COUNT,0) AS MD_COUNT,
IFNULL(sq3.DSM_COUNT,0) AS DSM_COUNT,
IFNULL(sq4.BMD_COUNT, 0) AS BMD_COUNT,
IFNULL(sq5.CDMD_COUNT,0) AS CDMD_COUNT,
IFNULL(sq6.DMD_COUNT,0) AS DMD_COUNT
FROM CHCO_DeID_Oct2018.condition_occurrence co
LEFT JOIN (SELECT person_id, COUNT(condition_concept_id) AS DMD_COUNT
FROM CHCO_DeID_Oct2018.condition_occurrence
WHERE condition_concept_id = 4296473
GROUP BY person_id
ORDER BY person_id) sq6
ON co.person_id = sq6.person_id
LEFT JOIN (SELECT person_id, COUNT(condition_concept_id) AS CDMD_COUNT
FROM CHCO_DeID_Oct2018.condition_occurrence
WHERE condition_concept_id = 4154093
GROUP BY person_id
ORDER BY person_id) sq5
ON co.person_id = sq5.person_id
LEFT JOIN (SELECT person_id, COUNT(condition_concept_id) AS BMD_COUNT
FROM CHCO_DeID_Oct2018.condition_occurrence
WHERE condition_concept_id = 4308668
GROUP BY person_id
ORDER BY person_id) sq4
ON co.person_id = sq4.person_id
LEFT JOIN (SELECT person_id, COUNT(condition_concept_id) AS DSM_COUNT
FROM CHCO_DeID_Oct2018.condition_occurrence
WHERE condition_concept_id = 72618
GROUP BY person_id
ORDER BY person_id) sq3
ON co.person_id = sq3.person_id
LEFT JOIN (SELECT person_id, COUNT(condition_concept_id) AS MD_COUNT
FROM CHCO_DeID_Oct2018.condition_occurrence
WHERE condition_concept_id = 4247802
GROUP BY person_id
ORDER BY person_id) sq2
ON co.person_id = sq2.person_id
LEFT JOIN (SELECT person_id, COUNT(condition_concept_id) AS HPMD_COUNT
FROM CHCO_DeID_Oct2018.condition_occurrence
WHERE condition_concept_id = 80399
GROUP BY person_id
ORDER BY person_id) sq1
ON co.person_id = sq1.person_id
WHERE co.person_id IN (SELECT DISTINCT person_id
FROM CHCO_DeID_Oct2018.condition_occurrence
WHERE condition_concept_id IN (4296473, 4154093, 4308668, 80399, 4247802, 72618))
-- get MD cohort
SELECT DISTINCT co.person_id,
'muscular dystrophy' AS cond,
p.birth_datetime,
condition_concept_id,
min(co.condition_start_datetime) AS cond_start,
TIMESTAMP_DIFF(min(co.condition_start_datetime), p.birth_datetime, DAY)/325.25 AS age_at_first_dx
FROM CHCO_DeID_Oct2018.condition_occurrence co
JOIN CHCO_DeID_Oct2018.person p
ON p.person_id = co.person_id
WHERE co.condition_concept_id IN (4296473, 4154093, 4308668)
GROUP BY co.person_id, cond, p.birth_datetime, condition_concept_id
ORDER BY co.person_id
-- get counts of visits
SELECT DISTINCT v.person_id,
c1.concept_name AS VISIT_TYPE,
c2.concept_name AS SPEC_CONCEPT,
COUNT(v.visit_occurrence_id) AS COUNT
FROM CHCO_DeID_Oct2018.visit_occurrence v
JOIN CHCO_DeID_Oct2018.concept c1
ON c1.concept_id = v.visit_concept_id
JOIN CHCO_DeID_Oct2018.care_site cs
ON cs.care_site_id = v.care_site_id
JOIN CHCO_DeID_Oct2018.concept c2
ON c2.concept_id = cs.specialty_concept_id
WHERE v.person_id IN (SELECT DISTINCT co.person_id
FROM CHCO_DeID_Oct2018.condition_occurrence co
WHERE co.condition_concept_id IN (4296473, 4154093, 4308668))
AND v.visit_concept_id != 0
GROUP BY v.person_id, c1.concept_name, c2.concept_name
ORDER BY v.person_id ASC
-- Most frequent conditions
SELECT DISTINCT co.condition_concept_id,
co.condition_source_value,
c3.concept_name AS CODE,
c1.concept_name AS VISIT_TYPE,
COUNT(co.condition_concept_id) AS COUNT
FROM CHCO_DeID_Oct2018.condition_occurrence co
JOIN CHCO_DeID_Oct2018.visit_occurrence v
ON v.visit_occurrence_id = co.visit_occurrence_id
JOIN CHCO_DeID_Oct2018.concept c1
ON c1.concept_id = v.visit_concept_id
JOIN CHCO_DeID_Oct2018.care_site cs
ON cs.care_site_id = v.care_site_id
JOIN CHCO_DeID_Oct2018.concept c3
ON c3.concept_id = co.condition_concept_id
WHERE co.person_id IN (SELECT DISTINCT co.person_id
FROM CHCO_DeID_Oct2018.condition_occurrence co
WHERE co.condition_concept_id IN (4296473, 4154093, 4308668))
AND co.condition_concept_id != 0
GROUP BY c1.concept_name, co.condition_concept_id, c3.concept_name, co.condition_source_value
ORDER BY c3.concept_name, c1.concept_name DESC
-- Most frequent drugs
SELECT DISTINCT d.drug_concept_id,
c3.concept_name AS CODE,
d.drug_source_value,
d.frequency,
c1.concept_name AS VISIT_TYPE,
c4.concept_name,
COUNT(d.drug_concept_id) AS COUNT
FROM CHCO_DeID_Oct2018.drug_exposure d
JOIN CHCO_DeID_Oct2018.visit_occurrence v
ON v.visit_occurrence_id = d.visit_occurrence_id
JOIN CHCO_DeID_Oct2018.concept c1
ON c1.concept_id = v.visit_concept_id
JOIN CHCO_DeID_Oct2018.care_site cs
ON cs.care_site_id = v.care_site_id
JOIN CHCO_DeID_Oct2018.concept c3
ON c3.concept_id = d.drug_concept_id
JOIN CHCO_DeID_Oct2018.concept c4
ON d.drug_type_concept_id = c4.concept_id
WHERE d.person_id IN (SELECT DISTINCT co.person_id
FROM CHCO_DeID_Oct2018.condition_occurrence co
WHERE co.condition_concept_id IN (4296473, 4154093, 4308668))
AND d.drug_concept_id != 0
GROUP BY c1.concept_name, d.drug_concept_id, c3.concept_name, c3.concept_class_id, c4.concept_name, drug_source_value, d.frequency
ORDER BY c3.concept_name, c1.concept_name DESC
-- Most frequent measurements
SELECT DISTINCT m.measurement_concept_id,
c3.concept_name AS CODE,
c1.concept_name AS VISIT_TYPE,
COUNT(m.measurement_concept_id) AS COUNT
FROM CHCO_DeID_Oct2018.measurement m
JOIN CHCO_DeID_Oct2018.visit_occurrence v
ON v.visit_occurrence_id = m.visit_occurrence_id
JOIN CHCO_DeID_Oct2018.concept c1
ON c1.concept_id = v.visit_concept_id
JOIN CHCO_DeID_Oct2018.care_site cs
ON cs.care_site_id = v.care_site_id
JOIN CHCO_DeID_Oct2018.concept c3
ON c3.concept_id = m.measurement_concept_id
WHERE m.person_id IN (SELECT DISTINCT co.person_id
FROM CHCO_DeID_Oct2018.condition_occurrence co
WHERE co.condition_concept_id IN (4296473, 4154093, 4308668))
AND m.measurement_concept_id != 0
GROUP BY c1.concept_name, m.measurement_concept_id, c3.concept_name
ORDER BY c3.concept_name, c1.concept_name DESC
-- Most frequent procedures
SELECT DISTINCT p.procedure_concept_id,
c3.concept_name AS CODE,
c1.concept_name AS VISIT_TYPE,
c3.domain_id,
COUNT(p.procedure_concept_id) AS COUNT
FROM CHCO_DeID_Oct2018.procedure_occurrence p
JOIN CHCO_DeID_Oct2018.visit_occurrence v
ON v.visit_occurrence_id = p.visit_occurrence_id
JOIN CHCO_DeID_Oct2018.concept c1
ON c1.concept_id = v.visit_concept_id
JOIN CHCO_DeID_Oct2018.concept c3
ON c3.concept_id = p.procedure_concept_id
WHERE p.person_id IN (SELECT DISTINCT co.person_id
FROM CHCO_DeID_Oct2018.condition_occurrence co
WHERE co.condition_concept_id IN (4296473, 4154093, 4308668))
AND p.procedure_concept_id != 0
GROUP BY c1.concept_name, p.procedure_concept_id, c3.concept_name, c3.domain_id
ORDER BY c3.concept_name, c1.concept_name DESC;
-- RANDOM PATIENTS: Query is designed to retrieve 15,000 random patients having >9 visits
SELECT
v.person_id AS patient_id,
count(v.visit_occurrence_id) AS visit_count,
p_dat.cond As disease_group_label,
p_dat.cond_num As disease_group_num,
p_dat.cond_short As disease_group_abbreviation,
p_dat.DOB AS date_of_birth,
p_dat.cond_start AS earliest_dx_date,
p_dat.age_at_dx AS age_at_earliest_dx
FROM
CHCO_DeID_Oct2018.visit_occurrence v
RIGHT JOIN (
SELECT
co.person_id,
'Control' AS cond,
99 AS cond_num,
'Control' AS cond_short,
min(co.condition_start_date) AS cond_start,
DATE_DIFF(
min(co.condition_start_date),
CAST(
DATE(p.birth_datetime) AS DATE
),
DAY
)/ 325.25 AS age_at_dx,
p.birth_datetime AS DOB
FROM
CHCO_DeID_Oct2018.condition_occurrence co
JOIN CHCO_DeID_Oct2018.person p ON p.person_id = co.person_id
WHERE
p.person_id NOT IN (
SELECT
patient_id
FROM
CHCO_DeID_Oct2018.Med2Mech_RareDisease_CasePatients
)
AND p.person_id IS NOT NULL
GROUP BY
co.person_id,
cond,
cond_num,
cond_short,
p.birth_datetime
ORDER BY
co.person_id
) AS p_dat ON v.person_id = p_dat.person_id
WHERE
v.visit_occurrence_id IS NOT NULL
GROUP BY
v.person_id,
p_dat.cond,
p_dat.cond_num,
p_dat.cond_short,
date_of_birth,
p_dat.cond_start,
p_dat.age_at_dx
HAVING
visit_count > 9
ORDER BY
RAND()
LIMIT
15000;
-- Query 1: Search for specific patients using specific vocabulary codes
SELECT DISTINCT
CONCAT('"', CAST(c1.concept_id AS STRING), '"') AS OMOP_ID,
c1.concept_name AS OMOP_LABEL,
CONCAT('"', CAST(c.concept_code AS STRING), '"') AS CLINICAL_CODE,
CONCAT('"', CAST(c1.concept_code AS STRING), '"') AS SNOMED_CODE,
c.vocabulary_id AS VOCABULARY
FROM CHCO_DeID_Oct2018.concept c
JOIN CHCO_DeID_Oct2018.concept_relationship r ON r.concept_id_1 = c.concept_id
JOIN CHCO_DeID_Oct2018.concept c1 ON r.concept_id_2 = c1.concept_id
WHERE r.relationship_id IN ("Maps to")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ("E70.0", "270.1")
AND c.vocabulary_id IN ("ICD9", "ICD9CM", "ICD10", "ICD10CM")
AND c.domain_id = "Condition"
AND c1.vocabulary_id = "SNOMED"
AND c1.domain_id = "Condition"
ORDER BY CLINICAL_CODE, OMOP_ID;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- Query 2: Search for all relevant ICD-9CM and ICD-10 codes
SELECT DISTINCT
search_string AS SEARCH_STRING,
CONCAT('"', concept_id, '"') AS OMOP_ID,
concept_name AS OMOP_LABEL,
CONCAT('"', concept_code, '"') AS CLINICAL_CODE,
vocabulary_id AS VOCABULARY
FROM
(
SELECT
DISTINCT c.concept_name,
CAST(c.concept_id AS STRING) AS concept_id,
c.concept_code,
c.domain_id,
c.vocabulary_id,
CASE
WHEN LOWER(c.concept_name) LIKE "%phenylketonuria%" THEN "phenylketonuria"
ELSE "NOT A MATCH TO SOURCE STRING" END AS search_string
FROM
CHCO_DeID_Oct2018.concept c
WHERE c.domain_id = "Condition"
)
WHERE search_string != "NOT A MATCH TO SOURCE STRING"
ORDER BY vocabulary_id, OMOP_ID, search_string, CLINICAL_CODE;
-- RARE DISEASE CASES: Query is designed to retrieve test-cases having specific concept codes indicative of CF, DMD, and SC as well as having >9 visits
SELECT
v.person_id AS patient_id,
count(v.visit_occurrence_id) AS visit_count,
p_dat.cond As disease_group_label,
p_dat.cond_num As disease_group_num,
p_dat.cond_short As disease_group_abbreviation,
p_dat.birth_datetime as date_of_birth,
p_dat.cond_start AS earliest_dx_date,
p_dat.age_at_dx AS age_at_earliest_dx
FROM
CHCO_DeID_Oct2018.visit_occurrence v
JOIN CHCO_DeID_Oct2018.person d ON d.person_id = v.person_id
RIGHT JOIN (
SELECT
*
FROM
(
SELECT
co.person_id,
'Phenylketonuria' AS cond,
1 AS cond_num,
'PKU' AS cond_short,
p.birth_datetime,
min(co.condition_start_datetime) AS cond_start,
TIMESTAMP_DIFF(
min(co.condition_start_datetime),
p.birth_datetime,
DAY
)/ 325.25 AS age_at_dx
FROM
CHCO_DeID_Oct2018.condition_occurrence co
JOIN CHCO_DeID_Oct2018.concept c1 ON c1.concept_id = co.condition_concept_id
JOIN CHCO_DeID_Oct2018.person p ON p.person_id = co.person_id
WHERE
c1.vocabulary_id = 'SNOMED'
AND c1.domain_id = 'Condition'
AND c1.concept_id IN (
432872, 45576461, 35207031, 45576462,
35207032, 44821794
)
GROUP BY
co.person_id,
cond,
cond_num,
cond_short,
p.birth_datetime
ORDER BY
co.person_id
)
UNION ALL
(
SELECT
co.person_id,
'Congenital Hypothyroidism' AS cond,
2 AS cond_num,
'CAH' AS cond_short,
p.birth_datetime,
min(co.condition_start_datetime) AS cond_start,
TIMESTAMP_DIFF(
min(co.condition_start_datetime),
p.birth_datetime,
DAY
)/ 325.25 AS age_at_dx
FROM
CHCO_DeID_Oct2018.condition_occurrence co
JOIN CHCO_DeID_Oct2018.concept c1 ON c1.concept_id = co.condition_concept_id
JOIN CHCO_DeID_Oct2018.person p ON p.person_id = co.person_id
WHERE
c1.vocabulary_id = 'SNOMED'
AND c1.domain_id = 'Condition'
AND c1.concept_id IN (
44829873, 133728, 35206853, 45576424,
45524819
)
GROUP BY
co.person_id,
cond,
cond_num,
cond_short,
p.birth_datetime
ORDER BY
co.person_id
)
UNION ALL
(
SELECT
co.person_id,
'Sickle Cell Disease' AS cond,
3 AS cond_num,
'SCD' AS cond_short,
p.birth_datetime,
min(co.condition_start_datetime) AS cond_start,
TIMESTAMP_DIFF(
min(co.condition_start_datetime),
p.birth_datetime,
DAY
)/ 325.25 AS age_at_dx
FROM
CHCO_DeID_Oct2018.condition_occurrence co
JOIN CHCO_DeID_Oct2018.concept c1 ON c1.concept_id = co.condition_concept_id
JOIN CHCO_DeID_Oct2018.person p ON p.person_id = co.person_id
WHERE
c1.vocabulary_id = 'SNOMED'
AND c1.domain_id = 'Condition'
AND c1.concept_id IN (
45566700, 22281, 1567845, 45571634,
45542709, 35206724, 44831065, 1567846
)
GROUP BY
co.person_id,
cond,
cond_num,
cond_short,
p.birth_datetime
ORDER BY
co.person_id
)
UNION ALL
(
SELECT
co.person_id,
'Cystic Fibrosis' AS cond,
4 AS cond_num,
'CF' AS cond_short,
p.birth_datetime,
min(co.condition_start_datetime) AS cond_start,
TIMESTAMP_DIFF(
min(co.condition_start_datetime),
p.birth_datetime,
DAY
)/ 325.25 AS age_at_dx
FROM
CHCO_DeID_Oct2018.condition_occurrence co
JOIN CHCO_DeID_Oct2018.concept c1 ON c1.concept_id = co.condition_concept_id
JOIN CHCO_DeID_Oct2018.person p ON p.person_id = co.person_id
WHERE
c1.vocabulary_id = 'SNOMED'
AND c1.domain_id = 'Condition'
AND c1.concept_id IN (
441267, 45605436, 1568075, 45605437,
35207083, 45605435, 35207082, 44821799,
45576477, 35207084
)
GROUP BY
co.person_id,
cond,
cond_num,
cond_short,
p.birth_datetime
ORDER BY
co.person_id
)
) AS p_dat ON v.person_id = p_dat.person_id
GROUP BY
v.person_id,
p_dat.cond,
p_dat.cond_num,
p_dat.cond_short,
p_dat.birth_datetime,
p_dat.cond_start,
p_dat.age_at_dx
HAVING
visit_count > 9
ORDER BY
visit_count;
-- Query 1: Search for specific patients using specific vocabulary codes
SELECT DISTINCT
CONCAT('"', CAST(c1.concept_id AS STRING), '"') AS OMOP_ID,
c1.concept_name AS OMOP_LABEL,
CONCAT('"', CAST(c.concept_code AS STRING), '"') AS CLINICAL_CODE,
CONCAT('"', CAST(c1.concept_code AS STRING), '"') AS SNOMED_CODE,
c.vocabulary_id AS VOCABULARY
FROM CHCO_DeID_Oct2018.concept c
JOIN CHCO_DeID_Oct2018.concept_relationship r ON r.concept_id_1 = c.concept_id
JOIN CHCO_DeID_Oct2018.concept c1 ON r.concept_id_2 = c1.concept_id
WHERE r.relationship_id IN ("Maps to")
AND c1.standard_concept in ("S", "C")
AND c.concept_code IN ("D57.0", "D57.1", "D57.2", "282.6", "282.60", "282.61", "282.68")
AND c.vocabulary_id IN ("ICD9", "ICD9CM", "ICD10", "ICD10CM")
AND c.domain_id = "Condition"
AND c1.vocabulary_id = "SNOMED"
AND c1.domain_id = "Condition"
ORDER BY CLINICAL_CODE, OMOP_ID;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- Query 2: Search for all relevant ICD-9CM and ICD-10 codes
SELECT DISTINCT
search_string AS SEARCH_STRING,
CONCAT('"', concept_id, '"') AS OMOP_ID,
concept_name AS OMOP_LABEL,
CONCAT('"', concept_code, '"') AS CLINICAL_CODE,
vocabulary_id AS VOCABULARY
FROM
(
SELECT
DISTINCT c.concept_name,
CAST(c.concept_id AS STRING) AS concept_id,
c.concept_code,
c.domain_id,
c.vocabulary_id,
CASE
WHEN LOWER(c.concept_name) LIKE "%sickle%cell disease%" THEN "sickle-cell disease"
WHEN LOWER(c.concept_name) LIKE "%hb%ss%disease%" THEN "Hb SS disease"
ELSE "NOT A MATCH TO SOURCE STRING" END AS search_string
FROM
CHCO_DeID_Oct2018.concept c
WHERE c.domain_id = "Condition"
)
WHERE search_string != "NOT A MATCH TO SOURCE STRING"
ORDER BY vocabulary_id, OMOP_ID, search_string, CLINICAL_CODE;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment