Empty csv files as result of Feasability step

[salvagarcia]

We (Hospital de Denia) have executed the feasability step and the process ended with no errors, but the csv files obtained as result had no content. The only check we came up with was to filter content of both drug_exposure and drug_era with the drug_ids present in the R source code of the MegaStudy, and we found more than 100K records.

The SELECTs mentioned to check: SELECT count(*) FROM [drug_exposure / drug_era] where drug_concept_id IN (19098548,1347450,1594587,912803,40171288,780442,45892599,1741122,45892906,1333379,40239665,1536743,1742432,1348407,1525746,1734104,1750500,1729720,1728416,1713332,1759842,1777806,1774470,1709170,1778162,1746114,19136187,1307515,1186087,42904205,1510627,1361580,1151789,937368,912263,36857573,19041065,1114375,19080982,1397141,718583,40242044,40168938,36878937,35200405,37003361,1311078,902730,36863408,1311799,19078097,903643,19012565,19034726,19010482,950637,1503983,40174604,42800246,36878851)

So we don't know where the problem is. Any help would be appreciated. Thank you.

[tiozab]

@salvagarcia thanks for reaching out. Let's take it step by step.

Can you clarify what the name of your data is? And what your database management system is? I was looking at the excel file in TEAMS where we store the information of data partners and could not find you there (searching for "denia")

Then, can you confirm that the renv has worked by giving me the package version of the CDMConnector package? Please type in the console packageVersion("CDMConnector") and give me the output. Thank you!

[salvagarcia]

Hi Theresa, just to let you know why this issue with our denomination 🙂 : we've been included in EHDEN as "Marina Salud" since this was effectively the name of our organizartion (Hospital) until last Feb 1st 2024. From that time onwards our official denomination is "Hospital de Denia" since we returned to be part of the public healthcare system and thus our old name steemed from our private condition has changed.

So this is our information in the excel:

DP Counts Call ID Data Partner Country Type of Data Date when interest was expressed Database name DBMS system STATUS Feasability Data sharing approval for publication Inc/Prev recived? DUS recived? Notes from DP

DP3 4 Marina Salud S.A. Spain Hospital 7-aug ATENEA_OMOP SQLServer FAIL (no drugs included in feasibility csv, please contact us) Pend.

And this is the output from the R studio you requested:

packageVersion("CDMConnector") [1] ‘1.3.1’

Thank you. Salva.

[cid:82bd1577-f6c0-4d6a-ac92-f5fdf30a379a]

Salvador Garcia Torrens Àrea de Gestió de Dades - Hospital de Denia

Partida Beniadlà, s/n. 03700 Dénia 966 000 000 @.**@.> · https://denia.san.gva.eshttps://denia.san.gva.es/

El contingut d'aquest missatge, inclosos els fitxers adjunts, és confidencial i va dirigit exclusivament als destinataris que s'indiquen. Si vosté no fóra un d'ells li pregue que m'ho comunique i esborre el missatge, sense copiar-lo ni revelar el seu contingut a tercers

---

De: Theresa Burkard @.> Enviado: miércoles, 15 de mayo de 2024 13:57 Para: oxford-pharmacoepi/MegaStudy @.> Cc: SALVADOR GARCIA TORRENS @.>; Mention @.> Asunto: Re: [oxford-pharmacoepi/MegaStudy] Empty csv files as result of Feasability step (Issue #54)

@salvagarciahttps://github.com/salvagarcia thanks for reaching out. Let's take it step by step.

Can you clarify what the name of your data is? And what your database management system is? I was looking at the excel file in TEAMS where we store the information of data partners and could not find you there (searching for "denia")

Then, can you confirm that the renv has worked by giving me the package version of the CDMConnector package? Please type in the console packageVersion("CDMConnector") and give me the output. Thank you!

— Reply to this email directly, view it on GitHubhttps://github.com/oxford-pharmacoepi/MegaStudy/issues/54#issuecomment-2112334490, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AHIGT7GBPHTV3WW53LQ46ODZCNESLAVCNFSM6AAAAABHWQ257KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMJSGMZTINBZGA. You are receiving this because you were mentioned.

[tiozab]

@salvagarcia Thank you.

It seems the renv did not work because the CDMConnector version is not correct.

The top part of the CodeToRun.R file should work without errors in order for the correct package versions to be installed to run the feasibility:

Please restore the renv file first:

if you have not installed renv, please first install: install.packages("renv")

renv::activate() renv::restore()

[salvagarcia]

@tiozab , fist of all let me say I'm not an expert in R. then I explain the steps done: 1.-renv package is installed (v1.0.7) 2.-renv::activate() done 3.-renv::restore() --> it requests to crete an snapshot by "renv::snapshot()". 3.a) I did it ans selected "OPTION 2: Install the packages, then snapshot." It warns me the the following required packages are not installed:

• bslib
• Capr
• CodelistGenerator
• devtools
• DrugUtilisation
• DT
• fresh
• ggplot2
• gt
• IncidencePrevalence
• kableExtra
• PatientProfiles
• plotly
• rmarkdown
• scales
• shiny
• shinycssloaders
• shinydashboard
• shinyjs
• shinyWidgets
• testthat 3.b) It starts installing packages and stops with "Error: package 'Capr' is not available". And I can't install it even with "install.packages("Capr")" since I got "Error: package 'Capr' is not available"

But the thing is that if I repeat the process, the system founds already installed the packages before dthe Capr error, and itrelevant that shows "- Downloading CDMConnector from CRAN ... OK [file is up to date]" when it is still in version 1.3.1

Which version of the CDMConnector should we have? Could you help me with this? I'm available to have a call sharing desktop to speed it up.

[salvagarcia]

More updates:

• yesterday all the packages were updated, did the activate() and restore(). Now CDMConnector is on version 1.4.0.
• we've checked the data in the CDM tables involved, with the following results:

We guess the problem could be related to the fact that our "concept_relationship" table has no records refered to the drugs under study. And it neither has any record related to the type of relation "RxNorm has dose form", which has been advertised while tracing the queries that the Fesability code executes to create the "#" temporary tables. Precisely:

declare @p1 int set @p1=107 exec sp_prepexec @p1 output,NULL,N'SELECT "concept_id", "RHS"."concept_name" AS "dose_form" FROM "#dbplyr_1453" LEFT JOIN ( SELECT DISTINCT "q01". FROM ( SELECT "concept_id_1", "concept_id_2", "concept_name" FROM ( SELECT "concept_relationship". FROM "dbo"."concept_relationship" WHERE ("relationship_id" = ''RxNorm has dose form'') ) "LHS" LEFT JOIN "dbo"."concept" ON ("LHS"."concept_id_2" = "concept"."concept_id") ) "q01" ) "RHS" ON ("#dbplyr_1453"."concept_id" = "RHS"."concept_id_1")' select @p1

[tiozab]

@salvagarcia thanks, indeed for the feasibilty step you need the following tables cdm$concept_relationship (from this one you need the relationship_id =="RxNorm has dose form", however this is not the killer) cdm$concept_ancestor cdm$concept cdm$drug_exposure cdm$drug_strength (from here we are getting the ingredient concepts that are then used for all the checks, this is the killer)

So yes, please fill the drug_strength table :-) before we can continue.

[tiozab]

Thanks everybody, especially @janblom.