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

How to compute a moving average within a by group?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

How to compute a moving average within a by group?

[ Edited ]

I am trying to compute an average within each ID group but with constraints. 

 

For example, I would like to start taking average of two cells that are continuously ABOVE 25 and assign it a value.

- if the average is between 26-50 then 'Medium'

- if the average is between 51-60 then 'High'

 

If the average needs to start for the FIRST value after 25. 

 

Here's the dataset:

IDMAXVELseq_id
A151
A272
A293
A524
B601
B262
B403
B24
B95
B456
C601
C122
C603
C504
C125

 

Output:

For example, starting point for A group will be at 27 as 15 is less than 26

IDMAXVELseq_idAverageOutput
A151  
A27228Medium
A293  
A524  
B60143Medium
B262  
B403  
B24  
B95  
B456  
C601  
C122  
C60355High
C504  
C125  

<

 

Thank you! Smiley Happy

  

Any help would be greatly appreciated! 


Accepted Solutions
Solution
a month ago
Trusted Advisor
Posts: 1,128

Re: How to compute a moving average within a by group?

data have;
input ID$   MAXVEL  seq_id;
cards;
A   15  1
A   27  2
A   29  3
A   52  4
B   60  1
B   26  2
B   40  3
B   2   4
B   9   5
B   45  6
C   60  1
C   12  2
C   60  3
C   50  4
C   12  5
;

data want;
length cnt $10;
set have;
by id;
retain cnt val;
if first.id then do;cnt='';val=.;end;
if maxvel >25 then check='1';
if check ne '' then cnt=cats(check,cnt);
if check='' then cnt='';
if cnt in ('1','11') then do;
val+maxvel;
end;
seq=lag(seq_id);
if check='' then val=.;
if cnt='11' then val=val/2;
if val in (26:50) then out='Medium';
else if val in (51:60) then out='High';
run;

proc sort data=want;
by id seq_id ;
run;

data want2(drop=_cnt check seq cnt );
merge want(in=a rename=(cnt=_cnt) drop=val out) want(in=b keep=id val out seq cnt rename=(seq=seq_id) where=(cnt='11'));
by id seq_id;
if a;
run;
Thanks,
Jag

View solution in original post


All Replies
Solution
a month ago
Trusted Advisor
Posts: 1,128

Re: How to compute a moving average within a by group?

data have;
input ID$   MAXVEL  seq_id;
cards;
A   15  1
A   27  2
A   29  3
A   52  4
B   60  1
B   26  2
B   40  3
B   2   4
B   9   5
B   45  6
C   60  1
C   12  2
C   60  3
C   50  4
C   12  5
;

data want;
length cnt $10;
set have;
by id;
retain cnt val;
if first.id then do;cnt='';val=.;end;
if maxvel >25 then check='1';
if check ne '' then cnt=cats(check,cnt);
if check='' then cnt='';
if cnt in ('1','11') then do;
val+maxvel;
end;
seq=lag(seq_id);
if check='' then val=.;
if cnt='11' then val=val/2;
if val in (26:50) then out='Medium';
else if val in (51:60) then out='High';
run;

proc sort data=want;
by id seq_id ;
run;

data want2(drop=_cnt check seq cnt );
merge want(in=a rename=(cnt=_cnt) drop=val out) want(in=b keep=id val out seq cnt rename=(seq=seq_id) where=(cnt='11'));
by id seq_id;
if a;
run;
Thanks,
Jag
Occasional Contributor
Posts: 16

Re: How to compute a moving average within a by group?

Hello. Thank you for the suggestion. I tested it and it works. Really appreciate it. 

 

Another quick question - is there an easy fix if I have the following?

Link: https://communities.sas.com/t5/SAS-Data-Management/Starting-point-average/m-p/376087

 

It's almost similar to this one but there is one other constraint.

 

Really appreciate it!

Trusted Advisor
Posts: 1,128

Re: How to compute a moving average within a by group?

Thank you, I responded to the other thread. Please check.

If the solution helped, could you please mark as answered. So it will help others if they have a similar query.
Thanks,
Jag
☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 234 views
  • 1 like
  • 2 in conversation