stem_table
Reading From
Reading from basedata.csv
The record_source_value is used to link the events events in the stem_table (e.g. observation and measurement records) to episodes. This variable is not included in OMOP CDM v6 + oncology extensions and should be removed from the episode table after all mappings are finished.
For each person in the dataset, map the Condition Occurrence concept “Carcinoma of prostate” - 4116087 The source_appendix section of he mapping document provides more information about the (semantic) mappings.
Inclusion/Exclusion criteria
No filters applied
| Destination Field | Source field | Comment field |
|---|---|---|
| domain_id | ||
| id | Auto-increment | |
| person_id | p_id | |
| visit_occurrence_id | p_id | Concatenate p_id-“basedata”-visit_type and use visit_occurrence lookup to get visit_occurrence.visit_occurrence_id.Visit type is either “standard” or “mri”. When MRI is taken (mri_taken.0 == 1) and variable starts with “mri_”, visit is “mri”. Else visit is “standard” |
| concept_id | psa prostatic_vol dre num_cores* num_cores_pc* gleason* free_psa phi charlson length weight biopt_prob_type biopt_infection biopt_inf_urine_culture biopt_inf_urine_bacterium biopt_inf_hospitalisation biopt_inf_outcome biopt_hematuria biopt_hemospermia biopt_pain biopt_route biopt_max_cancer_score_lenght mri_taken.0 mri_lesions.0 mri_suspected_number.0 mri_pirads_* mri_largest_dia_* mri_location_* mri_location_free_* mri_progrssion_lesions.0 mri_targeted_biopsy.0 mri_targeted_cores.0 mri_targeted_taken0 mri_targeted_gleason* mri_lesion_positive.0 mri_method_used.0 mri_prostate_volume_method.0 first_500 | Each person has a Condition Occurrence record of 4116087 - Carcinoma of prostate (see inclusion of PRIAS, low-grade form, non-aggresive) From variable_mapping or variable_value_mapping. |
| value_as_number | psa prostatic_vol num_cores* num_cores_pc* free_psa phi charlson length weight biopt_inf_hospitalization_days biopt_max_cancer_score_lenght mri_suspected_number.0 mri_largest_dia_* mri_targeted_cores.0 mri_targeted_taken.0 mri_lesions_positive.0 mri_prostate_volume.0 mri_progrssion_lesions.0 mri_pirads* | |
| unit_concept_id | psa prostatic_vol free_psa length weight biopt_max_cancer_score_lenght mri_prostate_volume.0 biopt_inf_hospitalisation_days mri_largest_dia_* | From unit_mapping. Semantic mappings (unit mappings) in source_appendix |
| value_as_concept_id | gleason* biopt_prob_type biopt_infection biopt_inf_urine_culture biopt_inf_urine_bacterium mri_location_* mri_location_free_* mri_targeted_gleason* | From value_mapping. Check source_appendix for value mappings. |
| visit_detail_id | ||
| provider_id | ||
| source_value | psa prostatic_vol dre num_cores* num_cores_pc* gleason* free_psa phi charlson length weight biopt_prob_type biopt_infection biopt_inf_urine_culture biopt_inf_urine_bacterium biopt_inf_hospitalisation biopt_inf_outcome biopt_hematuria biopt_hemospermia biopt_pain biopt_route biopt_max_cancer_score_lenght mri_taken.0 mri_lesions.0 mri_suspected_number.0 mri_pirads_* mri_largest_dia_* mri_location_* mri_location_free_* mri_progrssion_lesions.0 mri_targeted_biopsy.0 mri_targeted_cores.0 mri_targeted_taken0 mri_targeted_gleason* mri_lesion_positive.0 mri_method_used.0 mri_prostate_volume_method.0 first_500-1 | |
| source_concept_id | 0 | |
| type_concept_id | Depending on vocabulary domains: Condition occurrence: 32535 - Tumor Registry Measurement: 44818701 - From Physical Examination Observation: 45905771 - Observation Recorded from a Survey Procedure occurrence: 581412 - Procedure Recorded from a Survey Specimen: 581378-EHR Detail | |
| start_date | year_diagnosis | year-07-01 |
| start_datetime | year_diagnosis | year-07-01 00:00:00 Do not store for Condition Occurrence |
| end_date | year_diagnosis | year-07-01 Do not store for Condition Occurrence |
| end_datetime | year_diagnosis | year-07-01 00:00:00 |
| verbatim_end_date | ||
| days_supply | ||
| dose_unit_source_value | ||
| lot_number | ||
| modifier_concept_id | 0 | |
| operator_concept_id | 0 | |
| modifier_concept_id | 0 | |
| modifier_source_value | ||
| quantity | ||
| range_high | ||
| range_low | ||
| refills | ||
| route_concept_id | 0 | |
| route_source_value | ||
| sig | ||
| stop_reason | ||
| unique_device_id | ||
| unit_source_value | psa prostatic_vol free_psa length weight biopt_max_cancer_score_lenght mri_prostate_volume.0 biopt_inf_hospitalisation_days mri_largest_dia_* | |
| value_as_string | ||
| value_source_value | psa prostatic_vol num_cores* num_cores_pc* free_psa phi charlson length weight biopt_inf_hospitalization_days biopt_max_cancer_score_lenght mri_suspected_number.0 mri_largest_dia_* mri_targeted_cores.0 mri_targeted_taken.0 mri_lesions_positive.0 mri_prostate_volume.0 gleason* gleason* biopt_prob_type biopt_infection biopt_inf_urine_culture biopt_inf_urine_bacterium mri_pirads_* mri_location_* mri_location_free_* mri_progrssion_lesions.0 mri_targeted_gleason* | |
| anatomic_site_concept_id | 0 | |
| disease_status_concept_id | 0 | |
| specimen_source_id | ||
| anatomic_site_source_value | ||
| disease_status_source_value | ||
| condition_status_concept_id | 0 | |
| condition_status_source_value | ||
| event_id | ||
| event_field_concept_id | ||
| value_as_datetime | ||
| qualifier_concept_id | 0 | |
| qualifier_source_value | ||
| record_source_value | p_id | Concatenate p_id-“basedata”-variable (e.g. 1-basedata_mri_pirads_1.0) |
Reading from fulong.csv
Most variables map the same as basedata variables.
The record_source_value is used to link the events events in the stem_table (e.g. observation and measurement records) to episodes. This variable is not included in OMOP CDM v6 + oncology extensions and should be removed from the episode table after all mappings are finished.
Inclusion/Exclusion criteria
No filters applied
| Destination Field | Source field | Comment field |
|---|---|---|
| domain_id | ||
| id | Auto-increment | |
| person_id | p_id | |
| visit_occurrence_id | p_id time | Concatenate p_id-“basedata”-time-visit_type and use visit_occurrence lookup to get visit_occurrence_id.Visit type is either “standard” or “mri” When MRI is taken (mri_taken.0 == 1) and variable starts with “mri_”, visit is “mri”. Else visit is “standard” |
| concept_id | psa_fu dre_fu num_cores_biop_fu num_cores_pc_fu asa_fu psadt gleason*_fu free_psa_fu phi_fu biopt_prob_type_fu biopt_infection_fu biopt_inf_urine_culture_fu biopt_inf_urine_bacterium_fu«br>biopt_inf_hospitalisation_fu biopt_inf_outcome_fu biopt_hematuria_fu biopt_hemospermia_fu biopt_pain_fu biopt_route_fu biopt_max_cancer_score_lenght_fu mri_taken.0 mri_lesions mri_suspected_number mri_pirads_* mri_largest_dia_* mri_location_* mri_location_free_* mri_progrssion_lesions mri_targeted_biopsy mri_targeted_cores mri_targeted_taken0 mri_targeted_gleason* mri_lesion_positive mri_method_used mri_prostate_volume_method | From variable_mapping or variable_value_mapping. Semantic mappings (both variable and value mappings) in source_appendix. |
| value_as_number | psa_fu num_cores_biop_fu num_cores_pc_fu asa_fu psadt free_psa_fu phi_fu biopt_inf_hospitalization_days_fu biopt_max_cancer_score_lenght_fu mri_suspected_number mri_largest_dia_* mri_targeted_cores mri_targeted_taken mri_lesions_positive mri_prostate_volume mri_pirads* mri_progrssion_lesions | |
| unit_concept_id | psa_fu free_psa_fu biopt_max_cancer_score_lenght_fu mri_prostate_volume biopt_inf_hospitalization_days_fu mri_largest_dia* psadt | From unit_mapping. Semantic mappings (unit mappings) in source_appendix |
| value_as_concept_id | gleason*_fu biopt_prob_type_fu biopt_infection_fu biopt_inf_urine_culture_fu biopt_inf_urine_bacterium_fu biopt_inf_urine_resistant_fu mri_location_* mri_location_free_* mri_targeted_gleason1 | From value_mapping. Check source_appendix for value mappings. |
| visit_detail_id | ||
| provider_id | ||
| source_value | psa_fu dre_fu num_cores_biop_fu num_cores_pc_fu asa_fu psadt gleason*_fu free_psa_fu phi_fu biopt_prob_type_fu biopt_infection_fu biopt_inf_urine_culture_fu biopt_inf_urine_bacterium_fu biopt_inf_hospitalisation_fu biopt_inf_outcome_fu biopt_hematuria_fu biopt_hemospermia_fu biopt_pain_fu biopt_route_fu biopt_max_cancer_score_lenght_fu mri_taken.0 mri_lesions mri_suspected_number mri_pirads_* mri_largest_dia_* mri_location_* mri_location_free_* mri_progrssion_lesions mri_targeted_biopsy mri_targeted_cores mri_targeted_taken0 mri_targeted_gleason* mri_lesion_positive mri_method_used mri_prostate_volume_method | |
| source_concept_id | 0 | |
| type_concept_id | Depending on vocabulary domains: Condition Occurrence: 44786627 - Primary Condition Measurement: 44818701 - From Physical Examination Observation: 45905771 - Observation Recorded from a Survey Procedure Occurrence: 581412 - Procedure Recorded from a Survey Specimen: 581378-EHR Detail | |
| start_date | days_psa_diag | year_diag (from basedata) + days_psa_diag |
| start_datetime | days_psa_diag | year_diag (from basedata) + days_psa_diag + 00:00:00 |
| end_date | days_psa_diag | year_diag (from basedata) + days_psa_diagDo not store for Condition Occurrence |
| end_datetime | days_psa_diag | year_diag (from basedata) + days_psa_diag + 00:00:00Do not store for Condition Occurrence |
| verbatim_end_date | ||
| days_supply | ||
| dose_unit_source_value | ||
| lot_number | ||
| modifier_concept_id | ||
| operator_concept_id | ||
| modifier_concept_id | ||
| modifier_source_value | ||
| quantity | ||
| range_high | ||
| range_low | ||
| refills | ||
| route_concept_id | ||
| route_source_value | ||
| sig | ||
| stop_reason | ||
| unique_device_id | ||
| unit_source_value | psa_fu free_psa_fu biopt_max_cancer_score_lenght_fu mri_prostate_volume biopt_inf_hospitalization_days_fu mri_largest_dia* psadt | |
| value_as_string | ||
| value_source_value | psa_fu num_cores_biop_fu num_cores_pc_fu asa_fu psadt free_psa_fu phi_fu biopt_inf_hospitalization_days_fu biopt_max_cancer_score_lenght_fu mri_suspected_number mri_largest_dia_* mri_targeted_cores mri_targeted_taken mri_lesions_positive mri_prostate_volume gleason*_fu biopt_prob_type_fu biopt_infection_fu biopt_inf_urine_culture_fu biopt_inf_urine_bacterium_fu mri_pirads_* mri_location_* mri_location_free_* mri_progrssion_lesions mri_targeted_gleason1 | |
| anatomic_site_concept_id | ||
| disease_status_concept_id | ||
| specimen_source_id | ||
| anatomic_site_source_value | ||
| disease_status_source_value | ||
| condition_status_concept_id | ||
| condition_status_source_value | ||
| event_id | ||
| event_field_concept_id | ||
| value_as_datetime | ||
| qualifier_concept_id | ||
| qualifier_source_value | ||
| record_source_value | p_id time | Concatenate p_id-“basedata”-time-variable (e.g. 1-fulong-37-mri_pirads_1) |
Reading from enddata.csv
Inclusion/Exclusion criteria
No filters applied
| Destination Field | Source field | Comment field |
|---|---|---|
| domain_id | ||
| id | Auto-increment | |
| person_id | p_id | |
| visit_occurrence_id | ||
| concept_id | discontinued reason_treatment pt pn pm gleason1_rad_prost gleason2_rad_prost prostatevolume tumorvolume ece svi pos_surgical_margins postoperative_psa adjuvant_radiotherapy | Discontinued – every person with value in Discontinued should have a mapping for 44810920 - Withdrawn from research study (mapping to the Observation table)From variable_mapping or variable_value_mapping. Semantic mappings (both variable and value mappings) in source_appendix. |
| value_as_number | prostatevolume tumorvolume postoperative_psa | |
| unit_concept_id | prostatevolume tumorvolume postoperative_psa | From unit_mapping. Semantic mappings (unit mappings) in source_appendix |
| value_as_concept_id | reason_treatment pt pn pm pos_surgical_margins | From value_mapping. Check source_appendix for value mappings. |
| visit_detail_id | ||
| provider_id | ||
| source_value | discontinued reason_treatment pt pn pm gleason1_rad_prost gleason2_rad_prost prostatevolume tumorvolume ece svi pos_surgical_margins postoperative_psa adjuvant_radiotherapy | |
| source_concept_id | 0 | |
| type_concept_id | Depending on vocabulary domains: Condition occurrence: 44786627 - Primary Condition Measurement: 44818701 - From Physical Examination Observation: 45905771 - Observation Recorded from a Survey Procedure occurrence: 581412 - Procedure Recorded from a Survey Specimen: 581378-EHR Detail | |
| start_date | days_discontinued_diagnosis | year_diag (from basedata) + days_discontinued_diagnosis |
| start_datetime | days_discontinued_diagnosis | year_diag (from basedata) + days_discontinued_diagnosis+ 00:00:00 |
| end_date | days_discontinued_diagnosis | year_diag (from basedata) + days_discontinued_diagnosis |
| end_datetime | days_discontinued_diagnosis | year_diag (from basedata) + days_discontinued_diagnosis+ 00:00:00 |
| verbatim_end_date | ||
| days_supply | ||
| dose_unit_source_value | ||
| lot_number | ||
| modifier_concept_id | ||
| operator_concept_id | postoperative_psa | |
| modifier_concept_id | ||
| modifier_source_value | ||
| quantity | ||
| range_high | ||
| range_low | ||
| refills | ||
| route_concept_id | ||
| route_source_value | ||
| sig | ||
| stop_reason | ||
| unique_device_id | ||
| unit_source_value | ||
| value_as_string | ||
| value_source_value | prostatevolume tumorvolume postoperative_psa reason_treatment pt pn pm pos_surgical_margins | |
| anatomic_site_concept_id | ||
| disease_status_concept_id | ||
| specimen_source_id | ||
| anatomic_site_source_value | ||
| disease_status_source_value | ||
| condition_status_concept_id | ||
| condition_status_source_value | ||
| event_id | ||
| event_field_concept_id | ||
| value_as_datetime | ||
| qualifier_concept_id | ||
| qualifier_source_value |