From: Bos, Angela Sent: Wednesday, March 16, 2016 4:08 PM To: Michael Prittie <[email protected]>; Kowalski, George <[email protected]> Cc: Dan Connolly <[email protected]> Subject: RE: Question on SAS query PROD_P02_DQA_FDPRO_DIAQ_NSD6_r001
George, Also if needs, also see this post process script from KU: https://github.com/kumc-bmi/i2p-transform/pull/10 To be run in between steps 2 & 3 described below. -Angela From: Michael Prittie [mailto:[email protected]] Sent: Wednesday, March 16, 2016 12:49 PM To: Bos, Angela; Kowalski, George Cc: Dan Connolly Subject: Re: Question on SAS query PROD_P02_DQA_FDPRO_DIAQ_NSD6_r001 Hi George, Angela does a good job of explaining how to use the SAS code I wrote to produce the SAS data-step-views, and how to use them (steps 3 and 4). If you have any additional questions feel free to contact me. Angela, Your instructions for this are really good, you should find a relevant ticket on GPC Trac and post them there so others in the GPC have access to them as well (just a thought). Best, Michael Prittie Biomedical Informatics Software Engineer Division of Medical Informatics University of Kansas Medical Center From: "Bos, Angela" <[email protected]<mailto:[email protected]>> Date: Wednesday, March 16, 2016 at 11:54 AM To: "Kowalski, George" <[email protected]<mailto:[email protected]>>, Michael Prittie <[email protected]<mailto:[email protected]>> Subject: RE: Question on SAS query PROD_P02_DQA_FDPRO_DIAQ_NSD6_r001 I am running SAS 9.4 on my Windows 7 workstation, and our i2b2 databases are Oracle 12 on Centos 6.5. Here's our entire process. Sorry if this is overkill. --------------------------------------------- 0) CREATE PCORNET_CDM DATABASE USER create user PCORNET_CDM identified by "XXXXX" default tablespace ENC_DATA temporary tablespace TEMP0 quota unlimited on ENC_DATA; grant create procedure to PCORNET_CDM; grant create sequence to PCORNET_CDM; grant create session to PCORNET_CDM; grant create synonym to PCORNET_CDM; grant create trigger to PCORNET_CDM; grant create any index to PCORNET_CDM; grant create any view to PCORNET_CDM; grant create any table to PCORNET_CDM; grant alter any table to PCORNET_CDM; grant drop any table to PCORNET_CDM; grant select any table to PCORNET_CDM; grant update any table to PCORNET_CDM; grant insert any table to PCORNET_CDM; grant delete any table to PCORNET_CDM; grant analyze any to PCORNET_CDM; --------------------------------------------- 1) LOAD SCILHS ONTOLOGY Clone https://github.com/njgraham/scilhs-ontology In Release/Oracle dir, edit the export vars at the top of load_scilhs_ontology.sh, e.g. # Variables expected to be in the environment (for Jenkins, etc.) export i2b2_meta_schema=I2B2METADATA export ontology_tables=ontology_tables.sql # created by this script export drop_ontology_tables=ontology_tables_drop.sql # created by this script create_oracle_metadata_tables=create_oracle_metadata_tables.sql export pcornet_cdm_user=PCORNET_CDM #export pcornet_cdm= # do on CLI w/ set +o history export sid=i2b2db # service name for UTHSCSA At UTHSCSA we use Oracle service names instead of SIDs, so we also changed these lines: connect ${pcornet_cdm_user}/${pcornet_cdm}; to connect ${pcornet_cdm_user}@${sid}/${pcornet_cdm}; ORACLE_SID=${sid} sqlldr ${pcornet_cdm_user}/${pcornet_cdm} control="$ctl" data="$base".TXT bad="$base".bad log... to sqlldr ${pcornet_cdm_user}@${sid}/${pcornet_cdm} control="$ctl" data="$base".TXT bad="$base".bad log="$base".lo... We run the script like so: $ nohup bash load_scilhs_ontology-uthscsa.sh >load_scilhs_ontology-uthscsa.out & Verify i2b2 metadata schema has the PCORNET_* tables, and they were loaded with data, review log/bad files from sqlldr. --------------------------------------------- 2) CREATE CDM TABLES We forked this and created a local uthscsa branch https://github.com/kumc-bmi/i2p-transform Edit pcornet_mapping.csv & various other things (see github commit log if interested) Edit the export vars at the top of run-i2p-transform.sh, e.g. # Database SID export sid=i2b2db # service name for UTHSCSA # User and password for CDM user export pcornet_cdm_user=PCORNET_CDM #export pcornet_cdm= # do on CLI w/ set +o history export i2b2_data_schema=I2B2DATA export i2b2_meta_schema= I2B2METADATA export datamart_id=C4UTHSCSA export datamart_name=C4-GPC-UTHSCSA export network_id=C4 export network_name=GPC # All i2b2 terms - used for local path mapping export terms_table=HERON_TERMS IF NEEDED, run these ONE TIME patch scripts: update_ethnicity_pdim.sql - set ethnicity_cd in i2b2 patient dimension update_inout_cd_vdim.sql - set inout_cd in i2b2 visit dimension heron_load/provider_mashup.sql<https://github.com/kumc-bmi/heron/blob/prov_enc_mapping_3588/heron_load/provider_mashup.sql> - set providerid in i2b2 visit dimension Run as follows: $ nohup bash run-i2p-transform-uthscsa.sh >run-i2p-transform.out & --------------------------------------------- 3) CREATE THE DATA VIEWS (ON ORACLE) Download/clone a local working copy of https://github.com/kumc-bmi/i2p-transform to my workstation. I manually downloaded only the SAS folder. Copy configuration.example.sas as configuration.sas, and set your local parameters in the new file. Launch SAS 9.4, open and run data_step_view_prep.sas. I run it by highlighting all the code and clicking the "Run" button (looks like a guy in a crosswalk). I think it is designed for you to have the sas code and config file in the same directory, and it parses the paths to the working dir for you. But I kept having issues getting that to work, so I set include path to the config manually in the sas code by changing %include '&path/configuration.sas'; to %include 'C:\Users\bos\Documents\SAS_pcornet_diagnostic\i2p-transform-SAS\configuration.sas'; After you run the sas code, you should see the views it created in the path you set as libname sasdata in the config file. data_step_view_inspect.sas is optional code for inspecting the resulting views. You can run it fully, or one proc at a time to view the first 10 rows in each view. --------------------------------------------- 4) RUN DIAGNOSTIC SAS QUERY Edit the lines below (dpath=data view dir, qpath=sas code dir): /*Enter directory where data is stored:*/ %let dpath=C:/Users/bos/Documents/SAS_pcornet_diagnostic/UTHSCSA_datamart/data-i2p-transform/; /*Enter network/server directory:*/ %let qpath=C:/Users/bos/Documents/SAS_pcornet_diagnostic/PROD_P02_DQA_FDPRO_DIAQ_NSD_r001_v01/; We may have had to create this subdirs under qpath: dmlocal, drnoc. Launch SAS 9.4, open and run the code by highlighting all the code and clicking the "Run" button (looks like a guy in a crosswalk). View output in results viewer and/or drnoc subdir. -Angela From: Kowalski, George [mailto:[email protected]] Sent: Wednesday, March 16, 2016 10:39 AM To: Michael Prittie Cc: Bos, Angela Subject: Re: Question on SAS query PROD_P02_DQA_FDPRO_DIAQ_NSD6_r001 Micheal, I'm not sure who to start with Angela Bos also volunteered to help and we need help as no one here has ever seen SAS code. But we're programmers and think we can pick it up eventually. So yes if you could let us know what you did to get the diagnostic.sas script running ( and how to integrate these 3 other scripts into the process ) I would much appreciate it . G George Kowalski 414.805.7318 (office) / [email protected]<mailto:[email protected]> From: Michael Prittie <[email protected]<mailto:[email protected]>> Date: Wednesday, March 16, 2016 at 8:20 AM To: "Kowalski, George" <[email protected]<mailto:[email protected]>> Cc: Laura Qualls <[email protected]<mailto:[email protected]>>, James Topping <[email protected]<mailto:[email protected]>>, Michael Park <[email protected]<mailto:[email protected]>>, LAUREL VERHAGEN <[email protected]<mailto:[email protected]>> Subject: Re: Question on SAS query PROD_P02_DQA_FDPRO_DIAQ_NSD6_r001 George, I'd be happy to share with you what I did to get the data step view approach standing up here at KUMC. Let me know if you're interested. Best, Michael Prittie Biomedical Informatics Software Engineer Division of Medical Informatics University of Kansas Medical Center From: Laura Qualls <[email protected]<mailto:[email protected]>> Date: Tuesday, March 15, 2016 at 6:51 PM To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Cc: James Topping <[email protected]<mailto:[email protected]>>, Michael Park <[email protected]<mailto:[email protected]>>, "verhagen.laurel ([email protected]<mailto:[email protected]>)" <[email protected]<mailto:[email protected]>>, Michael Prittie <[email protected]<mailto:[email protected]>> Subject: RE: Question on SAS query PROD_P02_DQA_FDPRO_DIAQ_NSD6_r001 Hi George. You can obtain the information you need for the HARVEST information from the attached document. The PROC PRINTO error you're seeing is probably due to the lack of information in this table, as the file name uses the DATAMARTID from the HARVEST table. As far the connection to an RDBMS, I'm afraid that I'm not very familiar with database connections so I can't provide any more guidance than the example provided in the work plan. However, your colleague Michael Prittie at KUMC has successfully created an Oracle connection, so perhaps he can help out. Best, Laura From: Kowalski, George [mailto:[email protected]] Sent: Monday, March 14, 2016 4:21 PM To: Michael Park Subject: Re: Question on SAS query PROD_P02_DQA_FDPRO_DIAQ_NSD6_r001 I did change this partially per the doc to %let dpath=%str(oracle user=userid orapw='passed' path=FLOADDV1 schema=fmlh_clarity_deid); and libname pcordata "&dpath"; Still see errors in the script like below . G George Kowalski 414.805.7318 (office) / [email protected]<mailto:[email protected]> From: "Kowalski, George" <[email protected]<mailto:[email protected]>> Date: Monday, March 14, 2016 at 3:05 PM To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Cc: "Kowalski, George" <[email protected]<mailto:[email protected]>> Subject: Question on SAS query PROD_P02_DQA_FDPRO_DIAQ_NSD6_r001 Micheal, Received the query today. We're talking to oracle for the backend data source and I have a question on how it should be run . I've updated the line pcordata lib name to read : ********************************************************************************; * Set LIBNAMES for data *******************************************************************************; libname pcordata oracle USER='fmlh_clarity_deid' PASSWORD='passed' path='FLOADDV1' schema='schema'; and it talks to database , but what about the line : ********************************************************************************* * Provide user defined values ********************************************************************************; /*Enter directory where data is stored:*/ %let dpath=K:/PRAGMATIC-HSR/DRN Operations/Data transformations/PCORnet test data/PCORnet_v3_data_alt/; should I just point it to an empty directory as our directories have no data ? ********************************************************************************* * Provide user defined values ********************************************************************************; /*Enter directory where data is stored:*/ %let dpath=/users/gkowalsk/sas/PROD_FDPRO_DIAQ_NSD6_r001/input; I'm seeing errors while running the script : [cid:[email protected]] Even thought that dir is writable . Also where do I get the NETWORKID , NETWORK_NAME, DATAMARTID and DATAMART_NAME for the HARVEST table ? G George Kowalski Biomedical Informatics Software Engineer Clinical & Translational Science Institute Medical College of Wisconsin 9200 West Wisconsin Avenue, Suite L722A Milwaukee, Wisconsin USA 53226 414.805.7318 (office) / [email protected]<mailto:[email protected]>
_______________________________________________ Gpc-dev mailing list [email protected] http://listserv.kumc.edu/mailman/listinfo/gpc-dev
