OMOP2OBO - OMOP Concept Queries: used to pull the OMOP concepts for input into the OBO mapping pipeline

OMOP2OBO_ConceptPrevalence_ErrorAnalysis.sql

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;

OMOPConcepts_ConditionOccurrence.sql

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;

OMOPConcepts_ConditionOccurrence_AllStandardConcepts.sql

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);

OMOPConcepts_DrugExposure.sql

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;

OMOPConcepts_DrugExposure_AllStandardConcepts.sql

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);

OMOPConcepts_Measurements.sql

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;

OMOPConcepts_Measurements_LOINC2HPO.sql

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);

OMOPConcepts_ProcedureOccurence.sql

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;

Updated queries to include most recent specifications for selecting parent concepts of conditions and ingredients for each medication

Updated queries to return clinical codes regardless of whether or not they have been linked to a patient

Adding queries to pull all standard concepts for SNOMED CT and RxNorm