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!
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;
Find the max per group.
Merge it back in and create the flag.
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.