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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.