How do I extract consecutive numbers into a new dataset?

[radhikaa4]

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!

[Reeza]

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. 

---

@radhikaa4 wrote:

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!

---

[radhikaa4]

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

[Astounding]

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?

[radhikaa4]

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

[Astounding · Accepted solution]

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;

[kiranv_]

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;