As per item #6 on yesterday's GPC-DEV call agenda...
? Alex will send out scripts in the next day or two.
? Volunteers to try by the next call: Hubert (Nebraska) via Jim,
? Wisconsin
? MCW / Glenn
...here is the script. I put all instructions and explanations I could
think of into the comments, but I welcome questions or feedback via this
mailing list or direct email as well.
Regarding metadata: if we define metadata as the contents of the
I2B2METADATA or BLUEHERONMETADATA schema, then unfortunately that is at
the moment outside the scope of this script. I do, however, agree with
Phillip's point about the need to write something similar for the
metadata schema, and welcome advice on what to put in it.
If we define metadata as modifiers and various data descriptor fields
/within/ the data schema, you will find that those things get examined
in considerable detail.
Thank you all very much.
/**
I originally started out writing a cohort-characterization script that could be
adapted to the needs of many different research project just by altering the
cohort selection criteria (since most cohort characterization needs seem to
follow the same basic pattern).
However, from UTHSCSA's own trial-and-error and from discussions on the mailing
lists it has become clear that for multi-site counts to get non-misleading
results we need to have a very detailed characterization of each site's I2B2
semantics within its CRC (data repository) cell. The immediate goals of this
script are:
* To create a sort of empirical data dictionary we can all refer to in
constructing federated queries that will run correctly at GPC sites as they
*currently* are, so that cohort characterization can proceed in parallel with
data standardization.
* To help those working on data standardization see what else needs to be done
and where it needs to be done the most.
* To make certain data quality issues easier to spot.
This script ONLY does counts-- no individual records are extracted into the
output tables. Also, no dates, patient_nums, or location information will end
up in the output tables.
This script was tested on SQL*Developer but will probably work in
a SQL*Plus console. The runtime at UTHSCSA was about 20 minutes.
INSTRUCTIONS:
1. Set the appropriate values for `dataschema` and `yoursite`. Please leave
`conceptinfo` as-is.
2. Run this script as a user who has select access to the schema you specified
as your `dataschema`. That is the only external schema you'll need for this
version of the script. The user running this script will also need
permissions to create tables in their own schema. No tables will be created
anywhere else. For some of the tables, SQL*Developer might output...
"Failed: Warning: execution completed with warning"
This might have something to do with ...
https://support.oracle.com/rs?type=bug&id=7526579
Long story short, the tables do get created, so disregard the warning.
3. After running the script, please either export the tables OUTPUT_CON_MOD,
OUTPUT_BASIC_DEMOG, and OUTPUT_PROVIDER to .csv files or view them in
SQL*Developer, select all, and copy-paste into spreadsheets.
4. Email to [email protected] or upload to Shared Queries/Site Semantics on
PCORnet Central Desktop
(https://pcornet.centraldesktop.com/c4gpc/folder/4212054/)
Thank you very, very much. Please let me know if you want a copy the GPC-wide
results or if you have questions or suggestions.
**/
/** the name of the schema where you keep your DE-identified data
(e.g. OBSERVATION_FACT) **/
define dataschema = BLUEHERONDATA;
/** A string by which to identify your site **/
define yoursite = UTHSCSA;
-- to empirically find out what data-type each concept_cd
define conceptinfo = "valtype_cd,tval_char,valueflag_cd,location_cd,units_cd";
/** temporary table needed for faster creation of the other tables **/
create table obs_temp as select distinct patient_num,start_date
,concept_cd,modifier_cd
,&conceptinfo
,case when nval_num is NULL then 'None' else 'Numeric' end nval
from &dataschema..observation_fact;
/**
This is a table counting the number of occurrences for each combination of
CONCEPT_CD and its various attributes along with their paths and descriptions
where available. Some uses for this table:
* What MODIFIER_CDs can accompany a given CONCEPT_CD? For example at UTHSCSA if
I run ...
SELECT * FROM OUTPUT_CON_MOD WHERE CONCEPT_CD = 'ICD9:135';
...I find that a substantial number of diagnoses are part of patient medical
history, so I definitely need to filter that out if I'm looking for
observations recorded on patients with active sarcoidosis.
* What categoric values exist for a given CONCEPT_CD?
SELECT DISTINCT TVAL_CHAR FROM OUTPUT_CON_MOD WHERE CONCEPT_CD LIKE
'%COMPONENT_ID:2199' AND MODIFIER_CD = '@';
Turns out this lab-value (a TSH screen) can have the following categoric
values: <0.1, <1.0, <2.90, <0.10, E, L, and TNP.
* You can also figure out if and when this lab result has a numeric value:
SELECT DISTINCT TVAL_CHAR,VALTYPE_CD,NVAL FROM OUTPUT_CON_MOD WHERE
CONCEPT_CD LIKE '%COMPONENT_ID:2199' AND MODIFIER_CD = '@';
At UTHSCSA it looks like the VALTYPE_CD is correctly assigned for this lab,
and numeric values are available when TVAL_CHAR is either 'L' or 'E'.
* You can see what types of concepts are the most prevalent at a site... even if
their metadata tables are out of date because this table doesn't use
metadata, it's based directly on counts of OBSERVATION_FACT:
SELECT CONCEPT_DOMAIN,SUM(N_VISITS) N_VISITS FROM
(SELECT REGEXP_SUBSTR(CONCEPT_CD, '^.*:') CONCEPT_DOMAIN,N_VISITS
FROM OUTPUT_CON_MOD) GROUP BY CONCEPT_DOMAIN
ORDER BY N_VISITS DESC;
At UTHSCSA, it looks like DX_ID type diagnoses are more common than
ICD9 type diagnoses. Observations in the ALLERGEN domain are relatively rare
and ones in the MicroPositive domain are very rare.
* You can globally see what types of concepts can occur with what types of
modifiers:
SELECT DISTINCT REGEXP_SUBSTR(CONCEPT_CD, '^.*:') CONCEPT_DOMAIN,MODIFIER_CD
FROM OUTPUT_CON_MOD ORDER BY CONCEPT_DOMAIN, MODIFIER_CD;
Looks like observations in the PAT_ENC domain never have modifiers, and most
modifiers for the MEDICATION_ID domain describe dosage.
* Let's say you're looking for _every_ type of observation having something to
do with Helicobactor pylori, so that you can look them over and decide what
filters to use in a federated query:
SELECT * FROM OUTPUT_CON_MOD
WHERE LOWER(CONPATH) LIKE '%pylori%' OR LOWER(CONPATH) LIKE '%helico%'
OR LOWER(CONNAME) LIKE '%pylori%' OR LOWER(CONNAME) LIKE '%helico%';
...you get a number of different diagnoses and procedures and at the same time
information about modifiers, data types, and counts.
* If you have CONNAMEs or CONPATHs that are null, it may mean that those
observatoins are orphaned and not visible from queries that depend on
metadata... but now you know about their existance and can query for them in
OBSERVATION_FACT if they are relevant to your research.
**/
create table OUTPUT_CON_MOD as
with
encs as (select concept_cd,modifier_cd, &conceptinfo,nval, count(*) N_visits
from obs_temp
group by concept_cd,modifier_cd, &conceptinfo ,nval),
/** modifier_cd information, for readability/interpretability/finding orphaned
modifiers**/
mods as (select modifier_cd modcd,name_char modname,modifier_path modpath
from &dataschema..modifier_dimension where modifier_cd is not NULL),
/** concept_cd information, for readability/interpretability/finding orphaned
concepts **/
cons as (select concept_cd concd,min(name_char) conname,min(concept_path)
conpath
from &dataschema..concept_dimension group by concept_cd)
select '&yoursite' site,encs.*,conpath,conname,modpath,modname
from encs
left join mods on encs.modifier_cd = mods.modcd
left join cons on encs.concept_cd = cons.concd
order by concept_cd,modifier_cd
;
/** This counts all existing distinct combinations of demographic variables
in patient_dimension and pivots over a range of years. The first 'year', 9998
actually represents ALL distinct patients in the database regardless of year.
(e.g. total number of living female white english-speakers aged 6-12, total
number of living male white english-speakers aged 6-12, and so on). The
remaining columns are likewise, but counts of patients from each group seen only
during those respective years. There is a method to the madness for how the
other years were chosen: 1988 is the earliest year for which any GPC reports
having records. 2015 and 2016 haven't happened yet, so if you have any counts in
those years, you have a good reason to be curious. 9999 and 0 are the 'foo' and
'bar' of four digit integers, and might have been used by someone to tag
missing or otherwise problematic records.
some uses for this table:
* Spotting nonsensical combinations of demographic values, like patients in the
1-5 age-range who have a marital status of 'divorced'
* Spotting negative ages or missing ages
* Spotting demographic variables that aren't used at a given site... for example
at UTHSCSA, it looks like we don't currently use RELIGION_CD or INCOME_CD, so
so if someone sends us a query that filters on those, they won't get any
results!
* Seeing what terms are valid for the various columns of PATIENT_DIMENSION at
each site... if your query filters on `SEX_CD = 'f'` and a given site uses
values of 'Male','Female', and '@' for SEX_CD, that's another query that
won't
return results.
* Collapsing it into a more manageable pivot table and using it to see which
years have the most patients in the demographic groups that interest you. For
example, here's how you would do an age breakdown for the years 2012-2014:
SELECT
AGE_RANGE,SUM("2012_PATIENTS"),SUM("2013_PATIENTS"),SUM("2014_PATIENTS")
FROM OUTPUT_BASIC_DEMOG GROUP BY AGE_RANGE ORDER BY AGE_RANGE;
...or race and age:
SELECT
RACE,AGE_RANGE,SUM("2012_PATIENTS"),SUM("2013_PATIENTS"),SUM("2014_PATIENTS")
FROM OUTPUT_BASIC_DEMOG GROUP BY RACE,AGE_RANGE ORDER BY RACE,AGE_RANGE;
**/
create table OUTPUT_BASIC_DEMOG as
with
eachyr as (select distinct patient_num pn,extract(year from start_date) yr
from obs_temp),
allyrs as (select * from eachyr union all
select distinct pn,9998 yr from eachyr)
select * from (select '&yoursite' SITE,yr,VITAL_STATUS_CD,SEX_CD
,case when AGE_IN_YEARS_NUM is NULL then 'Missing'
when AGE_IN_YEARS_NUM < 0 then 'Negative'
when AGE_IN_YEARS_NUM < 1 then '< 1'
when AGE_IN_YEARS_NUM <= 5 then '1-5'
when AGE_IN_YEARS_NUM <= 12 then '6-12'
when AGE_IN_YEARS_NUM <= 18 then '13-18'
when AGE_IN_YEARS_NUM <= 65 then '19-65'
when AGE_IN_YEARS_NUM <= 85 then '66-85'
else '>85' end age_range
,LANGUAGE_CD,RACE_CD,MARITAL_STATUS_CD,RELIGION_CD,INCOME_CD
from allyrs left join &dataschema..patient_dimension pat
on allyrs.pn = pat.patient_num)
pivot( count(*) patients FOR yr
IN ( 9998,1988,1989,1990,1991,1992,1993,1994,1995,1996,1997,1998
,1999,2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010
,2011,2012,2013,2014,2015,2016,9999,0))
order by age_range,sex_cd,race_cd
;
/** Surveying to see for what if anything people are using the columns within
VISIT_DIMENSION. Might identify sites where it's possible to query by clinic or
by visit type.
**/
create table OUTPUT_VISIT as
select '&yoursite' site,ACTIVE_STATUS_CD,INOUT_CD
,LOCATION_CD,LOCATION_PATH
,min(LENGTH_OF_STAY)+0 minstay,max(LENGTH_OF_STAY)+0 maxstay,count(*) N
from &dataschema..visit_dimension
group by ACTIVE_STATUS_CD,INOUT_CD,LOCATION_CD,LOCATION_PATH;
/** If your sites has provider information, this counts distinct patients by
provider. At least, that's what I hope it does: UTHSCSA's PROVIDER_DIMENSION is
currently empty. :-(
**/
create table OUTPUT_PROVIDER as
select '&yoursite' site,count(*) patients
,min(provider_path) provider_path,min(name_char) name_char
from (select distinct provider_id pi,patient_num
from &dataschema..observation_fact) obs
left join &dataschema..provider_dimension prv
on obs.pi = prv.provider_id
where obs.pi is not NULL
group by pi;
/** cleanup **/
drop table OBS_TEMP;
/*
drop table OUTPUT_CON_MOD;
drop table OUTPUT_BASIC_DEMOG;
drop table OUTPUT_PROVIDER;
drop table OUTPUT_VISIT;
*/
_______________________________________________
Gpc-dev mailing list
[email protected]
http://listserv.kumc.edu/mailman/listinfo/gpc-dev
