OHDSI Home | Forums | Wiki | Github

SNOMED to MEDDRA mapping

Hello,

We were getting NA in ACHILLES .

Considering this due to missing MedDra vocabulary, we have added MedDra vocabularies in our standard vocabularies in omop_cdm tables , re-run the R scripts but still we are getting “NA” in ACHILLES for Condition Era and Condition Occurrence treemap.

In our Condition_Occurrence entity we are using Condition_concept_id of SNOMED vocabulary.

Do we have to change from SNOMED to MedDra in condition_Occurrence entity to resolve the issue ?

If Yes, how should we implement that?

If No, how to get MedDra data in ACHILLES?

Please let me know if there is something else to think of other than MedDRA data dictionary.

TIA
–Shweta

@Christian_Reich

Hello @Christian_Reich, @Chris_Knoll,

I checked in CONCEPT ,CONCEPT_RELATIONSHIP tables but could not find any mapping between MedDRA and SNOMED concepts.

Thanks In Advance.
-Chetan

@CSC:

They are all there, but not between every concept. Check out the CONCEPT_ANCESTOR table.

@Christian_Reich @Chris_Knoll
Any Idea why i am getting NA in ACHILLES ??

Is your concept_hierarchy table initialized? You can find the definition for this table here:

Yes, we have done it.

Ok, so now try to query the concept_hierarchy table for the concept_id of one of the concept_id’s that has NA’s for it, and see if it appears in the concept_hierarchy table.

Edit:
I looked closer at your screenshot, are all the records in that table have a null level1_concept_name and level2_concept_name, etc?

If so, you should go into the init concept_hierarchy table query, and understand why you’re not getting any matches for level’s 1-4 for the hierarchy.

For example - In our Condition_Occurrence entity we have condition_concept_id =441594 i.e for Dysphasia.

when we search for Dysphasia in the above provided code we got output as below -

Question - Do we need to put MEDDRA concept_id instead of SNOMED concept_id (=441594) in condition_concept_id and then search in provided code?

SELECT
snomed.concept_id,
snomed.concept_name AS snomed_concept_name,
CAST(‘Condition’ AS VARCHAR(20)) AS treemap,
pt_to_hlt.pt_concept_name,
hlt_to_hlgt.hlt_concept_name,
hlgt_to_soc.hlgt_concept_name,
soc.concept_name AS soc_concept_name
FROM (
SELECT
concept_id,
concept_name
FROM @vocab_schema.concept
WHERE domain_id = ‘Condition’ and concept_id =441594
) snomed
LEFT JOIN (
SELECT
c1.concept_id AS snomed_concept_id,
max(c2.concept_id) AS pt_concept_id
FROM @vocab_schema.concept c1
INNER JOIN @vocab_schema.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id
AND c1.domain_id = ‘Condition’
AND ca1.min_levels_of_separation = 1
INNER JOIN @vocab_schema.concept c2 ON ca1.ancestor_concept_id = c2.concept_id
AND c2.vocabulary_id = ‘MedDRA’
GROUP BY c1.concept_id
) snomed_to_pt ON snomed.concept_id = snomed_to_pt.snomed_concept_id
LEFT JOIN (
SELECT
c1.concept_id AS pt_concept_id,
c1.concept_name AS pt_concept_name,
max(c2.concept_id) AS hlt_concept_id
FROM @vocab_schema.concept c1
INNER JOIN @vocab_schema.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id
AND c1.vocabulary_id = ‘MedDRA’
AND ca1.min_levels_of_separation = 1
INNER JOIN @vocab_schema.concept c2 ON ca1.ancestor_concept_id = c2.concept_id
AND c2.vocabulary_id = ‘MedDRA’
GROUP BY c1.concept_id, c1.concept_name
) pt_to_hlt ON snomed_to_pt.pt_concept_id = pt_to_hlt.pt_concept_id
LEFT JOIN (
SELECT
c1.concept_id AS hlt_concept_id,
c1.concept_name AS hlt_concept_name,
max(c2.concept_id) AS hlgt_concept_id
FROM @vocab_schema.concept c1
INNER JOIN @vocab_schema.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id
AND c1.vocabulary_id = ‘MedDRA’
AND ca1.min_levels_of_separation = 1
INNER JOIN @vocab_schema.concept c2 ON ca1.ancestor_concept_id = c2.concept_id
AND c2.vocabulary_id = ‘MedDRA’
GROUP BY c1.concept_id, c1.concept_name
) hlt_to_hlgt ON pt_to_hlt.hlt_concept_id = hlt_to_hlgt.hlt_concept_id
LEFT JOIN (
SELECT
c1.concept_id AS hlgt_concept_id,
c1.concept_name AS hlgt_concept_name,
max(c2.concept_id) AS soc_concept_id
FROM @vocab_schema.concept c1
INNER JOIN @vocab_schema.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id
AND c1.vocabulary_id = ‘MedDRA’
AND ca1.min_levels_of_separation = 1
INNER JOIN @vocab_schema.concept c2 ON ca1.ancestor_concept_id = c2.concept_id
AND c2.vocabulary_id = ‘MedDRA’
GROUP BY c1.concept_id, c1.concept_name
) hlgt_to_soc ON hlt_to_hlgt.hlgt_concept_id = hlgt_to_soc.hlgt_concept_id
LEFT JOIN @vocab_schema.concept soc ON hlgt_to_soc.soc_concept_id = soc.concept_id;

Isn’t it because you haven’t downloaded MedDRA (which requires a separate license)?

You should only put standard concepts in condition_occurrence, so if the SNOMED is the standard concept, then use the SNOMED concept. The concept_hierarchy table just tries to roll it up in a friendly way.

