integrate VITAL table into OBS_CLIN

[sxinger]

• [ ] which sites are and are not populating OBS_CLIN table?
• [ ] among sites with OBS_CLIN tables, which include or not include VITAL data(weight, height, BMI, DBP, SBP, Smoking, tobacco use) in OBS_CLIN table?
• [ ] develop ETL codes for transforming VITAL table into OBS_CLIN

[rwaitman]

Who's populating OBS_CLIN.. Check for if the data is duplicated between OBS_CLIN and VITALS.

Work with Vasanthi and Song on the naming convention of clean schema's versus views. Do this on limited data first.

[vasanthi014]

@alabidi21 You should now have role 'grouse_role_b_admin' in identified snowflake account. Please create new tables/views as needed for exploration on GROUSE_DB_DEV database. All limited tables are in GROUSEDB and have a prefix of **LDS**

[vasanthi014]

@alabidi21 After discussion with Dr. Song, I created a new database GROUSE_DB_QUAIL and new role GROUSE_ROLE_B_QA for this work.

GROUSE_ROLE_B_QA role has read/write/create permissions in GROUSE_DB_QUAIL and has read only permissions to the GROUSE_DB.

Please let us know if you have any questions with this setup. Also, Please post your findings about the GPC sites's OBS_CLIN tables in this github ticket as you make progress.

[alabidi21]

Thank you Vasanthi!

[vasanthi014]

MU populated both VITALS and OBS_CLIN tables with vital data.

[vasanthi014]

@kzraryan-mu fyi

[alabidi21]

@vasanthi014 I need to push my code, but it needs reviewer approval. I did not have permission to create a folder or file in the Quail repo.

[alabidi21]

@vasanthi014 OBCLIN_RESULT_UNIT is not populated in MU, which will limit our use of LOINIC. Still, we can not use only LOINIC code '8302-2', as it was used for other height categories. Do you have any suggestions?

DQ issues with the other sites Time format for measure time in ALLINA and IHC in vital table Propal duplication because of NULL in OBSCLIN KUMC

[alabidi21]

@vasanthi014 @kzraryan-mu I updated my codes but I think it approval to be updated in src file on this repo

[sxinger]

• Sites with VITAL already contained in OBS_CLIN: KUMC, UIOWA, UTSW, UNMC
  • clean up the units, loinc codes, and names
  • move values from _TEXT into _NUM (KUMC)
• Sites with no or partial VITAL table integrated in OBS_CLIN: MU, MCW, ALLINA, MCRI, IHC,UTHOUSTON, UU, UTHSCSA
  • transform VITAL table and integrate into OBS_CLIN
  • dedup
• Sites don't have OBS_CLIN table at all: WASHU
  • create a OBS_CLIN table
  • transform VITAL table into OBS_CLIN

@alabidi21

[vasanthi014]

@alabidi21 As discussed let's create a mapping table "obsclin_mapping" with following columns raw_obsclin_name, obsclin_unit, obsclin_code, LOINC

I will let you know once I copy the CDM schemas and latest deid tables over to the GROUSE_DB_QUAIL database. You can recreate all the obsclin tables using the deid vital tables in each site CDM.

[alabidi21]

@vasanthi014 @sxinger The codes were updated in the repo . What should I include in the obsclin_mapping table? Do you have any source on the github that helps me understand the mapping process?

[vasanthi014]

"obsclin_mapping" table should have the following columns(add more columns as needed). And then insert all the combinations of these values that you used in your script(case statements). Once the mapping table is created we can modify your code to be generic using the mapping table. see sample values below:

raw_obsclin_name	obsclin_unit	obsclin_code	LOINC
Weight (kg)	kg		29463-7
Height (cm)	cm		8302-2

[alabidi21]

@vasanthi014 Thank you! Done

[vasanthi014]

Can you rerun your scripts to replace the obsclin tables under the PCORNET_CDM_XXX schemas.

[alabidi21]

Yes, I rerun the script using 'GROUSE_DB.PCORNET_CDM_MU.VDEID'

[vasanthi014]

Where are the new quail(obsclin) tables? Can you have them under GROUSE_DB_QUAIL.PCORNET_CDM_XXX schemas.

[alabidi21]

I will clone all tables. I checked yesterday these table was not exist. on the Quail database.