I’ve recently joined the AoU workbench working on SQLs to query OMOP data. I’ve been through the OHDSI tutorials knowing how important to use standard concepts. However, in the Workbench tutorial, the example queries didnt reflect this standard concepts. Please see below their way to pull type I diabetes concepts IDs:
condition_concepts_t1d_df = pd.read_gbq(f"""
SELECT
DISTINCT c.concept_name,
c.concept_code,
c.concept_id
FROM
{DATASET}.concept c
JOIN {DATASET}.condition_occurrence co
ON c.concept_id = co.condition_source_concept_id
WHERE
(vocabulary_id=‘ICD9CM’ AND concept_code IN {condition_codes_t1d_icd9})
OR (vocabulary_id=‘ICD10CM’ AND concept_code IN {condition_codes_t1d_icd10})
GROUP BY
c.concept_name,
c.concept_code,
c.concept_id
“”",dialect = “standard”)
I don’t agree because there are no ‘standard’ or ‘maps to’ criteria included. Here is my version based on the principle illustrated in the tutorial video:
condition_concepts_t1d_df = pd.read_gbq(f"""
SELECT
DISTINCT c2.concept_name,
c2.concept_code,
c2.concept_id
FROM
{DATASET}.concept c
JOIN {DATASET}.condition_occurrence co
ON c.concept_id = co.condition_source_concept_id
JOIN {DATASET}.concept_relationship cr
ON cr.concept_id_1 = co.condition_source_concept_id
JOIN {DATASET}.concept c2
ON cr.concept_id_2 = c2.concept_id and LOWER(TRIM(cr.relationship_id)) = ‘maps to’
WHERE
(c.vocabulary_id=‘ICD9CM’ AND c.concept_code IN {condition_codes_t1d_icd9})
OR (c.vocabulary_id=‘ICD10CM’ AND c.concept_code IN {condition_codes_t1d_icd10})
GROUP BY
c2.concept_name,
c2.concept_code,
c2.concept_id
“”",dialect = “standard”)
I believe mine is correct because all resulting concepts are standard. Please verify if I am right. Thank you in advance. YuanYuan