topic How select groups by condition? in SAS Data Management

How select groups by condition?

CarolBarahona — Wed, 31 May 2017 20:01:49 GMT

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.

I will appreciate your help! Please...

Re: How select groups by condition?

art297 — Wed, 31 May 2017 20:14:09 GMT

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

Re: How select groups by condition?

LinusH — Wed, 31 May 2017 20:17:34 GMT

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.

Re: How select groups by condition?

Astounding — Wed, 31 May 2017 20:20:48 GMT

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;

Re: How select groups by condition?

CarolBarahona — Wed, 31 May 2017 21:05:31 GMT

Yes! It worked perfectly... that's was I was looking for! Thanks a lot!

Re: How select groups by condition?

Ksharp — Thu, 01 Jun 2017 13:22:08 GMT

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;