BookmarkSubscribeRSS Feed
avama
Calcite | Level 5

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!

11 REPLIES 11
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

avama
Calcite | Level 5

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!

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

avama
Calcite | Level 5

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;

avama
Calcite | Level 5

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!

Ksharp
Super User
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;
avama
Calcite | Level 5

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

avama
Calcite | Level 5

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  
Ksharp
Super User

That would be set to missing value.

avama
Calcite | Level 5

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

Ksharp
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 11 replies
  • 1434 views
  • 0 likes
  • 3 in conversation