topic Re: finding rolling 3 month average and remerging into original dataset in SAS Programming

finding rolling 3 month average and remerging into original dataset

learner_sas — Thu, 11 Jul 2013 15:21:03 GMT

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

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

Reeza — Thu, 11 Jul 2013 15:29:35 GMT

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?

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

learner_sas — Thu, 11 Jul 2013 16:01:45 GMT

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

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

RichardinOz — Fri, 12 Jul 2013 07:55:14 GMT

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