· 8 years ago · May 10, 2018, 02:16 PM
1
2/*-------------------------
3--ICD9PROC_TO_SNOMED_PROC--
4---------------------------*/
5-- create manual mapping table (Polina T.'s PC)
6CREATE TABLE ICD9PROC_MANMAP_16042018
7(ICD_ID integer, ICD_CODE varchar (50), ICD_NAME varchar (300), RELATIONSHIP_ID varchar (50), CONCEPT_CODE varchar (50), CONCEPT_NAME varchar (300), CONCEPT_ID integer, ICD_CONCEPT_CLASS_ID varchar (20), VOCABULARY_ID varchar (20), STANDARD_CONCEPT varchar (1), INVALID_REASON varchar (1));
8WbImport -file=C:/Users/pdmitrenko/Desktop/Attributes/ICD9PROC_FINAL_26042018.txt
9 -type=text
10 -table=ICD9PROC_MANMAP_16042018
11 -encoding="UTF-8"
12 -header=true
13 -decode=false
14 -dateFormat="yyyy-MM-dd"
15 -timestampFormat="yyyy-MM-dd HH:mm:ss"
16 -delimiter='\t'
17 -decimal=.
18 -fileColumns=ICD_ID,ICD_CODE,ICD_NAME,RELATIONSHIP_ID,CONCEPT_CODE,CONCEPT_NAME,CONCEPT_ID,ICD_CONCEPT_CLASS_ID,VOCABULARY_ID,STANDARD_CONCEPT,INVALID_REASON
19 -quoteCharEscaping=none
20 -ignoreIdentityColumns=false
21 -deleteTarget=false
22 -continueOnError=false
23 -batchSize=1000;
24
25-- change relationship_id from 'ICD9P - SNOMED eq' to 'Is a'
26UPDATE ICD9PROC_MANMAP_16042018
27 SET relationship_id = 'Is a'
28WHERE relationship_id = 'ICD9P - SNOMED eq'; --2621
29
30-- fix icd_codes, that were affected by EXEL or bad guy;
31UPDATE ICD9PROC_MANMAP_16042018 a
32 SET icd_code= c.concept_code,
33 invalid_reason = c.invalid_reason,
34 icd_concept_class_id = c.concept_class_id
35FROM devv5.concept c
36WHERE c. vocabulary_id = 'ICD9Proc'
37AND a.icd_id = c.concept_id; -- 6540
38;
39
40-- make correct concept_id (for convenience). *another fields also can be updated for convenience and presentation purpose;
41UPDATE ICD9PROC_MANMAP_16042018 a
42 SET concept_id = c.concept_id,
43 vocabulary_id = c.vocabulary_id,
44 standard_concept = c.standard_concept
45FROM devv5.concept c
46WHERE a.concept_code = c.concept_code
47AND c.vocabulary_id = 'SNOMED'
48AND c.standard_concept = 'S'
49AND c.concept_class_id = 'Procedure'; -- 6540
50
51---- fix all 'unspecified', 'miscellaneous', 'not otherwise specified', 'Not Elsewhere Classified'. They should have relationship_id 'Is a' (ICD9P - SNOMED eq).
52UPDATE ICD9PROC_MANMAP_16042018
53 SET RELATIONSHIP_ID = 'Is a'
54WHERE relationship_id NOT IN ('Is a', 'Subsumes')
55AND (lower (icd_name) LIKE'%unspecified%'
56OR lower (icd_name) LIKE'%miscellaneous%'
57OR lower (icd_name) LIKE'% otherwise %'
58OR lower (icd_name) LIKE'%not elsewhere%'); -- 0
59
60--- fix relationship_id for concepts with '% other %' if needed
61UPDATE ICD9PROC_MANMAP_16042018
62 SET RELATIONSHIP_ID = 'Is a'
63WHERE relationship_id NOT IN ('Is a', 'Subsumes')
64AND icd_code NOT IN ('61.92', '38.55', '38.52', '42.58')
65AND (lower (icd_name) LIKE '%other %'
66OR (icd_name) LIKE '% other%'
67OR (icd_name) LIKE '% other %'); -- 0
68
69-- fix particular cases
70UPDATE ICD9PROC_MANMAP_16042018
71 SET CONCEPT_CODE = '450608000',
72 CONCEPT_NAME = 'Destruction of lesion of lung',
73 CONCEPT_ID = 42872617
74WHERE CONCEPT_CODE = '426987001'
75AND CONCEPT_NAME = 'Percutaneous radiofrequency ablation of lesion of lung'
76AND CONCEPT_ID = 4143832;
77
78UPDATE ICD9PROC_MANMAP_16042018
79 SET relationship_id = 'Is a'
80WHERE icd_code = '23.43'; --2
81
82UPDATE ICD9PROC_MANMAP_16042018
83SET RELATIONSHIP_ID = 'Is a'
84WHERE icd_id = 2007569; -- 2
85
86/*-------------------------
87--ICD9PROC_TO_SNOMED_ATTR--
88----------PARSING----------
89---------------------------*/
90
91--create table to look at the SNOMED attributes of ICD9Proc_to_SNOMED mapping (with subsumes);
92CREATE TABLE ICD9PROC_NEW_ATTR AS
93SELECT a.icd_code,
94a.icd_id,
95a.icd_name,
96a.relationship_id AS sno_rel_id,
97a.concept_id AS sno_id,
98a.concept_code AS sno_code,
99a.concept_name AS sno_name,
100cr.relationship_id AS attr_rel_id,
101c.concept_id AS attr_id,
102c.concept_code AS attr_code,
103c.concept_name AS attr_name,
104c.domain_id, c.concept_class_id,
105c.standard_concept,
106cr.invalid_reason,
107c.invalid_reason as category
108FROM ICD9PROC_MANMAP_16042018 a
109JOIN devv5.concept_relationship cr
110ON a.concept_id = cr.concept_id_1 and cr.invalid_reason IS NULL
111AND cr.relationship_id NOT IN ('Maps to', 'Mapped from', 'Is a', 'Subsumes','Follows', 'Occurs before', 'Asso proc of', 'Due to of', 'Focus of', 'Interprets of', 'Asso with finding', 'Finding method of', 'Concept poss_eq from',
112'Has complication', 'Specimen proc of', 'Concept was_a from', 'Concept same_as from', 'Concept replaces', 'Has temp finding', 'Concept replaced by', 'Method of', 'Has finding site')
113JOIN concept c
114ON c.concept_id = cr.concept_id_2
115AND c.vocabulary_id = 'SNOMED'
116AND c.standard_concept='S'
117
118-- mark concepts for convenience;
119UPDATE ICD9PROC_NEW_ATTR
120 SET category = 'M'
121WHERE sno_rel_id = 'Maps to'
122; -- 6454
123
124UPDATE ICD9PROC_NEW_ATTR
125 SET category = 'I'
126WHERE icd_id NOT IN ( SELECT icd_id FROM ICD9PROC_NEW_ATTR WHERE sno_rel_id IN ( 'Subsumes', 'Maps to') )
127; -- 4453
128
129UPDATE ICD9PROC_NEW_ATTR
130 SET category = 'S'
131WHERE icd_id IN ( SELECT icd_id FROM ICD9PROC_MANMAP_16042018 WHERE sno_rel_id = 'Subsumes')
132; -- 4106
133
134UPDATE ICD9PROC_NEW_ATTR
135 SET category = 'S'
136WHERE category IS NULL
137; -- 1333
138
139--delete rows with 'Subsumes'
140DELETE FROM ICD9PROC_NEW_ATTR
141WHERE sno_rel_id = 'Subsumes'
142; --4106
143
144-- insert non-standard drugs from SNOMED;
145INSERT INTO ICD9PROC_NEW_ATTR
146SELECT
147icd_code,
148icd_id,
149icd_name,
150sno_rel_id,
151sno_id,
152sno_code,
153sno_name,
154relationship_id,
155concept_id,
156concept_code,
157concept_name,
158domain_id,
159concept_class_id,
160standard_concept,
161invalid_reason
162FROM (SELECT DISTINCT icd_id, icd_code, icd_name, sno_rel_id, sno_id, sno_code, sno_name, cr.relationship_id, c.concept_id, c.concept_code, c.concept_name, c.domain_id, c.concept_class_id, c.standard_concept, a.invalid_reason
163FROM ICD9PROC_NEW_ATTR a
164JOIN devv5.concept_relationship cr
165ON a.sno_id = cr.concept_id_1
166AND cr.invalid_reason IS NULL
167AND cr.relationship_id IN ('Has dir subst')
168AND icd_id IN (SELECT DISTINCT icd_id FROM ICD9PROC_NEW_ATTR)
169JOIN devv5.concept c ON c.concept_id = cr.concept_id_2
170AND c.vocabulary_id = 'SNOMED'
171AND c.domain_id = 'Drug'
172)a
173; -- 109
174
175--create the table with pairs 'ancestor-descendant' within the one icd_id group;
176CREATE TABLE icd9proc_new_false_pairs AS
177SELECT i.icd_id,
178 i.attr_id
179FROM ICD9PROC_NEW_ATTR i
180JOIN devv5.concept_ancestor a
181ON i.attr_id = a.ancestor_concept_id
182AND a.min_levels_of_separation != 0
183JOIN ICD9PROC_NEW_ATTR f
184ON f.icd_id = i.icd_id
185AND a.descendant_concept_id = f.attr_id
186;
187
188--delete ancestors when descendant is present;
189DELETE FROM ICD9PROC_NEW_ATTR
190WHERE (icd_id, attr_id) IN (SELECT * FROM icd9proc_new_false_pairs)
191; -- 300
192
193/*-------------------------
194--ICD9PROC_TO_SNOMED_ATTR--
195---------MODIFICATION-------
196---------------------------*/
197
198--create table with icd_concepts + attributes;
199CREATE TABLE icd9proc_new_attr_all AS
200(SELECT icd_code,
201 icd_id,
202 icd_name,
203 attr_rel_id,
204 attr_id,
205 attr_code,
206 attr_name,
207 domain_id,
208 concept_class_id,
209 category
210FROM icd9proc_new_attr)
211;
212
213--create table for subsequent insert of common attributes of subsumes;
214CREATE TABLE icd9proc_new_attr_subs AS
215SELECT a.icd_code,
216 a.icd_id,
217 a.icd_name,
218 a.relationship_id AS sno_rel_id,
219 a.concept_id AS sno_id,
220 a.concept_code AS sno_code,
221 a.concept_name AS sno_name,
222 cr.relationship_id AS attr_rel_id,
223 c.concept_id AS attr_id,
224 c.concept_code AS attr_code,
225 c.concept_name AS attr_name,
226 c.domain_id,
227 c.concept_class_id,
228 c.standard_concept,
229 c.invalid_reason AS category
230FROM ICD9PROC_MANMAP_16042018 a
231JOIN devv5.concept_relationship cr
232ON a.concept_id = cr.concept_id_1
233AND cr.invalid_reason IS NULL
234AND cr.relationship_id NOT IN
235('Maps to', 'Mapped from', 'Is a', 'Subsumes','Follows', 'Occurs before', 'Asso proc of', 'Due to of', 'Focus of', 'Interprets of', 'Asso with finding', 'Finding method of', 'Concept poss_eq from',
236'Has complication', 'Specimen proc of', 'Concept was_a from', 'Concept same_as from', 'Concept replaces', 'Has temp finding', 'Concept replaced by', 'Method of', 'Has finding site')
237JOIN concept c
238ON c.concept_id = cr.concept_id_2
239AND c.vocabulary_id = 'SNOMED'
240AND c.standard_concept='S'
241AND a. icd_id IN
242(SELECT DISTINCT icd_id FROM icd9proc_manmap_16042018 WHERE relationship_id = 'Subsumes')
243AND a.icd_id IN
244(SELECT DISTINCT icd_id FROM icd9proc_manmap_16042018 WHERE relationship_id = 'Is a')
245;
246
247-- insert general attributes of 'Subsumes' of the one 'Is a'
248INSERT INTO icd9proc_new_attr_all
249SELECT
250icd_code,
251icd_id,
252icd_name,
253attr_rel_id,
254attr_id,
255attr_code,
256attr_name,
257domain_id,
258concept_class_id,
259'N'
260FROM (
261SELECT DISTINCT icd_code, icd_id, icd_name, attr_rel_id, attr_id, attr_code, attr_name, domain_id, concept_class_id, category
262FROM icd9proc_new_attr_subs
263WHERE attr_id IN
264(SELECT attr_id FROM icd9proc_new_attr_subs
265WHERE sno_rel_id = 'Subsumes'
266GROUP BY attr_id, attr_rel_id, icd_id
267HAVING COUNT (1)>=2)
268AND attr_id NOT IN
269(SELECT attr_id FROM icd9proc_new_attr_subs
270WHERE sno_rel_id = 'Is a'
271GROUP BY attr_id, attr_rel_id, icd_id
272HAVING COUNT (1)=1)
273AND attr_id NOT IN
274(SELECT attr_id FROM icd9proc_new_attr_subs
275GROUP BY attr_id, attr_rel_id, icd_id
276HAVING COUNT (1)=1)
277)a
278WHERE (icd_id, attr_rel_id,attr_name) NOT IN
279(SELECT icd_id, attr_rel_id, attr_name
280FROM ICD9PROC_NEW_ATTR_ALL)
281 ; -- 40
282
283-- insert percutaneous access where it's absent;
284INSERT INTO icd9proc_new_attr_all
285SELECT
286d.concept_code,
287d.concept_id,
288d.concept_name,
289'Has access',
290'4013298',
291'103388001',
292'Percutaneous approach',
293'Observation',
294'Qualifier Value',
295'N'
296FROM devv5.concept d
297JOIN
298(SELECT DISTINCT icd_id FROM icd9proc_new_attr_all
299WHERE LOWER (icd_name) LIKE '%percutaneous%'
300AND icd_id NOT IN
301(SELECT icd_id FROM icd9proc_new_attr_all
302WHERE lower(attr_name) LIKE '%percutaneous%approach%'
303OR lower(attr_name) LIKE '%percutaneous%transluminal%'
304OR icd_code IN ('37.68', '36.01', '36.02', '36.05')
305)
306) a
307ON d.concept_id = a.icd_id
308; -- 22
309
310-- insert closed access where it's absent (excluding '%closed fracture%' and '%closed reduction%')
311INSERT INTO icd9proc_new_attr_all
312SELECT
313d.concept_code,
314d.concept_id,
315d.concept_name,
316'Has access',
317'4044379',
318'129237003',
319'Closed approach',
320'Observation',
321'Qualifier Value',
322'N'
323FROM devv5.concept d
324JOIN
325(SELECT DISTINCT icd_id
326FROM icd9proc_new_attr_all
327WHERE lower (icd_name) LIKE '%closed %'
328AND icd_id NOT IN
329(SELECT icd_id FROM icd9proc_new_attr_all
330WHERE attr_name = 'Closed approach'
331AND attr_rel_id = 'Has access')
332AND lower (icd_name) NOT LIKE '%closed fracture%'
333AND lower (icd_name) NOT LIKE '%closed reduction%'
334AND lower (icd_name) NOT LIKE '%percutaneous%'
335)a
336ON d.concept_id = a.icd_id
337; --23
338
339-- insert open approach where it's absent (excluding '%open fracture%', '%open reduction%','open and other%', 'other and open%' )
340INSERT INTO icd9proc_new_attr_all
341SELECT
342d.concept_code,
343d.concept_id,
344d.concept_name,
345'Has access',
346'4044378',
347'129236007',
348'Open approach',
349'Observation',
350'Qualifier Value',
351'N'
352FROM devv5.concept d
353JOIN
354(SELECT DISTINCT icd_id
355FROM ICD9PROC_NEW_ATTR_ALL
356WHERE lower (icd_name) LIKE '%open %'
357AND icd_id NOT IN
358(SELECT icd_id FROM icd9proc_new_attr_all WHERE attr_name = 'Open approach' AND attr_rel_id = 'Has access')
359AND lower (icd_name) NOT LIKE '%open fracture%'
360AND lower (icd_name) NOT LIKE '%open reduction%'
361AND lower (icd_name) NOT LIKE '%open heart%'
362AND lower (icd_name) NOT LIKE 'open and other%'
363AND lower (icd_name) NOT LIKE 'other and open%') a
364ON d.concept_id = a.icd_id
365; --18
366
367-- Insert 'Checking - action' where concepts have '% check %' (if it's appropriate);
368INSERT INTO icd9proc_new_attr_all
369SELECT
370d.concept_code,
371d.concept_id,
372d.concept_name,
373'Has method',
374'4232679',
375'360160009',
376'Checking - action',
377'Observation',
378'Qualifier Value',
379'N'
380FROM devv5.concept d
381JOIN
382(SELECT DISTINCT icd_id
383FROM icd9proc_new_attr_all
384WHERE lower (icd_name) LIKE '%check%'
385AND icd_id NOT IN
386(SELECT icd_id FROM icd9proc_new_attr_all
387WHERE attr_rel_id = 'Has method')
388) a
389ON d.concept_id = a.icd_id
390; -- 5
391
392-- Insert 'Evaluation - action' where concepts have '%stress test%' and Has method ! = 'Evaluation - action' or 'Measurement - action'
393INSERT INTO icd9proc_new_attr_all
394SELECT
395d.concept_code,
396d.concept_id,
397d.concept_name,
398'Has method',
399'4044176',
400'129265001',
401'Evaluation - action',
402'Observation',
403'Qualifier Value',
404'N'
405FROM devv5.concept d
406JOIN
407(SELECT DISTINCT icd_id
408FROM icd9proc_new_attr_all
409WHERE lower (icd_name) LIKE '%stress test%'
410AND icd_id NOT IN
411(SELECT icd_id FROM icd9proc_new_attr_ALL
412WHERE attr_name IN ('Evaluation - action', 'Measurement - action')
413)
414) a
415ON d.concept_id = a.icd_id
416; -- 2
417
418-- Insert 'Diagnostinc intent' for diagnostic procedures
419insert into icd9proc_new_attr_all
420select
421d.concept_code,
422d.concept_id,
423d.concept_name,
424'Has intent',
425'4129646',
426'261004008',
427'Diagnostic intent',
428'Observation',
429'Qualifier Value',
430 'N'
431from devv5.concept d
432join
433(select distinct icd_id
434from icd9proc_new_attr_all
435where lower (icd_name) like '%diagnostic%'
436and icd_id not in
437(select icd_id from icd9proc_new_attr_all
438where attr_rel_id = 'Has intent'
439or attr_name = 'Examination - action')
440) a
441on d.concept_id = a.icd_id
442; -- 102;
443
444--Insert 'Using subst' - 'Contrast media', where icd_name with '% contrast %', bit attr_rel_id != 'Using subst'
445insert into icd9proc_new_attr_all
446select
447d.concept_code,
448d.concept_id,
449d.concept_name,
450'Using subst',
451'4299338',
452'385420005',
453'Contrast media',
454'Observation',
455'Substance',
456 'N'
457from devv5.concept d
458join
459(select distinct icd_id from icd9proc_new_attr_all
460where (lower (icd_name) like '% contrast %'
461or lower (icd_name) like 'contrast%'
462or lower (icd_name) like '%contrast')
463and icd_id not in
464(select icd_id from icd9proc_new_attr_all where attr_rel_id = 'Using subst')) a
465on d.concept_id = a.icd_id
466; --12
467
468--Insert 'Has focus - Alcoholism' for concepts with alcohol issues;
469insert into icd9proc_new_attr_all
470select
471d.concept_code,
472d.concept_id,
473d.concept_name,
474'Has focus',
475'4218106',
476'7200002',
477'Alcoholism',
478'Condition',
479'Clinical Finding',
480 'N'
481from devv5.concept d
482join ( select distinct icd_id from icd9proc_new_attr_all where lower (icd_name) like '%alcohol%' and lower (icd_name) not like '%drug%'
483and icd_id not in (select icd_id from icd9proc_new_attr_all where ATTR_REL_ID = 'Has focus')) a
484on d.concept_id = a.icd_id
485; -- 4
486
487--Insert 'Has focus - Substance abuse' for concepts with drug issues (be careful with it!);
488insert into icd9proc_new_attr_all
489select
490d.concept_code,
491d.concept_id,
492d.concept_name,
493'Has focus',
494'4279309',
495'66214007',
496'Substance abuse',
497'Condition',
498'Clinical Finding',
499'N'
500from devv5.concept d
501join ( select distinct icd_id from icd9proc_new_attr_all
502where icd_code != '94.25' and (
503lower (icd_name) like '%drug %'
504or lower (icd_name) like '%alcohol and drug%'
505and lower (icd_name) not like '%drug therapy%')
506and icd_id not in
507(select icd_id from icd9proc_new_attr_all
508where ATTR_REL_ID = 'Has focus')) a
509on d.concept_id = a.icd_id
510; --8
511
512--Insert 'Inspection - action' for '%control of%' and attr_name like '%bleeding%';
513insert into icd9proc_new_attr_all
514select
515d.concept_code,
516d.concept_id,
517d.concept_name,
518'Has method',
519'4043866',
520'129433002',
521'Inspection - action',
522'Observation',
523'Qualifier Value',
524'N'
525from devv5.concept d
526join ( select distinct icd_id from icd9proc_new_attr_all where lower (icd_name) like '%control of%' -- and attr_name like '%bleeding%'
527and icd_id not in (select icd_id from icd9proc_new_attr_all where ATTR_REL_ID = 'Has method' or icd_code = '93.98')) a
528on d.concept_id = a.icd_id
529; -- 10
530
531-- Insert 'Has revision status' for 'reopening', 'revisoin', 'secondary procedure';
532insert into icd9proc_new_attr_all
533select
534d.concept_code,
535d.concept_id,
536d.concept_name,
537'Has revision status',
538'4116366',
539'255231005',
540'Revision - value',
541'Observation',
542'Qualifier Value',
543'N'
544from devv5.concept d
545join ( select distinct icd_id from icd9proc_new_attr_all where (lower (icd_name) like '%revision%' or lower (icd_name) like '%reopening%' or lower (icd_name) like '%secondary procedure%' )
546and icd_id not in (select icd_id from icd9proc_new_attr_all where attr_rel_id = 'Has revision status' or icd_name like '%secondary membrane%')
547and icd_code not in ('84.8', '49.75','37.8', '37.7', '57.22', '37.89', '84.66', '84.68' , '84.69', '84.67', '02.4' )) a
548on d.concept_id = a.icd_id
549; -- 10
550
551-- Insert 'Evaluation - action' for concepts with '%evaluation%'
552insert into icd9proc_new_attr_all
553select
554d.concept_code,
555d.concept_id,
556d.concept_name,
557'Has method',
558'4044176',
559'129265001',
560'Evaluation - action',
561'Observation',
562'Qualifier Value',
563'N'
564from devv5.concept d
565join ( select distinct icd_id from icd9proc_new_attr_all where lower (icd_name) like '%evaluation%'
566and icd_id not in (select icd_id from icd9proc_new_attr_all where attr_rel_id = 'Has method')) a
567on d.concept_id = a.icd_id
568; -- 2
569
570-- Insert 'Therapy - action' for concepts with '%therapy%'
571insert into icd9proc_new_attr_all
572select
573d.concept_code,
574d.concept_id,
575d.concept_name,
576'Has method',
577'4236214',
578'360270004',
579'Therapy - action',
580'Observation',
581'Qualifier Value',
582'N'
583from devv5.concept d
584join
585(select distinct icd_id from icd9proc_new_attr_all
586where lower (icd_name) like '%therapy%'
587and icd_id not in (select icd_id from icd9proc_new_attr_all where attr_rel_id = 'Has method')
588 ) a
589on d.concept_id = a.icd_id
590; --29
591
592-- Insert 'Using energy' for Hypothermia and Hyperthermia
593insert into icd9proc_new_attr_all
594select
595d.concept_code,
596d.concept_id,
597d.concept_name,
598'Using energy',
599'4112792',
600'285717004',
601'High temperature',
602'Observation',
603'Physical Force',
604'N'
605from devv5.concept d
606join ( select distinct icd_id from icd9proc_new_attr_all where icd_id in (select icd_id from icd9proc_new_attr_all where lower(icd_name) like '%hyperthermia%')
607 and icd_id not in (select icd_id from icd9proc_new_attr_all where attr_rel_id = 'Using energy' )) a
608on d.concept_id = a.icd_id
609; --1
610
611insert into icd9proc_new_attr_all
612select
613d.concept_code,
614d.concept_id,
615d.concept_name,
616'Using energy',
617'4110148',
618'285686007',
619'Low temperature',
620'Observation',
621'Physical Force',
622'N'
623from devv5.concept d
624join ( select distinct icd_id from icd9proc_new_attr_all where icd_id in (select icd_id from ICD9PROC_NEW_ATTR_ALL where lower(icd_name) like '%hypothermia%')
625 and icd_id not in (select icd_id from icd9proc_new_attr_all where attr_rel_id = 'Using energy' )) a
626on d.concept_id = a.icd_id
627; --2
628
629 -- Insert 'Using energy - Radiation' where (icd_name) like '%radiation%', but attr_rel_id != 'Using energy'
630insert into icd9proc_new_attr_all
631select
632d.concept_code,
633d.concept_id,
634d.concept_name,
635'Using energy',
636'4220084',
637'82107009',
638'Radiation',
639'Observation',
640'Physical Force',
641'N'
642from devv5.concept d
643join ( select distinct icd_id from icd9proc_new_attr_all where lower (icd_name) like '%radiation%' and icd_id not in (select icd_id from icd9proc_new_attr_all where attr_rel_id = 'Using energy')
644 ) a
645on d.concept_id = a.icd_id
646; --2
647
648-- Insert 'Using substance' for concepts with '%radio%, when attr_rel_id not in ( 'Using subst', 'Using energy', 'Has dir subst') and icd_id not in ( '2007015', '2006620')
649 insert into icd9proc_new_attr_all
650select
651d.concept_code,
652d.concept_id,
653d.concept_name,
654'Using subst',
655'4232493',
656'89457008',
657'Radioactive isotope',
658'Observation',
659'Substance',
660'N'
661from devv5.concept d
662join ( select distinct icd_id from icd9proc_new_attr_all where lower (icd_name) like '%radio%' and icd_id not in ( '2007015', '2006620', '2006633', '2007446', '2006779', '2007468' )
663and icd_id not in (select icd_id from icd9proc_new_attr_all where attr_rel_id in ( 'Using subst', 'Using energy', 'Has dir subst'))) a
664on d.concept_id = a.icd_id
665;--2
666
667 -- Insert 'Has method - Radionuclide imaging - action' for concepts with '%radioisotope%, when attr_rel_id ! = 'Has method'
668insert into icd9proc_new_attr_all
669select
670d.concept_code,
671d.concept_id,
672d.concept_name,
673'Has method',
674'4207470',
675'312421008',
676'Radionuclide imaging - action',
677'Observation',
678'Qualifier Value',
679'N'
680from devv5.concept d
681join ( select distinct icd_id from icd9proc_new_attr_all where lower (icd_name) like '%radioisotope%'
682and icd_id not in (select icd_id from icd9proc_new_attr_all where attr_rel_id = 'Has method' )) a
683on d.concept_id = a.icd_id; -- 2
684
685--Insert 'Has focus' - ' for heart surgery'
686insert into icd9proc_new_attr_all
687select
688d.concept_code,
689d.concept_id,
690d.concept_name,
691'Has focus',
692'4275564',
693'64915003',
694'Operation on heart',
695'Procedure',
696'Procedure',
697'N'
698from devv5.concept d
699join ( select distinct icd_id from icd9proc_new_attr_all where lower (icd_name) like '% to %surgery%' and icd_id not in (select icd_id from icd9proc_new_attr_all where attr_rel_id = 'Has focus')
700 ) a
701on d.concept_id = a.icd_id
702; --3
703
704--Insert 'Has focus' for 'treatment for cancer'
705insert into icd9proc_new_attr_all
706select
707d.concept_code,
708d.concept_id,
709d.concept_name,
710'Has focus',
711'443392',
712'363346000',
713'Malignant neoplastic disease',
714'Condition',
715'Clinical Finding',
716'N'
717from devv5.concept d
718join ( select distinct icd_id from icd9proc_new_attr_all where lower (icd_name) like '%for %cancer%' and icd_id not in (select icd_id from icd9proc_new_attr_all where attr_rel_id = 'Has focus')
719 ) a
720on d.concept_id = a.icd_id
721; -- 1
722
723-- Insert 'Has method - insertion' where it's absent
724insert into icd9proc_new_attr_all
725select
726d.concept_code,
727d.concept_id,
728d.concept_name,
729'Has method',
730'4106470',
731'257867005',
732'Insertion - action',
733'Observation',
734'Qualifier Value',
735'N'
736from devv5.concept d
737join ( select distinct icd_id from icd9proc_new_attr_all
738where icd_code != '16.62'
739and lower (icd_name) like '%insertion%'
740and lower (icd_name) not like '%insertion or%'
741and lower (icd_name) not like '%insertion,%'
742and icd_id not in
743(select icd_id from icd9proc_new_attr_all
744where lower (attr_name) like '%insertion%'
745or lower (attr_name) like '%intubation%'
746or lower (attr_name) like '%reimplantation%'
747and lower (attr_name) like 'insertion%' )
748 ) a
749on d.concept_id = a.icd_id
750;--13
751
752-- Insert 'Biopsy' 4043842 129314006 Biopsy - action Qualifier Value Observation where it's absent
753insert into icd9proc_new_attr_all
754select
755d.concept_code,
756d.concept_id,
757d.concept_name,
758'Has method',
759'4043842',
760'129314006',
761'Biopsy - action',
762'Observation',
763'Qualifier Value',
764'N'
765from devv5.concept d
766join ( select distinct icd_id from icd9proc_new_attr_all where lower (icd_name) like '%biopsy%'
767and icd_id not in (select icd_id from icd9proc_new_attr_all where lower (attr_name) like '%biopsy%' or lower (attr_name) like 'biopsy%' )
768 ) a
769on d.concept_id = a.icd_id; -- 7
770
771-- Insert Incision - action
772Insert into icd9proc_new_attr_all
773select
774d.concept_code,
775d.concept_id,
776d.concept_name,
777'Has method',
778'4044182',
779'129287005',
780'Incision - action',
781'Observation',
782'Qualifier Value',
783'N'
784from devv5.concept d
785join ( select distinct icd_id from icd9proc_new_attr_all where (lower (icd_name) like '%incision of%' or lower (icd_name) like '%incision') and lower (icd_name) not like '%without incision'
786and icd_id not in (select icd_id from icd9proc_new_attr_all where lower (attr_name) like '%incision%' or lower (attr_name) like 'incision%' )) a
787on d.concept_id = a.icd_id
788; -- 7
789
790--Insert Using device for '%microscopy%' where it's absent
791Insert into icd9proc_new_attr_all
792select
793d.concept_code,
794d.concept_id,
795d.concept_name,
796'Using device',
797'4279164',
798'65473004',
799'Microscope',
800'Device',
801'Physical Object',
802'N'
803from devv5.concept d
804join ( select distinct icd_id from icd9proc_new_attr_all where lower (icd_name) like 'microscop%'
805and icd_id not in (select icd_id from icd9proc_new_attr_all where lower (attr_name) like 'microscope%' or attr_rel_id = 'Using device')) a
806on d.concept_id = a.icd_id; -- 48
807
808-- Insert 'Has method - Surgical action', where it's needed, but absent (esp. for Is a)
809insert into icd9proc_new_attr_all
810select
811d.concept_code,
812d.concept_id,
813d.concept_name,
814'Has method',
815'4045049',
816'129284003',
817'Surgical action',
818'Observation',
819'Qualifier Value',
820'N'
821from devv5.concept d
822join ( select distinct icd_id from icd9proc_new_attr_all where( lower (icd_name) like '%destruction%' or lower (icd_name) like '%implantation or replacement%'
823 or lower (icd_name) like '%incision%' or lower (icd_name) like '%insertion or replacement%' or lower (icd_name) like 'insertion%' or lower (icd_name) like 'operations%')
824and icd_id not in ( select icd_id from icd9proc_new_attr_all where attr_rel_id = 'Has method')
825and icd_id not in (select icd_id from icd9proc_new_attr_all
826where (lower (attr_name) like '%destruction%'
827or lower (attr_name) like '%surg%'
828or lower (attr_name) like '%cauterization%'
829or lower (attr_name) like '%coagulation%'))
830 ) a
831on d.concept_id = a.icd_id
832; -- 37
833
834-- insert missed concepts due to absent SNOMED attributes at all (they have 'F' category)
835create table MISSED_ATTRIBUTES (icd_code varchar (50) , icd_id integer, icd_name varchar (300), attr_rel_id varchar (20), attr_id integer, attr_code varchar (50), attr_name varchar (255), domain_id varchar (20), concept_class_id varchar (20), category varchar (1));
836-- Polina T. home PC
837WbImport -file=C:/Users/Polina/Desktop/ICD9Proc_Attributes_missed_attributes_0705.tsv
838 -type=text
839 -table=missed_attributes
840 -encoding="UTF-8"
841 -header=true
842 -decode=false
843 -dateFormat="yyyy-MM-dd"
844 -timestampFormat="yyyy-MM-dd HH:mm:ss"
845 -delimiter='\t'
846 -decimal=.
847 -fileColumns=icd_id,icd_code,icd_name,attr_rel_id,attr_id,attr_code,attr_name,domain_id,concept_class_id,category
848 -quoteCharEscaping=none
849 -ignoreIdentityColumns=false
850 -deleteTarget=false
851 -continueOnError=false
852 -batchSize=1000;
853
854UPDATE missed_attributes
855 SET attr_id = c.concept_id,
856 domain_id = c.domain_id,
857 concept_class_id = c.concept_class_id
858 from devv5.concept c
859 where attr_code = c.concept_code
860 and c.vocabulary_id = 'SNOMED'
861 ; -- 77
862
863UPDATE missed_attributes
864SET icd_code = '00'
865WHERE icd_code = '0'
866; -- 1
867
868insert into ICD9PROC_NEW_ATTR_ALL
869select
870icd_code,
871icd_id,
872icd_name,
873attr_rel_id,
874attr_id,
875attr_code,
876attr_name,
877domain_id ,
878concept_class_id,
879category
880from MISSED_ATTRIBUTES
881; -- 77
882
883-- delete them (use CTID for pg instead of ROWID)
884delete from ICD9PROC_NEW_ATTR_ALL a
885where exists (select 1
886from ICD9PROC_NEW_ATTR_ALL b
887where b.icd_id = a.icd_id and
888b.attr_rel_id = a.attr_rel_id and
889b.attr_id = a.attr_id and
890b.ctid > a.ctid)
891; -- 75
892
893/*-------------------------
894-------LOAD_INTO-----------
895------STAGE TABLES---------
896---------------------------*/
8971. Update latest_update field to new date
898DO $_$
899BEGIN
900 PERFORM VOCABULARY_PACK.SetLatestUpdate(
901 pVocabularyName => 'ICD9Proc',
902 pVocabularyDate => to_date ('2014-10-01','yyyy-mm-dd'),
903 pVocabularyVersion => 'ICD9CM v32 master descriptions' ,
904 pVocabularyDevSchema => 'DEV_ICD9PROC'
905 );
906END $_$;
907
908
909--2. Truncate all working tables
910TRUNCATE TABLE concept_stage;
911TRUNCATE TABLE concept_relationship_stage;
912TRUNCATE TABLE concept_synonym_stage;
913TRUNCATE TABLE pack_content_stage;
914TRUNCATE TABLE drug_strength_stage;
915
916
917--3. Load into concept_stage
918/*) insertion of icd9proc_concepts from devv5.concept
919with changes in standard_concept value (case when relationship_id ='Maps to' in manual mapping table, then null instead of 'S')
9202) deprecated icd_concepts are excluded from concept_stage */
921INSERT INTO concept_stage (
922 concept_name,
923 domain_id,
924 vocabulary_id,
925 concept_class_id,
926 standard_concept,
927 concept_code,
928 valid_start_date,
929 valid_end_date,
930 invalid_reason
931 )
932SELECT DISTINCT c.concept_name,
933 c.domain_id,
934 c.vocabulary_id,
935 c.concept_class_id,
936 (case when a.relationship_id = 'Maps to' then null else 'S' end),
937 c.concept_code,
938 c.valid_start_date,
939 c.valid_end_date,
940 NULL AS invalid_reason
941FROM devv5.concept c
942LEFT JOIN ICD9PROC_MANMAP_16042018 a
943ON a.icd_id = c.concept_id
944WHERE c.vocabulary_id = 'ICD9Proc'
945AND c.standard_concept = 'S'; -- 4651
946
947--4. load into concept_synonym_stage - isertion of standard icd_concepts from devv5.concept only
948INSERT INTO concept_synonym_stage (
949 synonym_concept_code,
950 synonym_name,
951 synonym_vocabulary_id,
952 language_concept_id
953 )
954SELECT concept_code AS synonym_concept_code,
955 concept_name AS synonym_name,
956 vocabulary_id AS synonym_vocabulary_id,
957 4180186 AS language_concept_id -- English
958FROM devv5.concept
959WHERE vocabulary_id = 'ICD9Proc'
960AND standard_concept = 'S'; -- 4651
961
962--7. Load concept_relationship_stage
963/*1) Insertion of relationships between icd_codes and SNOMED procedures from ICD9PROC_MANMAP_16042018 ('Maps to', 'Is a' 'Subsumes').
9642). Insertion of relationships between icd_codes and SNOMED attributes from ICD9PROC_NEW_ATTR_ALL (excluding 'Maps to')
9653) Insertion of inner icd9proc relationships ('Maps to, Mapped from', 'Subsumes') with changes in invalid_reason value
966(case when r.relationship_id in ('SNOMED - ICD9P eq', 'ICD9P - SNOMED eq' ) then 'D' else null -- 9304 rows) */
967
968INSERT INTO concept_relationship_stage
969(CONCEPT_CODE_1,CONCEPT_CODE_2,VOCABULARY_ID_1,VOCABULARY_ID_2,RELATIONSHIP_ID,VALID_START_DATE,VALID_END_DATE, INVALID_REASON)
970SELECT
971icd_code,
972concept_code,
973'ICD9Proc',
974'SNOMED',
975relationship_id,
976current_date -1,
977to_date ('20991231', 'yyyymmdd'),
978null
979FROM ICD9PROC_MANMAP_16042018
980UNION
981SELECT
982icd_code,
983attr_code,
984'ICD9Proc',
985'SNOMED',
986attr_rel_id,
987current_date -1,
988to_date ('20991231', 'yyyymmdd'),
989null
990FROM ICD9PROC_NEW_ATTR_ALL
991WHERE icd_id NOT IN
992 (SELECT icd_id FROM ICD9PROC_MANMAP_16042018
993 WHERE relationship_id = 'Maps to')
994UNION
995SELECT
996c1.concept_code,
997 c2.concept_code,
998 c1.vocabulary_id,
999 c2.vocabulary_id,
1000 r.relationship_id,
1001 r.valid_start_date,
1002 r.valid_end_date,
1003 (case when r.relationship_id in ('SNOMED - ICD9P eq', 'ICD9P - SNOMED eq' ) then 'D' else null end)
1004FROM devv5.concept_relationship r,
1005 devv5.concept c1,
1006 devv5.concept c2
1007WHERE c1.concept_id = r.concept_id_1
1008 AND (
1009 c1.vocabulary_id = 'ICD9Proc'
1010 OR c2.vocabulary_id = 'ICD9Proc'
1011 )
1012 AND C2.CONCEPT_ID = r.concept_id_2
1013 AND r.invalid_reason IS NULL -- only fresh ones
1014 AND r.relationship_id NOT IN (
1015 'Domain subsumes',
1016 'Is domain') ; -- 35042 (total 54017)
1017
1018--check duplicates
1019SELECT * FROM concept_relationship_stage
1020WHERE (concept_code_1, concept_code_2) in
1021(SELECT concept_code_1, concept_code_2
1022FROM concept_relationship_stage
1023GROUP BY concept_code_1, concept_code_2, relationship_id
1024HAVING COUNT (1) >=2);