Contributor
Posts: 21

# Checking 1-3 values or sequence

If there are two continuous value within a group above >=170 then I would like to test the following:

• However, if the second value is >200 then I would like to test if the THIRD value is over, 200 too. If so I would like to code it as "CHECK"
• If there are no two continuous numbers >=170 within that group then would like to code as "NORMAL"

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!

Super User
Posts: 9,441

## Re: Checking 1-3 values or sequence

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.

Contributor
Posts: 21

## Re: Checking 1-3 values or sequence

[ Edited ]

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!

Super User
Posts: 9,441

## Re: Checking 1-3 values or sequence

Add a flag, if the two records are > 170, set flag to 1 and use that also in the logic.

Contributor
Posts: 21

## Re: Checking 1-3 values or sequence

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;

Contributor
Posts: 21

## Re: Checking 1-3 values or sequence

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!

Super User
Posts: 10,698

## Re: Checking 1-3 values or sequence

``````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;``````
Contributor
Posts: 21

## Re: Checking 1-3 values or sequence

Hello. Thank you. What if the first value is not >=170? I would like to see if the next two consequentives are.

Contributor
Posts: 21

## running average by constraints

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
Super User
Posts: 10,698

## Re: Checking 1-3 values or sequence

That would be set to missing value.

Contributor
Posts: 21

## Re: Checking 1-3 values or sequence

Yes. I think that's where I am having trouble setting it to missing value or adding an flag :/

Super User
Posts: 10,698

## Re: Checking 1-3 values or sequence

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;

``````
Discussion stats
• 11 replies
• 423 views
• 0 likes
• 3 in conversation