SPSSX Discussion

compute total amount over any consecutive X years

Classic

List

Threaded

7 messages Options

Mark Palmberg-2

compute total amount over any consecutive X years

A while back I inquired about a method for merging gift data with an ID
in order to begin to get a handle on an individual prospect's giving
pattern. I ended up working 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
cummulative 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
etc.

Thanks very much.

Mark

====================To manage your subscription to SPSSX-L, send a message to
[hidden email] (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

Richard Ristow

Re: compute total amount over any consecutive X years

>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
[hidden email] (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

Mark Palmberg-2

Re: compute total amount over any consecutive X years

Thanks very much for this, Richard. I do have another file that's already "unrolled" the way you recommend (one record per gift). From what you and ViAnn have said, it sounds like it might be more prudent to use that file for this task.

Mark

-----Original Message-----
From: Richard Ristow [mailto:[hidden email]]
Sent: Wed 1/23/2008 6:58 PM
To: Mark Palmberg; [hidden email]
Subject: Re: compute total amount over any consecutive X years

Maguin, Eugene

Re: compute total amount over any consecutive X years

In reply to this post by Mark Palmberg-2

Mark,

>>... I ended up working 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
cummulative giving of at least $100K over any consecutive five-year (or
shorter) period in the file.
Variables are:

ID
DATE0001
GFCRAM0001
DATE0002
GFCRAM0002
DATE0003
GFCRAM0003
etc.

First of all, I'm not at all clear on your file structure. This sentence
seems to suggest a 'long' structure ('... file that contains [gift amount]
and [gift date] tied to [ID] for an individual's last 120 gifts'). But your
variable name list seems to suggest a 'wide' structure of the form

ID DATE0001 GFCRAM0001 DATE0002 GFCRAM0002 ... DATE0120 GFCRAM0120.

One question is which, if either, is correct.

Gene Maguin

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (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

Mark Palmberg-2

Re: compute total amount over any consecutive X years

Sorry for the confusion, Gene. The format of the file is the latter:

ID DATE0001 GFCRAM0001 DATE0002 GFCRAM0002 ... DATE0120 GFCRAM0120.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Gene Maguin
Sent: Thursday, January 24, 2008 8:38 AM
To: [hidden email]
Subject: Re: compute total amount over any consecutive X years

Mark,

>>... I ended up working 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 cummulative giving of at least $100K over any consecutive
five-year (or
shorter) period in the file.
Variables are:

ID
DATE0001
GFCRAM0001
DATE0002
GFCRAM0002
DATE0003
GFCRAM0003
etc.

First of all, I'm not at all clear on your file structure. This sentence
seems to suggest a 'long' structure ('... file that contains [gift
amount] and [gift date] tied to [ID] for an individual's last 120
gifts'). But your variable name list seems to suggest a 'wide' structure
of the form

ID DATE0001 GFCRAM0001 DATE0002 GFCRAM0002 ... DATE0120 GFCRAM0120.

One question is which, if either, is correct.

Gene Maguin

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (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

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (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

Maguin, Eugene

Re: compute total amount over any consecutive X years

Thanks, Mark,

That's what I was thinking it was but I wanted to be sure. What follows is
an outline of a solution. Try it if you wish but I haven't tested it yet and
so there may pieces of the code that won't work. I will make up a test
dataset tonight to try it out and refine it. If you do try it, I'd
appreciate details of problems (and the solutions you find).

I think this is a vector problem. (I'd assume that if you are skilled in
Python, then other, maybe better, options are available. I'm not skilled in
python, so I can't help you on this.)

I'm going to assume that gift entries are absolutely contigious so that a
0.00 or sysmis gift amount is never between two non-zero gift amounts, that
if the date is sysmis that is the end of the gifts for that person, and that
every nonzero gift has a valid date and every non-sysmis date has a nonzero
gift.

So the first thing to do is to rearrange the variables so that the structure
is

ID DATE0001 DATE0002 ... DATE0120 GFCRAM0001 GFCRAM0002 ... GFCRAM0120.

The only way to do this that I know of is a Save command with the variables
listed in desired order (a pain in this case). Once done,

Vector date=date0001 to date0120/gift=GFCRAM0001 to GFCRAM0120.
Compute bigdonor=0.
Set maxloops=120.
Compute #i=0.
Loop if (non(sysmis(date(#i)))).
+ compute #i=#i+1.
+ compute cum=0
+ compute start=date(#i).
+ compute #j=#i.
+ loop if (start+(5*365.25*24*3600) le date(#j) and not(sysmis(date(#j)))).
+ compute cum=cum+gift(#j)
+ compute #j=#j+1.
+ end loop.
+ do if (cum ge 100000).
+ compute bigdonor=1.
+ break.
+ end if.
End loop.

Gene Maguin

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (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

Mark Palmberg-2

Re: compute total amount over any consecutive X years

Thank *you* Gene.

I'm looking at the syntax for the SAVE command to get this reordering
done. Do I need a subcommand of some sort to indicate I'm reordering
the variables? RENAME and MAP don't appear to be what I'm looking for,
I don't think.

Regards,

Mark

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Gene Maguin
Sent: Thursday, January 24, 2008 1:22 PM
To: [hidden email]
Subject: Re: compute total amount over any consecutive X years

Thanks, Mark,

That's what I was thinking it was but I wanted to be sure. What follows
is an outline of a solution. Try it if you wish but I haven't tested it
yet and so there may pieces of the code that won't work. I will make up
a test dataset tonight to try it out and refine it. If you do try it,
I'd appreciate details of problems (and the solutions you find).

I think this is a vector problem. (I'd assume that if you are skilled in
Python, then other, maybe better, options are available. I'm not skilled
in python, so I can't help you on this.)

I'm going to assume that gift entries are absolutely contigious so that
a 0.00 or sysmis gift amount is never between two non-zero gift amounts,
that if the date is sysmis that is the end of the gifts for that person,
and that every nonzero gift has a valid date and every non-sysmis date
has a nonzero gift.

So the first thing to do is to rearrange the variables so that the
structure is

ID DATE0001 DATE0002 ... DATE0120 GFCRAM0001 GFCRAM0002 ... GFCRAM0120.

The only way to do this that I know of is a Save command with the
variables listed in desired order (a pain in this case). Once done,

Vector date=date0001 to date0120/gift=GFCRAM0001 to GFCRAM0120.
Compute bigdonor=0.
Set maxloops=120.
Compute #i=0.
Loop if (non(sysmis(date(#i)))).
+ compute #i=#i+1.
+ compute cum=0
+ compute start=date(#i).
+ compute #j=#i.
+ loop if (start+(5*365.25*24*3600) le date(#j) and
not(sysmis(date(#j)))).
+ compute cum=cum+gift(#j)
+ compute #j=#j+1.
+ end loop.
+ do if (cum ge 100000).
+ compute bigdonor=1.
+ break.
+ end if.
End loop.

Gene Maguin

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (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

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (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