cumulative question

[hdg]

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

[Linlin · Accepted solution]

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

[Ksharp]

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

[Haikuo]

Nice, first time see 'groupformat' in action! Thanks for sharing!

[Linlin]

what is groupformat?

[Haikuo]

LinLin, do a google on "sas groupformat", you will know it instantly.  for some reason, I can't paste links.

[Linlin]

Thank you Haikuo! I have found it.  - Linlin