I'd like to create a data set containing only observations that have a record where FIRST=1 within each ID group. In the example below, I'd like to keep all observations except those where ID=60, since this ID group does not have any records where FIRST=1.
HAVE:
ID FIRST
7 0
7 1
18 0
18 1
56 0
56 0
56 0
56 1
60 0
60 0
76 0
76 0
76 1
WANT:
ID FIRST
7 0
7 1
18 0
18 1
56 0
56 0
56 0
56 1
76 0
76 0
76 1
This seems like it should be very simple coding, but I'm spinning my wheels here!
Thank you,
Stephanie
Alternatively via data step
proc sort data=have;
by id descending first;
run;
data want;
set have;
by id descending first;
retain test;
if first.id then test=first;
if test>0;
drop test;
run;
Thanks,
Jag
Are you looking for a data step or SQL solution? What hasn't worked for you?
I am much more familiar/comfortable with coding using data step.
The response by Jag below worked for me.
proc sql;
create table want as
select * from have
group by id
having sum(first)>0
order by id, first;
quit;
Alternatively via data step
proc sort data=have;
by id descending first;
run;
data want;
set have;
by id descending first;
retain test;
if first.id then test=first;
if test>0;
drop test;
run;
Thanks,
Jag
Thank you!
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.