SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Starting point average

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

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:

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


Accepted Solutions
Solution
‎07-15-2017 02:24 PM
Trusted Advisor
Posts: 1,129

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

View solution in original post


All Replies
Occasional Contributor
Posts: 16

Re: Starting point average

 
Solution
‎07-15-2017 02:24 PM
Trusted Advisor
Posts: 1,129

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 187 views
  • 0 likes
  • 2 in conversation