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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.