Help using Base SAS procedures

Selecting Observations

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Selecting Observations

Hi,

Could you please help me to select few observation based on certain conditions.

I have a dataset with 2 variables like below.

Variable 1Variable 2
AAS
AAL
AAW
ABS
ACS
ACL
ADL
ADW
AES
AFS
AGL
AHW

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 1Variable 2
AAS
AAL
AAW
ACS
ACL
ADL
ADW


Could you please help me on this regard.


Accepted Solutions
Solution
‎10-13-2011 01:23 AM
Super User
Posts: 10,035

Selecting Observations

Posted in reply to Narasimha_Kulkarni
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

View solution in original post


All Replies
PROC Star
Posts: 7,477

Selecting Observations

Posted in reply to Narasimha_Kulkarni

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;

Occasional Contributor
Posts: 12

Selecting Observations

Hi Art297

Thank you for the help works kool...

Great...

Solution
‎10-13-2011 01:23 AM
Super User
Posts: 10,035

Selecting Observations

Posted in reply to Narasimha_Kulkarni
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

Occasional Contributor
Posts: 12

Selecting Observations

Hi Ksharp,

Answer works correct,

Kool thank you

Super User
Super User
Posts: 7,060

Selecting Observations

Posted in reply to Narasimha_Kulkarni

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;

Occasional Contributor
Posts: 6

Selecting Observations

Posted in reply to Narasimha_Kulkarni

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 169 views
  • 3 likes
  • 5 in conversation