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:
ID | VALUE | SEQ_ID |
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 |
Here's an output:
ID | VALUE | SEQ_ID | average |
1 | 153 | 1 | |
1 | 171 | 2 | 186 |
1 | 201 | 3 | |
1 | 150 | 4 | |
1 | 174 | 5 | |
2 | 143 | 1 | |
2 | 157 | 2 | |
2 | 175 | 3 | |
2 | 205 | 4 | 208 |
2 | 210 | 5 | |
2 | 153 | 6 | |
3 | 154 | 1 | |
3 | 184 | 2 | |
3 | 153 | 3 | |
3 | 186 | 4 | 182 |
3 | 178 | 5 | |
3 | 133 | 6 |
THANK YOU!!
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;
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;
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.
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.