Solved
New Contributor
Posts: 4

# How do I extract consecutive numbers into a new dataset?

[ Edited ]

Hello I am trying to EXTRACT consequectives numbers where the value is over >200 ATLEAST in a row

Sample data:

 ID value seq_id 1 120 1 1 201 2 1 214 3 1 100 4 1 150 5 2 150 1 2 200 2 2 179 3 3 201 1 3 219 2 3 149 3 3 174 4 3 135 5 3 124 6

So I want the following output:

 ID value seq_id 1 201 2 1 214 3 3 201 1 3 219 2

Thank you!

Accepted Solutions
Solution
‎07-12-2017 03:50 PM
Super User
Posts: 6,637

## Re: How do I extract consecutive numbers into a new dataset?

Simplest would be to move through the data twice:

data halfway_there;

set have;

flag = (value > 200);

run;

data want;

set halfway_there;

by id flag notsorted;

if flag=1 and first.flag=0 or last.flag=0;

run;

All Replies
Super User
Posts: 23,320

## Re: How do I extract consecutive numbers into a new dataset?

[ Edited ]

What if it's three times in a row? or 5, 6, 7 etc? What if it's a single one?

Your use cases in the example is too small, you need to expand it to at least be able to test the condition for a single occurence over 200, and then again with multiples.

Hello I am trying to EXTRACT consequectives numbers where the value is over >200 TWICE in a row

Sample data:

 ID value seq_id 1 120 1 1 201 2 1 214 3 1 100 4 1 150 5 2 150 1 2 200 2 2 179 3 3 201 1 3 219 2 3 149 3 3 174 4 3 135 5 3 124 6

So I want the following output:

 ID value seq_id 1 201 2 1 214 3 3 201 1 3 219 2

Thank you!

New Contributor
Posts: 4

## Re: How do I extract consecutive numbers into a new dataset?

Hello. Atleast twice continuous occurrence of >200 should be enough..

Super User
Posts: 6,637

## Re: How do I extract consecutive numbers into a new dataset?

Must "two in a row" be for the same ID?

If you have three in a row instead of two in a row, do you want each of the three output exactly once?

New Contributor
Posts: 4

## Re: How do I extract consecutive numbers into a new dataset?

Yea so atleast twice in a row. If there are more than 2, it would be helpful to include that value was as well Thank you

Solution
‎07-12-2017 03:50 PM
Super User
Posts: 6,637

## Re: How do I extract consecutive numbers into a new dataset?

Simplest would be to move through the data twice:

data halfway_there;

set have;

flag = (value > 200);

run;

data want;

set halfway_there;

by id flag notsorted;

if flag=1 and first.flag=0 or last.flag=0;

run;

PROC Star
Posts: 503

## Re: How do I extract consecutive numbers into a new dataset?

I have expanded your example little bit. As per @Reeza suggestion please check whether you can come up with different scenarios to test

```data have;
input ID value seq_id ;
datalines;
1 120 1
1 201 2
1 214 3
1 100 4
1 150 5
2 200 1
2 100 2
2 200 3
3 201 1
3 219 2
3 149 3
3 174 4
3 135 5
3 124 6
;
run;

proc sql;
select distinct a.* from have a
inner join have b
on a.id =b.id
and (a.seq_id =b.seq_id +1
or a.seq_id+1 =b.seq_id)
and a.value > 200;```
☑ This topic is solved.