OHDSI Home | Forums | Wiki | Github

How to get concept for _event_field_concept_id

CDM Builders
#1

Version 6.0 cost.cost_event_field_concept_id, observation.obs_event_field_concept_id and note.note_event_field_concept_id require the concept that represents the CDM Version/Table/column that the cost, observation or note refers to. For example the cost record for a drug needs the concept for drug_exposure.drug_exposure_id (for the specific CDM version) for the cost_event_field_concept_id.

Here is a query to get the concept for the CDM version 6.0.0 drug_exposure.drug_exposure_id.

         SELECT column_concept.concept_id 
           FROM concept column_concept
             JOIN concept_relationship cr ON column_concept.concept_id = cr.concept_id_1 
                                         AND cr.invalid_reason IS NULL 
                                         AND cr.relationship_id = 'Contained in version'
             JOIN concept version_concept ON version_concept.concept_id = cr.concept_id_2 
                                         AND version_concept.concept_code = 'CDM v6.0.0'
         WHERE column_concept.concept_class_id = 'Field' 
           AND column_concept.standard_concept = 'S' 
           AND column_concept.concept_name = 'drug_exposure.drug_exposure_id'

@clairblacketer we need to add this to ETL conventions.

1 Like
t