BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
avama
Calcite | Level 5

I am trying to do average. 

1. When the value starts at least 171, I would like to see if the next value is above 200. 

Option #1 - If the value >200 then I would like to see if the next value after that is also >200. If it is then I want to take average of those too or atleast FLAG them as yes.

Option #2 - if the value is between 171 and 200 then I would like to take average of those two numbers only

Option #3 - if it is <171 then skip to entries to see if they are >171 

 

Sample:

IDVALUESEQ_ID
11531
11712
12013
11504
11745
21431
21572
21753
22054
22105
21536
31541
31842
31533
31864
31785
31336

Here's an output:

 

IDVALUESEQ_IDaverage
11531 
11712186
12013 
11504 
11745 
21431 
21572 
21753 
22054208
22105 
21536 
31541 
31842 
31533 
31864182
31785 
31336 

 

THANK YOU!!

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

 

data have;
input ID$   value  seq_id;
cards;
1	153	1
1	171	2
1	201	3
1	150	4
1	174	5
2	143	1
2	157	2
2	175	3
2	205	4
2	210	5
2	153	6
3	154	1
3	184	2
3	153	3
3	186	4
3	178	5
3	133	6
;

data want;
n=_n_+1;
set have;
set have(keep=id value rename=(value=_value) ) point=n;
run;

data want2;
length cnt $10;
set want;
by id;
retain cnt val;
if last.id then _value=.;
if first.id then do;cnt='';val=.;end;
if value >=171 and _value>=200 then check='1';
if value in (171:200) and _value in (171:200) then check='1';
if check ne '' then cnt=cats(check,cnt);
if check='' then cnt='';
if cnt in ('1','11') then do;
val=round(sum(value,_value)/2);
end;
if check='' then val=.;
if cnt ne '';
run;

data want3;
set want2;
by id ;
if last.id ;
run;

proc sort data=want;
by id seq_id ;
run;

data want4(drop=cnt _value );
merge want(in=a) want3(in=b keep=id val cnt seq_id);
by id seq_id;
if a;
run;
Thanks,
Jag

View solution in original post

2 REPLIES 2
avama
Calcite | Level 5
 
Jagadishkatam
Amethyst | Level 16

 

data have;
input ID$   value  seq_id;
cards;
1	153	1
1	171	2
1	201	3
1	150	4
1	174	5
2	143	1
2	157	2
2	175	3
2	205	4
2	210	5
2	153	6
3	154	1
3	184	2
3	153	3
3	186	4
3	178	5
3	133	6
;

data want;
n=_n_+1;
set have;
set have(keep=id value rename=(value=_value) ) point=n;
run;

data want2;
length cnt $10;
set want;
by id;
retain cnt val;
if last.id then _value=.;
if first.id then do;cnt='';val=.;end;
if value >=171 and _value>=200 then check='1';
if value in (171:200) and _value in (171:200) then check='1';
if check ne '' then cnt=cats(check,cnt);
if check='' then cnt='';
if cnt in ('1','11') then do;
val=round(sum(value,_value)/2);
end;
if check='' then val=.;
if cnt ne '';
run;

data want3;
set want2;
by id ;
if last.id ;
run;

proc sort data=want;
by id seq_id ;
run;

data want4(drop=cnt _value );
merge want(in=a) want3(in=b keep=id val cnt seq_id);
by id seq_id;
if a;
run;
Thanks,
Jag

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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
  • 2 replies
  • 1105 views
  • 0 likes
  • 2 in conversation