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

Cost

Reading from Synthea tables procedures.csv

| Destination Field | Source field | Logic | Comment field | | --- | --- | --- | --- | | cost_id | |Autogenerated | | | cost_event_id | code |Map by mapping the cdm.procedure_occurrence.procedure_source_value to procedures.code, map to a unique visit_occurrence_id and and pull in the associated visit procedure_occurrence_id | | | cost_domain_id | | Set to 'Procedure' for all records | | | cost_type_concept_id | | Set to 32814 (Cost Record) for all records | | | currency_concept_id | | Set to 44818668 (United States Dollars) for all records | | | total_charge | | encounters.total_claim_cost + procedures.base_cost | | | total_cost | | encounters.total_claim_cost + procedures.base_cost | | | total_paid | | encounters.payer_coverage + procedures.base_cost | | | paid_by_payer | | encounters.payer_coverage | | | paid_by_patient | | encounters.total_claim_cost + procedures.base_cost - encounters.payer_coverage | | | paid_patient_copay| | cast(null as numeric) | | | paid_patient_coinsurance| | cast(null as numeric) | | | paid_patient_deductible| | cast(null as numeric) | | | paid_by_primary| | cast(null as numeric) | | | paid_ingredient_cost| | cast(null as numeric) | | | paid_dispensing_fee| | cast(null as numeric) | | | payer_plan_period_id| | Map to cdm.payer_plan_period.payer_plan_period_id using person_id and where payer_plan_period_start_date <= procedure_date and payer_plan_period_end_date >= procedure_date | | | amount_allowed| | cast(null as numeric) | | | revenue_code_concept_id| | Set to 0 for all records. | | | revenue_code_source_value| | Set to 'UNKNOWN/UKNOWN' for all records. | | | drg_concept_id| | Set to 0 for all records. | | | drg_source_value| | Set to '000' for all records. | |

Reading from Synthea tables immunizations.csv

| Destination Field | Source field | Logic | Comment field | | --- | --- | --- | --- | | cost_id | |Autogenerated | | | cost_event_id | code |Map by mapping the cdm.drug_exposure.drug_source_value to immunizations.code, map to a unique visit_occurrence_id and and pull in the associated visit drug_exposure_id | | | cost_domain_id | | Set to 'Drug' for all records | | | cost_type_concept_id | | Set to 32814 (Cost Record) for all records | | | currency_concept_id | | Set to 44818668 (United States Dollars) for all records | | | total_charge | | encounters.total_claim_cost + immunizatons.base_cost | | | total_cost | | encounters.total_claim_cost + immunizatons.base_cost | | | total_paid | | encounters.payer_coverage + immunizatons.base_cost | | | paid_by_payer | | encounters.payer_coverage | | | paid_by_patient | | encounters.total_claim_cost + immunizatons.base_cost - encounters.payer_coverage | | | paid_patient_copay| | cast(null as numeric) | | | paid_patient_coinsurance| | cast(null as numeric) | | | paid_patient_deductible| | cast(null as numeric) | | | paid_by_primary| | cast(null as numeric) | | | paid_ingredient_cost| | cast(null as numeric) | | | paid_dispensing_fee| | cast(null as numeric) | | | payer_plan_period_id| | Map to cdm.payer_plan_period.payer_plan_period_id using person_id and where payer_plan_period_start_date <= drug_exposure_start_date and payer_plan_period_end_date >= drug_exposure_start_date | | | amount_allowed| | cast(null as numeric) | | | revenue_code_concept_id| | Set to 0 for all records. | | | revenue_code_source_value| | Set to 'UNKNOWN/UKNOWN' for all records. | | | drg_concept_id| | Set to 0 for all records. | | | drg_source_value| | Set to '000' for all records. | |

Reading from Synthea tables medications.csv

| Destination Field | Source field | Logic | Comment field | | --- | --- | --- | --- | | cost_id | |Autogenerated | | | cost_event_id | code |Map by mapping the cdm.drug_exposure.drug_source_value to immunizations.code, map to a unique visit_occurrence_id and and pull in the associated visit drug_exposure_id | | | cost_domain_id | | Set to 'Drug' for all records | | | cost_type_concept_id | | Set to 32814 (Cost Record) for all records | | | currency_concept_id | | Set to 44818668 (United States Dollars) for all records | | | total_charge | | encounters.total_claim_cost + medications.base_cost | | | total_cost | | encounters.total_claim_cost + medications.base_cost | | | total_paid | | encounters.payer_coverage + medications.base_cost | | | paid_by_payer | | encounters.payer_coverage | | | paid_by_patient | | encounters.total_claim_cost + medications.base_cost - encounters.payer_coverage | | | paid_patient_copay| | cast(null as numeric) | | | paid_patient_coinsurance| | cast(null as numeric) | | | paid_patient_deductible| | cast(null as numeric) | | | paid_by_primary| | cast(null as numeric) | | | paid_ingredient_cost| | cast(null as numeric) | | | paid_dispensing_fee| | cast(null as numeric) | | | payer_plan_period_id| | Map to cdm.payer_plan_period.payer_plan_period_id using person_id and where payer_plan_period_start_date <= drug_exposure_start_date and payer_plan_period_end_date >= drug_exposure_start_date | | | amount_allowed| | cast(null as numeric) | | | revenue_code_concept_id| | Set to 0 for all records. | | | revenue_code_source_value| | Set to 'UNKNOWN/UKNOWN' for all records. | | | drg_concept_id| | Set to 0 for all records. | | | drg_source_value| | Set to '000' for all records. | |

Reading from Synthea tables conditions.csv, claims_transations.csv and claims.csv (v3.0.0)

• Filter where claims_transactions.transfertype in ('1','p')
• Group by condition_occurrence_id, payer_plan_period_id
• Payer_paid = coalesce(sum(case when claims_transactions.transfertype = '1' then claims_transactions.amount end),0)
• Patient_paid = coalesce(sum(case when claims_transactions.transfertype = 'p' then claims_transactions.amount end),0)

| Destination Field | Source field | Logic | Comment field | | --- | --- | --- | --- | | cost_id | |Autogenerated | | | cost_event_id | code |Map by mapping the cdm.condition_occurrence.condition_source_value to conditions.code, map to a unique visit_occurrence_id and pull in the associated visit condition_occurrence_id | | | cost_domain_id | | Set to 'Condition' for all records | | | cost_type_concept_id | | Set to 32814 (Cost Record) for all records | | | currency_concept_id | | Set to 44818668 (United States Dollars) for all records | | | total_charge | | payer_paid+patient_paid | | | total_cost | | payer_paid+patient_paid | | | total_paid | | epayer_paid+patient_paid | | | paid_by_payer | | payer_paid | | | paid_by_patient | | patient_paid | | | paid_patient_copay| | cast(null as numeric) | | | paid_patient_coinsurance| | cast(null as numeric) | | | paid_patient_deductible| | cast(null as numeric) | | | paid_by_primary| |payer_paid | | | paid_ingredient_cost| | cast(null as numeric) | | | paid_dispensing_fee| | cast(null as numeric) | | | payer_plan_period_id| | Map to cdm.payer_plan_period.payer_plan_period_id using person_id and where payer_plan_period_start_date <= condition_start_date and payer_plan_period_end_date >= condition_start_date | | | amount_allowed| | cast(null as numeric) | | | revenue_code_concept_id| | Set to 0 for all records. | | | revenue_code_source_value| | Set to 'UNKNOWN/UKNOWN' for all records. | | | drg_concept_id| | Set to 0 for all records. | | | drg_source_value| | Set to '000' for all records. | |

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