topic Re: cumulative question in SAS Procedures

cumulative question

hdg — Tue, 10 Apr 2012 18:21:02 GMT

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

Re: cumulative question

Linlin — Tue, 10 Apr 2012 18:35:57 GMT

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

Re: cumulative question

Ksharp — Wed, 11 Apr 2012 03:30:52 GMT

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

Re: cumulative question

Haikuo — Wed, 11 Apr 2012 12:52:18 GMT

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

Re: cumulative question

Linlin — Wed, 11 Apr 2012 13:05:50 GMT

what is groupformat?

Re: cumulative question

Haikuo — Wed, 11 Apr 2012 13:23:32 GMT

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

Re: cumulative question

Linlin — Wed, 11 Apr 2012 13:35:44 GMT

Thank you Haikuo! I have found it. - Linlin