· 8 years ago · Jan 29, 2018, 04:42 PM
1-- my draft;
2create table icd9cm_to_snom_allmap_dr as (select * from icd9cm_to_snom_allmap);
3
4-- create source table after manual correction;
5
6create 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),
7 CONCEPT_ID number, CONCEPT_CLASS_ID VARCHAR (20), DOMAIN_ID VARCHAR (20), CNT number, ICD_INV_REASON VARCHAR (1), EXTRA VARCHAR (20));
8
9WbImport -file=C:/Users/pdmitrenko/Desktop/icd9cm_to_snomed_verylast.txt
10 -type=text
11 -table=ICD9CM_TO_SNOM_ALLMAP
12 -encoding="UTF-8"
13 -header=true
14 -decode=false
15 -dateFormat="yyyy-MM-dd"
16 -timestampFormat="yyyy-MM-dd HH:mm:ss"
17 -delimiter='\t'
18 -decimal=.
19 -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
20 -quoteCharEscaping=none
21 -ignoreIdentityColumns=false
22 -deleteTarget=false
23 -continueOnError=false
24 -batchSize=1000;
25
26
27select count (*) from icd9cm_to_snom_allmap;
28--check for any absent data in the fields ;
29
30select * from icd9cm_to_snom_allmap a
31where icd_id is null
32or icd_code is null
33or icd_name is null
34or relationship_id is null
35or concept_code is null
36or concept_name is null;
37
38--use name_matching for cases, when the concept_code is rounded somehow ;
39update icd9cm_to_snom_allmap m set concept_code = (
40select aa.concept_code from
41(
42select M.concept_name,m.icd_code, c.concept_code from icd9cm_to_snom_allmap m
43join 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'
44left join concept c2 on c2.concept_code = m.concept_code and c2.invalid_reason is null and c2.vocabulary_id ='SNOMED'
45where c2.concept_id is null
46) aa where aa.concept_name = m.concept_name and aa.icd_code = m.icd_code )
47where exists
48(
49select 1 from
50(
51select M.concept_name ,m.icd_code ,c.concept_code from icd9cm_to_snom_allmap m
52join 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'
53left join concept c2 on c2.concept_code = m.concept_code and c2.invalid_reason is null and c2.vocabulary_id ='SNOMED'
54where c2.concept_id is null
55) aa where aa.concept_name = m.concept_name and aa.icd_code = m.icd_code )
56;
57
58--fill the possible gaps due to lazy mapping;
59
60MERGE INTO icd9cm_to_snom_allmap e
61 USING devv5.concept h
62 ON (e.concept_code = h.concept_code and h. vocabulary_id = 'SNOMED' and h.standard_concept = 'S'
63and h.invalid_reason is null)
64 WHEN MATCHED THEN
65 UPDATE SET e.concept_name = h.concept_name,
66 e.concept_id = h.concept_id,
67 e.domain_id = h.domain_id,
68 e.concept_class_id = h.concept_class_id ;
69
70
71
72MERGE INTO icd9cm_to_snom_allmap e
73 USING devv5.concept h
74 ON (e.icd_id = h.concept_id and h. vocabulary_id = 'ICD9CM'
75and h.invalid_reason is null)
76 WHEN MATCHED THEN
77 UPDATE SET e.icd_code = h.concept_code;
78
79
80commit;
81
82-- check mapping for invalid and non-standard concepts;
83select * from icd9cm_to_snom_allmap a
84join concept c on a.concept_code = c.concept_code and c.vocabulary_id = 'SNOMED'
85where
86c.standard_concept is null
87and c.invalid_reason is not null;
88
89--check the mapping by the concept_code similarity (19348 from 19348, so all here)
90;
91select count (*) from
92 (
93select a.ICD_ID,a.ICD_CODE,a.ICD_NAME,a.RELATIONSHIP_ID, a.CONCEPT_CODE, a.CONCEPT_NAME,
94a.EXTRA, c.CONCEPT_ID, c.CONCEPT_CLASS_ID,c.DOMAIN_ID,a.CNT, a.ICD_INV_REASON from icd9cm_to_snom_allmap a
95 join devv5.concept c on a.CONCEPT_CODE = c.concept_code
96where c.vocabulary_id = 'SNOMED'
97and standard_concept = 'S');
98
99-- look at them if needed;
100select * from icd9cm_to_snom_allmap where concept_code not in
101 (select a.concept_code from icd9cm_to_snom_allmap a
102 join devv5.concept c on a.CONCEPT_CODE = c.concept_code
103where c.vocabulary_id = 'SNOMED'
104and standard_concept = 'S') ;
105
106
107--chcek deprecated icd_codepts (there are 11 cocnepts);
108select * from icd9cm_to_snom_allmap where icd_code in (select concept_code from devv5.concept where invalid_reason is not null and vocabulary_id = 'ICD9CM');
109
110
111--check used domains and concept classes (Observation, Device, Procedure, Measurement, Spec Disease Status, Meas Value, Condition);
112select distinct c.domain_id from icd9cm_to_snom_allmap a
113join concept c on a.concept_code = c.concept_code
114where c.vocabulary_id = 'SNOMED'
115and c.standard_concept = 'S'
116and c.invalid_reason is null;
117
118--look at them( so, relationship_id of 13 concepts was corrected according to the domain);
119select * from icd9cm_to_snom_allmap a
120join concept c on a.concept_code = c.concept_code
121where c.vocabulary_id = 'SNOMED'
122and c.standard_concept = 'S'
123and c.invalid_reason is null
124and c.domain_id in ('Device', 'Spec Disease Status', 'Meas Value');
125
126--check of concept_class_id;
127select distinct c.concept_class_id from icd9cm_to_snom_allmap a
128join concept c on a.concept_code = c.concept_code
129where c.vocabulary_id = 'SNOMED'
130and c.standard_concept = 'S'
131and c.invalid_reason is null;
132
133--look at them (there are 5 Morph abnormality and 3 Organisms);
134select 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
135join concept c on a.concept_code = c.concept_code
136where c.vocabulary_id = 'SNOMED'
137and c.standard_concept = 'S'
138and c.invalid_reason is null
139and c.concept_class_id not in ('Procedure', 'Clinical Finding', 'Context-dependent', 'Event');
140
141--check classification concepts (they shoud be mapped one-to-one) - there were 6 duplicated rows;
142select * from (
143select icd_code from icd9cm_to_snom_allmap where icd_code not like '%.%')
144GROUP BY icd_code
145HAVING COUNT( 1 ) >1 ;
146
147select * from concept where concept_id = '44830932';
148
149select * from
150
151-- Look at or_concepts; -- 49 concepts for Snomed_Extension))
152select * from icd9cm_to_snom_allmap where extra = 'orc';
153select * from icd9cm_to_snom_allmap where icd_name like '% and %';
154
155--Look at concepts with incorrect domain (according to 'Extra' field - indom) -- 17 (all refer to 'Unilateral');
156select * from icd9cm_to_snom_allmap where extra like 'indom';
157
158--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. Also guys put 'pregnant'/'postpartum' to classification concepts, but this is mistake;
159select count ( icd_code) from icd9cm_to_snom_allmap where extra like 'pregn%'; -- 801;
160
161select count ( distinct icd_code) from ( select icd_code, icd_name from icd9cm_to_snom_allmap where extra = 'pregnant' and icd_code like '%.%'
162GROUP BY icd_code, icd_name HAVING COUNT(1)=1); -- 800;
163
164select count ( icd_code) from icd9cm_to_snom_allmap where extra like 'postp%'; -- 496;
165
166select count ( distinct icd_code) from ( select icd_code, icd_name from icd9cm_to_snom_allmap where extra = 'postpartum' and icd_code like '%.%'
167GROUP BY icd_code, icd_name HAVING COUNT(1)=1); -- 496;
168
169
170----------------
171
172create table icd9cm_to_snom_allmap_dr as (select * from icd9cm_to_snom_allmap);
173
174---------------
175--then insert 'pregnant' concepts;
176
177--check for possible duplicates;
178select * from icd9cm_to_snom_allmap where concept_name = 'Finding related to pregnancy';
179
180
181insert 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)
182select
183ICD_ID,
184ICD_CODE,
185ICD_NAME,
186RELATIONSHIP_ID,
187'118185001',
188'Finding related to pregnancy',
189null,
190'Clinical Finding',
191'Condition',
192null,
193null,
194null
195from icd9cm_to_snom_allmap
196where extra = 'pregnant';
197
198commit;
199select * from icd9cm_to_snom_allmap where concept_code = '118185001';
200
201-- do the same thing with 'postpartum';
202insert 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)
203select
204ICD_ID,
205ICD_CODE,
206ICD_NAME,
207RELATIONSHIP_ID,
208'118213005',
209'Postpartum finding',
210null,
211'Clinical Finding',
212'Condition',
213null,
214null,
215null
216from icd9cm_to_snom_allmap
217where extra = 'postpartum';
218
219commit;
220
221--Check all malignangt neoplasms without mention of their origin ( 'Primary' or 'Secondary'). They should be mapped to 'Primary';
222select icd_code, icd_name, concept_name, concept_code from icd9cm_to_snom_allmap where icd_name like '%malignant%';
223select * from icd9cm_to_snom_allmap where icd_name like '%primary%';
224
225--Check all 'Unilateral' concepts (everything is OK);
226
227select icd_code, icd_name, concept_name, concept_code from icd9cm_to_snom_allmap where lower( icd_name) like '%unilateral%';
228
229
230--check if we have something that not exist in SNOMED (25 concepts!)
231select * from icd9cm_to_snom_allmap m
232left join concept c2 on c2.concept_code = m.concept_code and c2.vocabulary_id ='SNOMED' and c2.invalid_reason is null
233where c2.concept_id is null;
234------------------------------------------------------
235--check of family history and history of
236select *from icd9cm_to_snom_allmap where concept_code ='416471007' and icd_code like '%.%'; -- 62
237select * from icd9cm_to_snom_allmap where icd_name like 'Family history%' and icd_code like '%.%' -- excluding classification concepts;
238; --120
239
240select * from icd9cm_to_snom_allmap where icd_name like 'Personal history%' and icd_code like '%.%';
241
242select icd_code, icd_name from icd9cm_to_snom_allmap where icd_name like 'Personal history%' and icd_code like '%.%'
243GROUP BY icd_code, icd_name HAVING COUNT(1)!=2;
244
245
246;
247/*--develop a Maps to value entity (smthg) from "history of smthg"
248 insert into icd9cm_to_snom_allmap
249select 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
250join concept c on c.concept_code = a.concept_code and c.invalid_reason is null and c.vocabulary_id ='SNOMED'
251join concept_relationship r on r.concept_id_1 = c.concept_id and r.relationship_id like 'Has asso%'
252join concept c2 on r.concept_id_2 = c2.concept_id and c2.vocabulary_id ='SNOMED' and c2.invalid_reason is null
253
254left join icd9cm_to_snom_allmap b on a.ICD_CODE = b.ICD_CODE and b.RELATIONSHIP_ID ='Maps to value'
255 where (a.icd_name like 'Family history%' or a.icd_name like 'Personal history%') and a.RELATIONSHIP_ID = 'Maps to'
256 and b.icd_code is null */
257;
258
259--check whether we have suspicious domains and / or classes
260select distinct c.domain_id from icd9cm_to_snom_allmap m
261join concept c on m.concept_code = c.concept_code and c.vocabulary_id ='SNOMED' and c.invalid_reason is null and relationship_id= 'Maps to'
262;
263
264--look at 'Procedure' domain;
265select * from icd9cm_to_snom_allmap m
266join concept c on m.concept_code = c.concept_code and c.vocabulary_id ='SNOMED' and c.invalid_reason is null and relationship_id= 'Maps to' where c.domain_id = 'Procedure';
267
268
269--to check whether we have suspicious domains and / or classes
270select * from icd9cm_to_snom_allmap m
271join concept c on m.concept_code = c.concept_code and c.vocabulary_id ='SNOMED' and c.invalid_reason is null and relationship_id= 'Maps to'
272where c.domain_id in ('Spec Disease Status');
273
274select * from icd9cm_to_snom_allmap m
275join concept c on m.concept_code = c.concept_code and c.vocabulary_id ='SNOMED' and c.invalid_reason is null and relationship_id= 'Maps to'
276where c.concept_class_id = 'Qualifier Value' ;
277
278
279--to check whether we've lost something (there are 32 rows);
280select * from concept c
281left join icd9cm_to_snom_allmap m on c.concept_code = m.icd_code
282where c.vocabulary_id ='ICD9CM' and c.invalid_reason is null
283and m.icd_code is null ;
284
285--invalid concepts;
286select * from concept c
287 join icd9cm_to_snom_allmap m on c.concept_code = m.icd_code
288where c.vocabulary_id ='ICD9CM' and c.invalid_reason is not null ;
289
290--mappings should exist even from deprecated concepts anyway
291select count (1) from concept where vocabulary_id = 'ICD9CM' and invalid_reason is not null and valid_end_date > to_date ('?????', 'yyyymmdd');
292;
293
294
295--concept_relationship_manual ;
296
297select * from concept_relationship_manual crm
298join concept c on crm.concept_code_1 = c.concept_code
299join concept d on crm.concept_code_2 = c.concept_code
300where d.vocabulary_id = 'ICD9CM'
301and c.vocabulary_id = 'SNOMED';
302
303create table concept_relationship_manual_draft as select * from concept_relationship_manual;
304
305truncate table concept_relationship_manual_draft
306;
307
308select * from icd9cm_to_snom_allmap where relationship_id is null;
309
310-- insert into concept_relationship_manual_draft;
311insert into concept_relationship_manual_draft
312(
313CONCEPT_CODE_1,CONCEPT_CODE_2,VOCABULARY_ID_1,VOCABULARY_ID_2,RELATIONSHIP_ID,VALID_START_DATE,VALID_END_DATE
314)
315select distinct icd_code, concept_code, 'ICD9CM', 'SNOMED', relationship_id,trunc( sysdate) -1, to_date ('20991231', 'yyyymmdd')
316from icd9cm_to_snom_allmap;
317;
318commit
319;
320--important check invented by Timur -- should return NULL (nothing), otherwise generic_update will fail
321 SELECT *
322 FROM concept_relationship_manual_draft crm
323 LEFT JOIN concept c1 ON c1.concept_code = crm.concept_code_1 AND c1.vocabulary_id = crm.vocabulary_id_1
324 LEFT JOIN concept_stage cs1 ON cs1.concept_code = crm.concept_code_1 AND cs1.vocabulary_id = crm.vocabulary_id_1
325 LEFT JOIN concept c2 ON c2.concept_code = crm.concept_code_2 AND c2.vocabulary_id = crm.vocabulary_id_2
326 LEFT JOIN concept_stage cs2 ON cs2.concept_code = crm.concept_code_2 AND cs2.vocabulary_id = crm.vocabulary_id_2
327 LEFT JOIN vocabulary v1 ON v1.vocabulary_id = crm.vocabulary_id_1
328 LEFT JOIN vocabulary v2 ON v2.vocabulary_id = crm.vocabulary_id_2
329 LEFT JOIN relationship rl ON rl.relationship_id = crm.relationship_id
330 LEFT JOIN (SELECT crm_int.concept_code_1, crm_int.vocabulary_id_1, crm_int.concept_code_2, crm_int.vocabulary_id_2, crm_int.relationship_id
331 FROM concept_relationship_manual crm_int
332 GROUP BY crm_int.concept_code_1, crm_int.vocabulary_id_1, crm_int.concept_code_2, crm_int.vocabulary_id_2, crm_int.relationship_id
333 HAVING COUNT(*)>1) c_i ON c_i.concept_code_1=crm.concept_code_1 AND c_i.vocabulary_id_1=crm.vocabulary_id_1 AND c_i.concept_code_2=crm.concept_code_2
334 AND c_i.vocabulary_id_2=crm.vocabulary_id_2 AND c_i.relationship_id=crm.relationship_id
335 WHERE (c1.concept_code IS NULL AND cs1.concept_code IS NULL)
336 OR (c2.concept_code IS NULL AND cs2.concept_code IS NULL)
337 OR v1.vocabulary_id IS NULL
338 OR v2.vocabulary_id IS NULL
339 OR rl.relationship_id IS NULL
340 OR crm.valid_start_date > SYSDATE
341 OR crm.valid_end_date < crm.valid_start_date
342 OR TRUNC (crm.valid_start_date) <> crm.valid_start_date
343 OR TRUNC (crm.valid_end_date) <> crm.valid_end_date
344 OR (crm.invalid_reason IS NULL AND crm.valid_end_date <> TO_DATE ('20991231', 'yyyymmdd'))
345 OR c_i.concept_code_1 IS NOT NULL;