It does appear that there’s a dependency on MedDRA vocabulary to build the concept_hierarchy. If that’s a problem, could someone suggest a standard concept hiearchy rollup? This logic for building the concept_hiearchy into 4 distinct levels is very legacy code, and while works OK for things like drug (ingredient->ATC5-ATC3-ATC1) it is showing signs of stress in the condition domain…and procedures are even more unstructured. If we could get a standard definition for these 4-levels (and, requiring 4 levels is again an old ACHILLES reporting requirement) then we could get something that will work license-free.

-Chris

Very well aware of that problem, and @ericaVoss had a Forum debate a while ago about that with some ideas. Since we have this new warm relationship with SNOMED we will bring that up. Will work both in Conditions and Procedures (where we have to finish the hierarchy). Devices - all bets are off for now.

@Chris_Knoll @Christian_Reich

We have around 14,500 Standard (SNOMED) condition_concept_id in condition_occurrence table. These concept_id’s are also present in results.concept_hierarchy.

But, the values for level1_concept_name,level2_concept_name,level3_concept_name,level4_concept_name columns are NULL even after importing the data for MedDRA.

Strange part is level1_concept_name,level2_concept_name,level3_concept_name,level4_concept_name have NOT NULL values for treemap=‘Drug’ but are NULL for treemap=‘Condition’

We have already imported MedDRA vocabulary in Standard Vocabularies and executed init concept_hierarchy script.

Please let me know what can be done to have data populated for ‘Condition’ treemap too.

After importing the MedDRA concepts into vocabulary, did you re-build the concept_hierarchy table? This query is defined here. To test it, you can just execute the portion of the query that builds the ‘Condition’ part of the concept_hiearchy for one of your SNOMED concepts to verify that the concept hierarchy records look right. If it looks like the query is pulling in the MedDRA terms, then truncate the table and rebuild it with the above query.

For your convenience, here’s the Condition treemap portion of the query you can modify to just look for the single SNOMED concept:

SELECT
	snomed.concept_id,
  snomed.concept_name AS snomed_concept_name,
	CAST('Condition' AS VARCHAR(20)) AS treemap,
	pt_to_hlt.pt_concept_name,
	hlt_to_hlgt.hlt_concept_name,
	hlgt_to_soc.hlgt_concept_name,
	soc.concept_name AS soc_concept_name
FROM (
	SELECT
		concept_id,
		concept_name
	FROM @vocab_schema.concept
	WHERE domain_id = 'Condition'
        -- add a condition HERE for the specific snomed concept you wish to test.
) snomed
LEFT JOIN (
	SELECT
		c1.concept_id      AS snomed_concept_id,
		max(c2.concept_id) AS pt_concept_id
	FROM @vocab_schema.concept c1
	INNER JOIN @vocab_schema.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id
		AND c1.domain_id = 'Condition'
		AND ca1.min_levels_of_separation = 1
	INNER JOIN @vocab_schema.concept c2 ON ca1.ancestor_concept_id = c2.concept_id
		AND c2.vocabulary_id = 'MedDRA'
	GROUP BY c1.concept_id
) snomed_to_pt ON snomed.concept_id = snomed_to_pt.snomed_concept_id
LEFT JOIN (
	SELECT
		c1.concept_id      AS pt_concept_id,
		c1.concept_name    AS pt_concept_name,
		max(c2.concept_id) AS hlt_concept_id
	FROM @vocab_schema.concept c1
	INNER JOIN @vocab_schema.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id
		AND c1.vocabulary_id = 'MedDRA'
		AND ca1.min_levels_of_separation = 1
	INNER JOIN @vocab_schema.concept c2 ON ca1.ancestor_concept_id = c2.concept_id
		AND c2.vocabulary_id = 'MedDRA'
	GROUP BY c1.concept_id, c1.concept_name
) pt_to_hlt ON snomed_to_pt.pt_concept_id = pt_to_hlt.pt_concept_id
LEFT JOIN (
	SELECT
		c1.concept_id      AS hlt_concept_id,
		c1.concept_name    AS hlt_concept_name,
		max(c2.concept_id) AS hlgt_concept_id
	FROM @vocab_schema.concept c1
	INNER JOIN @vocab_schema.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id
		AND c1.vocabulary_id = 'MedDRA'
		AND ca1.min_levels_of_separation = 1
	INNER JOIN @vocab_schema.concept c2 ON ca1.ancestor_concept_id = c2.concept_id
		AND c2.vocabulary_id = 'MedDRA'
	GROUP BY c1.concept_id, c1.concept_name
) hlt_to_hlgt ON pt_to_hlt.hlt_concept_id = hlt_to_hlgt.hlt_concept_id
LEFT JOIN (
	SELECT
		c1.concept_id      AS hlgt_concept_id,
		c1.concept_name    AS hlgt_concept_name,
		max(c2.concept_id) AS soc_concept_id
	FROM @vocab_schema.concept c1
	INNER JOIN @vocab_schema.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id
		AND c1.vocabulary_id = 'MedDRA'
		AND ca1.min_levels_of_separation = 1
	INNER JOIN @vocab_schema.concept c2 ON ca1.ancestor_concept_id = c2.concept_id
		AND c2.vocabulary_id = 'MedDRA'
	GROUP BY c1.concept_id, c1.concept_name
) hlgt_to_soc ON hlt_to_hlgt.hlgt_concept_id = hlgt_to_soc.hlgt_concept_id
LEFT JOIN @vocab_schema.concept soc ON hlgt_to_soc.soc_concept_id = soc.concept_id;

This isn’t strange: the ATC rollup for the ‘Drug’ treemap is not built from a special vocabulary (ATC is available by default). MedDRA is special, so after inserting MedDRA into the vocab you’ll have to rebuild the concept_hierarchy table.

t