Selecting cases based on all cells from another table

[ammarhm]

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 have
where 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

[novinosrin · Accepted solution]

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

[ChrisNZ]

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 + ;

[Reeza]

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

[ammarhm]

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