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.