How to just keep ADJACENT records that have a common value

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

How to just keep ADJACENT records that have a common value

I want to select the adjacent records that have the same value in a variable. For example, in this data set, I just need the records with ID 2,3, 5, 6, 7, 9 and 10, they all have a value '1' in variable 'b' and adacent to each other.

I have a solution following the data set, but wondering if there is a more elegant way?

data have;

input ID a$ b;

datalines;

1 F 0

2 M 1

3 F 1

4 M 0

5 F 1

6 M 1

7 F 1

8 M 0

9 F 1

10 M 1

11 F 0

12 M 0

13 F 1

14 M 0

15 F 1

;

run;

data have2;

set have;

_n_ + 1;

  if _n_ <= N then do;

    Set have POINT=_N_;

    b2 = b;

                   end;

   else b2 = .;

Set have nobs = N;

b3=lag(b);

run;

data want;

set have2;

if (b=1 and b2=1) or (b=1 and b3=1) then output;

run;

proc print data=want;

run;


Accepted Solutions
Solution
‎04-19-2012 11:05 AM
Super User
Posts: 5,513

Re: How to just keep ADJACENT records that have a common value

Yes, I guess what I posted is a little more generalized than what you wanted.  It selects ALL groups rather than just those with b=1.  Switch to:

if b=1 and (first.b=0 or last.b=0);

That should do it.

View solution in original post


All Replies
Super User
Posts: 5,513

Re: How to just keep ADJACENT records that have a common value

imc,

You are making life difficult for yourself.  Try this variation.

data want;

   set have;

   by b notsorted;

   if first.b=0 or last.b=0;

run;

Good luck.

Contributor
Posts: 40

Re: How to just keep ADJACENT records that have a common value

Posted in reply to Astounding

But you get this result:

ObsIDab

2M1
3F1
5F1
6M1
7F1
9F1
7 10M1
8 11F0
9 12M0
Solution
‎04-19-2012 11:05 AM
Super User
Posts: 5,513

Re: How to just keep ADJACENT records that have a common value

Yes, I guess what I posted is a little more generalized than what you wanted.  It selects ALL groups rather than just those with b=1.  Switch to:

if b=1 and (first.b=0 or last.b=0);

That should do it.

Contributor
Posts: 40

Re: How to just keep ADJACENT records that have a common value

Posted in reply to Astounding

Great!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 273 views
  • 0 likes
  • 2 in conversation