Better way to filter codes

Accepted Solution Solved
Reply
Super User
Posts: 19,870
Accepted Solution

Better way to filter codes

Hi,

I feel like there should be a better way to code this, but I can't think of one off the top of my head.

The code are diagnosis codes, say diag1-diag25 and I need to check that the first three digits of the code are in a certain list. Example:

where substr(diag1, 1,3) in ('F12', 'G13', 'H25', 'L26', 'R27')

or substr(diag2, 1,3) in ('F12', 'G13', 'H25', 'L26', 'R27')

or substr(diag3, 1,3) in ('F12', 'G13', 'H25', 'L26', 'R27')

...

or substr(diag25, 1,3) in ('F12', 'G13', 'H25', 'L26', 'R27');

This is currently in a where clause in SQL but I think we could switch it to a datastep if performance was similar.

Thanks!


Accepted Solutions
Solution
‎04-05-2013 04:06 PM
Respected Advisor
Posts: 3,156

Re: Better way to filter codes

Hi ,

I can only think of using Array() to save some of your typing:

data want;

  set have;

  array di diag1-diag23;

do over di;

if di in: ('F12', 'G13', 'H25', 'L26', 'R27') then output;

end;

run;

Haikuo

View solution in original post


All Replies
Super Contributor
Posts: 578

Re: Better way to filter codes

I don't know how fast it would run...but you could build a temp table to use in a join:

data dxs;

length dx $3.;

cards;

F12

G13

H25

L26

R27

;

run;

proc sql;

create table want as

select

     t1.*

from

     have t1

     inner join dxs t2

          on t1.diag1 =: t2.dx

            or t1.diag2 =: t2.dx

              ....

               or t1.diag25 =: t2.dx

;

quit;

Solution
‎04-05-2013 04:06 PM
Respected Advisor
Posts: 3,156

Re: Better way to filter codes

Hi ,

I can only think of using Array() to save some of your typing:

data want;

  set have;

  array di diag1-diag23;

do over di;

if di in: ('F12', 'G13', 'H25', 'L26', 'R27') then output;

end;

run;

Haikuo

Respected Advisor
Posts: 4,934

Re: Better way to filter codes

Something like :

allDiag = catx("@", of diag1-diag25);
if  index(allDiag,"@F12") +
index(allDiag,"@G13") +
index(allDiag,"@H25") +
index(allDiag, "@L26") +
index(allDiag, "@R27");

PG

PG
Respected Advisor
Posts: 3,156

Re: Better way to filter codes

Then I would hope there is no @F12 as part of the diag code string, from the very limited knowledge of mine about ICDs, I suppose not.

Haikuo

Super User
Posts: 19,870

Re: Better way to filter codes

There could be an F12 I think, I'm not too familiar with the codes.

This is what I came up with, but I have an extra or at the end that I need to get rid off.

%macro diag_check(diag_list=);

*loop through list until the end;

%let diag_count=1;

%do %while (%scan(&diag_list, &diag_count, ",") ^=%str());

%let diag=%scan(&diag_list, &diag_count, ",");

    %put hlth_dx_code_mr =: &diag or;

    %do i=1 %to 24;

        %put hlth_dx_code_&i =: &diag or;

    %end;

    %put hlth_dx_code_25 =: &diag or;

%let diag_count=%eval(&diag_count+1);

%end;

%mend;

%diag_check(diag_list=%quote(&check_list));

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 215 views
  • 0 likes
  • 4 in conversation