Table name: PERSON

Key conventions

  • The ENROLLMENT_DETAIL table stores multiple records for each person, one for each month they are enrolled in a health plan. However, the CDM will only store one record per person in the PERSON table.
    • Only records where the person has prescription benefits (DRUGCOVG =1) or eligible for both Medicaid and Medicare coverage (MEDICARE =1) are used.
  • Start by evaluating all ENROLLMENT_DETAIL records and remove the following persons:
    • Individuals with two different, valid sex values (1 or 2) over different ENROLLMENT_DETAIL records
    • Individuals with Year(GETDATE()) - max(DOBYR) < 90 AND max(DOBYR) >= min(DOBYR) +2
  • After defining persons to remove, then use the most recent record in ENROLLMENT_DETAIL to define demographic information in the CDM for the remaining persons
  • After finding the latest record per person, delete the following:
    • Individuals whose DOBYR < 1900 or > the current year.
    • Individuals born > 1 year after their first enrollment period.
  • For MONTH_OF_BIRTH, the OBSERVATION_PERIOD table will need to be generated first. Then, the value can be derived from using YEAR_OF_BIRTH and OBSERVATION_PERIOD_START_DATE only if the year of birth is equal to the year of the first OBSERVATION_PERIOD_START_DATE.

Reading from ENROLLMENT_DETAIL

Destination Field Source field Logic Comment field
PERSON_ID ENROLID - -
GENDER_CONCEPT_ID SEX Map source values to their associated CONCEPT_IDs:
1 - 8507
2 - 8532
If SEX is not 1 or 2 exclude that person.
The exclusion of a person by gender should happen on last enrollment record not just if they had one bad SEX record.

CONCEPT_IDs:
8507 = ‘Male’
8532 = ‘Female’
YEAR_OF_BIRTH DOBYR DOBYR needs to be > 1900 and <= current year. If the DOBYR does not meet this criteria, drop the person. -
MONTH_OF_BIRTH - NULL  
DAY_OF_BIRTH - NULL -
BIRTH_DATETIME - NULL -
RACE_CONCEPT_ID STDRACE Map values of STDRACE to their associated CONCEPT_IDs:
CASE
WHEN STDRACE ='1' THEN 8527 (White)
WHEN STDRACE ='2' THEN 8516 (Black)
ELSE 0 (OTHER)
END AS RACE_CONCEPT_ID
Codes from MDCD:
1: White
2: Black
4: Hispanic
9: Other`
ETHNICITY_CONCEPT_ID STDRACE Map values of STDRACE to their associated CONCEPT_IDs:
CASE
WHEN STDRACE ='4' THEN 38003563 (Hispanic or Latino)
ELSE 0 (OTHER)
END AS ETHNICITY_CONCEPT_ID
-
LOCATION_ID - NULL -
PROVIDER_ID - NULL -
CARE_SITE_ID - NULL -
PERSON_SOURCE_VALUE ENROLID - -
GENDER_SOURCE_VALUE SEX - -
GENDER_SOURCE_CONCEPT_ID - 0 -
RACE_SOURCE_VALUE STDRACE   -
RACE_SOURCE_CONCEPT_ID - 0 -
ETHNICITY_SOURCE_VALUE STDRACE   -
ETHNICITY_SOURCE_CONCEPT_ID - 0 -

Change Log

19-Feb-2021: Correct the exclusion based on year of birth to read “Delete the following members: Year(GETDATE()) - max(DOBYR) < 90 AND max(DOBYR) >= min(DOBYR) +2”. Once a person reaches 90 years of age their birth year is incremented up to protect patient privacy.


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