BVCVgydX

1-- create source table after manual correction; 
2
3create table icd9cm_to_snom_allmap (ICD_ID number,	ICD_CODE VARCHAR (50), 	ICD_NAME VARCHAR(255), 	RELATIONSHIP_ID VARCHAR (55),	CONCEPT_CODE VARCHAR2(255), 	CONCEPT_NAME VARCHAR (255),	
4	CONCEPT_ID number,	CONCEPT_CLASS_ID  VARCHAR (20),	DOMAIN_ID	VARCHAR (20), CNT number,	ICD_INV_REASON VARCHAR (1), EXTRA VARCHAR (20)); 
5
6
7WbImport -file=C:/Users/pdmitrenko/Desktop/icd9cm_to_snomed_verylast.txt
8         -type=text
9         -table=ICD9CM_TO_SNOM_ALLMAP
10         -encoding="UTF-8"
11         -header=true
12         -decode=false
13         -dateFormat="yyyy-MM-dd"
14         -timestampFormat="yyyy-MM-dd HH:mm:ss"
15         -delimiter='\t'
16         -decimal=.
17         -fileColumns=ICD_ID,ICD_CODE,ICD_NAME,RELATIONSHIP_ID,CONCEPT_CODE,CONCEPT_NAME,CONCEPT_ID,CONCEPT_CLASS_ID,DOMAIN_ID,CNT,ICD_INV_REASON,EXTRA
18         -quoteCharEscaping=none
19         -ignoreIdentityColumns=false
20         -deleteTarget=false
21         -continueOnError=false
22         -batchSize=1000;
23
24         
25--check for any absent data in the fields;  
26
27select * from icd9cm_to_snom_allmap a 
28where icd_id is null
29or icd_code is null
30or icd_name is null 
31or relationship_id is null
32or concept_code is null 
33or concept_name is null
34; 
35
36--use name_matching for cases, when the concept_code is rounded somehow;
37update icd9cm_to_snom_allmap m set concept_code = (
38select aa.concept_code from 
39(
40select M.concept_name,m.icd_code, c.concept_code from icd9cm_to_snom_allmap m 
41join concept c on m.concept_name =c.concept_name and c.invalid_reason is null and c.vocabulary_id ='SNOMED' and c.concept_class_id !='Morph Abnormality'  and c.concept_class_id !='Organism'
42left join concept c2 on c2.concept_code = m.concept_code and c2.invalid_reason is null and c2.vocabulary_id ='SNOMED'
43where c2.concept_id is null
44) aa where aa.concept_name = m.concept_name and aa.icd_code = m.icd_code )
45where exists 
46(
47select 1 from 
48(
49select M.concept_name ,m.icd_code ,c.concept_code from icd9cm_to_snom_allmap m 
50join concept c on m.concept_name =c.concept_name and c.invalid_reason is null and c.vocabulary_id ='SNOMED' and c.concept_class_id !='Morph Abnormality'  and c.concept_class_id !='Organism'
51left join concept c2 on c2.concept_code = m.concept_code and c2.invalid_reason is null and c2.vocabulary_id ='SNOMED'
52where c2.concept_id is null
53) aa where aa.concept_name = m.concept_name and aa.icd_code = m.icd_code )
54;
55
56--fill the possible gaps (in a case, when somebody has forgotten to put concept_names during the mapping. Another fields (concept_id, domain_id, concept_class_id) also can be updated for convenience and presentation purpose));  
57
58MERGE INTO icd9cm_to_snom_allmap e
59    USING devv5.concept h
60    ON (e.concept_code = h.concept_code and h. vocabulary_id = 'SNOMED'  and h.standard_concept = 'S'
61and h.invalid_reason is null) 
62  WHEN MATCHED THEN
63    UPDATE SET e.concept_name = h.concept_name,
64     e.concept_id = h.concept_id,
65     e.domain_id = h.domain_id,
66     e.concept_class_id = h.concept_class_id
67     ;
68     
69--check icd_codes (they could be rounded somehow or missed due to inconsiderate mapping);
70select count ( distinct icd_code)  from icd9cm_to_snom_allmap 
71union all
72select count (distinct a.icd_code) from devv5.concept c
73join icd9cm_to_snom_allmap a on a.icd_code=c .concept_code
74and c. vocabulary_id = 'ICD9CM' 
75; 
76--icd_codes could be rounded somehow or missed due to inconsiderate mapping, so fix it;
77  MERGE INTO icd9cm_to_snom_allmap e
78    USING devv5.concept h
79    ON (e.icd_id = h.concept_id and e.icd_name=h.concept_name and h. vocabulary_id = 'ICD9CM'  
80and h.invalid_reason is null) 
81  WHEN MATCHED THEN
82    UPDATE SET e.icd_code = h.concept_code
83    ;
84       
85commit
86;
87
88-- check mapping for invalid or non-standard concepts (if you wanted exactly  "invalid and non-standard concepts", you need to use OR query, but it's redundant as invalid concept is always non-standard);
89-- there is no need to use  'c.invalid_reason is not null' otherwise you miss the cases when it's mapped to a valid but non-standard concept
90select * from  icd9cm_to_snom_allmap a
91join concept c on  a.concept_code  = c.concept_code and c.vocabulary_id = 'SNOMED'
92where 
93c.standard_concept is null
94
95--check the mapping by the concept_code similarity;
96
97select count (*) from
98 (
99select a.ICD_ID,a.ICD_CODE,a.ICD_NAME,a.RELATIONSHIP_ID, a.CONCEPT_CODE, a.CONCEPT_NAME, 
100a.EXTRA, c.CONCEPT_ID, c.CONCEPT_CLASS_ID,c.DOMAIN_ID,a.CNT, a.ICD_INV_REASON from icd9cm_to_snom_allmap a
101 join devv5.concept c on a.CONCEPT_CODE = c.concept_code
102where c.vocabulary_id = 'SNOMED' 
103and standard_concept = 'S')
104;  
105
106-- look at them if needed; 
107select * from icd9cm_to_snom_allmap where concept_code not in 
108 (select a.concept_code  from icd9cm_to_snom_allmap a
109 join devv5.concept c on a.CONCEPT_CODE = c.concept_code
110where c.vocabulary_id = 'SNOMED' 
111and standard_concept = 'S')
112; 
113
114--check deprecated icd_codepts (there are 14 cocnepts);
115select * from icd9cm_to_snom_allmap 
116where icd_code in (select concept_code from devv5.concept where invalid_reason is not null and vocabulary_id = 'ICD9CM'
117;
118
119 
120--check used domains and concept classes (Observation, Device, Procedure, Measurement, Spec Disease Status, Meas Value, Condition);
121select distinct c.domain_id from  icd9cm_to_snom_allmap a
122join concept c on a.concept_code = c.concept_code
123where c.vocabulary_id = 'SNOMED'
124and c.standard_concept = 'S'
125and c.invalid_reason is null
126;
127
128--look at them( so, relationship_id of 13 concepts was corrected according to the domain);
129select * from  icd9cm_to_snom_allmap a
130join concept c on  a.concept_code = c.concept_code
131where c.vocabulary_id = 'SNOMED'
132and c.standard_concept = 'S'
133and c.invalid_reason is null
134and c.domain_id in ('Device', 'Spec Disease Status', 'Meas Value')
135;
136
137--check concept_class_id;
138select distinct c.concept_class_id from  icd9cm_to_snom_allmap a
139join concept c on  a.concept_code = c.concept_code
140where c.vocabulary_id = 'SNOMED'
141and c.standard_concept = 'S'
142and c.invalid_reason is null
143;
144
145--look at them (there are 5  Morph abnormality and 3 Organisms);
146select a.icd_code, a.icd_name, a.concept_name, a.relationship_id, a.concept_code, c.concept_class_id from  icd9cm_to_snom_allmap a
147join concept c on   a.concept_code = c.concept_code
148where c.vocabulary_id = 'SNOMED'
149and c.standard_concept = 'S'
150and c.invalid_reason is null
151and c.concept_class_id not in ('Procedure', 'Clinical Finding', 'Context-dependent', 'Event')
152;
153
154
155--check whether we have suspicious domains and / or classes
156select distinct c.domain_id from icd9cm_to_snom_allmap m
157join concept c on m.concept_code = c.concept_code 
158and c.vocabulary_id ='SNOMED' 
159and c.invalid_reason is null 
160and relationship_id= 'Maps to'
161;
162
163--look at  'Procedure' domain; 
164select  * from icd9cm_to_snom_allmap m
165join concept c on m.concept_code = c.concept_code   
166and c.vocabulary_id ='SNOMED' 
167and c.invalid_reason is null 
168and relationship_id= 'Maps to' 
169where c.domain_id = 'Procedure'
170;
171
172--check 'Spec Disease Status' domain;
173select * from icd9cm_to_snom_allmap m
174join concept c on m.concept_code = c.concept_code   
175and c.vocabulary_id ='SNOMED' 
176and c.invalid_reason is null 
177and relationship_id= 'Maps to'
178where c.domain_id in ('Spec Disease Status')
179;
180
181--check 'Qualifier Value' concept class;
182select * from icd9cm_to_snom_allmap m
183join concept c on m.concept_code = c.concept_code   
184and c.vocabulary_id ='SNOMED' 
185and c.invalid_reason is null 
186and relationship_id= 'Maps to'
187where c.concept_class_id = 'Qualifier Value' 
188;
189
190--check classification concepts (they shoud be mapped one-to-one);
191select * from 
192(select icd_code from icd9cm_to_snom_allmap 
193where icd_code not like  '%.%') 
194GROUP BY icd_code
195HAVING COUNT(1) >1 
196;
197      
198  
199--look at or_concepts (for Snomed_Extension) 
200select * from  icd9cm_to_snom_allmap where extra = 'orc'
201;
202
203--look at concepts with incorrect domain (according to 'Extra' field - indom) -- 17  (all refer to 'Unilateral', it's OK);
204select * from icd9cm_to_snom_allmap where extra like 'indom'
205;
206
207
208--Look at 'pregnant' concepts and make them pregnant (there is the same thing with 'postpartum'). Be careful, they can have duplicate according to the icd_code. 
209--Also guys put 'pregnant'/'postpartum' to classification concepts, but that was a mistake;  
210
211select count (icd_code) from icd9cm_to_snom_allmap where extra like 'pregn%'
212; 
213
214select count (distinct icd_code) from ( select icd_code, icd_name from icd9cm_to_snom_allmap where extra = 'pregnant' and icd_code like '%.%' 
215GROUP BY icd_code, icd_name HAVING COUNT(1)=1)
216; 
217 
218select count (icd_code) from icd9cm_to_snom_allmap where extra like 'postp%'
219; 
220
221select count ( distinct icd_code) from ( select icd_code, icd_name from icd9cm_to_snom_allmap where extra = 'postpartum' and icd_code like '%.%' 
222GROUP BY icd_code, icd_name HAVING COUNT(1)=1)
223; 
224
225--then insert 'pregnant' concepts;
226--check for possible duplicates in a case, when row with manually mapped 'Finding related to pregnancy' has 'pregnant' in the 'Extra' field; 
227
228select * from icd9cm_to_snom_allmap where concept_code =  '118185001'; --'Finding related to pregnancy'
229;
230
231insert into icd9cm_to_snom_allmap  (ICD_ID,ICD_CODE,ICD_NAME,RELATIONSHIP_ID,CONCEPT_CODE,CONCEPT_NAME,CONCEPT_ID,CONCEPT_CLASS_ID,DOMAIN_ID,CNT,ICD_INV_REASON,EXTRA)
232select 
233ICD_ID,
234ICD_CODE,
235ICD_NAME,
236RELATIONSHIP_ID,
237'118185001', 
238'Finding related to pregnancy',
239'444094',
240'Clinical Finding',
241'Condition',
242null,
243null,
244null 
245from icd9cm_to_snom_allmap
246where extra = 'pregnant' and icd_code like '%.%'
247;
248
249-- do the same thing with 'postpartum';
250insert into icd9cm_to_snom_allmap   (ICD_ID,ICD_CODE,ICD_NAME,RELATIONSHIP_ID,CONCEPT_CODE,CONCEPT_NAME,CONCEPT_ID,CONCEPT_CLASS_ID,DOMAIN_ID,CNT,ICD_INV_REASON,EXTRA)
251select 
252ICD_ID,
253ICD_CODE,
254ICD_NAME,
255RELATIONSHIP_ID,
256'118213005', 
257'Postpartum finding',
258'4041280', 
259'Clinical Finding',
260'Condition',
261null,
262null,
263null 
264from icd9cm_to_snom_allmap
265where extra = 'postpartum' and icd_code like '%.%'
266;
267commit
268;
269
270--Check all malignant neoplasms without mention of their origin ( 'Primary' or 'Secondary'). They should be mapped to 'Primary';
271select icd_code, icd_name, concept_name,  concept_code from icd9cm_to_snom_allmap where icd_name like '%malignant%'
272and icd_name not like '%lymphoma%'
273and lower (icd_name) not like '%genetic%'
274and lower (icd_name) not like '%history%'
275and lower (icd_name) not like '%malaria%'
276and lower (icd_name) not like '%hypertens%'
277and lower (icd_name) not like '%screening%'
278and lower (concept_name) not like '%secondary%'
279;
280
281select * from icd9cm_to_snom_allmap where icd_name like '%primary%'
282;
283
284--Check all 'Unilateral' concepts (everything is OK);
285
286select icd_code, icd_name, concept_name, concept_code from 	icd9cm_to_snom_allmap 
287where lower( icd_name) like '%unilateral%'
288;
289 
290
291;
292------------------------------------------------------
293--check of all 'history';
294select * from 	icd9cm_to_snom_allmap where lower (icd_name) like  '%history%';
295--check of 'Family history', pay attention to 'relationship_id''
296select *from icd9cm_to_snom_allmap where concept_code  ='416471007' and icd_code like '%.%'; -- 62
297select * from icd9cm_to_snom_allmap where icd_name like 'Family history%' and icd_code like '%.%' -- excluding classification concepts;
298; --120
299
300-- check of 'Personal history';
301select * from icd9cm_to_snom_allmap where   icd_name like 'Personal history%' and icd_code like '%.%';
302
303--check alone concepts (without 'maps to value'); 
304select icd_code, icd_name from icd9cm_to_snom_allmap 
305where icd_name like 'Personal history%' 
306and icd_code like '%.%'
307GROUP BY icd_code, icd_name HAVING COUNT(1)=1
308;
309
310
311/*--develop a Maps to value entity (smthg) from "history of smthg" (unfortunately, in this case it didn't help us)
312 insert into icd9cm_to_snom_allmap
313select a.ICD_ID, a.ICD_CODE, a.ICD_NAME, 'Maps to value', c2.concept_code, c2.concept_name, c.concept_id, c2.concept_class_id, c2.domain_id,null, null, null from icd9cm_to_snom_allmap a
314join concept c on c.concept_code = a.concept_code  and c.invalid_reason is null and c.vocabulary_id ='SNOMED'
315join concept_relationship r on r.concept_id_1 = c.concept_id and r.relationship_id like 'Has asso%'
316join concept c2 on r.concept_id_2 = c2.concept_id  and c2.vocabulary_id ='SNOMED' and c2.invalid_reason is null
317left join icd9cm_to_snom_allmap b on a.ICD_CODE = b.ICD_CODE and b.RELATIONSHIP_ID ='Maps to value'
318 where (a.icd_name   like 'Family history%' or a.icd_name  like 'Personal history%') and a.RELATIONSHIP_ID = 'Maps to'
319 and b.icd_code is null */
320;
321
322--check the group of 'Aftercare after procedures' concept's group	(Maps to - for 'Aftercare' and Maps to value - for specific procedure) (71 problems);
323
324select * from 	icd9cm_to_snom_allmap 
325where lower (icd_name) like  '%aftercare%'
326;
327
328--check of 'Late effects or sequelae of other conditions' concept's group;				
329
330select * from 	icd9cm_to_snom_allmap 
331where lower (icd_name) like  '%late effects%'
332;
333		
334--check of 'Adverse effect of medication' concept's group;		.		
335select * from 	icd9cm_to_snom_allmap 
336where lower (icd_name) like  '%adverse%'
337;
338
339select count (*) from icd9cm_to_snom_allmap 
340where lower (icd_name) like  '%adverse%'
341union 
342select count (*) from icd9cm_to_snom_allmap 
343where (lower (concept_name) like '%adverse%' 
344or  lower (concept_name) like '%poison%') 
345and lower (icd_name) like  '%adverse%' 
346;
347
348
349--check of status of organ absence or transplant/prostetic presence (mostly, in the range of V42%-V45%, V49.6 - );
350
351select * from 	icd9cm_to_snom_allmap 
352where lower (icd_name) like  '%transplant%' 
353or lower (icd_name) like '%prosthe%' 
354or lower (icd_name) like '%implant%'
355or lower (icd_name) like 'amputation'
356or lower (icd_name) like '%status%';
357
358
359-- check of 'Need for immunization' concept's group;		
360select * from 	icd9cm_to_snom_allmap 
361where lower (icd_name) like  '%need for%'
362;
363
364-- Conditions indicating abnormal levels of a test.		
365select icd_code from icd9cm_to_snom_allmap 
366where lower (icd_name) like  '%abnormal %' 
367and lower (icd_name) not like '%disorder%' 
368and lower (icd_name) not like '%disease%' 
369and lower (icd_name) not like '%tissue%' 
370;
371
372
373-- Conditions indicating abnormal levels of a test.		
374select * from 	icd9cm_to_snom_allmap where lower (icd_name) like  '%abnormal %' 
375and lower (icd_name) not like '%disorder%' 
376and lower (icd_name) not like '%disease%' 
377and lower (icd_name) not like '%tissue%' 
378;
379
380--Conditions resulting from the use of Devices (icd_code in '996%')';		
381
382select * from 	icd9cm_to_snom_allmap where lower (icd_name) like '% use %' or lower (icd_name) like '%device%' 
383; 
384
385--to check whether we've lost something from ICD9CM in concept (there are 32 rows);
386select * from concept c
387left join icd9cm_to_snom_allmap m on c.concept_code = m.icd_code 
388where c.vocabulary_id ='ICD9CM'  and c.invalid_reason is null
389and m.icd_code is null 
390;
391
392--invalid concepts (mappings should exist even from deprecated concepts anyway;); 
393select * from concept c
394join icd9cm_to_snom_allmap m on c.concept_code = m.icd_code 
395where c.vocabulary_id ='ICD9CM'  and c.invalid_reason is not null 
396;