topic Re: Scanning rows in search of specific values in SAS Programming

Scanning rows in search of specific values

EB1 — Wed, 20 Sep 2017 18:57:30 GMT

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

2 2

3 .

3 3

3 5

3 2

ID Outcome Flag

1 1 0

1 . 0

1 1 0

2 2 1

2 1 1

3 . 1

3 3 1

3 5 1

3 2 1

Thanks!

Re: Scanning rows in search of specific values

Reeza — Wed, 20 Sep 2017 19:16:09 GMT

Find the max per group.

Merge it back in and create the flag.

Re: Scanning rows in search of specific values

Haikuo — Wed, 20 Sep 2017 19:25:11 GMT

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;

Re: Scanning rows in search of specific values

kiranv_ — Wed, 20 Sep 2017 19:25:34 GMT

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;

Re: Scanning rows in search of specific values

HB — Wed, 20 Sep 2017 21:44:42 GMT

That is very slick.