topic Re: Starting point average in SAS Data Management

Starting point average

avama — Fri, 14 Jul 2017 16:12:20 GMT

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

Re: Starting point average

avama — Sat, 15 Jul 2017 01:32:30 GMT

Re: Starting point average

Jagadishkatam — Sat, 15 Jul 2017 03:52:21 GMT

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;