SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How select groups by condition?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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:

 

Var1Var2Var3Var4
1A2 
1A1620
1A3110
2B6 
2B9740
3C84 
3C6420
4D97 
4D63 
4D1230
4D4110

 

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:

 

Var1Var2Var3Var4
1A2 
1A1620
1A3110
3C84 
3C6420

 

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

 

I will appreciate your help! Please...


Accepted Solutions
Solution
‎05-31-2017 05:03 PM
Super User
Posts: 5,072

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;

View solution in original post


All Replies
PROC Star
Posts: 7,356

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: 5,072

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,255

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: 9,662

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.

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

Discussion stats
  • 5 replies
  • 465 views
  • 4 likes
  • 5 in conversation