OHDSI Home | Forums | Wiki | Github

Store original and transformed unit in measurement

I have a question regarding the storing of two values and their corresponding units in one measurement. Let’s say we extract data from different sources where several sources have their body height stored in inches and others in centimeters. Is there a way to store all the original values in measurements with their original units AND a transformed value. So if we would take centimeters as our ultimate standard; could we store in one measurement that the value is 62 with unit “inch” and transformed value is 157.48 with unit “cm”?

If not, how to store this in a neat way if we really want to store an original and a transformed value? By making 2 measurements and relate them?

Thank you in advance!

You could put the original value in the measurement_source_value column, no? We have been experimenting (per @mgkahn’s suggestion) with putting JSON objects in source_values to handle cases like this.

@borditewig:

Good question. The whole unit harmonization in THEMIS Group 1 has 4 components:

  1. Harmonize units into concepts - done
  2. Create fixed list of units allowable per Measurement (underway) with first results
  3. Create conversion algorithms for all if there is more than one allowable unit
  4. Decide on one unit, which all ETLs will produce (either original or converted) a completely standardized Measurement.

That will solve this problem.

Till then: like what @esholle says. If you are converting, store the original inches in value_source_value and unit_source_value and the converted centimeters into value_as_concept_id and unit_concept_id.

@Christian_Reich good to know. BTW if we store the local unit code into unit_source_value, we could also store the original local unit concept_id into a unit_source_concept_id column

so I suggest we add the unit_source_concept_id column

Hm. I am surprised it isn’t there. You are totally right.

replying to discussion on github on forum: https://github.com/OHDSI/CommonDataModel/issues/259

(we have a place for source value, I stand corrected)

Here is an example
source data says 121 pounds as weight
after utopian ETL we say 54.8847 kg

COLUMNS will be:
value_as_number = 54.8847
unit_concept_id = CID for kg
unit_source_value = “lb” (or “pounds”)
value_source_value = 121

(suggested)
unit_source_concept_id = CID for pounds

wiki link https://github.com/OHDSI/CommonDataModel/wiki/MEASUREMENT

if unit_concept_id != unit_source_concept_id - everyone knows that it was converted by the diligent ETLer (and we can have many Heel rules validating some parts of that)

my vote would be yes for adding unit_source_concept_id

btw. similar problem may be in OBSERVATION

t