Conditional selection of rows from a multiple rows per observation data

strqimr1 — Wed, 12 Jan 2022 09:25:25 GMT

Dear Community,

I need your kind help regarding the following:

I wanted to select rows based on conditions from a multiple rows per observation dataset. See attached dataset in xls.

I wanted to select the row if 'otherDiagnosis' variables contains some specific values. There are 62 variables with prefix otherDiagnosis; i.e. otherDiagnosis1, otherDiagnosis2, otherDiagnosis3,...otherDiagnosis62.

I have written the following code, which serve my purpose nicely.

But I wanted to know if there is any other efficient way so that I don't have to repeat the lines 62 times in the code.

data B;
set A;
where otherDiagnosis1 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis2 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis3 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis4 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis5 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis6 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis7 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis8 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis9 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis10 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis11 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis12 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis13 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis14 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis15 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis16 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis17 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis18 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis19 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis20 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis21 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis22 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis23 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis24 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis25 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis26 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis27 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis28 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis29 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis30 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis31 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis32 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis33 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis34 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis35 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis36 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis37 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis38 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis39 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis40 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis41 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis42 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis43 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis44 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis45 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis46 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis47 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis48 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis49 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis50 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis51 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis52 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis53 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis54 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis55 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis56 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis57 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis58 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis59 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis60 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis61 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
or otherDiagnosis62 in ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269")
; 
run;

I appreciate your help in advanced.

Regards

Sabbir

Re: Conditional selection of rows from a multiple rows per observation data

acordes — Wed, 12 Jan 2022 09:58:35 GMT

Use 2 arrays to accomplish this.
The first is for the values to check against. It resolves to true as row filter if any of the variables matches these values.

my sas viya is down again (hopefully my company did better administrating sas viya) but the code (not tested) should be similar to this.

data B;
set A;
array vary (*) otherDiagnosis1-otherDiagnosis62;
array check (8) $4. ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269");
stopy=0;
do i=1 to dim(check) until(stopy=1);
do j=1 to dim(vary);
if vary(j)=check(i) then stopy=1;
end;
end;
if stopy;
run;

Re: Conditional selection of rows from a multiple rows per observation data

andreas_lds — Wed, 12 Jan 2022 10:41:26 GMT

Start by transposing the data to have only one Diagnosis variable (and multiple observations). Then a simple data step can solve the problem without using arrays and loops.

Re: Conditional selection of rows from a multiple rows per observation data

Ksharp — Wed, 12 Jan 2022 12:46:55 GMT

Using array as acordes did is a good idea.

data B;
set A;
array vary (*) otherDiagnosis1-otherDiagnosis62;
array check (8) $4. ("H250", "H251", "H252", "H258", "H259", "H261", "H268", "H269");

do j=1 to dim(vary);
if vary(j) in check then do;output; leave;end;
end;

run;

Re: Conditional selection of rows from a multiple rows per observation data

strqimr1 — Thu, 13 Jan 2022 01:03:47 GMT

Many thanks @acordes for your help

topic Conditional selection of rows from a multiple rows per observation data in SAS Programming

Conditional selection of rows from a multiple rows per observation data

Re: Conditional selection of rows from a multiple rows per observation data

Re: Conditional selection of rows from a multiple rows per observation data

Re: Conditional selection of rows from a multiple rows per observation data

Re: Conditional selection of rows from a multiple rows per observation data