Adding value_source_value in observation table

[chandryou]

Hi all,

In measurement, the source value can be stored in 'value_source_value'. I couldn't find where to store those data in observation table though it is a lot more complicated, so definitely I need to store the source_value.

So I propose to add value_source_value in observation table, tool.

If I don't understand properly, please let me know.

Thank you.

[gowthamrao]

Could you use observation_source_value?

[cgreich]

@chandryou:

What's the problem you have? What's the example?

[clairblacketer]

@chandryou what about value_as_number or value_as_string?

[chandryou]

Thank you for the replies. I should've check this thread.

@gowthamrao I couldn't use observaton_source_value, because it's for the original concept of observation (not the value)

@cgreich Sorry, I didn't remember what my problem was...... It would be that I have some strings which couldn't be converted into observation_concept_ids..

Yes, @clairblacketer value_as_string or numbers will work because most of them are strings or numbers.

[cgreich]

"I didn't remember what my problem was" - I like those the most. Solved. :)

[MaximMoinat]

Sorry Christian, I do have an idea of what the problem is. There is an inconsistency with the measurement table, which does have a value_source_value column. We have been using the observation-value_as_string to store the source value.

It would be consistent if both have this value_source_value column to store how the source represents the value_as_concept_id (or the original value_as_number if unit conversion is done).

[Alexdavv]

We identified the same need here.

[clairblacketer]

Labelled as Documentation for the 2021 CDM Hackathon

Final Decision

The field VALUE_SOURCE_VALUE needs to be added to the OBSERVATION table. The VALUE_SOURCE_VALUE field in MEASUREMENT can be used as a template for the description and ETL conventions.

[aandryc]

Assigned Stephanie Hong

[stephanieshong]

using the varchar(50) for cdmDatatype for this field.

[stephanieshong]

Documentation pull request submitted for adding the value_source_value column to the Observation Table.

[Alexdavv]

@stephanieshong Can we add some more examples of how should be vaue_as_string used and how it differs from value_source_value?

[stephanieshong]

@Alexdavv - I think this addresses the semantic consistency between Observation and Measurement domain.

_concept_id may need to house the categorical data as a result stemming from the *_concept_id and they are stored in _value_as_concept_id _value_source_value. @MelaniePhilofsky clarified that this comes down to semantics and consistency. :-) value_as_string may now house other observational result of low value / unstructured data associated with the observation. Here is one example given to me by @MaximMoinat In the source data we have a bacterial culture variable for which the value is 'Pos' We will then store: value_as_concept_id=9191 and value_source_value='Pos' In the source data we have a free-text variable 'Reason missed appointment' for which we have the value 'patient was stuck in LA traffic' Then we will only store: value_as_string='patient was stuck in LA traffic' That said, value_as_string will be of low value (as it is unstructured) and this text might actually belong in the notes table. Hope this helps.

[Alexdavv]

@stephanieshong It helps! I just tried to suggest adding some more clarifications into the field description in the OMOP specs.

Now it says "...the fact can be... a verbatim string (value_as_string)." It would be good to know when this is the case, e.g. we just wait for better times until everything is mapped or we have some real cases when it is allowed by conventions.

393