## how to subset based on 2 variables

Solved
Occasional Contributor
Posts: 18

# 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,147

## 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

All Replies
Super User
Posts: 23,689

## 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,270

## 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,147

## 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
• 304 views
• 3 likes
• 4 in conversation