· 8 years ago · May 06, 2018, 11:02 PM
1-- pay attention to the specific relationship_ids for ICD9Proc, that are used:
2/*ATTR_REL_ID
3Has access
4Has asso proc
5Has component
6Has dir device
7Has dir morph
8Has dir proc site
9Has dir subst
10Has focus
11Has indir device
12Has indir morph
13Has indir proc site
14Has intent
15Has method
16Has priority
17Has proc device
18Has proc morph
19Has proc site
20Has property
21Has recipient cat
22Has revision status
23Has route of admin
24Has scale type
25Has specimen
26Has surgical appr
27Using acc device
28Using device
29Using energy
30Using subst*/
31
32-- 'Has component', 'Has specimen', 'Has scale type' - normal attributes for measurement
33
34
35--create table to look at the SNOMED attributes of ICD9Proc_to_SNOMED mapping (with subsumes);
36--drop table ICD9PROC_NEW_ATTR;
37create table ICD9PROC_NEW_ATTR as
38select a.icd_code, a.icd_id, a.icd_name, a.relationship_id as sno_rel_id, a.concept_id as sno_id, a.concept_code as sno_code, a.concept_name as sno_name,
39cr.relationship_id as attr_rel_id, c.concept_id as attr_id, c.concept_code as attr_code, c.concept_name as attr_name, c.domain_id, c.concept_class_id,
40c.standard_concept, cr.invalid_reason, c.invalid_reason as category
41from ICD9PROC_MANMAP_16042018 a
42join devv5.concept_relationship cr
43on a.concept_id = cr.concept_id_1 and cr.invalid_reason is null
44and 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',
45'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')
46join concept c
47on c.concept_id = cr.concept_id_2
48and c.vocabulary_id = 'SNOMED'
49and c.standard_concept='S'
50;
51
52-- mark concepts for convenience;
53update ICD9PROC_NEW_ATTR
54set category = 'M'
55where sno_rel_id = 'Maps to'
56; -- 6454
57
58update ICD9PROC_NEW_ATTR
59set category = 'I'
60where icd_id not in ( select icd_id from ICD9PROC_NEW_ATTR where sno_rel_id in ( 'Subsumes', 'Maps to') )
61; -- 4453
62
63update ICD9PROC_NEW_ATTR
64set category = 'S'
65where icd_id in ( select icd_id from ICD9PROC_MANMAP_16042018 where sno_rel_id = 'Subsumes')
66; -- 4106
67
68update ICD9PROC_NEW_ATTR
69set category = 'S'
70where category is null
71; -- 1333
72
73--delete rows with 'Subsumes'
74delete from ICD9PROC_NEW_ATTR where sno_rel_id = 'Subsumes'
75; --4106
76
77-- insert non-standard drugs from SNOMED;
78insert into ICD9PROC_NEW_ATTR
79select
80icd_code,
81icd_id,
82icd_name,
83sno_rel_id,
84sno_id,
85sno_code,
86sno_name,
87relationship_id,
88concept_id,
89concept_code,
90concept_name,
91domain_id,
92concept_class_id,
93standard_concept,
94invalid_reason
95from (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
96from ICD9PROC_NEW_ATTR a
97join devv5.concept_relationship cr on a.sno_id = cr.concept_id_1 and cr.invalid_reason is null
98and cr.relationship_id in ('Has dir subst') and icd_id in (select distinct icd_id from ICD9PROC_NEW_ATTR)
99join devv5.concept c on c.concept_id = cr.concept_id_2
100and c.vocabulary_id = 'SNOMED'
101and c.domain_id = 'Drug'
102)a
103; -- 81 rows (109)
104
105--create the table with pairs 'ancestor-descendant' within the one icd_id group;
106--drop table icd9proc_new_false_pairs;
107create table icd9proc_new_false_pairs as
108select i.icd_id, i.attr_id
109from ICD9PROC_NEW_ATTR i
110join devv5.concept_ancestor a on i.attr_id = a.ancestor_concept_id
111and a.min_levels_of_separation != 0
112join ICD9PROC_NEW_ATTR f on f.icd_id = i.icd_id
113and a.descendant_concept_id = f.attr_id
114;
115
116--delete ancestors when descendant is present;
117delete from ICD9PROC_NEW_ATTR where (icd_id, attr_id) in (select * from icd9proc_new_false_pairs)
118; -- 300
119
120--create table with icd_concepts;
121--drop table ICD9PROC_NEW_ATTR_ALL;
122create table ICD9PROC_NEW_ATTR_ALL as
123(select icd_code, icd_id, icd_name, attr_rel_id, attr_id, attr_code, attr_name, domain_id, concept_class_id, category from ICD9PROC_NEW_ATTR)
124;
125--check relationship_id;
126select distinct attr_rel_id from ICD9PROC_NEW_ATTR_ALL
127; -- 27
128
129--create table for subsequent insert of common attributes of subsumes;
130drop table ICD9PROC_NEW_ATTR_SUBS ;
131create table ICD9PROC_NEW_ATTR_SUBS as
132select a.icd_code, a.icd_id, a.icd_name, a.relationship_id as sno_rel_id, a.concept_id as sno_id, a.concept_code as sno_code, a.concept_name as sno_name,
133cr.relationship_id as attr_rel_id, c.concept_id as attr_id, c.concept_code as attr_code, c.concept_name as attr_name, c.domain_id, c.concept_class_id, c.standard_concept,
134c.invalid_reason as category
135from ICD9PROC_MANMAP_16042018 a
136join devv5.concept_relationship cr
137on a.concept_id = cr.concept_id_1
138and cr.invalid_reason is null
139and 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',
140'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')
141join concept c
142on c.concept_id = cr.concept_id_2
143and c.vocabulary_id = 'SNOMED'
144and c.standard_concept='S'
145and a. icd_id in (select distinct icd_id from ICD9PROC_MANMAP_16042018 where relationship_id = 'Subsumes')
146and a.icd_id in (select distinct icd_id from ICD9PROC_MANMAP_16042018 where relationship_id = 'Is a')
147;
148
149-- insert general attributes of 'Subsumes' of the one 'Is a'
150insert into ICD9PROC_NEW_ATTR_ALL
151select
152icd_code,
153icd_id,
154icd_name,
155attr_rel_id,
156attr_id,
157attr_code,
158attr_name,
159domain_id,
160concept_class_id,
161'N'
162from (select distinct icd_code, icd_id, icd_name, attr_rel_id, attr_id, attr_code, attr_name, domain_id, concept_class_id, category from ICD9PROC_NEW_ATTR_SUBS where
163 attr_id in (select attr_id from ICD9PROC_NEW_ATTR_SUBS where sno_rel_id = 'Subsumes' group by attr_id, attr_rel_id, icd_id having count (1)>=2)
164 and attr_id not in (select attr_id from ICD9PROC_NEW_ATTR_SUBS where sno_rel_id = 'Is a' group by attr_id, attr_rel_id, icd_id having count (1)=1)
165 and attr_id not in (select attr_id from ICD9PROC_NEW_ATTR_SUBS group by attr_id, attr_rel_id, icd_id having count (1)=1))a
166 where (icd_id, attr_rel_id,attr_name) not in (select icd_id, attr_rel_id, attr_name from ICD9PROC_NEW_ATTR_ALL)
167 ; -- 40
168
169-- insert percutaneous access where it's absent;
170insert into ICD9PROC_NEW_ATTR_ALL
171select
172d.concept_code,
173d.concept_id,
174d.concept_name,
175'Has access',
176'4013298',
177'103388001',
178'Percutaneous approach',
179'Observation',
180'Qualifier Value',
181'N'
182from devv5.concept d
183join ( select distinct icd_id from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%percutaneous%'
184and icd_id not in ( select icd_id from ICD9PROC_NEW_ATTR_ALL where lower(attr_name) like '%percutaneous%approach%' or lower(attr_name) like '%percutaneous%transluminal%' or icd_code in ('37.68', '36.01', '36.02', '36.05'))) a
185on d.concept_id = a.icd_id
186; -- 22
187
188-- insert closed access where it's absent (excluding '%closed fracture%' and '%closed reduction%')
189insert into ICD9PROC_NEW_ATTR_ALL
190select
191d.concept_code,
192d.concept_id,
193d.concept_name,
194'Has access',
195'4044379',
196'129237003',
197'Closed approach',
198'Observation',
199'Qualifier Value',
200'N'
201from devv5.concept d
202join ( select distinct icd_id from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%closed %'
203and icd_id not in ( select icd_id from ICD9PROC_NEW_ATTR_ALL where attr_name = 'Closed approach' and attr_rel_id = 'Has access')
204and lower (icd_name) not like '%closed fracture%' and lower (icd_name) not like '%closed reduction%' and lower (icd_name) not like '%percutaneous%') a
205on d.concept_id = a.icd_id
206; --23
207
208-- insert open approach where it's absent (excluding '%open fracture%', '%open reduction%','open and other%', 'other and open%' )
209insert into ICD9PROC_NEW_ATTR_ALL
210select
211d.concept_code,
212d.concept_id,
213d.concept_name,
214'Has access',
215'4044378',
216'129236007',
217'Open approach',
218'Observation',
219'Qualifier Value',
220'N'
221from devv5.concept d
222join ( select distinct icd_id from ICD9PROC_NEW_ATTR_ALL
223where lower (icd_name) like '%open %'
224and icd_id not in
225(select icd_id from ICD9PROC_NEW_ATTR_ALL where attr_name = 'Open approach' and attr_rel_id = 'Has access')
226and lower (icd_name) not like '%open fracture%'
227and lower (icd_name) not like '%open reduction%'
228and lower (icd_name) not like '%open heart%'
229and lower (icd_name) not like 'open and other%'
230and lower (icd_name) not like 'other and open%') a
231on d.concept_id = a.icd_id
232; --18
233
234-- Insert 'Checking - action' where concepts have '% check %' (if it's appropriate);
235insert into ICD9PROC_NEW_ATTR_ALL
236select
237d.concept_code,
238d.concept_id,
239d.concept_name,
240'Has method',
241'4232679',
242'360160009',
243'Checking - action',
244'Observation',
245'Qualifier Value',
246'N'
247from devv5.concept d
248join ( select distinct icd_id from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%check%'
249and icd_id not in (select icd_id from ICD9PROC_NEW_ATTR_ALL where attr_rel_id = 'Has method')) a
250on d.concept_id = a.icd_id
251; -- 5
252
253-- Insert 'Evaluation - action' where concepts have '%stress test%' and Has method ! = 'Evaluation - action' or 'Measurement - action' ;
254insert into ICD9PROC_NEW_ATTR_ALL
255select
256d.concept_code,
257d.concept_id,
258d.concept_name,
259'Has method',
260'4044176',
261'129265001',
262'Evaluation - action',
263'Observation',
264'Qualifier Value',
265'N'
266from devv5.concept d
267join ( select distinct icd_id from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%stress test%'
268and icd_id not in (select icd_id from ICD9PROC_NEW_ATTR_ALL where attr_name in ('Evaluation - action', 'Measurement - action'))) a
269on d.concept_id = a.icd_id
270; -- 2
271
272-- Insert 'Diagnostinc intent' for diagnostic procedures
273insert into ICD9PROC_NEW_ATTR_ALL
274select
275d.concept_code,
276d.concept_id,
277d.concept_name,
278'Has intent',
279'4129646',
280'261004008',
281'Diagnostic intent',
282'Observation',
283'Qualifier Value',
284 'N'
285from devv5.concept d
286join ( select distinct icd_id from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%diagnostic%'
287and icd_id not in (select icd_id from ICD9PROC_NEW_ATTR_ALL where attr_rel_id = 'Has intent' or attr_name = 'Examination - action')) a
288on d.concept_id = a.icd_id
289; -- 102;
290
291--Insert 'Using subst' - 'Contrast media', where icd_name with '% contrast %', bit attr_rel_id != 'Using subst'
292insert into ICD9PROC_NEW_ATTR_ALL
293select
294d.concept_code,
295d.concept_id,
296d.concept_name,
297'Using subst',
298'4299338',
299'385420005',
300'Contrast media',
301'Observation',
302'Substance',
303 'N'
304from devv5.concept d
305join ( select distinct icd_id from ICD9PROC_NEW_ATTR_ALL where (lower (icd_name) like '% contrast %' or lower (icd_name) like 'contrast%' or lower (icd_name) like '%contrast')
306and icd_id not in (select icd_id from ICD9PROC_NEW_ATTR_ALL where attr_rel_id = 'Using subst')) a
307on d.concept_id = a.icd_id
308; --12
309
310--Insert 'Has focus - Alcoholism' for concepts with alcohol issues;
311insert into ICD9PROC_NEW_ATTR_ALL
312select
313d.concept_code,
314d.concept_id,
315d.concept_name,
316'Has focus',
317'4218106',
318'7200002',
319'Alcoholism',
320'Condition',
321'Clinical Finding',
322 'N'
323from devv5.concept d
324join ( select distinct icd_id from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%alcohol%' and lower (icd_name) not like '%drug%'
325and icd_id not in (select icd_id from ICD9PROC_NEW_ATTR_ALL where ATTR_REL_ID = 'Has focus')) a
326on d.concept_id = a.icd_id
327; -- 4
328
329--Insert 'Has focus - Substance abuse' for concepts with drug issues (be careful with it!);
330insert into ICD9PROC_NEW_ATTR_ALL
331select
332d.concept_code,
333d.concept_id,
334d.concept_name,
335'Has focus',
336'4279309',
337'66214007',
338'Substance abuse',
339'Condition',
340'Clinical Finding',
341'N'
342from devv5.concept d
343join ( select distinct icd_id from ICD9PROC_NEW_ATTR_ALL
344where icd_code != '94.25' and (
345lower (icd_name) like '%drug %'
346or lower (icd_name) like '%alcohol and drug%'
347and lower (icd_name) not like '%drug therapy%')
348and icd_id not in
349(select icd_id from ICD9PROC_NEW_ATTR_ALL
350where ATTR_REL_ID = 'Has focus')) a
351on d.concept_id = a.icd_id
352; --8
353
354--Insert 'Inspection - action' for '%control of%' and attr_name like '%bleeding%';
355insert into ICD9PROC_NEW_ATTR_ALL
356select
357d.concept_code,
358d.concept_id,
359d.concept_name,
360'Has method',
361'4043866',
362'129433002',
363'Inspection - action',
364'Observation',
365'Qualifier Value',
366'N'
367from devv5.concept d
368join ( select distinct icd_id from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%control of%' -- and attr_name like '%bleeding%'
369and icd_id not in (select icd_id from ICD9PROC_NEW_ATTR_ALL where ATTR_REL_ID = 'Has method' or icd_code = '93.98')) a
370on d.concept_id = a.icd_id
371; -- 10
372
373-- Insert 'Has revision status' for 'reopening', 'revisoin', 'secondary procedure';
374insert into ICD9PROC_NEW_ATTR_ALL
375select
376d.concept_code,
377d.concept_id,
378d.concept_name,
379'Has revision status',
380'4116366',
381'255231005',
382'Revision - value',
383'Observation',
384'Qualifier Value',
385'N'
386from devv5.concept d
387join ( 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%' )
388and 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%')
389and 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
390on d.concept_id = a.icd_id
391; -- 10
392
393-- Insert 'Evaluation - action' for concepts with '%evaluation%'
394insert into ICD9PROC_NEW_ATTR_ALL
395select
396d.concept_code,
397d.concept_id,
398d.concept_name,
399'Has method',
400'4044176',
401'129265001',
402'Evaluation - action',
403'Observation',
404'Qualifier Value',
405'N'
406from devv5.concept d
407join ( select distinct icd_id from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%evaluation%'
408and icd_id not in (select icd_id from ICD9PROC_NEW_ATTR_ALL where attr_rel_id = 'Has method')) a
409on d.concept_id = a.icd_id
410; -- 2
411
412-- Insert 'Therapy - action' for concepts with '%therapy%'
413insert into ICD9PROC_NEW_ATTR_ALL
414select
415d.concept_code,
416d.concept_id,
417d.concept_name,
418'Has method',
419'4236214',
420'360270004',
421'Therapy - action',
422'Observation',
423'Qualifier Value',
424'N'
425from devv5.concept d
426join
427(select distinct icd_id from ICD9PROC_NEW_ATTR_ALL
428where lower (icd_name) like '%therapy%'
429and icd_id not in (select icd_id from ICD9PROC_NEW_ATTR_ALL where attr_rel_id = 'Has method')
430 ) a
431on d.concept_id = a.icd_id
432; --29
433
434-- Insert 'Using energy' for Hypothermia and Hyperthermia
435insert into ICD9PROC_NEW_ATTR_ALL
436select
437d.concept_code,
438d.concept_id,
439d.concept_name,
440'Using energy',
441'4112792',
442'285717004',
443'High temperature',
444'Observation',
445'Physical Force',
446'N'
447from devv5.concept d
448join ( 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%')
449 and icd_id not in (select icd_id from ICD9PROC_NEW_ATTR_ALL where attr_rel_id = 'Using energy' )) a
450on d.concept_id = a.icd_id
451; --1
452
453insert into ICD9PROC_NEW_ATTR_ALL
454select
455d.concept_code,
456d.concept_id,
457d.concept_name,
458'Using energy',
459'4110148',
460'285686007',
461'Low temperature',
462'Observation',
463'Physical Force',
464'N'
465from devv5.concept d
466join ( 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%')
467 and icd_id not in (select icd_id from ICD9PROC_NEW_ATTR_ALL where attr_rel_id = 'Using energy' )) a
468on d.concept_id = a.icd_id
469; --2
470
471 -- Insert 'Using energy - Radiation' where (icd_name) like '%radiation%', but attr_rel_id != 'Using energy'
472insert into ICD9PROC_NEW_ATTR_ALL
473select
474d.concept_code,
475d.concept_id,
476d.concept_name,
477'Using energy',
478'4220084',
479'82107009',
480'Radiation',
481'Observation',
482'Physical Force',
483'N'
484from devv5.concept d
485join ( 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')
486 ) a
487on d.concept_id = a.icd_id
488; --2
489
490-- 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')
491 insert into ICD9PROC_NEW_ATTR_ALL
492select
493d.concept_code,
494d.concept_id,
495d.concept_name,
496'Using substance',
497'4232493',
498'89457008',
499'Radioactive isotope',
500'Observation',
501'Substance',
502'N'
503from devv5.concept d
504join ( 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' )
505and 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
506on d.concept_id = a.icd_id
507;--2
508
509 -- Insert 'Has method - Radionuclide imaging - action' for concepts with '%radioisotope%, when attr_rel_id ! = 'Has method'
510insert into ICD9PROC_NEW_ATTR_ALL
511select
512d.concept_code,
513d.concept_id,
514d.concept_name,
515'Has method',
516'4207470',
517'312421008',
518'Radionuclide imaging - action',
519'Observation',
520'Qualifier Value',
521'N'
522from devv5.concept d
523join ( select distinct icd_id from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%radioisotope%'
524and icd_id not in (select icd_id from ICD9PROC_NEW_ATTR_ALL where attr_rel_id = 'Has method' )) a
525on d.concept_id = a.icd_id; -- 2
526
527--Insert 'Has focus' - ' for heart surgery'
528insert into ICD9PROC_NEW_ATTR_ALL
529select
530d.concept_code,
531d.concept_id,
532d.concept_name,
533'Has focus',
534'4275564',
535'64915003',
536'Operation on heart',
537'Procedure',
538'Procedure',
539'N'
540from devv5.concept d
541join ( 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')
542 ) a
543on d.concept_id = a.icd_id
544; --3
545
546--Insert 'Has focus' for 'treatment for cancer'
547insert into ICD9PROC_NEW_ATTR_ALL
548select
549d.concept_code,
550d.concept_id,
551d.concept_name,
552'Has focus',
553'443392',
554'363346000',
555'Malignant neoplastic disease',
556'Condition',
557'Clinical Finding',
558'N'
559from devv5.concept d
560join ( 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')
561 ) a
562on d.concept_id = a.icd_id
563; -- 1
564
565-- Insert 'Has method - insertion' where it's absent
566insert into ICD9PROC_NEW_ATTR_ALL
567select
568d.concept_code,
569d.concept_id,
570d.concept_name,
571'Has method',
572'4106470',
573'257867005',
574'Insertion - action',
575'Observation',
576'Qualifier Value',
577'N'
578from devv5.concept d
579join ( select distinct icd_id from ICD9PROC_NEW_ATTR_ALL
580where icd_code != '16.62'
581and lower (icd_name) like '%insertion%'
582and lower (icd_name) not like '%insertion or%'
583and lower (icd_name) not like '%insertion,%'
584and icd_id not in
585(select icd_id from ICD9PROC_NEW_ATTR_ALL
586where lower (attr_name) like '%insertion%'
587or lower (attr_name) like '%intubation%'
588or lower (attr_name) like '%reimplantation%'
589and lower (attr_name) like 'insertion%' )
590 ) a
591on d.concept_id = a.icd_id
592;--13
593
594-- Insert 'Biopsy' 4043842 129314006 Biopsy - action Qualifier Value Observation where it's absent
595insert into ICD9PROC_NEW_ATTR_ALL
596select
597d.concept_code,
598d.concept_id,
599d.concept_name,
600'Has method',
601'4043842',
602'129314006',
603'Biopsy - action',
604'Observation',
605'Qualifier Value',
606'N'
607from devv5.concept d
608join ( select distinct icd_id from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%biopsy%'
609and icd_id not in (select icd_id from ICD9PROC_NEW_ATTR_ALL where lower (attr_name) like '%biopsy%' or lower (attr_name) like 'biopsy%' )
610 ) a
611on d.concept_id = a.icd_id; -- 7
612
613
614-- Insert Incision - action
615Insert into ICD9PROC_NEW_ATTR_ALL
616select
617d.concept_code,
618d.concept_id,
619d.concept_name,
620'Has method',
621'4044182',
622'129287005',
623'Incision - action',
624'Observation',
625'Qualifier Value',
626'N'
627from devv5.concept d
628join ( 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'
629and 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
630on d.concept_id = a.icd_id
631; -- 7
632
633
634--Insert Using device for '%microscopy%' where it's absent
635Insert into ICD9PROC_NEW_ATTR_ALL
636select
637d.concept_code,
638d.concept_id,
639d.concept_name,
640'Using device',
641'4279164',
642'65473004',
643'Microscope',
644'Device',
645'Physical Object',
646'N'
647from devv5.concept d
648join ( select distinct icd_id from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like 'microscop%'
649and 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
650on d.concept_id = a.icd_id; -- 48
651
652-- Insert 'Has method - Surgical action', where it's needed, but absent (esp. for Is a)
653insert into ICD9PROC_NEW_ATTR_ALL
654select
655d.concept_code,
656d.concept_id,
657d.concept_name,
658'Has method',
659'4045049',
660'129284003',
661'Surgical action',
662'Observation',
663'Qualifier Value',
664'N'
665from devv5.concept d
666join ( select distinct icd_id from ICD9PROC_NEW_ATTR_ALL where( lower (icd_name) like '%destruction%' or lower (icd_name) like '%implantation or replacement%'
667 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%')
668and icd_id not in ( select icd_id from ICD9PROC_NEW_ATTR_ALL where attr_rel_id = 'Has method')
669and icd_id not in (select icd_id from ICD9PROC_NEW_ATTR_ALL
670where (lower (attr_name) like '%destruction%'
671or lower (attr_name) like '%surg%'
672or lower (attr_name) like '%cauterization%'
673or lower (attr_name) like '%coagulation%'))
674 ) a
675on d.concept_id = a.icd_id
676; -- 37
677
678-- insert missed concepts due to absent SNOMED attributes at all (they have 'F' category)
679--drop table MISSED_ATTRIBUTES;
680create table MISSED_ATTRIBUTES (icd_code varchar (50) , icd_id integer, icd_name varchar (300), attr_rel_id varchar (20), attr_id integer,
681attr_code varchar (50), attr_name varchar (255), domain_id varchar (20), concept_class_id varchar (20), category varchar (1));
682
683-- Polina T. home PC
684WbImport -file=C:/Users/Polina/Desktop/ICD9Proc_Attributes_missed_attributes_0705.tsv
685 -type=text
686 -table=missed_attributes
687 -encoding="UTF-8"
688 -header=true
689 -decode=false
690 -dateFormat="yyyy-MM-dd"
691 -timestampFormat="yyyy-MM-dd HH:mm:ss"
692 -delimiter='\t'
693 -decimal=.
694 -fileColumns=icd_id,icd_code,icd_name,attr_rel_id,attr_id,attr_code,attr_name,domain_id,concept_class_id,category
695 -quoteCharEscaping=none
696 -ignoreIdentityColumns=false
697 -deleteTarget=false
698 -continueOnError=false
699 -batchSize=1000;
700
701/*WbImport -file="C:/Users/pdmitrenko/Desktop/Attributes/ICD9Proc Attributes - Missed attributes.tsv"
702 -type=text
703 -table=missed_attributes
704 -encoding="UTF-8"
705 -header=true
706 -decode=false
707 -dateFormat="yyyy-MM-dd"
708 -timestampFormat="yyyy-MM-dd HH:mm:ss"
709 -delimiter='\t'
710 -decimal=.
711 -fileColumns=icd_id,icd_code,icd_name,attr_rel_id,attr_id,attr_code,attr_name,domain_id,concept_class_id,category
712 -quoteCharEscaping=none
713 -ignoreIdentityColumns=false
714 -deleteTarget=false
715 -continueOnError=false
716 -batchSize=1000; */
717
718UPDATE missed_attributes
719 SET attr_id = c.concept_id,
720 domain_id = c.domain_id,
721 concept_class_id = c.concept_class_id
722 from devv5.concept c
723 where attr_code = c.concept_code
724 and c.vocabulary_id = 'SNOMED'
725 ; -- 77
726
727UPDATE missed_attributes
728SET icd_code = '00'
729WHERE icd_code = '0'
730; -- 1
731
732insert into ICD9PROC_NEW_ATTR_ALL
733select
734icd_code,
735icd_id,
736icd_name,
737attr_rel_id,
738attr_id,
739attr_code,
740attr_name,
741domain_id ,
742concept_class_id,
743category
744from MISSED_ATTRIBUTES
745; -- 77
746
747--check attribute duplicates; all of them are not about new insertion;
748select * from ICD9PROC_NEW_ATTR_ALL
749where (icd_id, attr_id) in (
750select icd_id, attr_id from
751(select icd_id, attr_id, count (1) over (partition by icd_id, attr_rel_id, attr_id ) as cnt from ICD9PROC_NEW_ATTR_ALL i ) a
752where cnt >=2)
753 ; -- 146
754
755-- delete them (use CTID for pg instead of ROWID)
756delete from ICD9PROC_NEW_ATTR_ALL a
757where exists (select 1
758from ICD9PROC_NEW_ATTR_ALL b
759where b.icd_id = a.icd_id and
760b.attr_rel_id = a.attr_rel_id and
761b.attr_id = a.attr_id and
762b.ctid > a.ctid)
763; -- 75
764
765
766-----------------------------
767----- only visual check -----
768-----------------------------
769
770-- look at attr_rel_id = 'Has method' that absent
771select * from ICD9PROC_NEW_ATTR_ALL where icd_code not in (select icd_code from ICD9PROC_NEW_ATTR_ALL where
772attr_rel_id = 'Has method'); -- 431
773
774--cehck all using (%catheter% '%device%')
775select * from ICD9PROC_NEW_ATTR_ALL where icd_code in (select icd_code from ICD9PROC_NEW_ATTR_ALL where
776icd_name like '%using %'); -- 65
777
778-- check alone concepts;
779select * from ICD9PROC_NEW_ATTR_ALL where icd_code in (select icd_code from ICD9PROC_NEW_ATTR_ALL group by icd_code
780having count (1) = 1); -- 243
781
782-- chech 'incision'
783select * from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%incision %' and lower (icd_name) not like '%without incision%' and
784icd_id not in (select icd_id from ICD9PROC_NEW_ATTR_ALL where
785lower (attr_name) like '%incision%' or lower (attr_name) like '%dissection%' or lower (attr_name) like '%osteotomy%' or lower (attr_name) like '%surgical removal%'); -- 223
786
787-- check training
788select * from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%training %';--7
789
790select * from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%of%site' ;
791
792--check 'stoma' or 'stomy'
793select * from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%stoma %' or lower (icd_name) like '%stomy %';
794
795select * from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%jejunostomy%' or lower (icd_name) like '%stomy%';
796
797--what should we do with multiple 'procedure site'? -- nothing
798 select * from ICD9PROC_NEW_ATTR_ALL
799where (icd_id, attr_id) in (
800 select icd_id, attr_id from ( select icd_id, attr_id, count (1) over (partition by icd_id, attr_id ) as cnt from ICD9PROC_NEW_ATTR_ALL i )
801 where cnt >=2)
802 and icd_id in (select icd_id from ICD9PROC_NEW_ATTR_ALL where ATTR_REL_ID ='Has proc site');
803
804--check bypass;
805select * from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%bypass%';
806
807--check fracture;
808select * from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%open fracture%'
809and icd_id not in (select icd_id from ICD9PROC_NEW_ATTR_ALL where attr_name = 'Fracture, open'); -- it's about focus of
810
811--look at '%laparoscopic%', '%endoscop%';
812select * from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%laparoscopic%'
813and icd_code not in (select icd_code from ICD9PROC_NEW_ATTR_ALL where lower (attr_name) like '%laparoscope%');
814
815select * from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%endoscop%'
816and icd_code not in (select icd_code from ICD9PROC_NEW_ATTR_ALL where lower (attr_name) like '%scope%');
817
818--chceck contrast
819select * from ICD9PROC_NEW_ATTR_ALL
820where lower (icd_name) like '%contrast %'
821and icd_id not in (select icd_id from ICD9PROC_NEW_ATTR_ALL where attr_rel_id = 'Using subst');
822
823-- check injection
824select * from ICD9PROC_NEW_ATTR_ALL where icd_id in (select icd_id from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like '%injection%' and lower (icd_name) not like '%injection or%' )
825and icd_id not in (select icd_id from ICD9PROC_NEW_ATTR_ALL where lower (attr_name) like 'injection%' or lower (attr_name) like '%injection%' or lower (attr_name) like 'administration%') ;
826
827-- check method for concepts with '%microscop%'
828select * from ICD9PROC_NEW_ATTR_ALL where icd_id in (select icd_id from ICD9PROC_NEW_ATTR_ALL where lower (icd_name) like 'microscop%')
829and icd_id not in (select icd_id from ICD9PROC_NEW_ATTR_ALL where attr_rel_id = 'Has method') ;
830
831--check 'Substance'
832select * from ICD9PROC_NEW_ATTR_ALL where icd_id in (select icd_id from ICD9PROC_NEW_ATTR_ALL where attr_name = 'Substance');
833
834---------------------------------------
835---------------------------------------