BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
CarolBarahona
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

5 REPLIES 5
art297
Opal | Level 21

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

 

Astounding
PROC Star

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;

CarolBarahona
Fluorite | Level 6
Yes! It worked perfectly... that's was I was looking for! Thanks a lot!
LinusH
Tourmaline | Level 20
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
Ksharp
Super User

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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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