OHDSI Home | Forums | Wiki | Github

NDCs marked invalid with no standard concept mapping

I have a list of NDCs that have a concept marked as invalid (have valid_end_date). These concepts do not have a standard concept mapping to an RXNorm concept.

2 questions:
Are these invalid concepts without standard mappings because there was never a standard mapping?

Would it ever be the case that a concept to be expired/marked invalid in the future lose its standard concept mapping because it’s invalid? In other words, I have retrospective data with NDCs that may be marked invalid at some point in the future and has a mapping to a standard concept. Would the concept relationship lose the “maps to” relationship because it becomes invalid?

Here’s the list of NDCs that don’t have standard concept mapping and are invalid:

Here are more that are valid, but also don’t have a standard concept mapping:

True, we always preserve ‘Maps to’ link even though the concept itself becomes invalid.
Some of your NDCs had valid ‘Maps to’ links before.

The possible reasons why ‘Maps to’ link from NDC concept becomes invalid:

  • deprecation of a target RxNorm concept by the source (it’s not valid anymore and subsequently non-Standard);
  • NDC source doesn’t provide the mapping anymore (this small pool will be reviewed and restored soon).

Also, it’s true that a substantial NDC portion is not mapped to Standard.
We’re collecting the codes of interest here to perform and add these mappings manually.

That’s super helpful!!!

You mentioned that some the NDCs I listed had standard mappings in the past (“Maps to” relationship). Is there a way to know what those were?

I can see problems in the future where code looking through retrospective data expecting to see a standard mapping, but it disappears later. It would make sense if possible to write code that would be able to catch these instances.

Sure. Try this:


FROM concept c
JOIN concept_relationship cr
ON c.concept_id = cr.concept_id_1
AND relationship_id = ‘Maps to’
AND cr.invalid_reason IS NOT NULL
JOIN concept cc
ON cc.concept_id = cr.concept_id_2
WHERE c.concept_id IN

If target RxNorm concept becomes invalid being mapped by itself, the initial NDC will inherit this.

The mappings disappeared from the source are being reviewed and recovered by the vocabulary team.

Please do not forget that we also need to keep a full audit trail that will enable the tracking of the original terms that were used In the beginning, when the original data were generated. We do not want to move forward damaged goods.

I got no results. I changed the joins to left joins, and shows the concepts with no records to join in in the relationship table.
I looked in Athena as well, and I don’t see any relationship. It makes me assume that there was never a mapping for these concepts marked invalid. Or would it be possible my vocabulary tables don’t have all the data? I do have the NDC, RxNorm, and RxNorm Extension vocabulary sets in my local tables.

Oops, we do not export invalid historical relationships anymore.
Why do you need them?

You are totally right. We need a regression tool for cohort definitions. We could do that for the entire corpus of data, but usually folks don’t care about some obscure NDCs falling off until - they do care. And that is when they have a definition they want to keep using over time.

But this being Open Source: if you want something and it is not there - it is your job, @mjg. :slight_smile: If you can, please write and publish. Folks will appreciate that.

1 Like

As vocabularies are updated from time to time, it’s important to keep the concept tables up to date. There are two things I’m now assuming when I download updated vocabularies:

  1. New concepts are added to the vocabulary along with appropriate relationships, including “maps to” relationships where appropriate.
  2. (Based on your message) Any “maps to” relationship that has become invalid because the target concept has become “not standard” (for lack of better term), those relationships will no longer be part of the concept_relationship table.

The situation is when I have retrospective data and code that is looking for a “maps to” relationship for a non-standard concept so I can pull in the standard concept for harmonization of my data to help further analysis. I later update my concept tables because I got a notification from Athena that there is an update in the vocabulary. I find out after the update, the “maps to” relationship is gone when my code breaks and throws an error. The analysis is now broken or a change needs to happen in the analysis which becomes inconsistent.

2 things I could see happen to fix this.

  1. Keep the relationships in concept_relationship, and put a valid_end_date with a invalid_reason.
    I did a query on my current concept_relationship table:
    SELECT * FROM concept_relationship cr WHERE cr.invalid_reason IS NOT NULL;
    I found 0 records.
  2. A new “maps to” relationship is created with a new valid_start_date.

It would seem to make sense that both should happen. The 1st point would allow for consistent analysis. The 2nd point would allow for continuous harmonization of concepts.

Is there a history of the all the vocabularies? The only way I can think of keeping a record of historical vocabularies is to continually download vocabularies from Athena when I am notified of changes and creating historical schemas of the concept tables on my database. Not the most sustainable process because it’s quite manual.
Also, I’m not sure of any other tool besides the Athena website to obtain the vocabularies.

Wait, @mjg. Why do you want to track the “Maps to” relationships? Look: if they were wrong they should be deprecated. If the target concept changed the Maps to will point to the right one.

What is your use case?

Curious: Are people keeping track of all data mapping variations while maintaining a version controlled unified system with as date mapping?

I really don’t need to track “Maps to” relationships? I want to know if I can reliably use the relationship table in ongoing processes to find non-standard to standard mappings.

Is there a case in which a “Maps to” relationship disappears because it becomes “not valid” (lack of a better term)? And from what @Alexdavv has said:

That makes me believe that my processes will break in the future if I do not have an expected relationship from a nonstandard concept (in my example, an NDC code) to a “what used to be standard” concept (in my example, some RxNorm concept).

As far as I can tell based on the vocabularies I’ve downloaded from Athena, the concept_relationship table has a valid_end_date and an invalid_reason column that isn’t being used.

I’m building sustainable, consistent processes transforming data into OMOP for my organization and the question that has come up is “How can I be sure NDCs have a standard concept mapping now and in the future?”
I can take that question and make it generic “For a non-standard concept, will there always be a non-standard concept to standard concept mapping now and in the future?”

If what you say is true, then some of the NDC I have put in my original post have been identified as ones that don’t follow your statement. i.e. They used to have “… valid ‘Maps to’ links before.”

My post is more to understand the current process in how the vocabulary is updated. That way I may prepare my transformation process accordingly.

They might, but currently that is probably an overkill, and I guess few people do. The observational data have a lot bigger quality problems than a couple erroneous mapping relationships that got fixed over time.

You can. You don’t have to wade through obsolete ones. They are obsolete for a reason.

Why so? You should map all you can. The non-standards that have no mapping need to be mapped to concept_id=0. There are always some of those. We are working on the NDCs. It is getting better and better, but of course NDCs are a mess, and nobody has figured them all out.

Correct. They are all there, but no longer distributed. Because there is no use case to have obsolete relationships, which otherwise majorly blow up the download size.

You cannot. Unfortunately. All you can do is to submit missing mappings and wrong mappings, and the Vocab team will fix them. Together, we will make it better and better.

No. Some source codes are really junk. Their only correct mapping is to concept_id=0.

@Christian_Reich That makes a lot of sense to me!

The only thing I would argue is having the obsolete relationships would still be helpful in the context of consistency. Although the download size would increase, analysis that may have used an obsolete mapping would break since it’s nonexistent. There would be no indication that something has gone obsolete either.

This does give me things to think about! :smiley:

Thanks for the help @Christian_Reich and @Alexdavv

Colorado shares the same concern about obsolete mappings breaking existing reports re-run on a regular basis.

1 Like

Chiming in here. Obsolete mappings are important for multi-year historical datasets. It may be that an NDC is currently obsolete, but if it was a valid NDC 10 years ago I still want to be able to map it to an RxNorm