finding rolling 3 month average and remerging into original dataset

Reply
Frequent Contributor
Posts: 89

finding rolling 3 month average and remerging into original dataset

Hello

I am trying to find rolling 3 month average(fraction would be because my variable is binomial) for all the rows and merge back to the original dataset with encounter.

I am wondering if somebody can help.

Thanks

below is my dataset

encounterDischarge_datecategoryindicatorfraction_indicator_one_month_by grpFraction_indicator_Rolling_3month
1Jan-12grp113/5 11/16
2Jan-12grp204/6 10/13
3Jan-12grp214/6 10/13
4Jan-12grp204/6 10/13
5Jan-12grp103/5 11/16
6Jan-12grp214/6 10/13
7Jan-12grp103/5 11/16
8Jan-12grp214/6 10/13
9Jan-12grp113/5 11/16
10Jan-12grp214/6 10/13
11Jan-12grp113/5 11/16
12Feb-12grp213/5  8/13
13Feb-12grp114/5 11/15
14Feb-12grp203/5  8/13
15Feb-12grp104/5 11/15
16Feb-12grp213/5  8/13
17Feb-12grp114/5 11/15
18Feb-12grp203/5  8/13
19Feb-12grp114/5 11/15
20Feb-12grp213/5  8/13
21Feb-12grp114/5 11/15
22Mar-12grp203/40     
23Mar-12grp104/60     
24Mar-12grp213/40     
25Mar-12grp114/60     
26Mar-12grp203/40     
27Mar-12grp104/60     
28Mar-12grp114/60     
29Mar-12grp114/60     
30Mar-12grp213/40     
31Mar-12grp114/60     
32Apr-12grp202/40     
33Apr-12grp113/40     
34Apr-12grp212/40     
35Apr-12grp113/40     
36Apr-12grp202/40     
37Apr-12grp113/40     
38Apr-12grp212/40     
39Apr-12grp103/40     
Super User
Posts: 17,840

Re: finding rolling 3 month average and remerging into original dataset

How are you going to join it back? How are you defining 3 month average?

What I mean by joining it back is that each record can be in up to 3 rolling averages, so how would you expect to see that?

Frequent Contributor
Posts: 89

Re: finding rolling 3 month average and remerging into original dataset

Reeza,

I have table with first 5 columns in from my attached dataset. and I am trying to generate last column. I thought using group by function it might be possible to remerge to original dataset.I might be wrong.  I used excel to calculate 6 column. Definition of 3 month average would be average of running 3 month such as march+feb_jan  for all march data, Feb+March+April for April data and so on. I am trying to see exacly like my sixth column for example for Encounter = 1, and fraction_indicator_rolling_3month is  11/16 which I got from adding last 3 month (3/5+4/5+4/6) of column5 for category = grp1.

hope it make sense

thanks

Super Contributor
Posts: 644

Re: finding rolling 3 month average and remerging into original dataset

Try this for a start, assumin indicator is what you want the moving 3 months average.

Average3months = mean (indicator, lag(indicator), lag2(indicator)) ;

NB it will calculate a one month and a 2 month average for the first 2 rows.

I anticipate this will rais further questions, perhaps about grouping on an individaual.

Richard

Ask a Question
Discussion stats
  • 3 replies
  • 297 views
  • 0 likes
  • 3 in conversation