LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (January 2008)Back to main SPSSX-L pageJoin or leave SPSSX-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Wed, 23 Jan 2008 19:58:22 -0500
Reply-To:     Richard Ristow <>
Sender:       "SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>
From:         Richard Ristow <>
Subject:      Re: compute total amount over any consecutive X years
Comments: To: Mark Palmberg <>
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


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

Back to: Top of message | Previous page | Main SPSSX-L page