topic Selecting Observations in SAS Procedures

Selecting Observations

Narasimha_Kulkarni — Wed, 12 Oct 2011 17:10:48 GMT

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.

Selecting Observations

art297 — Wed, 12 Oct 2011 17:25:36 GMT

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;

Selecting Observations

Ksharp — Thu, 13 Oct 2011 05:23:32 GMT

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

Selecting Observations

Narasimha_Kulkarni — Thu, 13 Oct 2011 11:02:14 GMT

Hi Art297

Thank you for the help works kool...

Great...

Selecting Observations

Narasimha_Kulkarni — Thu, 13 Oct 2011 11:02:54 GMT

Hi Ksharp,

Answer works correct,

Kool thank you

Selecting Observations

Tom — Thu, 13 Oct 2011 13:39:06 GMT

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;

Selecting Observations

SAS_Niels — Wed, 19 Oct 2011 10:53:09 GMT

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;