Need help managing this data

[jmmedina25]

So i have a data set that has multiple observations for some users, but I only want 1 observation for each unique user ID depending on the variable 'consent'.  If a member has multiple Yes (Y) for consent, I want to keep one Y observation, if they have multiple No (N) for consent, I want to keep one N observation, and if they have some Y and some N, I only want to keep one Y observation.  Example:

HAVE:

User ID    Consent

1                 Y

2                 Y

2                N

3                N

3                N

4                Y

4                Y

4                N

WANT

User ID  Consent

1              Y

2             Y

3             N

4             Y

[PeterClemmensen · Accepted solution]

data have;
input UserID Consent $;
datalines;
1 Y
2 Y
2 N
3 N
3 N
4 Y
4 Y
4 N
;

proc sort data=have;
    by UserID Consent;
run;

data want;
    set have;
    by UserID;
    if last.UserID;
run;

[novinosrin]

data have;
input UserID    Consent $;
cards;
1                 Y
2                 Y
2                N
3                N
3                N
4                Y
4                Y
4                N
;


proc sql;
create table want as
select distinct *
from have
group by userid
having max(consent)=consent;
quit;

[novinosrin]

data have;
input UserID    Consent $;
cards;
1                 Y
2                 Y
2                N
3                N
3                N
4                Y
4                Y
4                N
;
data want;
do until(last.userid);
set have;
by userid;
if consent='Y' and _f=. then do; output;_f=1;end;
else if _f=. and last.userid then output;
end;
drop _f;
run;