- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi everyone.
I have a table (have) containing millions of entries with ICD code diagnosis.
I also have a table (ICD10) which contains some ICD codes of interest:
data ICD10;
input L1:$ L2:$ L3:$ ;
datalines;
%F70 %E16% %G12%
%F71 %E17% .
%F72 %E18% %H4%
%F73 %E19% .
%F74% %E76% .
run;
This table (ICD10) is made ion a such a way that the end use will be able to modify it, so that it could contain any number of columns and rows
What I am trying to go is to create a code that would extract cases from (have) based on ICD10. At first, i thought it would be a very straigt forward proc SQl
proc SQL;
create table want as
select *from havewhere icd like (select * from icd10);quit
Of course SAS was not happy to proceed as it expected a list of strings rather that what the (select * from icd10) returned.
I also considered joining the tables based on L1, L... but it proved to be difficult to create a generic code that would work. Remember the ICD10 table is user defined, so it could contain L1:L3 or L1:l10 column so I dont know in advance the number of columns (or rows) in the ICD10 table.
Could anyone suggest a solution please? One way I am considering is to "melt" table ICD10 into a along string but haven't worked that out yet..
All suggestions are appreciated
Kind regards
AM
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You could transpose all your values of ICD10 to one column in a temporary dataset and run your proc sql.
proc transpose data=ICD10 out=_ICD10;
var l:;
run;
proc transpose data=_ICD10 out=__ICD10;
by _name_;
var col:;
run;
proc SQL;
create table want as
select *from have where icd in (select transposed_column from __ICD10);quit
Of course there are other approaches however I am feeling too lazy and tired to demo other approaches
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You could transpose all your values of ICD10 to one column in a temporary dataset and run your proc sql.
proc transpose data=ICD10 out=_ICD10;
var l:;
run;
proc transpose data=_ICD10 out=__ICD10;
by _name_;
var col:;
run;
proc SQL;
create table want as
select *from have where icd in (select transposed_column from __ICD10);quit
Of course there are other approaches however I am feeling too lazy and tired to demo other approaches
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Like this?
data _null_;
set ICD10 end=LASTOBS;
array L (*) L1-L99 ;
if _N_=1 then call execute('proc sql; create table WANT as select * from HAVE where');
do I=1 to 99 ;
if L[I] = ' ' then leave;
if ADD_OR then call execute (' or ');
ADD_OR+1;
call execute(' ICD like ' || quote(trim(L[I]),"'") );
end;
if LASTOBS then call execute('; quit;');
run;
1 + proc sql;
1 + create table WANT as select * from HAVE where
2 + ICD like '%F70'
3 + or
4 + ICD like '%E16%'
5 + or
6 + ICD like '%G12%'
7 + or
8 + ICD like '%F71'
9 + or
10 + ICD like '%E17%'
11 + or
12 + ICD like '%F72'
13 + or
14 + ICD like '%E18%'
15 + or
16 + ICD like '%H4%'
17 + or
18 + ICD like '%F73'
19 + or
20 + ICD like '%E19%'
21 + or
22 + ICD like '%F74%'
23 + or
24 + ICD like '%E76%'
25 + ;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Enforce rules on your input otherwise you need your program to account for every single possible option - which is a bad idea.
This table (ICD10) is made ion a such a way that the end use will be able to modify it, so that it could contain any number of columns and rows
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you Reeza,
I fully agree that there should be certain rules enforced. However, in my example the only thing the users are allowed to do is to add rows/columns, and I believe this could be accounted for by the right code
Kind regards
AM