OHDSI Home | Forums | Wiki | Github

Retrieve concept_ids based on "Code" value

Implementers
, , , ,
#1

Hello Everyone,

I would like to get the concept_ids for my records in drug_exposure table.

Unfortunately, currently we have “ATHENA code” values (under drug_source_concept_id) of the drug_items like shown below

image
image970×299 31.4 KB

However the drug_concept_ids are marked as 0. So, I would like to get the corresponding concept_ids for all the drug_items now.

  1. Is it possible to extract the concept_ids for all the corresponding code values in an automated way/query approach? Or I only have to do it manually by referring ATHENA?

  2. Why is there a dependency between drug_source_concept_id and concept_id from concept table?

ALTER TABLE drug_exposure ADD CONSTRAINT fpk_drug_concept_s FOREIGN KEY (drug_source_concept_id) REFERENCES concept (concept_id);

Meaning when I try to create FK on drug_exposure table, it throws an error like shown below

Key (drug_source_concept_id)=(226264) is not present in table "concept".

But aren’t source_concept_ids non-standard source codes that will anyway not be present in concept table? Why do we expect them to be present (imposing FK constraint) in concept table as well? Am I missing something here. Can you correct me here ?

#2

Hi Selva,

  1. You can lookup the corresponding concept_id from the concept_relationship table, where relationship_id=‘Maps to’

  2. I think concept table is a master table of all concepts as part of the OMOP vocabulary, so both standard and non-standard concepts are included in this table

Not too sure about point 1. Hope someone can enlighten me as well :wink:

#3

@SELVA_MUTHU_KUMARAN, looks like you have a couple of questions to resolve.

  1. Why are you getting an error for concept id 226264
  2. How to get the standard concept from a source concept id

Why are you getting an error for concept id 226264? Because that concept does not exists in the OMOP concept table. I think what is going on is that what you call a drug_source_concept_id is an RxNorm code, not an OMOP concept_id. To get the OMOP concept id for the RxNorm RxCui 226264
SELECT * from concept where vocabulary_id = ‘RxNorm’ and concept_code = 226264;
This yields the OMOP concept

concept_id concept_name domain_id vocabulary_id concept_class_id standard_concept concept_code valid_start_date valid_end_date invalid_reason
19053957 Hibiscrub Drug RxNorm Brand Name 226264 1970-01-01 2099-12-31

19053957 is the value that OMOP expects to see in the drug.drug_source_concept_id. And the drug_source value would be 226264, or if you want to also maintain the description, something like ‘226264:Hibiscrub’

How to get the standard concept from a source concept id
I suggest you view the OMOP vocabulary tutorials on YouTube to get a better understanding of how to map to standard concepts. Then come back with the question about mapping to standard concepts using the Concept Relationship table.

t