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!
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;
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!
Hello. Atleast twice continuous occurrence of >200 should be enough..
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?
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
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;
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;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.