Hi,
Could you please help me to select few observation based on certain conditions.
I have a dataset with 2 variables like below.
Variable 1 | Variable 2 |
AA | S |
AA | L |
AA | W |
AB | S |
AC | S |
AC | L |
AD | L |
AD | W |
AE | S |
AF | S |
AG | L |
AH | W |
I want to select the observation if Variable 2 is a combination of S & L, L & W, S & W and S & L & W.. If alone S or L or W is there I don't want it.
The output I am expecting is like below
Variable 1 | Variable 2 |
AA | S |
AA | L |
AA | W |
AC | S |
AC | L |
AD | L |
AD | W |
Could you please help me on this regard.
data temp; input Var1 $ Var2 $ ; cards; AA S AA L AA W AB S AC S AC L AD L AD W AE S AF S AG L AH W ; run; proc sql; select * from temp group by var1 having count(distinct var2) gt 1; quit;
Ksharp
I think that proc sql offers the easiest solution to implement. How about:
proc sql noprint;
create table want as
select *
from have
group by variable1
having min(variable2) ne max(variable2)
;
quit;
Hi Art297
Thank you for the help works kool...
Great...
data temp; input Var1 $ Var2 $ ; cards; AA S AA L AA W AB S AC S AC L AD L AD W AE S AF S AG L AH W ; run; proc sql; select * from temp group by var1 having count(distinct var2) gt 1; quit;
Ksharp
Hi Ksharp,
Answer works correct,
Kool thank you
Did you mean to request that you want to delete the groups that only have one observation?
If your data is sorted by VARIABLE1 (is it an ID variable perhaps) and there are not duplicate identical rows then you can just use FIRST./LAST. processing.
data want;
set have;
by id;
if first.id and last.id then delete;
run;
Hi,
I found a way with proc transpose. You can check each single step.
data test;
input variable1 $ variable2;
datalines;
AA S
AA L
AA W
AB S
AC S
AC L
AD L
AD W
AE S
AF S
AG L
;
run;
proc sort data=test;
by variable1 variable2;
run;
proc transpose data=test out=turn prefix=value;
by variable1;
var variable1 variable2;
run;
data turn;
set turn;
if value2= '' and value3= '' then delete;
run;
proc transpose data=turn out=turnback (drop=_name_);
by variable1;
var value1 value2 value3;
run;
data testend;
set turnback;
if variable1= '' or variable2= '' then delete;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.