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
Content-Type: text/plain; charset="iso-8859-1"
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...
currmonth file - August 2004 data
lastmaster file - August 2003 through July 2004
When you read in currmonth file:
dateparms (keep=update startdate);
infile currmonth end=eof;
<...file layout w/ txndate...>;
<...sas code to process input file...>
if txndate <> . then do;
update = min(txndate,update); /* earliest date in the transaction file
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 */
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:
where txndate >= &startdate;
<...more sas statements...>
----- Original Message -----
From: "David Fickbohm" <DavidF@HOMEGAIN.COM>
Sent: Tuesday, August 31, 2004 2:31 PM
Subject: Rolling twelve months
> 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
> 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.
> Dave Fickbohm
> Data Mining Analyst
> 2450 45th St.
> Emeryville, CA, 94608
> Phone 510 655 0800 ext 4151