Context Navigation

select * from (
select distinct ri_dx_name, ri_order_name, dx_code, pcount from mburton.ind_dx_ord_summary_neg_review
where (dx_code != '305.1' and dx_code != '5105' and dx_code != '9738' and dx_code != '2289' and dx_code != '3692' and dx_code != '2860' and dx_code != '12911' and dx_code not like 'V%' and order_code !='8364' and order_code !='3624' and order_code !='3239' and order_code !='914' and order_code !='78' and order_code !='12983' and order_code !='1777')
order by pcount desc
) where rownum < 24398/4

create index mburton.ind_dx_idx1 on  mburton.ind_dx(pid, time, sys_id, code)
go
create index mburton.ind_med_order_idx1 on  mburton.ind_med_order(pid, time, code)
go
create index mburton.ind_med_supply_idx1 on  mburton.ind_med_order(pid, time, ndc)
go
create index mburton.ind_demog_idx1 on  mburton.ind_med_order(pid)

-- select * from v$session_longops inner join v$session using(sid, serial#)
-- analyze table mburton.ind_med_supply estimate statistics
drop table mburton.ind_map_med_order_ind
go
create table mburton.ind_map_med_order_ind AS 
SELECT DISTINCT m.term AS gopher_term, to_number(r5.target_value) AS snomed_code, 
         c1.rxcui in_rxcui, r5.source_id as ind_ndf
FROM lsimonaitis.MAP_TERM_RXCUI_SCD_200801 m                          -- 2283 -- 1885
inner join umls.RXNREL r1 on (m.RXCUI_SCD = r1.RXCUI2 and r1.RELA = 'isa')                      -- 1860 -- 1563
inner join umls.RXNREL r2 on (r1.RXCUI1 = r2.RXCUI2 and r2.RELA = 'has_ingredient')             -- 1860 -- 1563
inner join umls.RXNCONSO c1 on (r2.RXCUI1 = c1.RXCUI and c1.SAB = 'RXNORM' and c1.TTY = 'IN')   -- 1860 -- 1563
inner join umls.NDFRT_ROLE r3 on (c1.RXCUI = r3.TARGET_VALUE and r3.ROLE_DEF_ID = 'P818')       -- 1716 -- 1453
inner join umls.NDFRT_ROLE r4 on (r4.SOURCE_ID = r3.SOURCE_ID and r4.ROLE_DEF_ID = 'R35')              -- 1586 -- 1354
inner join umls.NDFRT_ROLE r5 on (r5.SOURCE_ID = r4.TARGET_ID and r5.ROLE_DEF_ID = 'P262795')          -- .... -- 1347
go
create index mburton.ind_map_med_order_ind_idx on mburton.ind_map_med_order_ind(gopher_term)
go
create index mburton.ind_map_med_order_ind_idx2 on mburton.ind_map_med_order_ind(snomed_code)





SELECT dx.code, dxn.name, 
       mds.snomed_code as dx_sct, dxsn.fullyspecifiedname,
       m2i.snomed_code as ind_code, indn.fullyspecifiedname, 
       md.code, mdn.name, ndfind.name, pid, dx.time AS dx_time, md.time AS med_time
  FROM mburton.IND_DX dx
    INNER JOIN mburton.ind_map_dx_snomed mds ON(mds.sys_id = dx.sys_id AND mds.code = dx.code)
    INNER JOIN mburton.ind_med_order md USING(pid)
    INNER JOIN mburton.ind_map_med_order_ind m2i ON(m2i.gopher_term = md.code)
    inner join umls.sct_isa_closure ic on(ic.conceptid1 = mds.snomed_code AND ic.conceptid2 = m2i.snomed_code)
    inner join umls.rmrs_concept dxn on(dxn.sys_id = dx.sys_id AND dxn.code = dx.code)
    inner join umls.rmrs_concept mdn on(mdn.sys_id = md.sys_id AND mdn.code = md.code)
    inner join umls.sct_concept indn on(indn.conceptid = m2i.snomed_code)
    inner join umls.sct_concept dxsn on(dxsn.conceptid = mds.snomed_code)
    inner join umls.ndfrt_concept ndfind on(ndfind.id = m2i.ind_ndf)



CREATE TABLE mburton.ind_dx_med_ind AS
SELECT DISTINCT 
            pid, dx.code AS dx_gopher_code, 
            mds.snomed_code as dx_snomed_code,
            dx.time AS dx_time, 
            md.code as med_gopher_code,
            m2i.snomed_code as ind_snomed_code,
            md.time AS med_time, 
            m2i.ind_ndf as ind_ndf_code
  FROM mburton.IND_DX dx
    INNER JOIN mburton.ind_map_dx_snomed mds ON(mds.sys_id = dx.sys_id AND mds.code = dx.code)
    INNER JOIN mburton.ind_med_order md USING(pid)
    INNER JOIN mburton.ind_map_med_order_ind m2i ON(m2i.gopher_term = md.code)
    inner join umls.sct_isa_closure ic on(ic.conceptid1 = mds.snomed_code AND ic.conceptid2 = m2i.snomed_code)
    inner join umls.rmrs_concept dxn on(dxn.sys_id = dx.sys_id AND dxn.code = dx.code)
    inner join umls.rmrs_concept mdn on(mdn.sys_id = md.sys_id AND mdn.code = md.code)
    inner join umls.sct_concept indn on(indn.conceptid = m2i.snomed_code)
    inner join umls.sct_concept dxsn on(dxsn.conceptid = mds.snomed_code)
    inner join umls.ndfrt_concept ndfind on(ndfind.id = m2i.ind_ndf)


create table mburton.ind_dx_ord_summary as
select dx.sys_id as dx_sys_id, dx.code as dx_code, rx.code as order_code, count(distinct pid) as pcount
  from mburton.ind_dx dx inner join mburton.ind_med_order rx using(pid)
  group by dx.sys_id, dx.code, rx.code

Indication Study

Background

see Attachments at bottom of page for learning Schema for UMLS/ SCT and RxNorm/ Meds Maps

What are the Dx & Complaints like clinical variables:

SELECT --+USE_NL(a b)
       a.code, a.name, b.pcount, b.count,
       TO_CHAR(b.time_low,'YYYY') as time_low, TO_CHAR(b.time_high,'YYYY') as time_high
       --, a.units_code, a.units_sys_id
FROM (
SELECT code, sys_id, c.name, c.units_code, c.units_sys_id
  from umls.rmrs_concept_alias_name a
    inner join umls.rmrs_concept c using(code, sys_id)
  where sys_id = 1 and lower(a.name) like '%dx%'
UNION
SELECT code, sys_id, name, units_code, units_sys_id
  from umls.rmrs_concept where sys_id = 1 and lower(name) like '%dx%'
) a 
INNER JOIN (
SELECT service_code as code, 
       service_sys_id as sys_id,
       sum(clinical_variable_count) as count,
       sum(patient_count) as pcount,
       min(earliest_time) as time_low,
       max(latest_time) as time_high
  FROM umls.rmrs_CLINICAL_VARIABLE_SERVICE
  GROUP BY service_code, service_sys_id
) b ON(b.sys_id = a.sys_id and b.code = a.code)

TERM Values Returned

Table Of Target Term Values

Code	Term	Pt Count	Instance Count	Pt %	Instance %
18075	Hosp ICD9 Dx	2,185,085	22,154,264	21.83%	16.53%
19307	Admitting ICD9 Dx	1,814,627	5,903,970	18.13%	4.41%
189	Dx & Complaints	567,590	89,625,264	5.67%	66.87%
4569	E.R. Diagnosis	483,459	3,854,070	4.83%	2.88%
21237	Primary Care Dx	215,010	2,839,275	2.15%	2.12%
7909	Disch Dx	190,130	784,231	1.90%	0.59%
4966	Hosp Dx	115,682	361,882	1.16%	0.27%

The Clinical Variable Values for These Terms

select sys_id, code, name, sum(clinical_variable_count) as count,
       sum(patient_count) as pcount
  from umls.rmrs_cv_coded_serv
  inner join umls.rmrs_concept on(sys_id = value_sys_id and code = value_code)
  where SERVICE_SYS_ID=1 and SERVICE_CODE='189'
  group by sys_id, code, name  order by count desc

Clinical Variable Values Returned

Diagnosis Mapping

ICD9 to SNOMED-CT

with icd2sct as (
  select targetcode, mapconceptid                                               /*  ICD-9 code, SCT ID mapped to */
    from umls.sct_crossmap_target_code                                          /*  SNOMED-CT  Crossmap  to ICD-9  */
    inner join umls.sct_crossmap on(maptargetid = targetid)
)
select sys_id, code, name, s.*
  from umls.rmrs_cv_coded_serv
  inner join umls.rmrs_concept on(sys_id = value_sys_id and code = value_code)  /*  Make sure concept is coded  */
  left outer join icd2sct on(targetcode = code)                                 /*  Use Mapping above on RMRS Concept  */
  left outer join umls.sct_concept s on(CONCEPTID = MAPCONCEPTID)               /*  Join Concept ID to Map for Fully Spec Name  */
  where SERVICE_SYS_ID=1 and SERVICE_CODE='189'                                 /*  189 = Dx and Complaints Term  */
    and VALUE_SYS_ID=2                                                          /*  SYS_ID = 2 for ICD-9  */

Repeat w/ other Terms: 18075, 19307, 4569, 7909, and 4966

Count

select count(distinct value_code)
  from umls.rmrs_cv_coded_serv
--  inner join umls.sct_crossmap_target_code mt on(targetcode = value_code)
--  inner join umls.sct_crossmap m on(m.maptargetid = mt.targetid)
  where SERVICE_SYS_ID=1 and SERVICE_CODE='189'
    and VALUE_SYS_ID=2

Diff

select * from (
select value_code
  from umls.rmrs_cv_coded_serv
  where SERVICE_SYS_ID=1 and SERVICE_CODE='189'
    and VALUE_SYS_ID=2
minus
select value_code
  from umls.rmrs_cv_coded_serv
  inner join umls.sct_crossmap_target_code mt on(targetcode = value_code)
  where SERVICE_SYS_ID=1 and SERVICE_CODE='189'
    and VALUE_SYS_ID=2
) inner join umls.rmrs_concept on(code = value_code and sys_id = 2)

TABLE

Code	Dx Term	Total	Mapped	% Mapped
18075	Hosp ICD9 Dx	13048	10,255	79%
19307	Admitting ICD9 Dx	11,322	8,609	76%
189	Dx & Complaints	7,082	6,173	87%
4569	E.R. Diagnosis	5,336	4,640	87%

		36788	29,677	81%

Potential Diagnosis Hierarchies

select institution_id, service_code, value_sys_id, sum(CLINICAL_VARIABLE_COUNT) 
 from umls.RMRS_CV_CODED_SERV
  where service_sys_id = 1
    AND service_code IN ('18075', '19307', '189', '4569')
 group by rollup(institution_id, service_code, value_sys_id)

select * from (
select institution_id, service_code, value_sys_id, sum(CLINICAL_VARIABLE_COUNT) 
 from umls.RMRS_CV_CODED_SERV
  where service_sys_id = 1
    AND service_code IN ('18075', '19307', '189', '4569')
 group by institution_id, service_code, value_sys_id
order by institution_id, service_code, value_sys_id
) inner join umls.RMRS_INSTITUTION using(institution_id)
 inner join umls.RMRS_code_system on(sys_id = VALUE_SYS_ID)

with vs as (
select institution_id, service_code, value_sys_id, value_code, c.name, sum(CLINICAL_VARIABLE_COUNT) as count
 from umls.RMRS_CV_CODED_SERV
   inner join umls.RMRS_CONCEPT c ON(c.sys_id = VALUE_SYS_ID and c.code = VALUE_CODE)
  where service_sys_id = 1
    AND service_code IN ('18075', '19307', '189', '4569')
    and value_sys_id <> 2
 group by institution_id, service_code, value_sys_id, value_code, c.name
order by institution_id, service_code, value_sys_id, count desc 
), unmapped as (
 select institution_id, service_code, value_sys_id, value_code, name, count, target_code, target_sys_id
    from vs left outer join umls.rmrs_concept_mapping on(source_sys_id = value_sys_id and source_code = value_code)
    where target_code is null
) select --+FIRST_ROWS
       institution_id, service_code, value_sys_id, value_code, name, count, uc.str, uc.code
    from unmapped u
      inner join umls.mrconso us on(lower(us.str) = lower(u.name))
      inner join umls.mrconso uc using(cui)
    where uc.sab = 'ICD9CM'
      and ts='P' and stt='PF'

Map additional RI Term Values to ICD9

drop table mburton.ridxnc2icd
go
create table mburton.ridxnc2icd as 
with vs as (
select distinct value_sys_id, value_code, c.name -- , sum(CLINICAL_VARIABLE_COUNT) as count
 from umls.RMRS_CV_CODED_SERV
   inner join umls.RMRS_CONCEPT c ON(c.sys_id = VALUE_SYS_ID and c.code = VALUE_CODE)
  where service_sys_id = 1
    AND service_code IN (/*'18075', '19307',*/ '189', '4569')
    and value_sys_id <> 2
), unmapped as (
 select value_code, name
    from vs 
    where not exists (select 1 from umls.rmrs_concept_mapping 
                        where source_sys_id = value_sys_id and source_code = value_code)
) select distinct value_code, name, uc.str, uc.code
    from unmapped u
      inner join umls.mrconso us on(lower(us.str) = lower(u.name))
      inner join umls.mrconso uc using(cui)
    where uc.sab = 'ICD9CM'
      and ts='P' and stt='PF'
      and us.suppress = 'N'

Map all directly to SNOMED-CT

DROP TABLE mburton.ind_map_dx_snomed
GO
CREATE TABLE mburton.ind_map_dx_snomed AS
WITH concept_name AS (
  SELECT sys_id, code, 'P' as name_type, regexp_replace(name,' NOS\b','') as name FROM umls.rmrs_concept
  UNION
  SELECT sys_id, code, 'S' as name_type, regexp_replace(name,' NOS\b','') as name FROM umls.rmrs_concept_alias_name WHERE length(name) > 5
  UNION
  SELECT 2, c.code, 'U' as name_type, regexp_replace(s.str,' NOS\b','') as name
    FROM umls.mrconso c
      INNER JOIN umls.mrconso s ON(s.cui = c.cui)
    WHERE c.sab='ICD9CM' AND s.sab IN('ICD9CM', 'ICD10AM', 'SNOMEDCT', 'MSH', 'MTHICD9', 'SNM')
      AND c.lat='ENG' AND c.suppress='N' AND c.tty='PT'
      AND s.lat='ENG' AND s.suppress='N' AND s.tty='PT'
)
SELECT DISTINCT c.sys_id, c.code, conceptid AS snomed_code, c.name_type, c.name, cn.term
  FROM mburton.ind_dx_summary ic 
    INNER JOIN concept_name c ON(ic.code = c.code AND ic.sys_id = c.sys_id)
    LEFT OUTER JOIN umls.sct_description cn ON(lower(cn.term) = lower(c.name))
    LEFT OUTER JOIN umls.sct_concept cc ON(cc.conceptid = cn.conceptid)
    WHERE NOT EXISTS (SELECT 1 FROM umls.sct_isa_closure x WHERE x.conceptid1 = cc.conceptid AND x.conceptid2 = 362955004)
      AND EXISTS (SELECT 1 FROM umls.sct_isa_closure x WHERE x.conceptid1 = cc.conceptid AND x.conceptid2 = 404684003)

15834 record(s) affected

WITH src AS (
  SELECT sys_id, code, sum(count) AS count
    FROM mburton.ind_dx_summary
    GROUP BY sys_id, code
) 
SELECT count(1), sum(count) FROM (
SELECT DISTINCT sys_id, code, count 
  FROM src INNER JOIN mburton.ind_map_dx_snomed USING(sys_id, code)
)
UNION ALL
SELECT count(1), sum(count) FROM (
SELECT sys_id, code, count FROM src
)

	Distinct	%	Occurrences	%
Mapped	8763	43.7%	76127376	68.3%
Total	20072		111493618

Overview of hits and misses:

SELECT count, sys_id, code, r.name, snomed_code, fullyspecifiedname, m.name_type, m.name, m.term
  FROM mburton.ind_dx_summary d
    LEFT OUTER JOIN mburton.ind_map_dx_snomed m USING(sys_id, code)
      INNER JOIN umls.rmrs_concept r USING(sys_id, code)
      LEFT OUTER JOIN umls.sct_concept s ON(conceptid = snomed_code)
  ORDER BY count DESC, sys_id, code, snomed_code

Still duplicates in here (note that I left the route of mapping through c.name and cn.term in here too)

WITH map AS (
  SELECT DISTINCT sys_id, code, snomed_code FROM mburton.ind_map_dx_snomed
), ambig AS (
SELECT sys_id, code, count(1) count
  FROM map GROUP BY sys_id, code HAVING count(1) > 1
) SELECT count, sys_id, code, name_type, snomed_code, r.name, s.fullyspecifiedname, m.name_type, m.name, m.term
    FROM ambig
      INNER JOIN mburton.ind_map_dx_snomed m USING(sys_id, code)
      INNER JOIN umls.rmrs_concept r USING(sys_id, code)
      INNER JOIN umls.sct_concept s ON(conceptid = snomed_code)
  ORDER BY count DESC, sys_id, code, snomed_code

The disease - finding split is a consistent source of this. I also see mappings to strange things with NOS etc. in the SNOMED term. Even though mappings to deprecated contents was filtered? Double check that. More of the duplicates could be removed by:

deciding on disorder and suppress finding (or vica versa -- not making that distinction in the original data there's no point in inconsistently introducing it at random.
taking votes by how many synonyms map to one or the other
using the most specific term (max distance from top) meaningful only in a set of concepts of direct lienage)

OTOH, it might not matter to be totally clean at this point. You might try to go ahead even with this imperfect mapping. We can't be perfect anyway.

Pull the Data

See: RmrsPullStudyData

Medications Mapping

Map and Data for Study Cohort

Rx Norm (RxNorm)

SELECT --+FIRST_ROWS
       m.ndc, r.rxcui, rx0.tty, rr.rela, rx.tty, rr2.rela, rx2.tty
  FROM pharmacy_allergy_medicine m
    INNER JOIN umls.rxnsat r ON(atn='NDC' and atv=ndc)
    INNER JOIN umls.rxnconso rx0 ON(rx0.rxcui=r.rxcui)
    INNER JOIN umls.rxnrel rr ON(rr.rxcui1 = r.rxcui and rr.rel='RO' and rr.rela NOT
in ('dose_form_of'))
    INNER JOIN umls.rxnconso rx on(rx.rxcui = rr.rxcui2)
    INNER JOIN umls.rxnrel rr2 ON(rr2.rxcui1 = r.rxcui and rr2.rel='RO' and rr2.rela
NOT in ('dose_form_of'))
    INNER JOIN umls.rxnconso rx2 on(rx2.rxcui = rr2.rxcui2)
  WHERE rx2.tty = 'IN'
    AND rx.tty NOT IN ('DF')

Rx Norm to NDF-RT and NDF-RT to SNOMED-CT

with MAP_TERM_NDFRT_PREP_200802 as (
select m.TERM, m.TERM_DESC, c.ID as NDFRT_ID, c.NAME as NDFRT_NAME
from lsimonaitis.map_term_rxcui_in_200802 m
inner join umls.NDFRT_ROLE r on (m.RXCUI = r.TARGET_VALUE)
inner join umls.NDFRT_CONCEPT c on (r.SOURCE_ID = c.ID)
where ROLE_DEF_ID = 'P818'                                                       /*  RxNorm_CUI  */
order by m.TERM_DESC
)

select -- m.TERM, m.TERM_DESC, m.NDFRT_ID, m.NDFRT_NAME, c.ID as TREATS_ID, 
  c.NAME as TREATS_NAME, s.target_value, 
  sc.FULLYSPECIFIEDNAME, srelc.FULLYSPECIFIEDNAME, isa.distance,
isac.FULLYSPECIFIEDNAME
from map_term_ndfrt_prep_200802 m
inner join umls.NDFRT_ROLE r on (m.NDFRT_ID = r.SOURCE_ID)
inner join umls.NDFRT_CONCEPT c on (r.TARGET_ID = c.ID)
inner join umls.NDFRT_ROLE s ON(s.source_id= c.id and s.role_def_id='P262795')    /* SNOMED_Mapping_ID */
inner join umls.SCT_CONCEPT sc ON(sc.CONCEPTID = s.target_value)
inner join umls.SCT_RELATIONSHIP srel oN(srel.CONCEPTID1 = sc.CONCEPTID and
srel.RELATIONSHIPTYPE=116680003)                                                  /* SCT Rel Type: "Disorder of" */
inner join umls.SCT_CONCEPT srelc on(srelc.CONCEPTID = srel.CONCEPTID2)
inner join umls.SCT_ISA_CLOSURE isa on(isa.CONCEPTID1 = srel.conceptid2)
inner join umls.SCT_CONCEPT isac on(isac.conceptid = isa.CONCEPTID2)
where r.ROLE_DEF_ID = 'R35'                                                        /* May Treat */
order by m.TERM_DESC

USING Relationship Type: "Finding Site" 363698007
Total Mapped: 213,380

USING Relationship Type: "Is A" 116680003 (In Relationship to Disorder)
Total Mapped: 79,780

* Sample of RxNorm to NDF-RT to SNOMED-CT

Medication Mapping Counts and Drop-offs

Term Counts

select count(distinct l.TERM)
from (select VALUE_CODE as term 
  from umls.rmrs_cv_coded_serv
  where SERVICE_SYS_ID=1 and SERVICE_CODE IN ('5837', '8897', '8898', '27510')
    and value_sys_id=1) l
inner join lsimonaitis.MAP_TERM_RXCUI_SCD_200801 m ON(m.term = l.term)                          -- 2283 -- 1885
inner join umls.RXNREL r1 on (m.RXCUI_SCD = r1.RXCUI2 and r1.RELA = 'isa')                      -- 1860 -- 1563
inner join umls.RXNREL r2 on (r1.RXCUI1 = r2.RXCUI2 and r2.RELA = 'has_ingredient')             -- 1860 -- 1563
inner join umls.RXNCONSO c1 on (r2.RXCUI1 = c1.RXCUI and c1.SAB = 'RXNORM' and c1.TTY = 'IN')   -- 1860 -- 1563
inner join umls.NDFRT_ROLE r3 on (c1.RXCUI = r3.TARGET_VALUE and r3.ROLE_DEF_ID = 'P818')       -- 1716 -- 1453
inner join umls.NDFRT_CONCEPT c2 on (r3.SOURCE_ID = c2.ID)                                      -- 1716 -- 1453
inner join umls.NDFRT_ROLE r4 on (c2.ID = r4.SOURCE_ID and r4.ROLE_DEF_ID = 'R35')              -- 1586 -- 1354
inner join umls.NDFRT_CONCEPT c3 on (r4.TARGET_ID = c3.ID)                                      -- 1586 -- 1354
inner join umls.NDFRT_ROLE r5 on (c3.ID = r5.SOURCE_ID and r5.ROLE_DEF_ID = 'P262795')          -- .... -- 1347

Patient and Instance Counts

select sum(pcount), sum(cvcount)
from (select VALUE_CODE as term, patient_count as pcount, clinical_variable_count as cvcount
  from umls.rmrs_cv_coded_serv
  where SERVICE_SYS_ID=1 and SERVICE_CODE IN ('5837', '8897', '8898', '27510')
    and value_sys_id=1) l -- 12022560	24493947
where exists (select 1 from lsimonaitis.MAP_TERM_RXCUI_SCD_200801 m WHERE m.term = l.term -- 3811457	8976989
and exists (select 1 from umls.RXNREL r1 WHERE m.RXCUI_SCD = r1.RXCUI2 and r1.RELA = 'isa' -- 3450823	8260001
and exists (select 1 from umls.RXNREL r2 WHERE r1.RXCUI1 = r2.RXCUI2 and r2.RELA = 'has_ingredient' -- 3450823	8260001
and exists (select 1 from umls.RXNCONSO c1 WHERE r2.RXCUI1 = c1.RXCUI and c1.SAB = 'RXNORM' and c1.TTY = 'IN' -- 3450823	8260001
and exists (select 1 from umls.NDFRT_ROLE r3 WHERE c1.RXCUI = r3.TARGET_VALUE and r3.ROLE_DEF_ID = 'P818' -- 3315786	7922894
and exists (select 1 from umls.NDFRT_CONCEPT c2 WHERE r3.SOURCE_ID = c2.ID -- 3315786	7922894
and exists (select 1 from umls.NDFRT_ROLE r4 WHERE c2.ID = r4.SOURCE_ID and r4.ROLE_DEF_ID = 'R35' -- 3174714	7648515
and exists (select 1 from umls.NDFRT_CONCEPT c3 WHERE r4.TARGET_ID = c3.ID -- 3174714	7648515
))))))))

Appendix

Getting the Entire RMRS Dictionary

CREATE TABLE umls.rmrs_concept AS
SELECT * FROM concept@condor
GO
CREATE TABLE umls.rmrs_concept_alias_name AS
SELECT * FROM concept_alias_name@condor
GO
CREATE TABLE umls.rmrs_clinical_variable_service AS
SELECT * FROM clinical_variable_service@condor
GO
CREATE TABLE umls.rmrs_cv_coded_serv AS
SELECT * FROM clinical_variable_coded_serv@condor
GO
CREATE UNIQUE INDEX umls.rmrs_concept_pk ON umls.rmrs_concept(sys_id, code)
GO
CREATE INDEX umls.rmrs_concept_alias_name_pk ON umls.rmrs_concept_alias_name(sys_id, code)
GO
CREATE INDEX umls.rmrs_cv_coded_serv_idx ON umls.rmrs_rmrs_cv_coded_serv(service_sys_id, service_code)
GO
GRANT SELECT ON umls.rmrs_concept TO umlsuser
GO
GRANT SELECT ON umls.rmrs_concept_alias_name TO umlsuser
GO
GRANT SELECT ON umls.rmrs_clinical_variable_service TO umlsuser
GO
GRANT SELECT ON umls.rmrs_cv_coded_serv TO umlsuser

Normalizing a SNOMED-CT Cross-Map

drop table umls.sct_crossmap_target_code
go
create table umls.sct_crossmap_target_code as
with parsed as (
select x.*, substr(cdr1,1,p5-1) as car5, substr(cdr4,p5+1) as cdr5
  from (
select x.*, instr(targetcodes,'|') as p5
  from (
select x.*, substr(cdr1,1,p4-1) as car4, substr(cdr3,p4+1) as cdr4
  from (
select x.*, instr(targetcodes,'|') as p4
  from (
select x.*, substr(cdr1,1,p3-1) as car3, substr(cdr2,p3+1) as cdr3
  from (
select x.*, instr(targetcodes,'|') as p3
  from (
select x.*, substr(cdr1,1,p2-1) as car2, substr(cdr1,p2+1) as cdr2
  from (
select x.*, instr(targetcodes,'|') as p2
  from (
select x.*, substr(targetcodes,1,p1-1) as car1, substr(targetcodes,p1+1) as cdr1
  from (
select x.*, instr(targetcodes,'|') as p1
  from umls.sct_crossmap_target x
) x ) x ) x ) x ) x ) x ) x ) x ) x )
select targetid, targetschemeid, targetcodes as targetcode, targetrule, targetadvice from parsed where p1 = 0
union all
select targetid, targetschemeid, car1 as targetcode, targetrule, targetadvice from parsed where p1 > 0
union all
select targetid, targetschemeid, car2 as targetcode, targetrule, targetadvice from parsed where p2 > 0
union all
select targetid, targetschemeid, car3 as targetcode, targetrule, targetadvice from parsed where p3 > 0
union all
select targetid, targetschemeid, car4 as targetcode, targetrule, targetadvice from parsed where p4 > 0
union all
select targetid, targetschemeid, car5 as targetcode, targetrule, targetadvice from parsed where p5 > 0
GO
create index umls.sct_crossmap_target_cdx on umls.sct_crossmap_target_code(targetcode)

Aditional Data Analysis and Mapping Tools

Attachments

umls_schema_1.png (104.1 KB) - added by mburton 2 years ago. UMLS Schema on Madhuri
UMLS Schema SCT Tables.xls (151.0 KB) - added by mburton 2 years ago. SCT Table Schema & sample data
RxNorm and Meds Map Schema.xls (91.5 KB) - added by mburton 2 years ago. RxNorm and Meds Maps Schema and sample data
RMRS Tables on Madhuri.xls (31.5 KB) - added by mburton 2 years ago.

Download in other formats:

Plain Text