## How select groups by condition?

Solved
Occasional Contributor
Posts: 5

# How select groups by condition?

[ Edited ]

Hi all

Today a have a problem and I don't know how resolve it! Any clue...

I have something like this:

 Var1 Var2 Var3 Var4 1 A 2 1 A 16 20 1 A 31 10 2 B 6 2 B 97 40 3 C 84 3 C 64 20 4 D 97 4 D 63 4 D 12 30 4 D 41 10

And I want to select some groups of observations with same conditions. For example, if Var4='20', I have to select all group with the same characteristics in 'Var1' and 'Var2'. Something like this:

 Var1 Var2 Var3 Var4 1 A 2 1 A 16 20 1 A 31 10 3 C 84 3 C 64 20

I sorted Var1 and Var2 previously, but after that, I don't know how to proceed.

Accepted Solutions
Solution
‎05-31-2017 05:03 PM
Super User
Posts: 6,213

## Re: How select groups by condition?

I like Art's approach, but I think you need to tweak it:

data want (drop=select);
do until (last.Var2);
set have;
by Var1 Var2;
if Var4 eq 20 then select=1;
end;
do until (last.Var2);
set have;
by Var1 Var2;
if select then output;
end;
run;

All Replies
PROC Star
Posts: 7,868

## Re: How select groups by condition?

Sounds like you are trying to do something like the following:

```data want (drop=select);
do until (last.Var1);
set have;
by Var1;
if Var4 eq 20 then select=1;
end;
do until (last.Var1);
set have;
by Var1;
if select then output;
end;
run;```

Art, CEO, AnalystFinder.com

Solution
‎05-31-2017 05:03 PM
Super User
Posts: 6,213

## Re: How select groups by condition?

I like Art's approach, but I think you need to tweak it:

data want (drop=select);
do until (last.Var2);
set have;
by Var1 Var2;
if Var4 eq 20 then select=1;
end;
do until (last.Var2);
set have;
by Var1 Var2;
if select then output;
end;
run;

Occasional Contributor
Posts: 5

## Re: How select groups by condition?

Yes! It worked perfectly... that's was I was looking for! Thanks a lot!
Super User
Posts: 5,700

## Re: How select groups by condition?

I would try an SQL, something like:

Proc sql;
Select a.*
From have as a
Inner join (
Select distinct var1, var2
From have
Where var4 = '20') as b
On a.var1 = b.var1 and a.var2 = b.var2
;
Quit;

Untested.
Data never sleeps
Super User
Posts: 10,415

## Re: How select groups by condition?

SQL solution.

``````data have;
infile cards truncover expandtabs;
input Var1	Var2 \$ Var3	Var4;
cards;
1	A	2
1	A	16	20
1	A	31	10
2	B	6
2	B	97	40
3	C	84
3	C	64	20
4	D	97
4	D	63
4	D	12	30
4	D	41	10
;
run;
proc sql;
create table want as
select * from have
group by var1
having sum(var4=20) ne 0;
quit;``````
☑ This topic is solved.