OHDSI Home | Forums | Wiki | Github

How to generate visit_occurrence_ids and relate to other clinic data tables

CDM Builders
#1

Hi guys,
I just recently started working on the CDM conversion.As the best practices,I have completed Person and Observation_period table.But I have problems when convert Visit_occurrence table:

1.How to generate visit_occurrence_ids,because there isn’t “auto-increment” properties in DDLs.
2.If i generate visit_occurrence_ids by some ways，how can i use the visit_occurrence_ids in other clinic data tables without source_visit_occurrence_ids in Visit_occurrence table.

Thank you.

#2
  1. You can do that whichever way you want. As long as the IDs are integers and unique (foreign keys), you are good.
  2. You need to use the visit_occurrence_id foreign keys in the other tables to link a record to a visit. For example, the CONDITION_OCCURRENCE table is linked to the VISIT_OCCURRENCE using this field.

It means you need to first create the Visits (just like Providers and Care Sites), so you can then link them while building the other clinical event table.

#4

Thank you for replying in your busy schedule.
I understand what you mean, but I’m still confused as to how to link the CONDITION_OCCURRENCE table to the VISIT_OCCURRENCE without the source_visit_occurrence_ids in VISIT_OCCURRENCE.
For example ,In source table, the id is “IP12345”.But it becomes “66778899” in VISIT_OCCURRENCE.There is no “IP12345” in my VISIT_OCCURRENCE and no “66778899” in my source clinical event table like Orders.

#5

Easy.

  • Build the VISIT_OCCURRENCE table from your source tables. When you come to Visit IP12345 write a record, put the generated 66778899 into visit_occurrence_id and IP12345 into visit_source_value.
  • Build the CONDITION_OCCURRENCE table from your source tables. When a condition is pulled out of a source table referencing IP12345 search the VISIT_OCCURRENCE table you just finished for the visit_source_value of IP12345, take the visit_occurrence_id of that record (66778899) and write it into the visit_occurrence_id of the CONDITION_OCCURRENCE table.

A simple join will do the thing no problem.

#7

Ok,i get that.
Before I thought the visit_source_value had to be among the IP、ER and OP.
Refer to this article：OMOP CDM v5.3

#8

The source value is what your source says. The OMOP CDM does not care. It’s for your convenience, mostly for debugging of the mapping. If you don’t care you can leave it blank.

t