When use Atlas to generate a cohort using Condition_Occurrence table, I can find subject _id (personid) from backend Cohort table. My question is that how to get diagnosis codes for each visit. Which table should I use?
The CONDITION_OCCURRENCE table contains optional (but usually occupied) fields condition_source_concept_id which could contain a reference to ICD10 diagnosis concept in CONCEPT table and visit_occurrence_id which links condition occurence to a particular visit in VISIT_OCCURENCE – presumably when the diagnosis was made.
If such semantic link was present in the source data, it is expected to be preserved during conversion to OMOP. Otherwise, if your source does not explicitly link diagnoses to visits, some guesswork using dates may have to be made.
So it’s either that or creating synthetic VISIT_OCCURENCE entries that represent a diagnosis event. That all depends on your use-case – but in the general use-case analysis is expected to be conducted on standard concepts in condition_concept_id field, not on source ICD10 diagnoses.
On top of this, when you find CONDITION_OCCURRENCE records, you can find which ICD9s were mapped to the standard concept by looking at CONCEPT_RELATIONSHIP, relationship id: ‘Maps To’ to determine which IC9 codes mapped over to the Standard Concept found in the standard concept table.
Looking at the condition_source_concept is a good idea, but it assumes your source codes came from ICD9s. I figured you may be asking in general how to find out what ICD9s mapped over to the standard concept when looking at any of the domain concepts.