Solved
Contributor
Posts: 40

# 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: 6,762

## 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.

All Replies
Super User
Posts: 6,762

## 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

But you get this result:

 Obs ID a b

 1 2 M 1 2 3 F 1 3 5 F 1 4 6 M 1 5 7 F 1 6 9 F 1 7 10 M 1 8 11 F 0 9 12 M 0
Solution
‎04-19-2012 11:05 AM
Super User
Posts: 6,762

## 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