drug_exposure.Rmd| 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 |
| 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) |