If there are two continuous value within a group above >=170 then I would like to test the following:
Here's the sample dataset:
id | seq_id | value |
1 | 1 | 184 |
1 | 2 | 211 |
1 | 3 | 217 |
1 | 4 | 156 |
2 | 1 | 145 |
2 | 2 | 184 |
2 | 3 | 201 |
2 | 4 | 201 |
2 | 5 | 140 |
3 | 1 | 175 |
3 | 2 | 205 |
3 | 3 | 184 |
3 | 4 | 111 |
3 | 5 | 126 |
Output
id | seq_id | value | yes | case |
1 | 1 | 184 | 1 | check |
1 | 2 | 211 | ||
1 | 3 | 217 | ||
1 | 4 | 156 | ||
2 | 1 | 145 | ||
2 | 2 | 184 | 1 | check |
2 | 3 | 201 | ||
2 | 4 | 201 | ||
2 | 5 | 140 | ||
3 | 1 | 175 | 0 | no check |
3 | 2 | 205 | ||
3 | 3 | 184 | ||
3 | 4 | 111 | ||
3 | 5 | 126 |
Thank you!
Reverse sort your data then use lag to check previous values. E.g:
proc sort data=have; by id descending seq_id; run; data want; set have; if lag(value) > 200 and lag2(value) > 200 then case="check"; run;
Note I have not checked this, post test data in the form of a datastep for checked code.
Hello. It works!!!
One quick question. What if it looked like this? For example if the two continuous cases are above 170 then i am not worried about next entries REGARDLESS OF The case above?
ID | seq_id | val | yes | case |
1 | 1 | 169 | ||
1 | 2 | 184 | 0 | no check |
1 | 3 | 175 | ||
1 | 4 | 142 | ||
1 | 5 | 184 | ||
1 | 6 | 211 | ||
1 | 7 | 217 |
THANK YOU!
Add a flag, if the two records are > 170, set flag to 1 and use that also in the logic.
Hello I tried but I am not getting what I am hoping for:
data want;
if value >=170 and _value<=199 then check='1';
if lag(value )>200 and lag2(value )> 200 then case="check";run;
I think that's where I am having trouble with adding Flags as SAS. I created if else statment but it is not working
thanks!
data have;
input id seq_id value;
cards;
1 1 184
1 2 211
1 3 217
1 4 156
2 1 145
2 2 184
2 3 201
2 4 201
2 5 140
3 1 175
3 2 205
3 3 184
3 4 111
3 5 126
;
run;
data want;
merge have have(firstobs=2 keep=id value rename=(id=id1 value=value1))
have(firstobs=3 keep=id value rename=(id=id2 value=value2));
if id=id1 and id=id2 then do;
if value>=170 and value1>=170 then do;
if value1>200 and value2>200 then check=1;
else check=0;
end;
end;
drop id1 id2 value1 value2;
run;
Hello. Thank you. What if the first value is not >=170? I would like to see if the next two consequentives are.
I have dataset that look like this. What I would like to do is I would like to see if the there are TWO continuous values >=170. I
- If they are, then I would like to take average of those two numbers. However if the there are three continuous values above >=170 and atleast two of them are >200 then I would like to indicate it as check =1.
- iF there is only one >=170, i would like to see if the group has any other two consequentive numbers that are >=170. If not, check = 2
id | val | seq |
1 | 145 | 1 |
1 | 201 | 2 |
1 | 183 | 3 |
1 | 210 | 4 |
2 | 165 | 1 |
2 | 199 | 2 |
2 | 210 | 3 |
2 | 175 | 4 |
3 | 175 | 1 |
3 | 149 | 2 |
3 | 175 | 3 |
3 | 180 | 4 |
3 | 145 | 5 |
4 | 145 | 1 |
4 | 175 | 2 |
4 | 144 | 3 |
4 | 134 | 4 |
Sample output:
id | val | seq | average | check |
1 | 145 | 1 | ||
1 | 201 | 2 | 192 | 1 |
1 | 183 | 3 | ||
1 | 210 | 4 | ||
2 | 165 | 1 | ||
2 | 199 | 2 | 205 | 0 |
2 | 210 | 3 | ||
2 | 175 | 4 | ||
3 | 175 | 1 | ||
3 | 149 | 2 | ||
3 | 175 | 3 | 178 | 0 |
3 | 180 | 4 | ||
3 | 145 | 5 | <170 | 2 |
4 | 145 | 1 | ||
4 | 175 | 2 | ||
4 | 144 | 3 | ||
4 | 134 | 4 |
That would be set to missing value.
Yes. I think that's where I am having trouble setting it to missing value or adding an flag 😕
You can according to the VALUE(current obs) ,VALUE1(then next obs),VALUE2(the next second obs) to modify code to fit your require.
data have;
input id seq_id value;
cards;
1 1 184
1 2 211
1 3 217
1 4 156
2 1 145
2 2 184
2 3 201
2 4 201
2 5 140
3 1 175
3 2 205
3 3 184
3 4 111
3 5 126
;
run;
data want;
merge have have(firstobs=2 keep=id value rename=(id=id1 value=value1))
have(firstobs=3 keep=id value rename=(id=id2 value=value2));
if id=id1 and id=id2 then do;
if value>=170 then do;
if value1>200 and value2>200 then check=1;
else check=0;
end;
else do;
if value1>170 and value2>170 then check=1;
end;
end;
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.