In OHDSI/ETL-Synthea: A Builder for Converting the Synthea Data to the OMOP CDM

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) | | |

OHDSI/ETL-Synthea documentation built on Feb. 24, 2025, 3:48 a.m.