BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ammarhm
Lapis Lazuli | Level 10

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

View solution in original post

4 REPLIES 4
novinosrin
Tourmaline | Level 20

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
Tourmaline | Level 20

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
Super User

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
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 804 views
  • 6 likes
  • 4 in conversation