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 encounters.patient to person.person_source_value. | |
| drug_concept_id | code | Use code to lookup target_concept_id in SOURCE_TO_STANDARD_MAP: select v.target_concept_id from medications m join source_to_standard_vocab_map v on v.source_code = m.code and v.target_domain_id = ‘Drug’ and v.target_standard_concept = ‘S’ and v.source_vocabulary_id = ‘NDC’ | |
| drug_exposure_start_date | start | ||
| drug_exposure_start_datetime | start | ||
| drug_exposure_end_date | stop | ||
| drug_exposure_end_datetime | stop | ||
| verbatim_end_date | |||
| drug_type_concept_id | |||
| 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 SOURCE_TO_SOURCE_MAP: select v.source_concept_id from medications m join source_to_source_vocab_map v on v.source_code = m.code and v.source_vocabulary_id = ‘NDC’ | |
| 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 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 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’ | |
| drug_exposure_start_date | date | ||
| drug_exposure_start_datetime | date | ||
| drug_exposure_end_date | date | ||
| drug_exposure_end_datetime | date | ||
| verbatim_end_date | date | ||
| drug_type_concept_id | |||
| 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’ | |
| 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 | |||
| 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 = ‘RxNorm’ | |
| route_source_value | |||
| dose_unit_source_value |