## Starting point average

Solved
Occasional Contributor
Posts: 18

# Starting point average

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!!

Accepted Solutions
Solution
‎07-15-2017 02:24 PM
Posts: 1,137

## Re: Starting point average

``````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

All Replies
Occasional Contributor
Posts: 18

## Re: Starting point average

Solution
‎07-15-2017 02:24 PM
Posts: 1,137

## Re: Starting point average

``````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
☑ This topic is solved.