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

Running average by group

Reply
Contributor
Posts: 21

Running average by group

I am trying to create a running average by group

 

For example, i want to start creating the average of 2 continuous numbers when it starts with value of 170. However, if it first hits 170 and then >=200 then another >=200, i want to start average of those three. if not, just the average of two is fine. 

 

Here's an example

 

IDvalueseq_id
11501
11902
12053
12104
11505
21691
21752
21503
21444
32001
32012
31993

 

Here's the output I am looking for: 

IDvalueseq_idavg
11501 
11902202
12053 
12104 
11505 
21691 
21752163
21503 
21444 
31901196
32012 
31853 

 

Thank you!

Respected Advisor
Posts: 3,162

Re: Running average by group

Rules seem not entirely clear to me.

1. What if you have more than 2 groups within the same id, do you only count the first one?

2.  What if you have more than 2 obs that are >=200 after the first >=170. Are they going to the first group or you kick off a new group/just ignore it?

3. What if first >=170 hitting the bottom, do you consider itself as the average or ignore it?

Contributor
Posts: 21

Re: Running average by group

Hi!

 

1. Each ID is unique but have multiple entries. so we count the first occurence of >=170 onwards

2. Yes, it'd be helpful to extract them into a new dataset if first >=170 then second >=200 and then the third is >=200

3. If the last row for that ID is >=170, new dataset would be helpful! 

 

Thank you!!

Ask a Question
Discussion stats
  • 2 replies
  • 171 views
  • 0 likes
  • 2 in conversation