topic Re: Removing observations where one variable is not unique in SAS Programming

Removing observations where one variable is not unique

Viktoreli — Thu, 26 Sep 2019 11:09:15 GMT

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.

Re: Removing observations where one variable is not unique

VinitvictorCorr — Thu, 26 Sep 2019 11:16:59 GMT

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?

Re: Removing observations where one variable is not unique

PeterClemmensen — Thu, 26 Sep 2019 11:19:26 GMT

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

Re: Removing observations where one variable is not unique

Viktoreli — Thu, 26 Sep 2019 11:20:43 GMT

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

Re: Removing observations where one variable is not unique

Kurt_Bremser — Thu, 26 Sep 2019 11:21:29 GMT

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:

Re: Removing observations where one variable is not unique

novinosrin — Thu, 26 Sep 2019 11:38:19 GMT

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;

Re: Removing observations where one variable is not unique

Viktoreli — Thu, 26 Sep 2019 11:49:32 GMT

Thank you,
worked like a charm aswell!

Re: Removing observations where one variable is not unique

Viktoreli — Thu, 26 Sep 2019 11:50:14 GMT

Thank you very much!