Date: Wed, 23 Jan 2008 19:58:22 -0500
Reply-To: Richard Ristow <wrristow@mindspring.com>
Sender: "SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>
From: Richard Ristow <wrristow@mindspring.com>
Subject: Re: compute total amount over any consecutive X years
In-Reply-To: <9232C79349284D4EB85A55DD3FE9F91D29241A@lcua11.uifoundation .org>
Content-Type: text/plain; charset="us-ascii"; format=flowed
>I [worked with] our IT dept. to build a standalone file that
>contains [gift amount] and [gift date] tied to [ID] for an
>individual's last 120 gifts. These gifts *could* all come in only a
>couple years in some cases.
>
>What I'd like to do now is determine which people in this file have
>cumulative giving of at least $100K over any consecutive five-year
>(or shorter) period in the file. Thoughts on how I might approach this?
>Variables are:
>
>ID
>DATE0001
>GFCRAM0001
>DATE0002
>GFCRAM0002
>DATE0003
>GFCRAM0003
Let's see: You want to attach to each gift, the total amount given
since the date five years earlier. It might go something like this:
I. For each gift, compute the total EVER given, through the date of
the gift. If I were doing it, I'd start by 'unrolling' the file to
one record per gift instead of one per donor, with variables
ID DATE GFCRAM
Then you can get the cumulant amounts with function CSUM in command
CREATE; or with LAG or LEAVE in the transformation language. (For
either, the data must be sorted by gift date within ID. Your data is
probably already in that form.)
II. For each gift, compute the total ever given, through FIVE YEARS
BEFORE THE GIFT DATE. Much trickier. I'd try,
A. Create an auxiliary file or dataset, with a record for each gift
but the date set 5 years backward. (Keep the correct date, also, in
the record, in a separate variable.)
B. Interleave this file with the master file:
ADD FILES
/FILE=5_yrs_back
/FILE=real
/BY ID DATE.
C. Attach to each five-years-back record, the cumulative gift amount
as of the next gift on record.
- This might work using function LEAD in CSUM, though if you have two
or more five-years-back records with no gift record in between, that
won't give you the value for any but the last one.
- Or, in the transformation language, with LEAVE you can carry
forward variables from the five-years-back records, and write a
record (maybe XSAVE?) when you reach the next record with a gift amount.
- Or, considerably more easily but sorting the file two extra times,
interleave gift records with five-years-back records by ID and
*descending* date. Then, you only need LEAVE to carry the cumulative
gift amount from a gift record to the next following (i.e., earlier)
five-years-back records.
D. Select out the five-years-back records, which now have the
cumulative giving total as of the next gift in the five-year window.
If necessary, re-sort into ascending order by date. Merge with the
original file (MATCH FILES), by original date.
Then, you have the five-years-back cumulative giving and the to-date
cumulative giving on every gift record, and you're done.
...........
OK, that's sketchy, likely enough too sketchy to implement. But I
think it's an outline that could work.
=====================
To manage your subscription to SPSSX-L, send a message to
LISTSERV@LISTSERV.UGA.EDU (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD
| 
