I am new to CDM,
I am working on EHR data and found that for some patient visit start date is missing.
Can I use visit_start_date as 1800-01-01 as default?
I have looked into some ETL work on Github, they have used 1900-01-01 as default date.
/*This file used to transform PCORnet ENCOUNTER table into the OMOP visit_occurrence, location and care_site table.*/
/*TRUNCATE TABLE OMOP.dbo.visit_occurrence;*/
/*TRUNCATE TABLE OMOP.dbo.location;*/
/*TRUNCATE TABLE OMOP.dbo.care_site;*/
/*Please run the Provider table ETL code first!!*/
/*Create location_id in the OMOP location table.*/
INSERT INTO OMOP.dbo.location(
location_id, zip, location_source_value)
SELECT
ROW_NUMBER() OVER (order by (select 1))+ (SELECT COALESCE(MAX(location_id),0) FROM OMOP.dbo.location),
FACILITY_LOCATION,
FACILITY_LOCATION
FROM PCORnet.dbo.ENCOUNTER
GROUP BY FACILITY_LOCATION;
/*Create care_site_id in the OMOP care_site table.*/
INSERT INTO OMOP.dbo.care_site(
care_site_id, care_site_name, place_of_service_concept_id, location_id,
show original
--By Diego Bosca 20201222
-- Needs clarification on visit_end_date and provider_id
INSERT INTO visit_occurrence
(
visit_occurrence_id,
person_id,
visit_concept_id, -- 38004515 Hospital? 8717 Inpatient Hospital?
visit_start_date,
visit_start_datetime,
visit_end_date,
visit_end_datetime,
visit_type_concept_id, -- Map to 44818518 (visit derived from EHR record)
provider_id,
care_site_id,
visit_source_value,
visit_source_concept_id,
admitting_source_concept_id,
admitting_source_value,
discharge_to_concept_id,
discharge_to_source_value,
show original
I don’t think so, @Dipak_Yadav . That solution gets you around the database constraints, that’s all. From an analytics perspective it is horrible, plus it violates a visit shouldn’t be outside the observation period, which then also would have to start in the 1800s. Sorry.
You could try guessing from the other data when the visit started. If that is impossible you should drop the visit entirely.
1 Like