Scanning rows in search of specific values

[EB1]

Hi all,

I need to find a way to scan rows of variables for each ID to look for a specific number (2 or 3). I would like to flag participants that have at least one 2, or one 3 as the outcome. This is the dataset I have:

ID    Outcome

1         1

1         .

1         1

1         1

2         2

2         2

3         .

3         3

3         5

3         2

ID    Outcome   Flag

1         1              0

1         .               0

1         1              0

1         1              0

2         2              1

2         1              1

3         .               1

3         3              1

3         5              1

3         2              1

Thanks!

[Reeza]

Find the max per group.

Merge it back in and create the flag.

[Haikuo · Accepted solution]

Proc SQL seems handy here (please note, the order of rows may be changed if you don't have an index variable):

Proc sql;
create table want as
select *, sum(outcome in (2,3))>0 as flag from have
group by id
;
quit;

[HB]

That is very slick.

[kiranv_]

something like this

data abc;
input ID    Outcome;
datalines;
1         1
1         .
1         1
1         1
2         2
2         2
3         .
3         3
3         5
3         2
;
 

proc sql;
select a.id, a.outcome , flag from 
(select 
id, outcome from abc)a left join 
(select id, max(flag) as flag from 
(select 
id, outcome, case when outcome in( 2, 3) then 1
else 0
end as flag from abc)
group by id)b
on a.id = b.id;
quit;