SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Checking 1-3 values or sequence

Reply
Occasional Contributor
Posts: 16

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:

idseq_idvalue
11184
12211
13217
14156
21145
22184
23201
24201
25140
31175
32205
33184
34111
35126

 

Output

idseq_idvalueyescase
111841check
12211  
13217  
14156  
21145  
221841check
23201  
24201  
25140  
311750no check
32205  
33184  
34111  
35126  

 

Thank you!

Super User
Super User
Posts: 7,413

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.

Occasional Contributor
Posts: 16

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?

 

IDseq_idvalyescase
11169  
12184 0no check
13175  
14142  
15184  
16211  
17217  

 

THANK YOU!

Super User
Super User
Posts: 7,413

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.

Occasional Contributor
Posts: 16

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;

Occasional Contributor
Posts: 16

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: 9,687

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;
Occasional Contributor
Posts: 16

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. 

Occasional Contributor
Posts: 16

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

 

 

idvalseq
11451
12012
11833
12104
21651
21992
22103
21754
31751
31492
31753
31804
31455
41451
41752
41443
41344

 

 

Sample output:

idvalseqaveragecheck
11451  
120121921
11833  
12104  
21651  
219922050
22103  
21754  
31751  
31492  
317531780
31804  
31455<1702
41451  
41752  
41443  
41344  
Super User
Posts: 9,687

Re: Checking 1-3 values or sequence

That would be set to missing value.

Occasional Contributor
Posts: 16

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: 9,687

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;

Ask a Question
Discussion stats
  • 11 replies
  • 308 views
  • 0 likes
  • 3 in conversation