Table name: STEM_TABLE

Key conventions

  • Medicare does not have HEALTH_RISK_ASSESSMENT (HRA) data

  • This data is in a wide format, meaning for a given patient and given date there are multiple column that represent conditions and obervations. We pivot that data so that one row of HRA data can become many rows within the appropriate CDM tables. The poster “Applying the OMOP Common Data Model to Survey Data” provides an illustration of how we pivot and work with the data.

  • Keep all records with valid values no matter whether SURVDATE is within observation period or not.

  • The following are list of HRA data variables, with character or numeric value. However, the data for all surveys can be converted into numeric value.

Test_Name Missing_Value Value_Type
ALCDYAMT ’-‘,’9’ String
ALCWEEK ’-‘,’9’ String
ALC_AMT ’-‘,’9’ String
CC_ALLERGY ’-‘,’9’ String
CC_ARTHRITIS ’-‘,’9’ String
CC_ASTHMA ’-‘,’9’ String
CC_BACKPAIN ’-‘,’9’ String
CC_CHF ’-‘,’9’ String
CC_DEPRESS ’-‘,’9’ String
CC_DIAB ’-‘,’9’ String
CC_HEARTDIS ’-‘,’9’ String
CC_HIGHBP ’-‘,’9’ String
CC_HIGHCOL ’-‘,’9’ String
CC_HRTBURN ’-‘,’9’ String
CC_LUNGDIS ’-‘,’9’ String
CC_MIGRAINE ’-‘,’9’ String
CC_NONSKINCAN ’-‘,’9’ String
CC_OSTEOPO ’-‘,’9’ String
CC_SKINCAN ’-‘,’9’ String
CGRAMT ’-‘,’9’ String
CGRCURR ’-‘,’9’ String
CGRDUR ’-‘,’9’ String
CGRPREV ’-‘,’9’ String
CGRQUIT ’-‘,’9’ String
CGTAMT ’-‘,’9’ String
CGTCURR ’-‘,’9’ String
CGTDUR ’-‘,’9’ String
CGTPKAMT ’-‘,’9’ String
CGTPREV ’-‘,’9’ String
CGTQTCAT ’-‘,’9’ String
CGTQUIT ’-‘,’9’ String
CHEWAMT ’-‘,’9’ String
CHEWCURR ’-‘,’9’ String
CHEWDUR ’-‘,’9’ String
CHEWPREV ’-‘,’9’ String
CHEWQUIT ’-‘,’9’ String
COPESTRS ’-‘,’9’ String
DIETFRT ’-‘,’9’ String
DIETFRVG ’-‘,’9’ String
DIETVEG ’-‘,’9’ String
DRNKDRV ’-‘,’9’ String
EDUC_LVL ’-‘,’9’ String
EXERMO ’-‘,’9’ String
FAMABSCAT12 ’-‘,’9’ String
FIREEXT ’-‘,’9’ String
FLU_SHOT ’-‘,’9’ String
HLTIMPCT ’-‘,’9’ String
JOB_SAT ’-‘,’9’ String
LIFE_SAT ’-‘,’9’ String
LIFTWGT ’-‘,’9’ String
MH_FREQ ’-‘,’9’ String
MH_PROB ’-‘,’9’ String
PIPEAMT ’-‘,’9’ String
PIPECURR ’-‘,’9’ String
PIPEDUR ’-‘,’9’ String
PIPEPREV ’-‘,’9’ String
PIPEQUIT ’-‘,’9’ String
PLANALC ’-‘,’9’ String
PLANDIET ’-‘,’9’ String
PLANDRAD ’-‘,’9’ String
PLANEXER ’-‘,’9’ String
PLANSLP ’-‘,’9’ String
PLANSTRS ’-‘,’9’ String
PLANTOB ’-‘,’9’ String
PLANWGT ’-‘,’9’ String
PREV_MAMMO ’-‘,’9’ String
PREV_PAPTEST ’-‘,’9’ String
PREV_PROSTEX ’-‘,’9’ String
PREV_SIGMOID ’-‘,’9’ String
PRODABSCAT ’-‘,’9’ String
RISK_ALC ’-‘,’9’ String
RISK_BP ’-‘,’9’ String
RISK_CHOL ’-‘,’9’ String
RISK_EXER ’-‘,’9’ String
RISK_GLUC ’-‘,’9’ String
RISK_MH ’-‘,’9’ String
RISK_NUTR ’-‘,’9’ String
RISK_SAFE ’-‘,’9’ String
RISK_SLEEP ’-‘,’9’ String
RISK_SMOK ’-‘,’9’ String
RISK_WGT ’-‘,’9’ String
SEATBELT ’-‘,’9’ String
SELFHLTH ’-‘,’9’ String
SLPAPNEA ’-‘,’9’ String
SLPPROB ’-‘,’9’ String
SMKDETECT ’-‘,’9’ String
STRETCH ’-‘,’9’ String
TOBCURR ’-‘,’9’ String
TOBPREV ’-‘,’9’ String
WRKABSCAT ’-‘,’9’ String
WRKABSCAT12 ’-‘,’9’ String
BMI NULL Numeric
CGRQTYR NULL Numeric
CGTQTYR NULL Numeric
CHEWQTYR NULL Numeric
CHOLESTR NULL Numeric
DIAST_BP NULL Numeric
EXERWEEK NULL Numeric
GLUCOSE NULL Numeric
HDL NULL Numeric
HEIGHT NULL Numeric
LDL NULL Numeric
PIPEQTYR NULL Numeric
SYSTO_BP NULL Numeric
TRIGLYCD NULL Numeric
WEIGHT NULL Numeric
WORKABS NULL Numeric

