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

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 2396 views
  • 4 likes
  • 5 in conversation