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

Measurement

Reading from procedures.csv

| Destination Field | Source field | Logic | Comment field | | --- | --- | --- | --- | | measurement_id | | Autogenerated | | | person_id | patient | Lookup in the person table: map by mapping person.person_source_value to patient. | | | measurement_concept_id | code | Use code to lookup target_concept_id in CTE_TARGET_VOCAB_MAP: select ctvm.target_concept_id from procedures pr join cte_target_vocab_map ctvm on ctvm.source_code = pr.code and ctvm.target_domain_id = 'Measurement' and ctvm.target_vocabulary_id = 'SNOMED' and ctvm.target_standard_concept = 'S' and ctvm.target_invalid_reason is NULL | | | measurement_date | date (v2.7.0) start (v3.0.0) | | | | measurement_datetime | date (v2.7.0) start (v3.0.0) | Use 00:00:00 as the time. | | | measurement_time | date (v2.7.0) start (v3.0.0) | | | | measurement_type_concept_id | | | Use concept_id 32827 (EHR encounter record) for all records | | operator_concept_id | | Set to 0 for all records | | | value_as_number | |cast(null as float) | | | value_as_concept_id | | Set to 0 for all records | | | unit_concept_id | |Set to 0 for all records | | | range_low | | cast(null as float) | | | range_high | | cast(null as float) | | | 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. | | | measurement_source_value | code | | | | measurement_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 procedures pr on csvm.source_code = pr.code and csvm.source_vocabulary_id = 'SNOMED' | | | unit_source_value | |cast(null as varchar) | | | value_source_value | | cast(null as varchar) | |

Reading from observations.csv

| Destination Field | Source field | Logic | Comment field | | --- | --- | --- | --- | | measurement_id | |Autogenerated | | | 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. | | | measurement_concept_id | code | Use code to lookup target_concept_id in CTE_TARGET_VOCAB_MAP: select ctvm.target_concept_id from observations o join cte_target_vocab_map ctvm on ctvm.source_code = o.code and ctvm.target_domain_id = 'Measurement' and ctvm.target_standard_concept = 'S' and ctvm.target_invalid_reason is NULL | | | measurement_date | date | | | | measurement_datetime | date | Use 00:00:00 as time. | | | measurement_time | | | | | measurement_type_concept_id | | | Use concept_id 32827 (EHR encounter record) for all records | | operator_concept_id | | Set as 0 for all records | | | value_as_number | value | | | | value_as_concept_id | value | units | Use code to lookup target_concept_id in CTE_TARGET_VOCAB_MAP: select ctvm.target_concept_id from observations o join cte_target_vocab_map ctvm on ctvm.source_code = o.value and ctvm.target_domain_id = 'Meas value' and ctvm.target_standard_concept = 'S' and ctvm.target_invalid_reason is NULL | | | unit_concept_id | units | Use code to lookup target_concept_id in CTE_TARGET_VOCAB_MAP: select ctvm.target_concept_id from observations o join cte_target_vocab_map ctvm on ctvm.source_code = o.units and ctvm.target_vocabulary_id = 'UCUM' and ctvm.source_vocabulary_id = 'UCUM' and ctvm.target_standard_concept = 'S' and ctvm.target_invalid_reason is NULL | | | range_low | |cast(null as float) | | | range_high | | cast(null as float) | | | provider_id |encounters.provider_id | | | | visit_occurrence_id | code | Lookup visit_occurrence_id using encounter, joining to temp table defined in final_visit_ids.sql. | | | visit_detail_id | code | Lookup visit_occurrence_id using encounter, joining to temp table defined in final_visit_ids.sql and add 1000000 | | | measurement_source_value | code | | | | measurement_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 observations o on csvm.source_code = o.code and csvm.source_vocabulary_id = 'SNOMED' | | | unit_source_value | units | | | | value_source_value | value | | |

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