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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.