Improved apde-aih-mcaid-mcare-pha xwalk

[dcolombara]

• now archive complete xwalk table with time stamp
• improved QA that ensures we do not have claims or pha IDs that match with more than one id_apde

[kai-fukutaki]

Are there cases when pha or claim IDs might be the same person? I feel like we sometimes get multiple pha_ids or claim_ids matching to the same KCMasterID

[dcolombara]

The IDH doesn't give us a perfect 1:1 match. So, there are times when a single id_apde will match to more than one claims and or pha ID. Check out the sample code below and let me know if you have more questions.

I think further down the pipeline, when creating the elig tables, I combine these cases into a single ID (the most recent) because it ends up breaking our code. For example, id_apde12345 matches with PHAs A12345 (prior to 2019) & B12345 (2020+). I end up calling both B12345 and combining the data so we have one ID with data for 2019-2020+.

At least I think I did this, because I remember thinking about it.

`

set up

library(rads) library(data.table)

db_hhsaw <- validate_hhsaw_key()

xwalk = setDT(DBI::dbGetQuery(conn = db_hhsaw, 'SELECT * from claims.final_xwalk_apde_mcaid_mcare_pha'))

each claims or PHA id is only linked to a single id_apde

identical(uniqueN(xwalk[!is.na(id_mcaid)]$id_mcaid), nrow(unique(xwalk[!is.na(id_mcaid), .(id_apde, id_mcaid)]))) identical(uniqueN(xwalk[!is.na(phousing_id)]$phousing_id), nrow(unique(xwalk[!is.na(phousing_id), .(id_apde, phousing_id)])))

however, a single id_apde can link to more than one claims or PHA ID

uniqueMcaid <- unique(xwalk[!is.na(id_mcaid), .(id_apde, id_mcaid)]) uniqueMcaid[id_apde %in% uniqueMcaid[duplicated(id_apde)]$id_apde]

uniquePHA <- unique(xwalk[!is.na(phousing_id), .(id_apde, phousing_id)]) uniquePHA[id_apde %in% uniquePHA[duplicated(id_apde)]$id_apde]`