## cumulative question

Solved
Frequent Contributor
Posts: 79

# cumulative question

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

Accepted Solutions
Solution
‎04-10-2012 02:35 PM
Super Contributor
Posts: 1,636

## Re: cumulative question

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

All Replies
Solution
‎04-10-2012 02:35 PM
Super Contributor
Posts: 1,636

## Re: cumulative question

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

Super User
Posts: 10,787

## Re: cumulative question

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

Posts: 3,167

## Re: cumulative question

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

Super Contributor
Posts: 1,636

## Re: cumulative question

what is groupformat?

Posts: 3,167

## Re: cumulative question

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

Super Contributor
Posts: 1,636

## Re: cumulative question

Thank you Haikuo! I have found it.  - Linlin

🔒 This topic is solved and locked.