BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Viktoreli
Obsidian | Level 7

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

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20
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

View solution in original post

7 REPLIES 7
VinitvictorCorr
Quartz | Level 8
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
Obsidian | Level 7
Thats right!
The above mentioned data would look like:
1 2 10
2 2 10
3 2 10
4 4 20
PeterClemmensen
Tourmaline | Level 20
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
Obsidian | Level 7
Thank you very much!
Kurt_Bremser
Super User

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
Tourmaline | Level 20

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
Obsidian | Level 7
Thank you,
worked like a charm aswell!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1808 views
  • 3 likes
  • 5 in conversation