Date:         Tue, 20 Aug 2002 19:14:19 -0700
Reply-To:     lpogoda <lpogoda@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         lpogoda <lpogoda@HOTMAIL.COM>
Organization: http://groups.google.com/
Subject:      Re: Rolling Total
Content-Type: text/plain; charset=ISO-8859-1

WEBBN@BUPA.COM (WEBB, Nina) wrote in message news:<B652147867BED411BF0C00508BCF8CC20A5CDF16@manex01.internal.bupa.co.uk>...
> I'd be very grateful if someone could let me know if there's an easy way to
> do a 12 month rolling total from 24 months worth of data.
>
> I've achieved the result I want with a very clumsy bit of programming:
>
> proc summary data  claims nway missing;
> class mnth dw rel;
> var paid;
> output out  claims2 sum;
> run;
>
> proc sort data  claims2;
> by dw rel;
> run;
>
> proc transpose data  claims2 out  claims3;
> by dw rel;
> id mnth;
> var paid;
> run;
>
> data final (drop  name   label   200009  200010  200011  200012  200101
>  200102  200103
>                          200104  200105  200106  200107  200108  200109
>  200110  200111  200112
>                          200201  200202  200203  200204  200205  200206
>  200207);
> set claims3;
> me1  sum( 200009,  200010,  200011,  200012,  200101,  200102,  200103,
>  200104,  200105,
>                          200106,  200107,  200108);
> me2  sum( 200010,  200011,  200012,  200101,  200102,  200103,  200104,
>  200105,  200106,
>                          200107,  200108,  200109);
> etc
> etc
> etc
> run;
>
> where mnth is in the format yymmn6.
>
> I suspect arrays and loops come into it somewhere!
>

I'm not at all sure I understand you correctly, but if I do, here's my
(tested)take on it:

/*create some simple test data*/
data test;
   id = 'a';
   do n = 1 to 24;
      output;
   end;
   id = 'b';
   do n = 25 to 48;
      output;
   end;
   stop;
run;
/*transpose to get one observation per by-value*/
proc transpose data = test out = xx;
   by id;
quit;
/*load values into an array and calculate sums into another array*/
data tots (keep = id sum1 - sum12);
  set xx;
  array sums {12} sum1 - sum12;
  array cols {24} col1 - col24;
  do n = 1 to 12;
     sums{n} = sum(cols{n},cols{n + 1}, cols{n + 2}, cols{n + 3},
                   cols{n + 4}, cols{n + 5}, cols{n + 6},
                   cols{n + 7}, cols{n + 8}, cols{n + 9},
                   cols{n + 10}, cols{n + 11});

  end;
run;