Table name: STEM_TABLE

Key conventions

  • VISIT_DETAIL must be built before STEM (refer to VISIT_DETAIL file)

  • DRUG_CLAIMS records for mail-in pharmacy (RXMR = 2) do not create visits so those STEM records will not have VISIT_DETAIL or VISIT_OCCURRENCE foreign key links

  • NDC codes are date specific so there is an extra filter for the concept id mapping to make sure it falls during the time an NDC is valid.

  • Some 11 digit NDC codes are invalid and instead need to mapped to the 9-digit version. To account for this map the 11-digit NDC code to SOURCE_CODE in OMOP vocab first. If no mapping is found, map the first 9 digits of NDC code to SOURCE_CODE.

  • In the DRUG_CLAIMS table some values in DAYSUPP are invalid. Any value < 0 or > 365 should be updated using this logic:

    CASE
    WHEN DAYSUPP < 0 THEN 0
    WHEN DAYSUPP > 365 THEN 365
    WHEN DAYSUPP IS NULL THEN 0
    ELSE DAYSUPP
    END

Reading from DRUG_CLAIMS

Destination Field Source field Logic Comment field
DOMAIN_ID - This should be the domain_id of the standard concept in the CONCEPT_ID field. If a code is mapped to CONCEPT_ID 0, put the domain_id as Observation -
PERSON_ID ENROLID - -
VISIT_OCCURRENCE_ID VISIT_DETAIL
VISIT_OCCURRENCE_ID		 NULL The mail order records will not have a VISIT_DETAIL to link back to. In that case set to NULL
VISIT_DETAIL_ID VISIT_DETAIL
VISIT_DETAIL_ID		 NULL The mail order records will not have a VISIT_DETAIL to link back to. In that case set to NULL
PROVIDER_ID VISIT_DETAIL
PROVIDER_ID

DRUG_CLAIMS
PROVID		 NULL The mail order records will not have a VISIT_DETAIL to link back to. In that case take PROVID from DRUG_CLAIMS and look up PROVIDER_ID from the PROVIDER table.
ID - System generated. -
CONCEPT_ID NDCNUM Use the Source-to-Standard Query
Use the filter:
WHERE SOURCE_VOCABULARY_ID IN ('NDC')
AND TARGET_STANDARD_CONCEPT = 'S'
AND TARGET_INVALID_REASON IS NULL
AND SVCDATE BETWEEN SOURCE_VALID_START_DATE AND SOURCE_VALID_END_DATE

NDCs are a date centric vocabulary, so we need to check that we are using the NDC from the right time.		 -
SOURCE_VALUE NDCNUM - -
SOURCE_CONCEPT_ID NDCNUM Use the Source-to-Source Query
Use the filter:
WHERE SOURCE_VOCABULARY_ID IN ('NDC')
AND TARGET_VOCABULARY_ID IN ('NDC')
AND SVCDATE BETWEEN SOURCE_VALID_START_DATE AND SOURCE_VALID_END_DATE		 -
TYPE_CONCEPT_ID RXMR When RXMR = 2 then 32857 (Mail order record) else set to 32869 (Pharmacy claim) -
START_DATE SVCDATE - -
START_DATETIME SVCDATE SVCDATE + Midnight -
END_DATE SVCDATE
DAYSUPP		 DRUG_EXPOSURE_END_DATE = SVCDATE + SUM(DAYSUPP) - 1

If DAYSUPP is NULL or a negative value set to DRUG_EXPOSURE_START_DATE		 -
END_DATETIME DAYSUPP
SVCDATE		 Date calculated for END_DATE + Midnight -
VERBATIM_END_DATE - NULL -
DAYS_SUPPLY DAYSUPP DAYSUPP -
DOSE_UNIT_SOURCE_VALUE - NULL -
LOT_NUMBER - NULL -
MODIFIER_CONCEPT_ID - 0 -
MODIFIER_SOURCE_VALUE - NULL -
OPERATOR_CONCEPT_ID - 0 -
QUANTITY METQTY METQTY -
RANGE_HIGH - NULL -
RANGE_LOW - NULL -
REFILLS REFILL - OMOP defines this column as the number of refills after the initial prescription.
The initial prescription is not counted, values start with 0.

REFILL from DRUG_CLAIM is defined as a number indicating whether this is the original prescription (0) or the refill number (e.g. 1, 2, etc.).
ROUTE_CONCEPT_ID 0 - -
ROUTE_SOURCE_VALUE - NULL -
SIG NULL - “Sig” is short for the Latin, signetur, or “let it be labeled.”
STOP_REASON - NULL -
UNIQUE_DEVICE_ID - NULL -
UNIT_CONCEPT_ID - 0 -
UNIT_SOURCE_VALUE - NULL -
VALUE_AS_CONCEPT_ID - 0 -
VALUE_AS_NUMBER - NULL -
VALUE_AS_STRING - NULL -
VALUE_SOURCE_VALUE - NULL -
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

June 10, 2021

  • Mail order pharmacy records will not create visits so logic was removed and updated to reflect the change.

June 8, 2021

  • Removed aggregates from the mapping. Prior logic attempted to remove duplicates by finding distinct values and then adding quantities by NDC and date. This logic was removed to preserve record-level referential integrity with the source and to aid in cost analyses later.
  • Added references to VISIT_DETAIL and VISIT_OCCURRENCE_ID

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