topic selecting observations that have different corresponding columns in SAS Programming

selecting observations that have different corresponding columns

Gladis6680 — Thu, 06 Jun 2019 17:12:58 GMT

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

Re: selecting observations that have different corresponding columns

PeterClemmensen — Thu, 06 Jun 2019 17:30:13 GMT

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;

Re: selecting observations that have different corresponding columns

Reeza — Thu, 06 Jun 2019 17:49:47 GMT

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

Re: selecting observations that have different corresponding columns

Gladis6680 — Thu, 06 Jun 2019 17:57:00 GMT

Thank you both so so so much !!

Re: selecting observations that have different corresponding columns

Gladis6680 — Thu, 06 Jun 2019 18:13:50 GMT

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

Re: selecting observations that have different corresponding columns

PeterClemmensen — Thu, 06 Jun 2019 18:27:19 GMT

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;

Re: selecting observations that have different corresponding columns

Gladis6680 — Thu, 06 Jun 2019 18:29:55 GMT

thank you so much !!!!!!

Re: selecting observations that have different corresponding columns

PeterClemmensen — Thu, 06 Jun 2019 18:32:08 GMT

No problem 🙂

Re: selecting observations that have different corresponding columns

Ksharp — Sat, 08 Jun 2019 16:25:51 GMT

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;