Hello, I am trying to find the rolling cumulative average by month
Suppose I have a dataset
var | date | info | begindate |
ann | 12/29/2000 | 2 | 12/1/2000 |
ann | 1/1/2001 | 3 | 1/1/2001 |
ann | 1/2/2001 | 5 | 1/1/2001 |
joe | 9/20/2001 | 6 | 9/1/2001 |
joe | 9/21/2001 | 7 | 9/1/2001 |
joe | 9/24/2001 | 8 | 9/1/2001 |
For each row I want to compute the rolling avg by each month on each available date grouped by var
So for example whatever dates are available within the month for each var i want to compute the average upto that date.
Thanks for your help on this!
Example
var | date | info | begindate | info avg |
ann | 12/29/2000 | 2 | 12/1/2000 | 2 |
ann | 1/1/2001 | 3 | 1/1/2001 | 3 |
ann | 1/2/2001 | 5 | 1/1/2001 | (3+5)/2 |
joe | 9/20/2001 | 6 | 9/1/2001 | 6 |
joe | 9/21/2001 | 7 | 9/1/2001 | (6+7)/2 |
joe | 9/24/2001 | 8 | 9/1/2001 | (6+7+8)/3 |
Thanks!!
data have;
informat date begindate mmddyy10.;
format date begindate mmddyy10.;
input var $ date info begindate ;
cards;
ann 12/29/2000 2 12/1/2000
ann 1/1/2001 3 1/1/2001
ann 1/2/2001 5 1/1/2001
joe 9/20/2001 6 9/1/2001
joe 9/21/2001 7 9/1/2001
joe 9/24/2001 8 9/1/2001
;
data want(drop=count total);
set have;
retain count total;
by var begindate;
if first.begindate then do;
count=0;
total=0; end;
count+1;
total+info;
info_ave=total/count;
run;
proc print;run;
obs date begindate var info info_ave
1 12/29/2000 12/01/2000 ann 2 2.0
2 01/01/2001 01/01/2001 ann 3 3.0
3 01/02/2001 01/01/2001 ann 5 4.0
4 09/20/2001 09/01/2001 joe 6 6.0
5 09/21/2001 09/01/2001 joe 7 6.5
6 09/24/2001 09/01/2001 joe 8 7.0
Linlin
data have;
informat date begindate mmddyy10.;
format date begindate mmddyy10.;
input var $ date info begindate ;
cards;
ann 12/29/2000 2 12/1/2000
ann 1/1/2001 3 1/1/2001
ann 1/2/2001 5 1/1/2001
joe 9/20/2001 6 9/1/2001
joe 9/21/2001 7 9/1/2001
joe 9/24/2001 8 9/1/2001
;
data want(drop=count total);
set have;
retain count total;
by var begindate;
if first.begindate then do;
count=0;
total=0; end;
count+1;
total+info;
info_ave=total/count;
run;
proc print;run;
obs date begindate var info info_ave
1 12/29/2000 12/01/2000 ann 2 2.0
2 01/01/2001 01/01/2001 ann 3 3.0
3 01/02/2001 01/01/2001 ann 5 4.0
4 09/20/2001 09/01/2001 joe 6 6.0
5 09/21/2001 09/01/2001 joe 7 6.5
6 09/24/2001 09/01/2001 joe 8 7.0
Linlin
data have; informat date begindate mmddyy10.; format date begindate mmddyy10.; input var $ date info begindate ; cards; ann 12/29/2000 2 12/1/2000 ann 1/1/2001 3 1/1/2001 ann 1/2/2001 5 1/1/2001 joe 9/20/2001 6 9/1/2001 joe 9/21/2001 7 9/1/2001 joe 9/24/2001 8 9/1/2001 ; run; data want(drop=count total); set have; retain count total; by var date groupformat; format date yymmd.; if first.date then do; count=0; total=0; end; count+1; total+info; info_ave=total/count; run;
Ksharp
Nice, first time see 'groupformat' in action! Thanks for sharing!
what is groupformat?
LinLin, do a google on "sas groupformat", you will know it instantly. for some reason, I can't paste links.
Thank you Haikuo! I have found it. - Linlin
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.