Duplicate epikeys across unique token_person_id (HES-APC)

[yaszadeh]

Hello!

I have been working through Matt's 'deliveries cohort' scripts (https://github.com/UCL-CHIG/deliveries-cohort) within my ECHILD project, for the years 2010-2022.

When trying to identify and de-duplicate delivery episodes, I find that a substantial number of "epikey" values are duplicated in my dataset across unique "token_person_id" values. Most of these occur within financial years. I.e., the same "epikey" is linked to different "token_person_id"s within financial years, affecting just over 10% of my token_person_id's.

Is this a known issue / am I missing something simple / or does this seem to be an issue with something I've done?

Any advice much appreciated!

Thank you,
Yasmin

[matthewjay15]

Hi Yasmin
Where abouts in the pipeline is this happening? And have you done any kind of merging based on variables not including epikey?
Best wishes
Matt

[yaszadeh]

Hi Matt,

I'm becoming aware of the issue when I use the full HES-APC sample (ie I didn't notice previously when selecting the top 1000 to get things running) in Script 4 (derive delivery episode) as the algorithm fails to select the most likely delivery episode due to the epikey duplicates.

I haven't edited any of the processes in the script pipeline, and I don't think there's any merging before Script 4.

I thought I'd ask on here if it was a known issue with the data before looking more closely into whether I can find the root of the duplicates - so I can do some more digging now if there's no immediate explanation!

Thanks for replying so fast,
Yasmin

[zcabh35]

Howdy

after further investigation, we believe some of your issues have been replicated. Under certain conditions, we've found duplicated keys in APC 2021 and APC 2022. The good news is that the the actual raw data isn't the issue, but that the code used to access the raw data would return the data in scientific (e) notation, causing duplicate epikeys. Could you kindly check if running the following line of code

sqlQuery(dbhandle, "SELECT TOKEN_PERSON_ID, EPIKEY FROM [INSERT TABLE NAME FOR APC 2021]"

Could you then confirm whether you can see the EPIKEY's are displayed in e notation please?

[yaszadeh]

Hi again, I have a follow-up question: do you have advice for loading 'odbc' in the TRE? I am getting an error:

Error: package or namespace load failed for 'odbc' in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck = vI[[i]]):
namespace 'rlang' 1.1.4 is already loaded, but >= 1.1.7 is required

I tried exiting my R session and re-loading the packages with the ECHILD script "001_detect_and_install_libraries.R" but this hasn't changed the error.

Thank you!
Yasmin

[zcabh35]

Howdy

please close all instances of RStudio. Open up R (and not R studio). Change line 57 from "rlang_1.1.4.zip" to "rlang_1.1.7.zip". You should only need to run line 57 in R only.

odbc should work in RStudio now.

[yaszadeh]

Thank you!!

[zcabh35]

Hi, we've also found out that a much friendlier option is to keep using RODBC and updating the sqlQuery() function and include the as.is = TRUE argument. Apologies if you've already spent a while updating your code to use odbc instead.

[yaszadeh]

That's very useful to know - thank you for following up!