Date:         Tue, 31 Aug 2004 15:51:56 -0400
Reply-To:     Pat Malarkey <pmalarkey@COX.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Pat Malarkey <pmalarkey@COX.NET>
Subject:      Re: Rolling twelve months
Comments: To: David Fickbohm <DavidF@HOMEGAIN.COM>
Content-Type: text/plain; charset="iso-8859-1"

Hi, David!

I worked on a data warehouse that had a 24-month rolling database.  If your
report is structured the same way - with some kind of "date of transaction"
(txndate) in the current month data file, you can use the data to calculate
current month and then go 12 months back from that...

For example:

currmonth file - August 2004 data
lastmaster file - August 2003 through July 2004

When you read in currmonth file:

data currmonth
        dateparms (keep=update startdate);
infile currmonth end=eof;
<...file layout w/ txndate...>;
retain txndate;
<...sas code to process input file...>
if txndate <> . then do;
  update = min(txndate,update);    /* earliest date in the transaction file
*/
end;
output currmonth;   /* output your raw data to a sas file */

if eof then do;     /* calculate date to begin from */
  startmo = month(update) + 1;   /* my start month will be one greater than
current month */
  startyr  = year(update) - 1;      /* my start year will be last year */
  startday = 1;                           /* start day assumed to be first
of month */
  startdatec = compress(startm0||'/'||startday||'/'||startyr);  /* string it
in a character format */
  startdate = put(startdatec,mmddyy10.);  /* put the string in a date format
*/
  output dateparms;  /* put out a temp file with earliest date in the
transaction file and beginning of month 1 year ago */
end;
run;

(untested!!!)

This way, when you read in a "master file" with data from August 2003
through July 2004, you can compare it against the temp dateparms file with
your earliest transaction date (update) and your 1 year ago date
(startdate).  In this example, update should = 08/whatever/2004 and
startdate should = 09/01/2004.

You can then use either a proc format or a symput to pick off the
appropriate records from your master file.  In this case, I'll use symput:

data newfile;
  set currmonth
       lastmaster;
where txndate >= &startdate;
<...more sas statements...>
run;

HTH

- Pat




----- Original Message -----
From: "David Fickbohm" <DavidF@HOMEGAIN.COM>
Newsgroups: bit.listserv.sas-l
To: <SAS-L@LISTSERV.UGA.EDU>
Sent: Tuesday, August 31, 2004 2:31 PM
Subject: Rolling twelve months


> People,
> Looking for your thoughts on the best way to create a monthly report based
> on a rolling twelve months.
>
> All of the calculations for this report are done and then the data is
split
> out by month.
>
> My thought is to create a simple macro that that just substitutes in a
> month.  Each month I remove one month from the top line  that executes the
> macro and add a month to the bottom line that executes the macro.
>
> I would appreciate people thoughts on this.
>
> Thanks
> Dave
>
> Dave Fickbohm
> Data Mining Analyst
> Homegain+
> 2450 45th St.
> Emeryville, CA, 94608
> Phone 510 655 0800 ext 4151
>