selecting observations that have different corresponding columns

[Gladis6680]

Is it possible to select rows where we have the same A, but different B and C. For example, from this table I would like rows where A=4 and A=10

A	B	C
1	yellow	italy
1	yellow	italy
2	green	greece
3	purple	london
4	orange	portugal
4	orange	belgium
5	red	spain
6	brown	netherlands
7	white	france
8	black	russia
8	black	russia
9	grey	barcelona
10	violet	turkey
10	violet	romania

[PeterClemmensen]

data have;
input A B $ C $20.;
datalines;
1 yellow italy
1 yellow italy
2 green greece
3 purple london
4 orange portugal
4 orange belgium
5 red spain
6 brown netherlands
7 white france
8 black russia
8 black russia
9 grey barcelona
10 violet turkey
10 violet romania
;

proc sql;
   create table want as
   select *
   from have
   group by A
   having count(distinct B) gt 1 | count(distinct C) gt 1;
quit;

[Reeza]

proc sql;
create table want as 
select distinct A from table 
group by A 
where max(b) ne min(b) 
or max(c) ne min(c));
quit;

---

@Gladis6680 wrote:

Is it possible to select rows where we have the same A, but different B and C. For example, from this table I would like rows where A=4 and A=10

A	B	C
1	yellow	italy
1	yellow	italy
2	green	greece
3	purple	london
4	orange	portugal
4	orange	belgium
5	red	spain
6	brown	netherlands
7	white	france
8	black	russia
8	black	russia
9	grey	barcelona
10	violet	turkey
10	violet	romania

---

[Gladis6680]

Thank you both so so so much !!

[Gladis6680]

Also, is this possible to do in a DATA step, I am not so familiar with proc sql. Thank you 

[PeterClemmensen]

Sure thing 🙂

data want(drop=flag);
   do until(last.A);
      set have;
      by A;
      if first.A=0 & first.A ne last.A & (B ne lag(B) | C ne lag(c)) then flag=1;
   end;
   do until (last.A);
      set have;
      by a;
      if flag=1 then output;
   end;
run;

[Gladis6680]

thank you so much !!!!!!

[PeterClemmensen]

No problem 🙂

[Ksharp · Accepted solution]

data have;
input A B $ C $20.;
datalines;
1 yellow italy
1 yellow italy
2 green greece
3 purple london
4 orange portugal
4 orange belgium
5 red spain
6 brown netherlands
7 white france
8 black russia
8 black russia
9 grey barcelona
10 violet turkey
10 violet romania
;
proc sort data=have out=temp nodupkey;
by _all_;
run;
proc sort data=temp out=want nouniquekey;
by a ;
run;

proc print;run;