Removing observations where one variable is not unique

[Viktoreli]

Hi,

I could not find any solution to my problem searching the forum so I thought I would ask instead.

I have a dataset where I want to drop observations if variable-c changes while variable-b remains the same.

A B C

1 1 10

2 1 20

3 2 10

4 2 10

5 2 10

6 3 10

7 3 20

8 4 20

Any observation where b is one number and C has more then one value I want dropped.

Im sorry that I could not express myself better, im fairly new at SAS.

I would be very thankfull for any response,

thanks in advance.

V

[VinitvictorCorr]

so you want your output to be :-

A B C
1 1 10
3 2 10
4 2 10
5 2 10
6 3 10
8 4 20


Am i right?

[Viktoreli]

Thats right!
The above mentioned data would look like:
1 2 10
2 2 10
3 2 10
4 4 20

[PeterClemmensen · Accepted solution]

data have;
input A B C;
datalines;
1 1 10
2 1 20
3 2 10
4 2 10
5 2 10
6 3 10
7 3 20
8 4 20
;

data want(drop=flag);
    do until (last.B);
        set have;
        by B notsorted;
        if first.B then _iorc_=C;
        if _iorc_ ne C then flag=1;
    end;
    do until (last.B);
        set have;
        by B notsorted;
        if not flag then output; 
    end;
run;

Result:

A       B       C
3	2	10
4	2	10
5	2	10
8	4	20

[Viktoreli]

Thank you very much!

[Kurt_Bremser]

Something like

data have;
input A B C;
datalines;
1 1 10
2 1 20
3 2 10
4 2 10
5 2 10
6 3 10
7 3 20
8 4 20
;

data want;
set have;
by b;
if not first.b and c ne lag(c) then delete;
run;

proc print data=want noobs;
run;

Result:

A	B	C
1	1	10
3	2	10
4	2	10
5	2	10
6	3	10
8	4	20

?

[novinosrin]

Hi @Viktoreli   Would this do?

data have;
input A B C;
datalines;
1 1 10
2 1 20
3 2 10
4 2 10
5 2 10
6 3 10
7 3 20
8 4 20
;

proc sql;
create table want as
select *
from have
group by b
having count(distinct c)=1;
quit;

[Viktoreli]

Thank you,
worked like a charm aswell!