Reading from HEALTH_RISK_ASSESSMENT

Destination Field Source field Logic Comment field
DOMAIN_ID - - -
PERSON_ID ENROLID - -
VISIT_OCCURRENCE_ID - - -
VISIT_DETAIL_ID - - -
PROVIDER_ID - - -
ID - System generated. -
CONCEPT_ID The test name or name of the column Use the Source-to-Standard Query.

WHERE SOURCE_VOCABULARY_ID IN ('JNJ_TRU_HRA_QUESTION')
AND TARGET_INVALID_REASON IS NULL		 -
SOURCE_VALUE The test name or name of the column - -
SOURCE_CONCEPT_ID - 0 -
TYPE_CONCEPT_ID - 32850 (Health Risk Assessment)  
START_DATE SURVDATE - -
START_DATETIME SURVDATE start_date + midnight -
END_DATE - NULL -
END_DATETIME - NULL -
VERBATIM_END_DATE - NULL -
DAYS_SUPPLY - NULL -
DOSE_UNIT_SOURCE_VALUE - NULL -
LOT_NUMBER - NULL -
MODIFIER_CONCEPT_ID - 0 -
MODIFIER_SOURCE_VALUE - NULL -
OPERATOR_CONCEPT_ID - 0 -
QUANTITY - NULL -
RANGE_HIGH - NULL -
RANGE_LOW - NULL -
REFILLS - NULL -
ROUTE_CONCEPT_ID - 0 -
ROUTE_SOURCE_VALUE - NULL -
SIG - NULL -
STOP_REASON - NULL -
UNIQUE_DEVICE_ID - NULL -
UNIT_CONCEPT_ID - 0 -
UNIT_SOURCE_VALUE - NULL -
VALUE_AS_CONCEPT_ID VALUE_AS_STRING Use the SOURCE_TO_STANDARD query with the filter

LOINC_CD
WHERE SOURCE_VOCABULARY_ID IN (‘LOINC’) AND TARGET_STANDARD_CONCEPT =’S’ AND TARGET_INVALID_REASON IS NULL, mapping to SOURCE_CODE_DESCRIPTION instead of SOURCE_CODE		 -
VALUE_AS_NUMBER - If a question has a numeric result, put that answer here. Use table above to help know if the value is numeric or categorical.
VALUE_AS_STRING - If a question has a string response, put that answer here. Use table above to help know if the value is numeric or categorical.
VALUE_SOURCE_VALUE The test name or name of the column - -
ANATOMIC_SITE_CONCEPT_ID - 0 -
DISEASE_STATUS_CONCEPT_ID - 0 -
SPECIMEN_SOURCE_ID - NULL -
ANATOMIC_SITE_SOURCE_VALUE - NULL -
DISEASE_STATUS_SOURCE_VALUE - NULL -
CONDITION_STATUS_CONCEPT_ID - 0 -
CONDITION_STATUS_SOURCE_VALUE - NULL -
EVENT_ID - NULL -
EVENT_FIELD_CONCEPT_ID - 0 -
VALUE_AS_DATETIME - NULL -
QUALIFIER_CONCEPT_ID - 0 -
QUALIFIER_SOURCE_VALUE - NULL -

Change Log

July 15, 2021

  • Added mapping from VALUE_AS_STRING to Standard Concepts in VALUE_AS_CONCEPT_ID

June 8, 2021

  • Changed the type concepts for HRA data

  • Removed the following logic:

    • Keep all records even if some data moves to other domains, the OBSERVATION_CONCEPT_ID will be 0 for the records that moved elsewhere.

Please contact Clair Blacketer (https://github.com/clairblacketer) if you have any questions