topic Re: Need help managing this data in SAS Programming

Need help managing this data

jmmedina25 — Wed, 03 Jul 2019 13:29:14 GMT

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

4 Y

4 N

WANT

User ID Consent

1 Y

2 Y

3 N

4 Y

Re: Need help managing this data

PeterClemmensen — Wed, 03 Jul 2019 13:33:03 GMT

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;

Re: Need help managing this data

novinosrin — Wed, 03 Jul 2019 13:35:33 GMT


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;

Re: Need help managing this data

novinosrin — Wed, 03 Jul 2019 13:47:30 GMT


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;