SAS Programming

DATA Step, Macro, Functions and more
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 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

 

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-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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