topic Re: Running average by group in SAS Data Management

Running average by group

avama — Wed, 20 Sep 2017 12:23:31 GMT

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

ID	value	seq_id
1	150	1
1	190	2
1	205	3
1	210	4
1	150	5
2	169	1
2	175	2
2	150	3
2	144	4
3	200	1
3	201	2
3	199	3

Here's the output I am looking for:

ID	value	seq_id	avg
1	150	1
1	190	2	202
1	205	3
1	210	4
1	150	5
2	169	1
2	175	2	163
2	150	3
2	144	4
3	190	1	196
3	201	2
3	185	3

Thank you!

Re: Running average by group

Haikuo — Wed, 20 Sep 2017 13:16:19 GMT

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?

Re: Running average by group

avama — Wed, 20 Sep 2017 13:22:07 GMT

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