Selecting Observations

[Narasimha_Kulkarni]

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.

[art297]

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;

[Narasimha_Kulkarni]

Hi Art297

Thank you for the help works kool...

Great...

[Ksharp · Accepted solution]

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

[Narasimha_Kulkarni]

Hi Ksharp,

Answer works correct,

Kool thank you

[Tom]

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;

[SAS_Niels]

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;