Scanning rows in search of specific values

Accepted Solution Solved
Reply
Occasional Contributor EB1
Occasional Contributor
Posts: 12
Accepted Solution

Scanning rows in search of specific values

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!


Accepted Solutions
Solution
‎09-20-2017 03:54 PM
Respected Advisor
Posts: 3,160

Re: Scanning rows in search of specific values

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;

View solution in original post


All Replies
Super User
Posts: 21,530

Re: Scanning rows in search of specific values

Find the max per group.

Merge it back in and create the flag.

 

 

Solution
‎09-20-2017 03:54 PM
Respected Advisor
Posts: 3,160

Re: Scanning rows in search of specific values

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;
Regular Contributor
Regular Contributor
Posts: 218

Re: Scanning rows in search of specific values

That is very slick.
PROC Star
Posts: 441

Re: Scanning rows in search of specific values

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;
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 241 views
  • 3 likes
  • 5 in conversation