drug_exposure missing the route_concept_id ?

[javier-gracia-tabuenca-tuni]

Trying to create a cohort in atlas with a list of ATC code we wanted to filter by route (oral) but found no subjects.

This is bcs Atlas filters using the field drug_exposure.route_concept_id which is empty.

Should it be fill during ETL with the info from the drug ??

And does it happen for other columns that we are missing ?

[shanmukhasampath]

Hi Javier, as of now the drug_exposure.route_concept_id is inserted as 0 as of now.

Yes, the route information needs to be filled out in the route_concept_id and route_source_value but Atlas only uses route_concept_id. To fix this we need the following steps needs to be done

1. We need to map the route source value 29 of them to the standard concept id under domain Route
2. Add these mappings to the VNRfi vocabulary
3. Update the fg_codes_info table with route mappings
4. Change the ETL drug_exposure table with this information

Then user will be able to filter the drug by administration route properly.

[javier-gracia-tabuenca-tuni]

i was thinking

cannot this be done at the ETL level?

where the route is found from the rxnorm selected, using the concept_relationship table ?

[shanmukhasampath]

Hi Javier,

This does not work using one simple addition to the SQL to get information from concept_relationship table. Here is the below SQL I tried out to get Has route relationship for every standard drug_concept_id. None of them work.

LEFT JOIN `etl_sam_dev_omop.concept_relationship` AS cr
ON drug_concept_id = cr.concept_id_1 AND relationship_id = 'Has route'

The way to solve this is to do above steps I have mentioned.