OHDSI Home | Forums | Wiki | Github

SPL to RxNorm

Vocabulary Users
#1

When using the CONCEPT_RELATIONSHIP to get from a SPL to an RxNorm, I see 2 types of RELATIONSHIP_IDs:

  • Maps to
  • SPL - RxNorm

What is the difference between these relationship types?

I see cases where there is only a Maps to (although I couldn’t find this drug in DailyMed):
000b8a5a-7408-458f-a623-17bd6be9a6e6

I see cases where where only the SPL - RxNorm exists:
00699692-532a-68fd-8a51-3770a81b2208

Then there are a lot that have both:
0003458f-352a-46fa-9d99-230daa76ae29

Here is how I’m thinking about navigating from SPL --> RxNorm, just ignoring those types for now and pulling back distinct concepts.

SELECT DISTINCT c.CONCEPT_CODE AS SPL, 
	c2.CONCEPT_ID AS TARGET_CONCEPT_ID, 
	c2.CONCEPT_NAME AS TARGET_CONCEPT_NAME
FROM CONCEPT c
	JOIN CONCEPT_RELATIONSHIP cr
		ON cr.CONCEPT_ID_1 = c.CONCEPT_ID
		AND (cr.INVALID_REASON IS NULL 
		OR cr.INVALID_REASON = '')
	JOIN CONCEPT c2
		ON c2.CONCEPT_ID = cr.CONCEPT_ID_2
		AND c2.STANDARD_CONCEPT = 'S'
		AND c2.DOMAIN_ID = 'Drug'
WHERE c.VOCABULARY_ID = 'SPL'
ORDER BY c.CONCEPT_CODE

Tagging @Christian_Reich.

#2

My understanding was Maps To always takes you to a standard concept. The SPL-RxNorm relationship may just point you from one concept to another but not for standard concept mapping purposes…more for informational purposes (I believe there’s some snomed->medra relationships out there that work like this). For the ones where there was only SP-RxNorm, was the target concept standard? And in the case of Maps To, were any non-standard?

-Chris

#3

@Chris_Knoll - the SPL - RxNorm and Maps to seemed to map to standards and you can see in my query I force it anyway. In general it looked like the SPL - RxNorm was a more complete mapping but as I highlighted a SPL - RxNorm mapping doesn’t always exist for each SPL.

Right now I’m taking the approach of using both relationship types.

#4

@ericaVoss:

Don’t use either. Use concept_ancestor. Here is how it works:

When the FDA approves “a drug”, they actually approve one or more drug products (ingredient-strength-form combinations). They are all under the same SPL. That is why we turned SPLs into classifications. Otherwise we would have had a one to many mapping, and in some cases a one to really many mappings.

1 Like
#5

@Christian_Reich - Thanks! I’ll give that a try.

I knew I should have asked! :smile:

#6

Well, it should be clear from a well-written comprehensive vocab documentation, which is still missing!

#7

@Christian_Reich - just to be clear, when discussing the "one to many mapping"s above you mean if I just use the CONCEPT_RELATIONSHIP I wouldn’t get all the relationships in 1 hop, therefore the CONCEPT_ANCESTOR is better to use because you pull back everything at once.

Just to complete the thread, here is how the lookup should be done:

SELECT DISTINCT c.CONCEPT_CODE AS SPL, 
	c2.CONCEPT_ID AS TARGET_CONCEPT_ID, 
	c2.CONCEPT_NAME AS TARGET_CONCEPT_NAME, c2.*
FROM VOCABULARY.dbo.CONCEPT c
	JOIN VOCABULARY.dbo.CONCEPT_ANCESTOR ca
		ON ca.ANCESTOR_CONCEPT_ID = c.CONCEPT_ID
	JOIN VOCABULARY.dbo.CONCEPT c2
		ON c2.CONCEPT_ID = ca.DESCENDANT_CONCEPT_ID
		AND c2.STANDARD_CONCEPT = 'S'
		AND c2.DOMAIN_ID = 'Drug'
WHERE c.VOCABULARY_ID = 'SPL'
1 Like
t