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
encounter | Discharge_date | category | indicator | fraction_indicator_one_month_by grp | Fraction_indicator_Rolling_3month |
1 | Jan-12 | grp1 | 1 | 3/5 | 11/16 |
2 | Jan-12 | grp2 | 0 | 4/6 | 10/13 |
3 | Jan-12 | grp2 | 1 | 4/6 | 10/13 |
4 | Jan-12 | grp2 | 0 | 4/6 | 10/13 |
5 | Jan-12 | grp1 | 0 | 3/5 | 11/16 |
6 | Jan-12 | grp2 | 1 | 4/6 | 10/13 |
7 | Jan-12 | grp1 | 0 | 3/5 | 11/16 |
8 | Jan-12 | grp2 | 1 | 4/6 | 10/13 |
9 | Jan-12 | grp1 | 1 | 3/5 | 11/16 |
10 | Jan-12 | grp2 | 1 | 4/6 | 10/13 |
11 | Jan-12 | grp1 | 1 | 3/5 | 11/16 |
12 | Feb-12 | grp2 | 1 | 3/5 | 8/13 |
13 | Feb-12 | grp1 | 1 | 4/5 | 11/15 |
14 | Feb-12 | grp2 | 0 | 3/5 | 8/13 |
15 | Feb-12 | grp1 | 0 | 4/5 | 11/15 |
16 | Feb-12 | grp2 | 1 | 3/5 | 8/13 |
17 | Feb-12 | grp1 | 1 | 4/5 | 11/15 |
18 | Feb-12 | grp2 | 0 | 3/5 | 8/13 |
19 | Feb-12 | grp1 | 1 | 4/5 | 11/15 |
20 | Feb-12 | grp2 | 1 | 3/5 | 8/13 |
21 | Feb-12 | grp1 | 1 | 4/5 | 11/15 |
22 | Mar-12 | grp2 | 0 | 3/4 | 0 |
23 | Mar-12 | grp1 | 0 | 4/6 | 0 |
24 | Mar-12 | grp2 | 1 | 3/4 | 0 |
25 | Mar-12 | grp1 | 1 | 4/6 | 0 |
26 | Mar-12 | grp2 | 0 | 3/4 | 0 |
27 | Mar-12 | grp1 | 0 | 4/6 | 0 |
28 | Mar-12 | grp1 | 1 | 4/6 | 0 |
29 | Mar-12 | grp1 | 1 | 4/6 | 0 |
30 | Mar-12 | grp2 | 1 | 3/4 | 0 |
31 | Mar-12 | grp1 | 1 | 4/6 | 0 |
32 | Apr-12 | grp2 | 0 | 2/4 | 0 |
33 | Apr-12 | grp1 | 1 | 3/4 | 0 |
34 | Apr-12 | grp2 | 1 | 2/4 | 0 |
35 | Apr-12 | grp1 | 1 | 3/4 | 0 |
36 | Apr-12 | grp2 | 0 | 2/4 | 0 |
37 | Apr-12 | grp1 | 1 | 3/4 | 0 |
38 | Apr-12 | grp2 | 1 | 2/4 | 0 |
39 | Apr-12 | grp1 | 0 | 3/4 | 0 |
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?
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
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.