Drug_exposure

Reading from Synthea medications.csv

Destination Field Source field Logic Comment field
drug_exposure_id Autogenerated
person_id patient Map by mapping person.person_source_value to patient. Find person.person_id by mapping medications.patient to person.person_source_value.
drug_concept_id code Use code to lookup target_concept_id in CTE_TARGET_VOCAB_MAP: select ctvm.target_concept_id from medications m join cte_target_vocab_map ctvm on ctvm.source_code = m.code and ctvm.target_domain_id = ‘Drug’ and ctvm.target_vocabulary_id = ‘RxNorm’ and ctvm.target_standard_concept = ‘S’ and ctvm.target_invalid_reason is NULL
drug_exposure_start_date start
drug_exposure_start_datetime start Use 00:00:00 as the time.
drug_exposure_end_date stop start (coalesce(stop,start))
drug_exposure_end_datetime stop start coalesce(stop,start) Use 00:00:00 as the time.
verbatim_end_date stop
drug_type_concept_id Use the concept_id 32838 (EHR prescription).
stop_reason
refills
quantity
days_supply start stop coalesce(datediff(day,start,stop),0)
sig
route_concept_id Set to 0 for all records
lot_number Set to 0 for all records
provider_id encounters.provider
visit_occurrence_id encounter Lookup visit_occurrence_id using encounter, joining to temp table defined in final_visit_ids.sql.
visit_detail_id encounter Lookup visit_occurrence_id using encounter, joining to temp table defined in final_visit_ids.sql and add 1000000.
drug_source_value code
drug_source_concept_id code Use code to lookup target_concept_id in CTE_SOURCE_VOCAB_MAP: select csvm.source_concept_id from medications m join cte_source_vocab_map csvm on cvm.source_code = m.code and cvm.source_vocabulary_id = ‘RxNorm’
route_source_value
dose_unit_source_value

Reading from Synthea.immunizations.csv

Destination Field Source field Logic Comment field
drug_exposure_id
person_id patient Map by mapping person.person_source_value to patient. Find person.person_id by mapping immunizations.patient to person.person_source_value.
drug_concept_id code Use code to lookup target_concept_id in CTE_TARGET_VOCAB_MAP: select ctvm.target_concept_id from immunizations i join cte_target_vocab_map ctvm on ctvm.source_code = i.code and ctvm.target_domain_id = ‘Drug’ and ctvm.target_vocabulary_id = ‘CVX’ and ctvm.target_standard_concept = ‘S’ and ctvm.target_invalid_reason is NULL
drug_exposure_start_date date
drug_exposure_start_datetime date Use 00:00:00 as the time.
drug_exposure_end_date date
drug_exposure_end_datetime date Use 00:00:00 as the time.
verbatim_end_date date
drug_type_concept_id Use the concept_id 32827 (EHR encounter record).
stop_reason cast(null as varchar)
refills Set to 0 for all records
quantity Set to 0 for all record
days_supply Set to 0 for all record
sig cast(null as varchar)
route_concept_id Set to 0 for all record
lot_number Set to 0 for all record
provider_id encounters.provider
visit_occurrence_id encounter Lookup visit_occurrence_id using encounter, joining to temp table defined in final_visit_ids.sql.
visit_detail_id encounter Lookup visit_occurrence_id using encounter, joining to temp table defined in final_visit_ids.sql and add 1000000.
drug_source_value code
drug_source_concept_id code Use code to lookup target_concept_id in CTE_SOURCE_VOCAB_MAP: select csvm.source_concept_id from immunizations i join cte_source_vocab_map csvm on csvm.source_code = i.code and csvm.source_vocabulary_id = ‘CVX’
route_source_value cast(null as varchar)
dose_unit_source_value cast(null as varchar)