Link

Drug_exposure

Reading from Synthea medications.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 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    
drug_exposure_end_datetime stop start Use 00:00:00 as the time.  
verbatim_end_date stop    
drug_type_concept_id     Use the concept_id 581452 for all records from the immunizations and conditions tables and concept_id 38000177 for all records from the medications table.
stop_reason      
refills      
quantity      
days_supply start stop    
sig      
route_concept_id      
lot_number      
provider_id      
visit_occurrence_id encounter Lookup visit_occurrence_id using encounter, joining to temp table defined in AllVisitTable.sql.  
visit_detail_id      
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 581452 for all records from the immunizations and conditions tables and concept_id 38000177 for all records from the medications table.
stop_reason      
refills      
quantity      
days_supply      
sig      
route_concept_id      
lot_number      
provider_id      
visit_occurrence_id encounter Lookup visit_occurrence_id using encounter, joining to temp table defined in AllVisitTable.sql.  
visit_detail_id      
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      
dose_unit_source_value      

Reading from Synthea table conditions.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 encouters.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 conditions c join cte_target_vocab_map ctvm on ctvm.source_code = c.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    
drug_exposure_end_date stop    
drug_exposure_end_datetime stop    
verbatim_end_date stop    
drug_type_concept_id     Use the concept_id 581452 for all records from the immunizations and conditions tables and concept_id 38000177 for all records from the medications table.
stop_reason      
refills      
quantity      
days_supply      
sig      
route_concept_id      
lot_number      
provider_id      
visit_occurrence_id encounter Lookup visit_occurrence_id using encounter, joining to temp table defined in AllVisitTable.sql.  
visit_detail_id      
drug_source_value code    
drug_source_concept_id code Use code to lookup source_concept_id in CTE_SOURCE_VOCAB_MAP: select csvm.source_concept_id from cte_source_vocab_map csvm join conditions c on csvm.source_code = c.code and csvm.source_vocabulary_id = ‘SNOMED’  
route_source_value      
dose_unit_source_value