DATA Step, Macro, Functions and more

how to subset based on 2 variables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

how to subset based on 2 variables

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


Accepted Solutions
Solution
‎04-16-2015 11:17 PM
Trusted Advisor
Posts: 1,137

Re: how to subset based on 2 variables

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

Thanks,
Jag

View solution in original post


All Replies
Super User
Posts: 19,789

Re: how to subset based on 2 variables

Are you looking for a data step or SQL solution? What hasn't worked for you?

Occasional Contributor
Posts: 18

Re: how to subset based on 2 variables

I am much more familiar/comfortable with coding using data step.

The response by Jag below worked for me.

Trusted Advisor
Posts: 1,228

Re: how to subset based on 2 variables

proc sql;

create table want as

select * from have

group by id

having sum(first)>0

order by id, first;

quit;

Solution
‎04-16-2015 11:17 PM
Trusted Advisor
Posts: 1,137

Re: how to subset based on 2 variables

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

Thanks,
Jag
Occasional Contributor
Posts: 18

Re: how to subset based on 2 variables

Posted in reply to Jagadishkatam

Thank you!

🔒 This topic is solved and locked.

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

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