Last active
October 30, 2020 02:20
-
-
Save callahantiff/7b84c1bc063ad162bf5bdf5e578d402f to your computer and use it in GitHub Desktop.
OMOP2OBO - OMOP Concept Queries: used to pull the OMOP concepts for input into the OBO mapping pipeline
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
DISTINCT r.relationship_id, | |
c1.concept_id AS SOURCE_CONCEPT_ID, | |
c1.concept_name AS SOURCE_CONCEPT_LABEL, | |
c2.concept_id AS TARGET_CONCEPT_ID, | |
c2.concept_name AS TARGET_CONCEPT_LABEL, | |
FROM | |
sandbox-omop.oct_2020.concept_relationship r | |
JOIN sandbox-omop.oct_2020.concept c1 ON c1.concept_id = r.concept_id_1 | |
JOIN sandbox-omop.oct_2020.concept c2 ON c2.concept_id = r.concept_id_2 | |
WHERE | |
r.concept_id_1 IN (SELECT concept_id FROM sandbox-tc.CHCO_DeID_Oct2018.OMOP2OBO_Conditions_Concepts_Merged | |
UNION DISTINCT | |
SELECT ingredient_concept_id FROM sandbox-tc.CHCO_DeID_Oct2018.OMOP2OBO_Medications_Concepts_Merged | |
UNION DISTINCT | |
SELECT concept_id FROM sandbox-tc.CHCO_DeID_Oct2018.OMOP2OBO_Measurements_Concepts_Merged) | |
AND r.relationship_id IN ("Concept replaced by", "Maps to", "Concept same_as from", "Concept poss_eq from", "Concept was_a from", "Is a") | |
AND (r.valid_start_date > '2018-06-26' AND r.valid_start_date < '2020-10-17') | |
ORDER BY r.relationship_id; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH | |
condition_concepts | |
AS (SELECT | |
c1.standard_concept AS STANDARD_CONCEPT, | |
c.condition_concept_id AS CONCEPT_ID, | |
c1.concept_name AS CONCEPT_LABEL, | |
STRING_AGG(DISTINCT(CONCAT(LOWER(v.vocabulary_id), ":", c1.concept_code)), " | ") AS CONCEPT_SOURCE_CODE, | |
STRING_AGG(DISTINCT(c1.concept_name), " | ") AS CONCEPT_SOURCE_LABEL, | |
STRING_AGG(DISTINCT(v.vocabulary_id), " | ") AS CONCEPT_VOCAB, | |
STRING_AGG(DISTINCT(v.vocabulary_version), " | ") AS CONCEPT_VOCAB_VERSION | |
FROM | |
CHCO_DeID_Oct2018.condition_occurrence c | |
JOIN CHCO_DeID_Oct2018.concept c1 ON c.condition_concept_id = c1.concept_id | |
JOIN CHCO_DeID_Oct2018.vocabulary v ON v.vocabulary_id = c1.vocabulary_id | |
WHERE | |
c1.concept_name != "No matching concept" | |
AND c1.domain_id = "Condition" | |
GROUP BY CONCEPT_ID, CONCEPT_LABEL, STANDARD_CONCEPT), | |
condition_ancestors | |
AS (SELECT | |
ca.descendant_concept_id AS CONCEPT_ID, | |
STRING_AGG(DISTINCT(CAST(c1.concept_id as STRING)), " | ") AS ANCESTOR_CONCEPT_ID, | |
STRING_AGG(DISTINCT(CONCAT(LOWER(c1.vocabulary_id), ":", c1.concept_code)), " | ") AS ANCESTOR_SOURCE_CODE, | |
STRING_AGG(DISTINCT(c1.concept_name), " | ") AS ANCESTOR_LABEL, | |
STRING_AGG(DISTINCT(c1.vocabulary_id), " | ") AS ANCESTOR_VOCAB, | |
STRING_AGG(DISTINCT(v.vocabulary_version), " | ") AS ANCESTOR_VOCAB_VERSION | |
FROM | |
CHCO_DeID_Oct2018.concept_ancestor ca | |
JOIN CHCO_DeID_Oct2018.concept c1 ON ca.ancestor_concept_id = c1.concept_id | |
JOIN CHCO_DeID_Oct2018.vocabulary v ON c1.vocabulary_id = v.vocabulary_id | |
WHERE | |
ca.descendant_concept_id IN (SELECT CONCEPT_ID FROM condition_concepts) | |
AND c1.concept_name != "No matching concept" | |
AND c1.concept_id IS NOT NULL | |
AND c1.domain_id = "Condition" | |
GROUP BY CONCEPT_ID), | |
condition_synonyms | |
AS (SELECT | |
s.concept_id AS CONCEPT_ID, | |
STRING_AGG(DISTINCT(s.concept_synonym_name), " | ") AS CONCEPT_SYNONYM | |
FROM CHCO_DeID_Oct2018.concept_synonym s | |
WHERE s.concept_id in (SELECT CONCEPT_ID FROM condition_concepts) | |
GROUP BY CONCEPT_ID) | |
SELECT | |
'Concept Used In Practice' AS CONCEPT_TYPE, | |
c.CONCEPT_ID, | |
c.STANDARD_CONCEPT, | |
c.CONCEPT_LABEL, | |
c.CONCEPT_SOURCE_CODE, | |
c.CONCEPT_SOURCE_LABEL, | |
c.CONCEPT_VOCAB, | |
c.CONCEPT_VOCAB_VERSION, | |
s.CONCEPT_SYNONYM, | |
a.ANCESTOR_CONCEPT_ID, | |
a.ANCESTOR_SOURCE_CODE, | |
a.ANCESTOR_LABEL, | |
a.ANCESTOR_VOCAB, | |
a.ANCESTOR_VOCAB_VERSION | |
FROM condition_concepts c | |
FULL JOIN condition_ancestors a ON c.CONCEPT_ID = a.CONCEPT_ID | |
FULL JOIN condition_synonyms s ON c.CONCEPT_ID = s.CONCEPT_ID; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH | |
condition_concepts | |
AS (SELECT | |
c1.standard_concept AS STANDARD_CONCEPT, | |
c1.concept_id AS CONCEPT_ID, | |
c1.concept_name AS CONCEPT_LABEL, | |
STRING_AGG(DISTINCT(CONCAT(LOWER(v.vocabulary_id), ":", c1.concept_code)), " | ") AS CONCEPT_SOURCE_CODE, | |
STRING_AGG(DISTINCT(c1.concept_name), " | ") AS CONCEPT_SOURCE_LABEL, | |
STRING_AGG(DISTINCT(v.vocabulary_id), " | ") AS CONCEPT_VOCAB, | |
STRING_AGG(DISTINCT(v.vocabulary_version), " | ") AS CONCEPT_VOCAB_VERSION | |
FROM | |
CHCO_DeID_Oct2018.concept c1 | |
JOIN CHCO_DeID_Oct2018.vocabulary v ON v.vocabulary_id = c1.vocabulary_id | |
WHERE | |
c1.concept_name != "No matching concept" | |
AND c1.domain_id = "Condition" | |
AND c1.standard_concept = "S" | |
AND c1.vocabulary_id = "SNOMED" | |
GROUP BY CONCEPT_ID, CONCEPT_LABEL, STANDARD_CONCEPT), | |
condition_ancestors | |
AS (SELECT | |
ca.descendant_concept_id AS CONCEPT_ID, | |
STRING_AGG(DISTINCT(CAST(c1.concept_id as STRING)), " | ") AS ANCESTOR_CONCEPT_ID, | |
STRING_AGG(DISTINCT(CONCAT(LOWER(c1.vocabulary_id), ":", c1.concept_code)), " | ") AS ANCESTOR_SOURCE_CODE, | |
STRING_AGG(DISTINCT(c1.concept_name), " | ") AS ANCESTOR_LABEL, | |
STRING_AGG(DISTINCT(c1.vocabulary_id), " | ") AS ANCESTOR_VOCAB, | |
STRING_AGG(DISTINCT(v.vocabulary_version), " | ") AS ANCESTOR_VOCAB_VERSION | |
FROM | |
CHCO_DeID_Oct2018.concept_ancestor ca | |
JOIN CHCO_DeID_Oct2018.concept c1 ON ca.ancestor_concept_id = c1.concept_id | |
JOIN CHCO_DeID_Oct2018.vocabulary v ON c1.vocabulary_id = v.vocabulary_id | |
WHERE | |
ca.descendant_concept_id IN (SELECT CONCEPT_ID FROM condition_concepts) | |
AND c1.concept_name != "No matching concept" | |
AND c1.concept_id IS NOT NULL | |
AND c1.domain_id = "Condition" | |
GROUP BY CONCEPT_ID), | |
condition_synonyms | |
AS (SELECT | |
s.concept_id AS CONCEPT_ID, | |
STRING_AGG(DISTINCT(s.concept_synonym_name), " | ") AS CONCEPT_SYNONYM | |
FROM CHCO_DeID_Oct2018.concept_synonym s | |
WHERE s.concept_id in (SELECT CONCEPT_ID FROM condition_concepts) | |
GROUP BY CONCEPT_ID) | |
SELECT | |
'Standard Concepts Not Used In Practice' AS CONCEPT_TYPE, | |
c.CONCEPT_ID, | |
c.STANDARD_CONCEPT, | |
c.CONCEPT_LABEL, | |
c.CONCEPT_SOURCE_CODE, | |
c.CONCEPT_SOURCE_LABEL, | |
c.CONCEPT_VOCAB, | |
c.CONCEPT_VOCAB_VERSION, | |
s.CONCEPT_SYNONYM, | |
a.ANCESTOR_CONCEPT_ID, | |
a.ANCESTOR_SOURCE_CODE, | |
a.ANCESTOR_LABEL, | |
a.ANCESTOR_VOCAB, | |
a.ANCESTOR_VOCAB_VERSION | |
FROM condition_concepts c | |
FULL JOIN condition_ancestors a ON c.CONCEPT_ID = a.CONCEPT_ID | |
FULL JOIN condition_synonyms s ON c.CONCEPT_ID = s.CONCEPT_ID | |
WHERE | |
c.CONCEPT_ID NOT IN (SELECT DISTINCT CONCEPT_ID FROM CHCO_DeID_Oct2018.OMOP2OBO_Conditions_Concepts_Used_In_Practice); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH | |
drug_concepts | |
AS (SELECT | |
c.standard_concept AS STANDARD_CONCEPT, | |
d.drug_concept_id AS CONCEPT_ID, | |
CONCAT(LOWER(c.vocabulary_id), ":", c.concept_code) AS CONCEPT_SOURCE_CODE, | |
c.concept_name AS CONCEPT_LABEL, | |
c.vocabulary_id AS CONCEPT_VOCAB, | |
v.vocabulary_version AS CONCEPT_VOCAB_VERSION | |
FROM | |
CHCO_DeID_Oct2018.drug_exposure d | |
JOIN CHCO_DeID_Oct2018.concept c ON d.drug_concept_id = c.concept_id | |
JOIN CHCO_DeID_Oct2018.vocabulary v ON c.vocabulary_id = v.vocabulary_id | |
WHERE | |
c.concept_name != "No matching concept" | |
AND c.domain_id = "Drug" | |
GROUP BY CONCEPT_ID, CONCEPT_SOURCE_CODE, CONCEPT_LABEL, CONCEPT_VOCAB, CONCEPT_VOCAB_VERSION, STANDARD_CONCEPT), | |
drug_ancestors | |
AS (SELECT | |
ca.descendant_concept_id AS CONCEPT_ID, | |
STRING_AGG(DISTINCT(CAST(c1.concept_id as STRING)), " | ") AS ANCESTOR_CONCEPT_ID, | |
STRING_AGG(DISTINCT(CONCAT(LOWER(c1.vocabulary_id), ":", c1.concept_code)), " | ") AS ANCESTOR_SOURCE_CODE, | |
STRING_AGG(DISTINCT(c1.concept_name), " | ") AS ANCESTOR_LABEL, | |
STRING_AGG(DISTINCT(c1.vocabulary_id), " | ") AS ANCESTOR_VOCAB, | |
STRING_AGG(DISTINCT(v.vocabulary_version), " | ") AS ANCESTOR_VOCAB_VERSION | |
FROM | |
CHCO_DeID_Oct2018.concept_ancestor ca | |
JOIN CHCO_DeID_Oct2018.concept c1 ON ca.ancestor_concept_id = c1.concept_id | |
JOIN CHCO_DeID_Oct2018.vocabulary v ON c1.vocabulary_id = v.vocabulary_id | |
WHERE | |
ca.descendant_concept_id IN (SELECT CONCEPT_ID FROM drug_concepts) | |
AND c1.concept_name != "No matching concept" | |
AND c1.concept_id IS NOT NULL | |
AND c1.domain_id = "Drug" | |
GROUP BY CONCEPT_ID), | |
drug_synonyms | |
AS (SELECT | |
s.concept_id AS CONCEPT_ID, | |
STRING_AGG(DISTINCT(s.concept_synonym_name), " | ") AS CONCEPT_SYNONYM | |
FROM CHCO_DeID_Oct2018.concept_synonym s | |
WHERE s.concept_id in (SELECT CONCEPT_ID FROM drug_concepts) | |
GROUP BY CONCEPT_ID), | |
ingredient_concepts | |
AS (SELECT | |
c1.standard_concept AS INGREDIENT_STANDARD_CONCEPT, | |
ca.descendant_concept_id AS CONCEPT_ID, | |
c1.concept_id AS INGREDIENT_CONCEPT_ID, | |
CONCAT(LOWER(c1.vocabulary_id), ":", c1.concept_code) AS INGREDIENT_SOURCE_CODE, | |
c1.concept_name AS INGREDIENT_LABEL, | |
c1.vocabulary_id AS INGREDIENT_VOCAB, | |
v.vocabulary_version AS INGREDIENT_VOCAB_VERSION | |
FROM | |
CHCO_DeID_Oct2018.concept_ancestor ca | |
JOIN CHCO_DeID_Oct2018.concept c1 ON ca.ancestor_concept_id = c1.concept_id | |
JOIN CHCO_DeID_Oct2018.vocabulary v ON c1.vocabulary_id = v.vocabulary_id | |
WHERE | |
ca.descendant_concept_id IN (SELECT CONCEPT_ID FROM drug_concepts) | |
AND c1.concept_name != "No matching concept" | |
AND c1.concept_id IS NOT NULL | |
AND c1.domain_id = "Drug" | |
AND c1.concept_class_id = "Ingredient" | |
GROUP BY CONCEPT_ID, INGREDIENT_CONCEPT_ID, INGREDIENT_SOURCE_CODE, INGREDIENT_LABEL, INGREDIENT_VOCAB, INGREDIENT_VOCAB_VERSION, INGREDIENT_STANDARD_CONCEPT), | |
ingredient_ancestors | |
AS (SELECT | |
ca.descendant_concept_id AS CONCEPT_ID, | |
STRING_AGG(DISTINCT(CAST(c1.concept_id as STRING)), " | ") AS INGRED_ANCESTOR_CONCEPT_ID, | |
STRING_AGG(DISTINCT(CONCAT(LOWER(c1.vocabulary_id), ":", c1.concept_code)), " | ") AS INGRED_ANCESTOR_SOURCE_CODE, | |
STRING_AGG(DISTINCT(c1.concept_name), " | ") AS INGRED_ANCESTOR_LABEL, | |
STRING_AGG(DISTINCT(c1.vocabulary_id), " | ") AS INGRED_ANCESTOR_VOCAB, | |
STRING_AGG(DISTINCT(v.vocabulary_version), " | ") AS INGRED_ANCESTOR_VOCAB_VERSION | |
FROM | |
CHCO_DeID_Oct2018.concept_ancestor ca | |
JOIN CHCO_DeID_Oct2018.concept c1 ON ca.ancestor_concept_id = c1.concept_id | |
JOIN CHCO_DeID_Oct2018.vocabulary v ON c1.vocabulary_id = v.vocabulary_id | |
WHERE | |
ca.descendant_concept_id IN (SELECT CONCEPT_ID FROM ingredient_concepts) | |
AND c1.concept_name != "No matching concept" | |
AND c1.concept_id IS NOT NULL | |
AND c1.domain_id = "Drug" | |
AND c1.concept_class_id = "Ingredient" | |
GROUP BY CONCEPT_ID), | |
ingredient_synonyms | |
AS (SELECT | |
s.concept_id AS INGREDIENT_CONCEPT_ID, | |
STRING_AGG(DISTINCT(s.concept_synonym_name), " | ") AS INGREDIENT_SYNONYM | |
FROM CHCO_DeID_Oct2018.concept_synonym s | |
WHERE s.concept_id in (SELECT INGREDIENT_CONCEPT_ID FROM ingredient_concepts) | |
GROUP BY INGREDIENT_CONCEPT_ID) | |
SELECT | |
'Concept Used In Practice' AS CONCEPT_TYPE, | |
d.CONCEPT_ID, | |
d.STANDARD_CONCEPT, | |
d.CONCEPT_SOURCE_CODE, | |
d.CONCEPT_LABEL, | |
d.CONCEPT_VOCAB, | |
d.CONCEPT_VOCAB_VERSION, | |
s.CONCEPT_SYNONYM, | |
a.ANCESTOR_CONCEPT_ID, | |
a.ANCESTOR_SOURCE_CODE, | |
a.ANCESTOR_LABEL, | |
a.ANCESTOR_VOCAB, | |
a.ANCESTOR_VOCAB_VERSION, | |
i.INGREDIENT_CONCEPT_ID, | |
i.INGREDIENT_SOURCE_CODE, | |
i.INGREDIENT_STANDARD_CONCEPT, | |
i.INGREDIENT_LABEL, | |
i.INGREDIENT_VOCAB, | |
i.INGREDIENT_VOCAB_VERSION, | |
s2.INGREDIENT_SYNONYM, | |
a2.INGRED_ANCESTOR_CONCEPT_ID, | |
a2.INGRED_ANCESTOR_SOURCE_CODE, | |
a2.INGRED_ANCESTOR_LABEL, | |
a2.INGRED_ANCESTOR_VOCAB, | |
a2.INGRED_ANCESTOR_VOCAB_VERSION | |
FROM drug_concepts d | |
FULL JOIN drug_ancestors a ON d.CONCEPT_ID = a.CONCEPT_ID | |
FULL JOIN drug_synonyms s ON d.CONCEPT_ID = s.CONCEPT_ID | |
FULL JOIN ingredient_concepts i ON d.CONCEPT_ID = i.CONCEPT_ID | |
FULL JOIN ingredient_ancestors a2 ON i.CONCEPT_ID = a2.CONCEPT_ID | |
FULL JOIN ingredient_synonyms s2 ON i.INGREDIENT_CONCEPT_ID = s2.INGREDIENT_CONCEPT_ID; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH | |
drug_concepts | |
AS (SELECT | |
c.standard_concept AS STANDARD_CONCEPT, | |
c.concept_id AS CONCEPT_ID, | |
CONCAT(LOWER(c.vocabulary_id), ":", c.concept_code) AS CONCEPT_SOURCE_CODE, | |
c.concept_name AS CONCEPT_LABEL, | |
c.vocabulary_id AS CONCEPT_VOCAB, | |
v.vocabulary_version AS CONCEPT_VOCAB_VERSION | |
FROM | |
CHCO_DeID_Oct2018.concept c | |
JOIN CHCO_DeID_Oct2018.vocabulary v ON c.vocabulary_id = v.vocabulary_id | |
WHERE | |
c.concept_name != "No matching concept" | |
AND c.domain_id = "Drug" | |
AND c.standard_concept = "S" | |
AND c.vocabulary_id = "RxNorm" | |
GROUP BY CONCEPT_ID, CONCEPT_SOURCE_CODE, CONCEPT_LABEL, CONCEPT_VOCAB, CONCEPT_VOCAB_VERSION, STANDARD_CONCEPT), | |
drug_ancestors | |
AS (SELECT | |
ca.descendant_concept_id AS CONCEPT_ID, | |
STRING_AGG(DISTINCT(CAST(c1.concept_id as STRING)), " | ") AS ANCESTOR_CONCEPT_ID, | |
STRING_AGG(DISTINCT(CONCAT(LOWER(c1.vocabulary_id), ":", c1.concept_code)), " | ") AS ANCESTOR_SOURCE_CODE, | |
STRING_AGG(DISTINCT(c1.concept_name), " | ") AS ANCESTOR_LABEL, | |
STRING_AGG(DISTINCT(c1.vocabulary_id), " | ") AS ANCESTOR_VOCAB, | |
STRING_AGG(DISTINCT(v.vocabulary_version), " | ") AS ANCESTOR_VOCAB_VERSION | |
FROM | |
CHCO_DeID_Oct2018.concept_ancestor ca | |
JOIN CHCO_DeID_Oct2018.concept c1 ON ca.ancestor_concept_id = c1.concept_id | |
JOIN CHCO_DeID_Oct2018.vocabulary v ON c1.vocabulary_id = v.vocabulary_id | |
WHERE | |
ca.descendant_concept_id IN (SELECT CONCEPT_ID FROM drug_concepts) | |
AND c1.concept_name != "No matching concept" | |
AND c1.concept_id IS NOT NULL | |
AND c1.domain_id = "Drug" | |
GROUP BY CONCEPT_ID), | |
drug_synonyms | |
AS (SELECT | |
s.concept_id AS CONCEPT_ID, | |
STRING_AGG(DISTINCT(s.concept_synonym_name), " | ") AS CONCEPT_SYNONYM | |
FROM CHCO_DeID_Oct2018.concept_synonym s | |
WHERE s.concept_id in (SELECT CONCEPT_ID FROM drug_concepts) | |
GROUP BY CONCEPT_ID), | |
ingredient_concepts | |
AS (SELECT | |
c1.standard_concept AS INGREDIENT_STANDARD_CONCEPT, | |
ca.descendant_concept_id AS CONCEPT_ID, | |
c1.concept_id AS INGREDIENT_CONCEPT_ID, | |
CONCAT(LOWER(c1.vocabulary_id), ":", c1.concept_code) AS INGREDIENT_SOURCE_CODE, | |
c1.concept_name AS INGREDIENT_LABEL, | |
c1.vocabulary_id AS INGREDIENT_VOCAB, | |
v.vocabulary_version AS INGREDIENT_VOCAB_VERSION | |
FROM | |
CHCO_DeID_Oct2018.concept_ancestor ca | |
JOIN CHCO_DeID_Oct2018.concept c1 ON ca.ancestor_concept_id = c1.concept_id | |
JOIN CHCO_DeID_Oct2018.vocabulary v ON c1.vocabulary_id = v.vocabulary_id | |
WHERE | |
ca.descendant_concept_id IN (SELECT CONCEPT_ID FROM drug_concepts) | |
AND c1.concept_name != "No matching concept" | |
AND c1.concept_id IS NOT NULL | |
AND c1.domain_id = "Drug" | |
AND c1.concept_class_id = "Ingredient" | |
AND c1.standard_concept = "S" | |
AND c1.vocabulary_id = "RxNorm" | |
GROUP BY CONCEPT_ID, INGREDIENT_CONCEPT_ID, INGREDIENT_SOURCE_CODE, INGREDIENT_LABEL, INGREDIENT_VOCAB, INGREDIENT_VOCAB_VERSION, INGREDIENT_STANDARD_CONCEPT), | |
ingredient_ancestors | |
AS (SELECT | |
ca.descendant_concept_id AS CONCEPT_ID, | |
STRING_AGG(DISTINCT(CAST(c1.concept_id as STRING)), " | ") AS INGRED_ANCESTOR_CONCEPT_ID, | |
STRING_AGG(DISTINCT(CONCAT(LOWER(c1.vocabulary_id), ":", c1.concept_code)), " | ") AS INGRED_ANCESTOR_SOURCE_CODE, | |
STRING_AGG(DISTINCT(c1.concept_name), " | ") AS INGRED_ANCESTOR_LABEL, | |
STRING_AGG(DISTINCT(c1.vocabulary_id), " | ") AS INGRED_ANCESTOR_VOCAB, | |
STRING_AGG(DISTINCT(v.vocabulary_version), " | ") AS INGRED_ANCESTOR_VOCAB_VERSION | |
FROM | |
CHCO_DeID_Oct2018.concept_ancestor ca | |
JOIN CHCO_DeID_Oct2018.concept c1 ON ca.ancestor_concept_id = c1.concept_id | |
JOIN CHCO_DeID_Oct2018.vocabulary v ON c1.vocabulary_id = v.vocabulary_id | |
WHERE | |
ca.descendant_concept_id IN (SELECT CONCEPT_ID FROM ingredient_concepts) | |
AND c1.concept_name != "No matching concept" | |
AND c1.concept_id IS NOT NULL | |
AND c1.domain_id = "Drug" | |
AND c1.concept_class_id = "Ingredient" | |
GROUP BY CONCEPT_ID), | |
ingredient_synonyms | |
AS (SELECT | |
s.concept_id AS INGREDIENT_CONCEPT_ID, | |
STRING_AGG(DISTINCT(s.concept_synonym_name), " | ") AS INGREDIENT_SYNONYM | |
FROM CHCO_DeID_Oct2018.concept_synonym s | |
WHERE s.concept_id in (SELECT INGREDIENT_CONCEPT_ID FROM ingredient_concepts) | |
GROUP BY INGREDIENT_CONCEPT_ID) | |
SELECT DISTINCT | |
'Standard Concept Not Used In Practice' AS CONCEPT_TYPE, | |
d.CONCEPT_ID, | |
d.STANDARD_CONCEPT, | |
d.CONCEPT_SOURCE_CODE, | |
d.CONCEPT_LABEL, | |
d.CONCEPT_VOCAB, | |
d.CONCEPT_VOCAB_VERSION, | |
s.CONCEPT_SYNONYM, | |
a.ANCESTOR_CONCEPT_ID, | |
a.ANCESTOR_SOURCE_CODE, | |
a.ANCESTOR_LABEL, | |
a.ANCESTOR_VOCAB, | |
a.ANCESTOR_VOCAB_VERSION, | |
i.INGREDIENT_CONCEPT_ID, | |
i.INGREDIENT_SOURCE_CODE, | |
i.INGREDIENT_STANDARD_CONCEPT, | |
i.INGREDIENT_LABEL, | |
i.INGREDIENT_VOCAB, | |
i.INGREDIENT_VOCAB_VERSION, | |
s2.INGREDIENT_SYNONYM, | |
a2.INGRED_ANCESTOR_CONCEPT_ID, | |
a2.INGRED_ANCESTOR_SOURCE_CODE, | |
a2.INGRED_ANCESTOR_LABEL, | |
a2.INGRED_ANCESTOR_VOCAB, | |
a2.INGRED_ANCESTOR_VOCAB_VERSION | |
FROM drug_concepts d | |
FULL JOIN drug_ancestors a ON d.CONCEPT_ID = a.CONCEPT_ID | |
FULL JOIN drug_synonyms s ON d.CONCEPT_ID = s.CONCEPT_ID | |
FULL JOIN ingredient_concepts i ON d.CONCEPT_ID = i.CONCEPT_ID | |
FULL JOIN ingredient_ancestors a2 ON i.CONCEPT_ID = a2.CONCEPT_ID | |
FULL JOIN ingredient_synonyms s2 ON i.INGREDIENT_CONCEPT_ID = s2.INGREDIENT_CONCEPT_ID | |
WHERE | |
i.INGREDIENT_CONCEPT_ID NOT IN (SELECT DISTINCT INGREDIENT_CONCEPT_ID FROM CHCO_DeID_Oct2018.OMOP2OBO_Medications_Concepts_Used_In_Practice); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH | |
measurement_concepts | |
AS (SELECT | |
c.standard_concept AS STANDARD_CONCEPT, | |
m.measurement_concept_id AS CONCEPT_ID, | |
CONCAT(LOWER(c.vocabulary_id), ":", c.concept_code) AS CONCEPT_SOURCE_CODE, | |
c.concept_name AS CONCEPT_LABEL, | |
c.vocabulary_id AS CONCEPT_VOCAB, | |
v.vocabulary_version AS CONCEPT_VOCAB_VERSION | |
FROM | |
CHCO_DeID_Oct2018.measurement m | |
JOIN CHCO_DeID_Oct2018.concept c ON m.measurement_concept_id = c.concept_id | |
JOIN CHCO_DeID_Oct2018.vocabulary v ON c.vocabulary_id = v.vocabulary_id | |
WHERE | |
c.concept_name != "No matching concept" | |
AND c.domain_id = "Measurement" | |
GROUP BY CONCEPT_ID, CONCEPT_SOURCE_CODE, CONCEPT_LABEL, CONCEPT_VOCAB, CONCEPT_VOCAB_VERSION, STANDARD_CONCEPT), | |
measurement_ancestors | |
AS (SELECT | |
ca.descendant_concept_id AS CONCEPT_ID, | |
STRING_AGG(DISTINCT(CAST(c1.concept_id as STRING)), " | ") AS ANCESTOR_CONCEPT_ID, | |
STRING_AGG(DISTINCT(CONCAT(LOWER(c1.vocabulary_id), ":", c1.concept_code)), " | ") AS ANCESTOR_SOURCE_CODE, | |
STRING_AGG(DISTINCT(c1.concept_name), " | ") AS ANCESTOR_LABEL, | |
STRING_AGG(DISTINCT(c1.vocabulary_id), " | ") AS ANCESTOR_VOCAB, | |
STRING_AGG(DISTINCT(v.vocabulary_version), " | ") AS ANCESTOR_VOCAB_VERSION | |
FROM | |
CHCO_DeID_Oct2018.concept_ancestor ca | |
JOIN CHCO_DeID_Oct2018.concept c1 ON ca.ancestor_concept_id = c1.concept_id | |
JOIN CHCO_DeID_Oct2018.vocabulary v ON c1.vocabulary_id = v.vocabulary_id | |
WHERE | |
ca.descendant_concept_id IN (SELECT CONCEPT_ID FROM measurement_concepts) | |
AND c1.concept_name != "No matching concept" | |
AND c1.concept_id IS NOT NULL | |
AND c1.domain_id = "Measurement" | |
GROUP BY CONCEPT_ID), | |
measurement_results | |
AS (SELECT | |
measurement_concept_id AS CONCEPT_ID, | |
CASE WHEN REGEXP_CONTAINS(STRING_AGG(range_low_source_value, ""), r'(?i)(positive|negative)') IS TRUE THEN "Negative/Positive" | |
WHEN REGEXP_CONTAINS(STRING_AGG(range_high_source_value, ""), r'(?i)(positive|negative)') IS TRUE THEN "Negative/Positive" | |
WHEN REGEXP_CONTAINS(STRING_AGG(range_low_source_value, ""), r'[[:digit:]]') IS TRUE THEN "Normal/Low/High" | |
WHEN REGEXP_CONTAINS(STRING_AGG(range_high_source_value, ""), r'[[:digit:]]') IS TRUE THEN "Normal/Low/High" | |
ELSE NULL END AS RESULT_TYPE | |
FROM CHCO_DeID_Oct2018.measurement | |
WHERE measurement_concept_id in (SELECT CONCEPT_ID FROM measurement_concepts) | |
GROUP BY CONCEPT_ID), | |
measurement_scale | |
AS (SELECT | |
s.concept_id AS CONCEPT_ID, | |
REPLACE(STRING_AGG(DISTINCT(s.concept_synonym_name), " | "), '; ', ' | ') AS CONCEPT_SYNONYM, | |
STRING_AGG(s.concept_synonym_name, ""), | |
CASE WHEN REGEXP_CONTAINS(STRING_AGG(s.concept_synonym_name, ""), r'(?i)ordinal') IS TRUE THEN "ORD" | |
WHEN REGEXP_CONTAINS(STRING_AGG(s.concept_synonym_name, ""), r'(?i)nominal') IS TRUE THEN "NOM" | |
WHEN REGEXP_CONTAINS(STRING_AGG(s.concept_synonym_name, ""), r'(?i)quantitative') IS TRUE THEN "QUANT" | |
WHEN REGEXP_CONTAINS(STRING_AGG(s.concept_synonym_name, ""), r'(?i)qualitative') IS TRUE THEN "QUAL" | |
WHEN REGEXP_CONTAINS(STRING_AGG(s.concept_synonym_name, ""), r'(?i)narrative') IS TRUE THEN "NAR" | |
WHEN REGEXP_CONTAINS(STRING_AGG(s.concept_synonym_name, ""), r'(?i)doc') IS TRUE THEN "DOC" | |
WHEN REGEXP_CONTAINS(STRING_AGG(s.concept_synonym_name, ""), r'(?i)(panel|pnl|panl)') IS TRUE THEN "PNL" | |
ELSE "Unmapped Scale Type" END AS SCALE | |
FROM CHCO_DeID_Oct2018.concept_synonym s | |
WHERE s.concept_id in (SELECT CONCEPT_ID FROM measurement_concepts) | |
GROUP BY CONCEPT_ID), | |
measurement_metadata_update | |
AS (SELECT | |
r.CONCEPT_ID, | |
CASE WHEN (r.RESULT_TYPE IS NULL AND s.SCALE = "ORD") AND REGEXP_CONTAINS(s.CONCEPT_SYNONYM, r'(?i)screen') IS TRUE THEN "Negative/Positive" | |
WHEN (r.RESULT_TYPE IS NULL AND s.SCALE = "ORD") AND REGEXP_CONTAINS(s.CONCEPT_SYNONYM, r'(?i)presence') IS TRUE THEN "Negative/Positive" | |
WHEN r.RESULT_TYPE IS NULL AND s.SCALE = "QUANT" THEN "Normal/Low/High" | |
WHEN r.RESULT_TYPE IS NOT NULL THEN r.RESULT_TYPE | |
ELSE "Unknown Result Type" END AS RESULT_TYPE, | |
CASE WHEN s.SCALE IS NULL THEN "Other" # for non-LOINC scale types | |
ELSE s.SCALE END AS SCALE | |
FROM | |
(SELECT * FROM measurement_results) r | |
FULL JOIN (SELECT * FROM measurement_scale) s ON r.CONCEPT_ID = s.CONCEPT_ID) | |
SELECT | |
'Concept Used In Practice' AS CONCEPT_TYPE, | |
m.STANDARD_CONCEPT, | |
m.CONCEPT_ID, | |
m.CONCEPT_SOURCE_CODE, | |
m.CONCEPT_LABEL, | |
m.CONCEPT_VOCAB, | |
m.CONCEPT_VOCAB_VERSION, | |
s.CONCEPT_SYNONYM, | |
a.ANCESTOR_CONCEPT_ID, | |
a.ANCESTOR_SOURCE_CODE, | |
a.ANCESTOR_LABEL, | |
a.ANCESTOR_VOCAB, | |
a.ANCESTOR_VOCAB_VERSION, | |
u.SCALE, | |
u.RESULT_TYPE | |
FROM measurement_concepts m | |
FULL JOIN measurement_ancestors a ON m.CONCEPT_ID = a.CONCEPT_ID | |
FULL JOIN measurement_scale s ON m.CONCEPT_ID = s.CONCEPT_ID | |
FULL JOIN measurement_metadata_update u ON m.CONCEPT_ID = u.CONCEPT_ID; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH | |
measurement_concepts | |
AS (SELECT | |
c.standard_concept AS STANDARD_CONCEPT, | |
c.concept_id AS CONCEPT_ID, | |
CONCAT(LOWER(c.vocabulary_id), ":", c.concept_code) AS CONCEPT_SOURCE_CODE, | |
c.concept_name AS CONCEPT_LABEL, | |
c.vocabulary_id AS CONCEPT_VOCAB, | |
v.vocabulary_version AS CONCEPT_VOCAB_VERSION | |
FROM | |
CHCO_DeID_Oct2018.concept c | |
JOIN CHCO_DeID_Oct2018.vocabulary v ON c.vocabulary_id = v.vocabulary_id | |
WHERE | |
c.concept_name != "No matching concept" | |
AND c.domain_id = "Measurement" | |
GROUP BY CONCEPT_ID, CONCEPT_SOURCE_CODE, CONCEPT_LABEL, CONCEPT_VOCAB, CONCEPT_VOCAB_VERSION, STANDARD_CONCEPT), | |
measurement_ancestors | |
AS (SELECT | |
ca.descendant_concept_id AS CONCEPT_ID, | |
STRING_AGG(DISTINCT(CAST(c1.concept_id as STRING)), " | ") AS ANCESTOR_CONCEPT_ID, | |
STRING_AGG(DISTINCT(CONCAT(LOWER(c1.vocabulary_id), ":", c1.concept_code)), " | ") AS ANCESTOR_SOURCE_CODE, | |
STRING_AGG(DISTINCT(c1.concept_name), " | ") AS ANCESTOR_LABEL, | |
STRING_AGG(DISTINCT(c1.vocabulary_id), " | ") AS ANCESTOR_VOCAB, | |
STRING_AGG(DISTINCT(v.vocabulary_version), " | ") AS ANCESTOR_VOCAB_VERSION | |
FROM | |
CHCO_DeID_Oct2018.concept_ancestor ca | |
JOIN CHCO_DeID_Oct2018.concept c1 ON ca.ancestor_concept_id = c1.concept_id | |
JOIN CHCO_DeID_Oct2018.vocabulary v ON c1.vocabulary_id = v.vocabulary_id | |
WHERE | |
ca.descendant_concept_id IN (SELECT CONCEPT_ID FROM measurement_concepts) | |
AND c1.concept_name != "No matching concept" | |
AND c1.concept_id IS NOT NULL | |
AND c1.domain_id = "Measurement" | |
GROUP BY CONCEPT_ID), | |
measurement_results | |
AS (SELECT | |
measurement_concept_id AS CONCEPT_ID, | |
CASE WHEN REGEXP_CONTAINS(STRING_AGG(range_low_source_value, ""), r'(?i)(positive|negative)') IS TRUE THEN "Negative/Positive" | |
WHEN REGEXP_CONTAINS(STRING_AGG(range_high_source_value, ""), r'(?i)(positive|negative)') IS TRUE THEN "Negative/Positive" | |
WHEN REGEXP_CONTAINS(STRING_AGG(range_low_source_value, ""), r'[[:digit:]]') IS TRUE THEN "Normal/Low/High" | |
WHEN REGEXP_CONTAINS(STRING_AGG(range_high_source_value, ""), r'[[:digit:]]') IS TRUE THEN "Normal/Low/High" | |
ELSE NULL END AS RESULT_TYPE | |
FROM CHCO_DeID_Oct2018.measurement | |
WHERE measurement_concept_id in (SELECT CONCEPT_ID FROM measurement_concepts) | |
GROUP BY CONCEPT_ID), | |
measurement_scale | |
AS (SELECT | |
s.concept_id AS CONCEPT_ID, | |
REPLACE(STRING_AGG(DISTINCT(s.concept_synonym_name), " | "), '; ', ' | ') AS CONCEPT_SYNONYM, | |
STRING_AGG(s.concept_synonym_name, ""), | |
CASE WHEN REGEXP_CONTAINS(STRING_AGG(s.concept_synonym_name, ""), r'(?i)ordinal') IS TRUE THEN "ORD" | |
WHEN REGEXP_CONTAINS(STRING_AGG(s.concept_synonym_name, ""), r'(?i)nominal') IS TRUE THEN "NOM" | |
WHEN REGEXP_CONTAINS(STRING_AGG(s.concept_synonym_name, ""), r'(?i)quantitative') IS TRUE THEN "QUANT" | |
WHEN REGEXP_CONTAINS(STRING_AGG(s.concept_synonym_name, ""), r'(?i)qualitative') IS TRUE THEN "QUAL" | |
WHEN REGEXP_CONTAINS(STRING_AGG(s.concept_synonym_name, ""), r'(?i)narrative') IS TRUE THEN "NAR" | |
WHEN REGEXP_CONTAINS(STRING_AGG(s.concept_synonym_name, ""), r'(?i)doc') IS TRUE THEN "DOC" | |
WHEN REGEXP_CONTAINS(STRING_AGG(s.concept_synonym_name, ""), r'(?i)(panel|pnl|panl)') IS TRUE THEN "PNL" | |
ELSE "Unmapped Scale Type" END AS SCALE | |
FROM CHCO_DeID_Oct2018.concept_synonym s | |
WHERE s.concept_id in (SELECT CONCEPT_ID FROM measurement_concepts) | |
GROUP BY CONCEPT_ID), | |
measurement_metadata_update | |
AS (SELECT | |
r.CONCEPT_ID, | |
CASE WHEN (r.RESULT_TYPE IS NULL AND s.SCALE = "ORD") AND REGEXP_CONTAINS(s.CONCEPT_SYNONYM, r'(?i)screen') IS TRUE THEN "Negative/Positive" | |
WHEN (r.RESULT_TYPE IS NULL AND s.SCALE = "ORD") AND REGEXP_CONTAINS(s.CONCEPT_SYNONYM, r'(?i)presence') IS TRUE THEN "Negative/Positive" | |
WHEN r.RESULT_TYPE IS NULL AND s.SCALE = "QUANT" THEN "Normal/Low/High" | |
WHEN r.RESULT_TYPE IS NOT NULL THEN r.RESULT_TYPE | |
ELSE "Unknown Result Type" END AS RESULT_TYPE, | |
CASE WHEN s.SCALE IS NULL THEN "Other" # for non-LOINC scale types | |
ELSE s.SCALE END AS SCALE | |
FROM | |
(SELECT * FROM measurement_results) r | |
FULL JOIN (SELECT * FROM measurement_scale) s ON r.CONCEPT_ID = s.CONCEPT_ID) | |
SELECT | |
'LOINC2HPO Concepts' AS CONCEPT_TYPE, | |
m.STANDARD_CONCEPT, | |
m.CONCEPT_ID, | |
m.CONCEPT_SOURCE_CODE, | |
m.CONCEPT_LABEL, | |
m.CONCEPT_VOCAB, | |
m.CONCEPT_VOCAB_VERSION, | |
s.CONCEPT_SYNONYM, | |
a.ANCESTOR_CONCEPT_ID, | |
a.ANCESTOR_SOURCE_CODE, | |
a.ANCESTOR_LABEL, | |
a.ANCESTOR_VOCAB, | |
a.ANCESTOR_VOCAB_VERSION, | |
u.SCALE, | |
u.RESULT_TYPE | |
FROM measurement_concepts m | |
FULL JOIN measurement_ancestors a ON m.CONCEPT_ID = a.CONCEPT_ID | |
FULL JOIN measurement_scale s ON m.CONCEPT_ID = s.CONCEPT_ID | |
FULL JOIN measurement_metadata_update u ON m.CONCEPT_ID = u.CONCEPT_ID | |
WHERE | |
m.CONCEPT_ID IN (3022189, 3023213, 3025460, 3037637, 3025992, 3016563, 3007005, 3015534, 3019295, 3002710, 3021249, 3036974, 3036795, 3003484, 3015138, 3031106, 3031144, 3037884, 3020885, 40771490, 3014208, 3001998, 3010815, 3034497, 3001061, 3036078, 3026951, 40769392, 3030582, 3033200, 3023447, 3000298, 3005550, 3030354, 3021715, 3032099, 3003819, 3025877, 3010582, 3028756, 3030568, 3026158, 3016436, 3031180, 3003153, 3028336, 3006587, 3013824, 3033190, 3031460, 3036345, 3003163, 3014287, 3003982, 3022195, 3019521, 3013423, 3030839, 3005984, 3026144, 3005227, 3013986, 3007071, 3004584, 3031134, 3010743, 3032656, 3012562, 3020096, 3028414, 3033201, 3030583, 3002269, 3000254, 3002897, 3005918, 3007613, 3014802, 3015639, 3021439, 3021410, 3000536, 3029162, 3045716, 3005937, 40768822, 3020509, 3035350, 3051014, 3040517, 3029315, 3038988, 3001308, 3027945, 3048402, 3002864, 3041096, 3037185, 3005673, 3035715, 40758569, 3000684, 3004398, 3033472, 3034789, 3030960, 3036477, 3020960, 42869603, 42869602, 42869604, 42529216, 3031631, 3016543, 44787047, 44787048, 44787041, 3042517, 3001643, 3014126, 40759897, 40758524, 40759652, 40760809, 3039584, 3041959, 3033026, 3041354, 3014502, 3021607, 3015916, 3018010, 3022260, 3005235, 3001185, 3003346, 3012864, 3016980, 3022455, 3031056, 3032971, 3037405, 3043371, 3044767, 3048795, 3052374, 3003939, 3030554, 3002074, 3001431, 3032767, 3003379, 3048550, 3014279, 3033745, 3019742, 3030402, 3004547, 3049681, 3000099, 3020299, 3035680, 3004153, 3042919, 3050763, 3033495, 3016621, 3036565, 3022098, 3049469, 3050609, 3052587, 3033194, 3035108, 3019710, 3044983, 3021147, 3022559, 3023804, 3033184, 3019775, 3009234, 3044375, 3045775, 3008136, 36304056, 40760990, 40760992, 40766208, 40760534, 21493669, 40758046, 40765931, 40766209, 36303820, 40758902, 36304574, 40759900, 42528720, 42528719, 46235895, 36305594, 3014295, 3000207, 3000823, 3000890, 3000967, 3001118, 3001378, 3001685, 3001782, 3001959, 3002052, 3002563, 3002887, 3003018, 3003545, 3003865, 3003976, 3005461, 3005931, 3006175, 3006208, 3006888, 3007046, 3007117, 3007379, 3007912, 3008589, 3009649, 3009693, 3010730, 3011283, 3011376, 3012424, 3012538, 3013210, 3013574, 3013653, 3013891, 3014006, 3014289, 3015331, 3016186, 3016683, 3017415, 3018262, 3018643, 3019386, 3019856, 3019909, 3020917, 3021221, 3021273, 3021316, 3022478, 3022552, 3022901, 3023015, 3023372, 3023845, 3024009, 3024295, 3024454, 3024613, 3024772, 3025291, 3025646, 3026309, 3026944, 3027566, 3027908, 3027986, 3029605, 3029669, 3030225, 3030489, 3031593, 3031910, 3032483, 3033620, 3033796, 3034707, 3034719, 3035411, 3035706, 3036470, 3037749, 3038386, 3041583, 3041601, 3042385, 3042698, 3043254, 3043551, 3043768, 3046561, 3048787, 3048845, 3049479, 3049735, 3053233, 3042927, 3001102, 3002478, 3004101, 3006528, 3006973, 3007307, 3017097, 3017711, 3018459, 3021012, 3022155, 3022330, 3023331, 3024584, 3027455, 3032718, 3033067, 3044118, 3044922, 3043196, 3011773, 3037762, 3031634, 3027873, 3036225, 3005343, 3027064, 3017515, 3050109, 3035596, 3014642, 3005409, 3042203, 3024913, 3036981, 3021880, 3020894, 3014366, 3011366, 3036049, 3021063, 3014485, 3029593, 3035364, 3022762, 3019451, 3008543, 3048842, 3044768, 3039842, 3040637, 3052931, 3016440, 3031006, 3036662, 3051598, 3001367, 3019467, 3018588, 3012183, 3051746, 3052893, 3011008, 3027874, 3052013, 3023312, 3002398, 3043853, 3032560, 3043428, 3013343, 3027454, 3018375, 3043054, 3034458, 3028697, 3003266, 3010594, 3037687, 3003519, 3007628, 3021085, 3043477, 3049810, 3047408, 3004490, 3039499, 3018258, 3052365, 3045780, 3030780, 3052351, 3002392, 3024819, 3023100, 3029400, 3052690, 3026558, 3028513, 3007151, 3021765, 3041008, 3001299, 3006212, 3003734, 3006606, 3017139, 3020868, 3010917, 3016885, 3025583, 3028593, 3010352, 3006013, 3026682, 3051943, 3044906, 3018786, 3013941, 3027069, 3023560, 3000568, 3037669, 3030998, 3014334, 3007880, 3010405, 3010725, 3020421, 3022785, 3002387, 3015033, 3008498, 3002455, 3034467, 3011804, 3000462, 3027686, 3038061, 3034102, 3002280, 3028970, 3027219, 3005047, 3011585, 3033308, 3032794, 3032491, 3031053, 3034183, 3042963, 3035623, 3009458, 3050746, 3010748, 3013752, 3012813, 3040547, 3004941, 3026147, 3033494, 3001064, 3016204, 3008955, 3023267, 3028738, 3042668, 3034037, 3013707, 3026407, 3049749, 3047586, 3029288, 3012925, 3052723, 3048532, 3030808, 3035714, 3034787, 3034275, 3053231, 3033936, 3050769, 3011228, 3038463, 3020800, 3035127, 3011188, 3012396, 3015989, 3023606, 3038051, 3039011, 3017792, 3002220, 3008699, 3001591, 3003061, 3009815, 3027728, 3002246, 3024334, 3024752, 3025956, 3033715, 3021688, 3017166, 3028305, 3011733, 3017165, 3009578, 3017469, 3025376, 3016982, 3037130, 3000143, 3005128, 3006543, 3021483, 3002612, 3002424, 3029026, 3004121, 3002230, 3005340, 3007681, 3010100, 3010478, 3012211, 3013703, 3024101, 3025871, 3036975, 3051335, 3026504, 3037139, 3011019, 3007578, 3007403, 3027530, 3053179, 3044914, 3000933, 3006479, 3032672, 3038066, 3024949, 3011949, 3027796, 3011134, 3006712, 3009176, 3030276, 3016037, 3011928, 3000003, 3016004, 3026707, 3021503, 3022003, 3043403, 3025822, 3028035, 3005102, 3020035, 3025729, 3020746, 3016026, 3021465, 3021203, 3030714, 3001497, 3034836, 3019073, 3030208, 3047462, 3051148, 3024525, 3007904, 3036241, 3005303, 3022422, 3012870, 3045373, 3047024, 3023923, 3032400, 3029301, 3047034, 3044698, 3046964, 3013374, 3036384, 3003427, 3018185, 3015203, 3029433, 3038242, 3039065, 3008587, 3036905, 3037797, 3053155, 3018798, 3005636, 3021971, 3030206, 3013099, 3021474, 3019692, 3029334, 3015105, 3006191, 3027004, 3029548, 3000228, 3010642, 3018458, 3020051, 3012713, 3027129, 3012180, 3016247, 3000609, 3016793, 3030252, 3009297, 3022509, 3029745, 3050416, 3014218, 3006538, 3000596, 3008251, 3031236, 3036265, 3051303, 3048529, 3021904, 3020446, 3044779, 3006257, 3009857, 3009091, 3001271, 3011395, 3000912, 3023863, 3026310, 3041779, 3019800, 3035953, 3037255, 3052678, 3028714, 3017179, 3014303, 3027033, 3053187, 3013537, 3017617, 3022859, 3033428, 3051946, 3029683, 3030122, 3002016, 3035155, 3012685, 3002953, 3015473, 3043706, 3019319, 3003704, 3000257, 3004817, 3012615, 3013909, 3025366, 3044925, 3051304, 3012244, 3053338, 3020603, 3050969, 3038505, 3014797, 3021101, 3009828, 3004843, 3014376, 3014414, 3011821, 3005731, 3025572, 3007467, 3046813, 3031038, 3002959, 3008386, 3028219, 3026866, 3021059, 3020752, 3013034, 3031326, 3000431, 3014957, 3011329, 3029296, 3013797, 3005890, 3037090, 3024512, 3010819, 3012689, 3013236, 3023131, 3002165, 3025612, 3008428, 3043570, 3033943, 3048375, 3005654, 3025599, 3052060, 3030298, 3030304, 3052953, 3006704, 3032836, 3012032, 3000224, 3033238, 3014718, 3005357, 3028973, 3034138, 3042837, 3028938, 3019572, 3002869, 3034976, 3028813, 3004218, 3022744, 3024242, 3027171, 21492940, 21492944, 21492949, 21492954, 21493052, 21494909, 21494927, 21494947, 21491257, 40766115, 21492936, 40771478, 40765978, 40760762, 21492947, 21494928, 42868448, 21492941, 42528737, 44816896, 42869585, 42528733, 40763967, 21492955, 40762104, 40761594, 40761619, 36306004, 42528729, 42528736, 42528731, 42528739, 43533737, 21493048, 21492931, 40763962, 40760470, 40761599, 40762247, 42869587, 40765944, 36306154, 40759443, 40759791, 40760450, 40760471, 40760475, 40760479, 40760759, 40760761, 40761146, 40761600, 40761601, 40761605, 40761613, 40762065, 40762083, 40762105, 40762120, 40763977, 40763996, 40764006, 40764045, 40764046, 40764048, 40764049, 40764055, 40764087, 40765974, 40759799, 40761935, 40761990, 40760840, 40760473, 40761602, 40762910, 40759053, 46236275, 40761606, 40759039, 40761614, 40761607, 46236274, 40761616, 40760757, 40760478, 40762099, 40760760, 40761603, 40763992, 40763964, 42528727, 43533742, 21494944, 21492930, 21492951, 21493144, 40764035, 43533739, 42528725, 40764024, 42528743, 42528755, 42528604, 21492945, 42528732, 40763966, 40764003, 21492948, 21493050, 21493051, 40762121, 40762061, 40763995, 21492937, 40762077, 42869583, 40762097, 40761608, 40761610, 40759703, 40760839, 40761609, 40761611, 40761617, 40760474, 40759702, 40769113, 21494945, 40763969, 21494781, 40763994, 40763993, 40764005, 21492934, 42528603, 40763509, 21492961, 42528735, 44816893, 40764080, 42528741, 42528756, 42868422, 21492952, 21492935, 42528742, 21492928, 42528670, 46236332, 40759636, 40761595, 40761618, 40761598, 40760472, 40761597, 40761024, 40761604, 40761596, 40760490, 40761612, 40761622, 40761003, 40761620, 40760480, 40760477, 40762098, 40762106, 40764097, 40765973, 36303363, 42868642, 21494932, 21493145, 21492932, 21494936, 43533736, 44816897, 42528728, 42528726, 40764065, 40760499, 21492953, 21492950, 42868453, 42868643, 40765977, 40760498, 21492929, 42870313, 42868413, 43055271, 21492938, 42528668, 42528738, 42528740, 42528745, 42528769, 42528730, 42528669, 42528770, 42869588, 21492942, 43055244, 43533740, 44816894, 44816895, 21492958, 21494934, 42528746, 40767667, 21493049, 42528671, 21494911, 21494935, 43533738, 40765910, 21493146, 21492933, 21492946, 21492939, 42528744, 42868447, 21492943, 40760763, 40761786, 40762100, 40760476, 40771457, 40761621, 40759040, 40760758, 40759704, 40761910, 40761615, 40765002, 42869584, 42869586, 36305733, 46236273, 46236331, 3037121, 3028437, 3035899, 3035009, 3053341, 3012516, 3039775, 3002719, 3003658, 3007830, 3008298, 3008675, 3009759, 3010886, 3011197, 3011627, 3012095, 3013594, 3015408, 3015665, 3016997, 3017801, 3018405, 3021591, 3022101, 3022425, 3023618, 3023643, 3024189, 3025022, 3025252, 3028745, 3028997, 3029851, 3030952, 3035079, 3035253, 3036270, 3037156, 3038403, 3041289, 3041755, 3042333, 3043516, 3044183, 3046559, 3047196, 3047420, 3049185, 3052284, 3023841, 3032448, 3034988, 3039272, 3040388, 3039340, 3010234, 3020233, 3015242, 3030361, 3015735, 3051693, 3024959, 3026112, 3008037, 3002720, 3019111, 3033503, 3034705, 3006594, 3009208, 3006234, 3017154, 3030446, 3022708, 3036567, 3027516, 3052099, 3006028, 3043823, 3039425, 3039293, 3019253, 3023984, 3009084, 3003026, 3022085, 3040858, 3017730, 3025449, 3040657, 3037278, 3032983, 3029641, 3011005, 3013882, 3024774, 3039265, 3005944, 3012550, 3000402, 3036177, 3023289, 3015275, 3016820, 3019999, 3023507, 3022907, 3005491, 3028625, 3024373, 3010101, 3005949, 3037292, 3034898, 3027297, 3009351, 3011652, 3024764, 3003614, 3009531, 3007126, 3045730, 3001289, 3053222, 3048533, 3005078, 3028229, 3011902, 3000155, 3021325, 3011121, 3002908, 3003729, 3025894, 3014896, 3036256, 3014644, 3020138, 3035561, 3005892, 3035380, 3038430, 3001887, 3001016, 3028201, 3013575, 3019636, 3015411, 3018966, 3009555, 3040612, 3030432, 3038134, 3023422, 3001580, 3013363, 3016336, 3031337, 3023036, 3035627, 3007272, 3005316, 3013931, 3026737, 3027794, 3016023, 3012372, 3011879, 3038776, 3007027, 3005019, 3024068, 3035999, 3025075, 3027592, 3027749, 3029509, 3040968, 3007393, 3002780, 3013515, 3018133, 3029991, 3014164, 3006420, 3049436, 3044763, 3027383, 3020059, 3001817, 3033819, 3028271, 3020410, 3008272, 3003576, 3019176, 3010587, 3032543, 3017400, 3029177, 3004064, 3005674, 3006661, 3010726, 3052699, 3024825, 3053232, 3007946, 3028304, 3051867, 3025119, 3004734, 3007723, 3029975, 3004426, 3030472, 3029681, 3030475, 3030739, 3040936, 3048459, 3051732, 3024957, 3025420, 3038396, 3047209, 3052390, 3005586, 3028429, 3012555, 3010523, 3003272, 3026938, 3025666, 3024897, 3021438, 3030339, 3005536, 3007026, 3041042, 3027896, 3038120, 3012978, 3026213, 3010475, 3040752, 3033359, 3039291, 3045601, 3016190, 3011177, 3049461, 3005162, 3022460, 3011919, 3026187, 3022154, 3008011, 3015502, 3005364, 3024565, 3008371, 3010467, 3019223, 3020305, 3041451, 3013341, 3000776, 3008865, 3002636, 3013292, 3040243, 3043915, 3000353, 3005562, 3035633, 3041927, 3013678, 3043751, 3001609, 3004441, 3041981, 3024545, 3026981, 3037659, 3030754, 3009905, 3006724, 3030975, 3000066, 3048861, 3027923, 3002106, 3011208, 3015883, 3021565, 3016156, 3015982, 21492315, 40767661, 40759854, 40762039, 40767686, 40768481, 40758599, 40758724, 40764023, 40767663, 21492313, 21492404, 40760222, 40760224, 46234842, 40766015, 40758719, 40759614, 40760223, 40760225, 40760226, 40762707, 40763889, 40767675, 40767676, 40767679, 40767682, 40767685, 40763991, 40762952, 21492312, 40761034, 40759610, 40759615, 40764098, 40768463, 40758721, 40758980, 40763998, 40767662, 40758735, 40758718, 40758725, 44816529, 44816530, 40757346, 40761138, 42868683, 40766754, 40768473, 40769112, 40758737, 40763997, 40764017, 40761624, 40763979, 21494776, 40767660, 42528717, 21494264, 21494931, 40764109, 40764110, 40759751, 40757344, 40761625, 40767683, 40757440, 21490939, 43055646, 40766203, 40760221, 40758630, 46234768, 40767684, 40764067, 40761623, 21494780, 21494775, 40758734, 40762708, 44786749, 40764034, 42527888, 42528718, 42528605, 40762006, 40764016, 21494905, 21492314, 40769114, 40768466, 40764014, 40758723, 40758720, 21494910, 40768461, 40759616, 40758598, 40762141, 46234767, 44816931, 40762951, 40760861, 46236949, 3020876, 3000427, 3001448, 3002273, 3002293, 3003344, 3003374, 3013784, 3014310, 3017753, 3019872, 3020199, 3022793, 3028300, 3035279, 3035514, 3037289, 3038034, 3041712, 3008946, 3016738, 3006270, 3011411, 3034198, 3033826, 3033401, 3005337, 3041012, 3033705, 3020623, 3044963, 3036462, 3032271, 3045209, 3000084, 3017269, 3035656, 3002112, 3005794, 3033343, 3009689, 3013479, 3015469, 3006401, 3037242, 3036259, 3050068, 3032051, 3042972, 3026388, 3032459, 3036486, 3035966, 3001053, 3021666, 3043812, 3037999, 3040439, 3041089, 3012131, 3024942, 3008708, 3035645, 3021071, 3021347, 3008274, 3030477, 3002702, 3023580, 3034076, 3035649, 3017277, 3026826, 3020408, 3036426, 3012589, 3048816, 3015774, 3023672, 3035118, 3027907, 3020113, 3011625, 3024250, 3035323, 3021417, 3048274, 3042802, 3003017, 3013169, 3013525, 3011496, 3031251, 3013134, 3000131, 3049848, 3022469, 3003875, 3011459, 3023038, 3036204, 3008116, 3014115, 3009354, 3001352, 3021997, 3016879, 3020825, 3042321, 3014200, 3001539, 3010702, 3000469, 3011015, 3001158, 3021960, 3016431, 3004675, 3050149, 3051659, 3019984, 3025770, 21492466, 42868622, 40759832, 21494768, 40770956, 40760844, 40761023, 40761039, 40766103, 40766104, 40766105, 40766186, 46234959, 40764092, 40766187, 40764002, 42868626, 21494770, 40766193, 21492476, 46234958, 40765224, 42868625, 21494769, 40764019, 40767680, 40764094, 46235202, 46234960, 40768478, 40762251, 42529220, 40761038, 43533388, 40763085, 3002827, 3031203, 3000059, 3006146, 3008631, 3009658, 3010316, 3011341, 3012712, 3013378, 3016360, 3018765, 3019108, 3021268, 3022751, 3023404, 3024857, 3027008, 3027266, 3029566, 3035018, 3036887, 3039588, 3046693, 3051366, 3052343, 3052673, 3052721, 3053286, 3026071, 3007150, 3029329, 3018015, 3030487, 3007268, 3018568, 3037024, 3019894, 3032039, 3043325, 3017087, 3027939, 3001318, 3013104, 3026436, 3003145, 3051913, 3033057, 3048525, 3032755, 3028893, 3049783, 3029031, 3010873, 3027641, 3003977, 3003298, 3012523, 3024635, 3035055, 3051872, 3029579, 3020485, 3046360, 3003709, 3032231, 3009069, 3028764, 3037013, 3002877, 3034498, 3009337, 3027944, 3040834, 3004870, 3006687, 3044583, 3013830, 3042514, 3029622, 3037518, 3029623, 3030798, 3030211, 3031620, 3024183, 3029190, 3030942, 3046816, 3044376, 3001573, 3002020, 3025176, 3003214, 3020058, 3045498, 3007783, 3008424, 3007048, 3024759, 3050732, 3021296, 3045260, 3051790, 3012085, 3011213, 3032992, 3045001, 3015671, 3005347, 3038182, 3002787, 3037283, 3028159, 3019420, 3050201, 3017359, 3022487, 3018380, 3012852, 3047226, 3046427, 3052633, 3028770, 3026781, 3029269, 3031623, 3052667, 3034446, 3051361, 3008294, 3021840, 3011487, 3035556, 3019762, 3033467, 3030536, 3031315, 3024111, 3005954, 3032421, 3002417, 3038208, 3044250, 3033896, 3034866, 3039873, 3048229, 3034482, 3011884, 3013215, 3014230, 3041499, 3051369, 3031643, 3032171, 3051604, 3007255, 3011968, 3033056, 3016874, 3006219, 3050633, 3021200, 3051635, 3035132, 3004947, 3034417, 3029490, 3050749, 3039077, 3028791, 3007237, 3034832, 3006796, 3008689, 3052732, 3013473, 3025258, 3048144, 3050457, 3007950, 3041426, 3004853, 3045205, 3012946, 3028016, 3011258, 3021737, 3013604, 3018207, 3034117, 3040200, 3010132, 3045323, 3023602, 3030496, 3049123, 3012725, 3025895, 3008413, 3030278, 3031940, 3033046, 3001813, 3000714, 3048463, 3016936, 3016097, 3045490, 3006224, 3046325, 3018479, 3007922, 3029251, 3043088, 3004684, 3026498, 3011755, 3037540, 3008044, 3036180, 3038158, 3022737, 3020424, 3025250, 3040051, 3026978, 21491557, 21494219, 40761668, 40768809, 40759818, 40761703, 40761676, 40761640, 40761655, 40761694, 36306212, 40761636, 40763075, 40761679, 40761659, 40761724, 42868677, 42528897, 40757368, 40759627, 40761044, 40761626, 40761631, 40761643, 40761654, 40761658, 40761660, 40761661, 40761663, 40761686, 40761700, 40761702, 40761713, 40763107, 40763858, 40765975, 40765979, 40766091, 40759626, 40761664, 40761645, 40761688, 40761690, 40761691, 40761678, 40762766, 40761715, 40761646, 40761681, 40761707, 40761648, 40761711, 40763076, 40761719, 40761650, 40761629, 21491375, 21493230, 40761696, 40761672, 40761697, 40761695, 40761685, 40761689, 40761704, 40761649, 40761637, 40761730, 42868672, 40761677, 40765976, 40763981, 40761656, 40761665, 40761716, 40761657, 40761706, 40761787, 40760449, 40761669, 40761674, 40761722, 40761701, 40763074, 40761721, 40761641, 40761705, 40761653, 40761666, 40760836, 40761682, 40761717, 40761644, 40761642, 40761662, 40761687, 40761670, 40761699, 40761680, 40761675, 40761639, 40761652, 40761651, 40761667, 40761638, 40761710, 40761635, 40761684, 40761692, 40761698, 46236874, 40759656, 42868620, 42868629, 40761683, 40761630, 40761634, 40761708, 40766092, 40761671, 40761728, 40761727, 40761693, 40761673, 40761647, 43533390, 40763983, 43533391, 43533389, 40763986, 40759625, 40761628, 40761720, 40761729, 40761723, 40761714, 40761725, 40761726, 40761632, 40761633, 40761712, 40761627, 40761709, 40761718, 42868673, 42528753, 44816785, 3014051, 3005335, 3020533, 3001834, 3004748, 3011751, 3014009, 3025817, 3003022, 3003469, 3004724, 3005865, 3005996, 3007427, 3008799, 3010794, 3013026, 3017083, 3018151, 3018287, 3019431, 3022161, 3022933, 3023306, 3023776, 3024886, 3025136, 3026567, 3027276, 3028607, 3030070, 3030575, 3033841, 3035352, 3035741, 3051833, 3001511, 3025113, 3035125, 3004681, 3025740, 3008770, 3035791, 3050458, 3018175, 3025866, 3012805, 3019757, 3033397, 3026493, 46236482, 3002688, 3020632, 3014163, 3030260, 3010722, 3004723, 3021525, 3020317, 3024583, 3015996, 3036375, 3036283, 3003435, 3017328, 3014537, 3018645, 3017988, 3016586, 3004099, 3005650, 3018998, 3026020, 3000940, 3024540, 3028247, 3025070, 3012413, 3026536, 3004351, 3006325, 3017634, 3007781, 3036807, 3002654, 3038388, 3035415, 3012341, 3046449, 3005449, 3017859, 3043568, 3022287, 3015861, 3004312, 3035554, 3023230, 3006887, 3025398, 3035759, 3012792, 3003824, 3016160, 3011161, 3016159, 3026604, 3022201, 3010030, 3019169, 3047468, 3016256, 3033906, 3026571, 3037787, 3007031, 3027936, 3013881, 3020193, 3002332, 3028112, 3030105, 3009397, 3049222, 3023379, 3024644, 3023243, 3000931, 3024047, 3002666, 3005030, 3017053, 3009245, 3023228, 3008349, 3004766, 3025232, 3004209, 3035858, 3034771, 3041884, 3018582, 3023186, 3038453, 3027206, 3030976, 3046860, 3031836, 3019452, 3013766, 3021835, 3015934, 3049613, 3004709, 3051382, 3022722, 3009537, 3015942, 3017140, 3029233, 3035457, 3021506, 3007820, 3014194, 3021580, 3009877, 3005793, 3007821, 3011296, 3021232, 3022079, 3017890, 3019765, 3014737, 3004389, 3010044, 3017592, 3009414, 3010118, 3019474, 3007864, 3021924, 3031955, 3018688, 3013802, 3015323, 3000545, 3017222, 3025658, 3012635, 3006669, 3023466, 3034662, 3019084, 3021860, 3027980, 3016567, 3025876, 3013145, 3005787, 3000992, 3026550, 3013219, 3002544, 3027145, 3017345, 3023489, 3030118, 3032127, 3007804, 3013361, 3017986, 3039530, 3041186, 3005851, 3025181, 3026728, 3006333, 3006289, 3007092, 3023125, 3036895, 3005487, 3011761, 3003334, 3001022, 3009006, 3033312, 3022268, 3006760, 3008191, 3017703, 3020260, 3015980, 3023758, 3007619, 3031349, 3035963, 3047337, 3038063, 3020440, 3010834, 3009356, 3016382, 3027674, 3029732, 3047107, 40759259, 40758688, 40759815, 40759866, 40763982, 40764010, 40769159, 40757598, 40757600, 40759848, 40759821, 40759260, 40761043, 40759817, 40760033, 40760032, 40759257, 44817028, 40763999, 40759814, 40759258, 21494921, 40759829, 40759253, 42529565, 44816898, 21494766, 40761042, 40769783, 46235691, 46235709, 3018612, 3037233, 3024960, 3018050, 3002862, 3012892, 3001692, 3017757, 3018671, 3041682, 3039000, 3033811, 3032503, 3028668, 3012336, 3018759, 3014111, 3002079, 3015377, 3041261, 3019761, 3019416, 3027790, 3003588, 3005790, 3005943, 3039334, 3039678, 3016330, 3005570, 3042401, 3007034, 3002490, 3020650, 3013826, 3014215, 3000950, 3003087, 3005478, 3023544, 3003541, 3005414, 3038484, 3000361, 3014053, 3018418, 3025893, 42529061, 40767678, 46235205, 40761868, 46235206, 46235203, 42529005, 46235204, 3013101, 3028226, 3013811, 3023548, 3045156, 3004559, 3012501, 3013393, 3008108, 3019964, 3019199, 3021800, 3000764, 3025285, 3049536, 3038610, 3004321, 3007175, 3024731, 3027694, 3044358, 3017978, 3009831, 3023632, 3001322, 3040068, 3021210, 3021751, 3025789, 3035732, 3015569, 3021431, 3009046, 3003101, 3016365, 40759807, 40759024, 44816982, 3027143, 3052261, 3033667, 3039447, 3036955, 3033058, 3050954, 3028707, 3018060, 3006363, 3038033, 3012812, 3042434, 3003132, 3016856, 3008679, 3006932, 40759754, 46235212, 43055031, 3043722, 3030981, 3039359, 3029363, 3030405, 3033262, 3019911, 3052862, 3045989, 3051288, 3046493, 3014173, 3045381, 3021258, 3019876, 3004067, 44816700, 3001975, 3052617, 3005834, 44816701, 3030931, 3010739, 3002310, 3021322, 3020407, 3003412, 42870529, 3030758, 3039919, 3030267, 3006576, 3014798, 3007262, 3018524, 3016625, 3004155, 3032080, 3020044, 3007090, 46235124, 40771461, 3034683, 3029870, 3030173, 3040519, 3033891, 3027315, 3021447, 3050955, 3013290, 3016417, 3025911, 3023596, 3001501, 3019110, 3023799, 3034807, 3034962, 3020491, 3002479, 3005625, 3002173, 3043747, 3007930, 3035082, 3043507, 3045414, 3005489, 3013838, 3014749, 3040340, 3022547, 40761899, 40766280, 3018849, 3009596, 3018658, 3026808, 3009105, 3001695, 3002582, 3046900, 3046030, 3053283, 40771454, 40771455, 40760857, 3049187, 40771458, 3010354, 3027300, 3008204, 3006513, 3037072, 40761551, 3027801, 3014716, 3016699, 3004562, 3006717, 3003540, 3007332, 3027017, 3003159, 3038058, 3025673, 3027457, 40766109, 40761550, 3010421, 3004428, 3017589, 3008804, 3002009, 3009582, 3033778, 3024762, 3022407, 3012415, 3017892, 3020869, 3034101, 3037432, 3019047, 3006520, 3009154, 3017048, 3025211, 3003912, 3015024, 3017091, 3016701, 40761549, 3015182, 3028597, 3037110, 3027198, 3014886, 3041154, 3002809, 3015679, 3018095, 40760845, 3018311, 40762890, 40763528, 3027970, 3014309, 3008934, 3010300, 3026300, 3017538, 3018251, 3015956, 3027651, 3009797, 3013869, 3024684, 3013429, 3028615, 3017501, 3021940, 3020688, 3010457, 3009932, 3001494, 40762891, 36203200) | |
AND m.CONCEPT_ID NOT IN (SELECT DISTINCT CONCEPT_ID FROM CHCO_DeID_Oct2018.OMOP2OBO_Measurements_Concepts_Used_In_Practice); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH | |
procedure_concepts | |
AS (SELECT | |
p.procedure_concept_id AS CONCEPT_ID, | |
CONCAT(LOWER(c1.vocabulary_id), ":", c1.concept_code) AS CONCEPT_SOURCE_CODE, | |
c1.concept_name AS CONCEPT_LABEL, | |
c1.vocabulary_id AS CONCEPT_SOURCE_VOCAB, | |
v.vocabulary_version AS CONCEPT_VOCAB_VERSION | |
FROM | |
CHCO_DeID_Oct2018.procedure_occurrence p | |
JOIN CHCO_DeID_Oct2018.concept c1 ON p.procedure_concept_id = c1.concept_id | |
JOIN CHCO_DeID_Oct2018.vocabulary v ON c1.vocabulary_id = v.vocabulary_id | |
WHERE | |
c1.concept_name != "No matching concept" | |
AND c1.domain_id = "Procedure" | |
GROUP BY CONCEPT_ID, CONCEPT_SOURCE_CODE, CONCEPT_LABEL, CONCEPT_SOURCE_VOCAB, CONCEPT_VOCAB_VERSION), | |
procedure_ancestors | |
AS (SELECT | |
ca.descendant_concept_id AS CONCEPT_ID, | |
STRING_AGG(DISTINCT(CAST(c1.concept_id as STRING)), " | ") AS ANCESTOR_CONCEPT_ID, | |
STRING_AGG(DISTINCT(CONCAT(LOWER(c1.vocabulary_id), ":", c1.concept_code)), " | ") AS ANCESTOR_SOURCE_CODE, | |
STRING_AGG(DISTINCT(c1.concept_name), " | ") AS ANCESTOR_LABEL, | |
STRING_AGG(DISTINCT(c1.vocabulary_id), " | ") AS ANCESTOR_VOCAB, | |
STRING_AGG(DISTINCT(v.vocabulary_version), " | ") AS ANCESTOR_VOCAB_VERSION | |
FROM | |
CHCO_DeID_Oct2018.concept_ancestor ca | |
JOIN CHCO_DeID_Oct2018.concept c1 ON ca.ancestor_concept_id = c1.concept_id | |
JOIN CHCO_DeID_Oct2018.vocabulary v ON c1.vocabulary_id = v.vocabulary_id | |
WHERE | |
ca.descendant_concept_id IN (SELECT CONCEPT_ID FROM procedure_concepts) | |
AND c1.concept_name != "No matching concept" | |
AND c1.concept_id IS NOT NULL | |
AND c1.domain_id = "Procedure" | |
GROUP BY CONCEPT_ID), | |
procedure_synonyms | |
AS (SELECT | |
s.concept_id AS CONCEPT_ID, | |
STRING_AGG(DISTINCT(s.concept_synonym_name), " | ") AS CONCEPT_SYNONYM | |
FROM CHCO_DeID_Oct2018.concept_synonym s | |
WHERE s.concept_id in (SELECT CONCEPT_ID FROM procedure_concepts) | |
GROUP BY CONCEPT_ID) | |
SELECT | |
p.CONCEPT_ID, | |
p.CONCEPT_SOURCE_CODE, | |
p.CONCEPT_LABEL, | |
p.CONCEPT_SOURCE_VOCAB, | |
p.CONCEPT_VOCAB_VERSION, | |
s.CONCEPT_SYNONYM, | |
a.ANCESTOR_CONCEPT_ID, | |
a.ANCESTOR_SOURCE_CODE, | |
a.ANCESTOR_LABEL, | |
a.ANCESTOR_VOCAB, | |
a.ANCESTOR_VOCAB_VERSION | |
FROM procedure_concepts p | |
FULL JOIN procedure_ancestors a ON p.CONCEPT_ID = a.CONCEPT_ID | |
FULL JOIN procedure_synonyms s ON p.CONCEPT_ID = s.CONCEPT_ID; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Adding queries to pull all standard concepts for SNOMED CT and RxNorm