topic How do I extract consecutive numbers into a new dataset? in SAS Data Management

How do I extract consecutive numbers into a new dataset?

radhikaa4 — Wed, 12 Jul 2017 19:26:38 GMT

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!

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

Reeza — Wed, 12 Jul 2017 19:22:59 GMT

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!

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

Astounding — Wed, 12 Jul 2017 19:23:46 GMT

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?

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

radhikaa4 — Wed, 12 Jul 2017 19:25:57 GMT

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

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

Astounding — Wed, 12 Jul 2017 19:29:48 GMT

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;

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

radhikaa4 — Wed, 12 Jul 2017 19:31:11 GMT

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

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

kiranv_ — Wed, 12 Jul 2017 19:33:24 GMT

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;