topic Re: Checking 1-3 values or sequence in SAS Data Management

Checking 1-3 values or sequence

avama — Wed, 19 Jul 2017 13:48:12 GMT

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!

Re: Checking 1-3 values or sequence

RW9 — Wed, 19 Jul 2017 13:56:47 GMT

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.

Re: Checking 1-3 values or sequence

avama — Wed, 19 Jul 2017 14:07:46 GMT

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!

Re: Checking 1-3 values or sequence

RW9 — Wed, 19 Jul 2017 14:13:45 GMT

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

Re: Checking 1-3 values or sequence

avama — Wed, 19 Jul 2017 14:18:25 GMT

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;

Re: Checking 1-3 values or sequence

Ksharp — Wed, 19 Jul 2017 14:20:29 GMT

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;

Re: Checking 1-3 values or sequence

avama — Wed, 19 Jul 2017 15:15:07 GMT

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

running average by constraints

avama — Thu, 20 Jul 2017 12:22:55 GMT

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

Re: Checking 1-3 values or sequence

avama — Thu, 20 Jul 2017 12:57:47 GMT

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!

Re: Checking 1-3 values or sequence

Ksharp — Thu, 20 Jul 2017 12:59:56 GMT

That would be set to missing value.

Re: Checking 1-3 values or sequence

avama — Thu, 20 Jul 2017 13:01:02 GMT

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

Re: Checking 1-3 values or sequence

Ksharp — Thu, 20 Jul 2017 14:13:48 GMT

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;

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

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

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