SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How do I extract consecutive numbers into a new dataset?

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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:

IDvalueseq_id
11201
12012
12143
11004
11505
21501
22002
21793
32011
32192
31493
31744
31355
31246

So I want the following output:

IDvalueseq_id
12012
12143
32011
32192

 

Thank you!


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

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;

View solution in original post


All Replies
Super User
Posts: 17,750

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. 

 


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!


 

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: 5,074

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: 5,074

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: 252

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.

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

Discussion stats
  • 6 replies
  • 168 views
  • 1 like
  • 4 in conversation