determining most recent activity

[wsu_wright]

I have a denormalized database in which the previous institutional
enrollment year is included (in slotted columns) for each applicant who
may appear in multiple records.  I need to determine from these slotted
previous entries which entry is the most recent in terms of the current
record (less than or equal in time).  Listed below is an example.

appy= application record year
prev= most recent (<=) of all slotted prior institution years (this is
the factor which I need to calculate)
year#= the year of prior institution attendance (these are always
slotted so that the most recent appears in year1, they slotted to 10
possible years)


ID appy prev year1 year2 year3 year4 year5...year10
12 1987 1983  2001  2000  1993  1983  1973
12 2003 2001  2001  2000  1993  1983  1973
43 2005 2005  2005
52 2007 2004  2008  2004  2001
52 2008 2008  2008  2004  2001
23 2001 1997  2004  2002  1999  1997  1983

So, "prev" is created from the slotted years to the right & cannot be
greater than the "appy" year.  The goal is to find which slotted year to
use based on the "appy".  My current thoughts would be to do a series of
if statements but it seems there must be an easier method.

  Since this will be part of a syntax shared among many users, python
cannot be used.  Any assistance would be greatly appreciated.

David,

=====================
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

[Matthew Pirritano]

Question: Is prev always the most recent value of the slotted year1 .. year10 that is still less than appy? This makes sense for your example except the case of ID 23. Shouldn't the value of prev for ID 23 be 1999?

Using that logic this syntax is tested and works.

vector year= year1 to year5.
loop #I = 1 to 5.
    if (year(#I) <= appy) prev_test = year(#I).
end loop if (year(#I) <= appy).
exe.


Year needs to be changed to the 10 that you have in your data. This will create a prev variable that I have called prev_test.

Let me know if I've misunderstood something. You might want to clarify your logic. It was a little unclear.

Thanks
Matt Pirritano

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of David Wright
Sent: Wednesday, November 24, 2010 4:01 AM
To: [hidden email]
Subject: determining most recent activity

I have a denormalized database in which the previous institutional
enrollment year is included (in slotted columns) for each applicant who
may appear in multiple records.  I need to determine from these slotted
previous entries which entry is the most recent in terms of the current
record (less than or equal in time).  Listed below is an example.

appy= application record year
prev= most recent (<=) of all slotted prior institution years (this is
the factor which I need to calculate)
year#= the year of prior institution attendance (these are always
slotted so that the most recent appears in year1, they slotted to 10
possible years)


ID appy prev year1 year2 year3 year4 year5...year10
12 1987 1983  2001  2000  1993  1983  1973
12 2003 2001  2001  2000  1993  1983  1973
43 2005 2005  2005
52 2007 2004  2008  2004  2001
52 2008 2008  2008  2004  2001
23 2001 1997  2004  2002  1999  1997  1983

So, "prev" is created from the slotted years to the right & cannot be
greater than the "appy" year.  The goal is to find which slotted year to
use based on the "appy".  My current thoughts would be to do a series of
if statements but it seems there must be an easier method.

  Since this will be part of a syntax shared among many users, python
cannot be used.  Any assistance would be greatly appreciated.

David,

=====================
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

[wsu_wright]

Matt,

This works great!  Yes, ID 23 was an error when I typed it in for the
email.  Thanks again and happy holidays.

David


On Wed, Nov 24, 2010 at 7:22 AM, Matthew Pirritano wrote:

> Question: Is prev always the most recent value of the slotted year1 ..
> year10 that is still less than appy? This makes sense for your example
> except the case of ID 23. Shouldn't the value of prev for ID 23 be
> 1999?
>
> Using that logic this syntax is tested and works.
>
> vector year= year1 to year5.
> loop #I = 1 to 5.
>     if (year(#I) <= appy) prev_test = year(#I).
> end loop if (year(#I) <= appy).
> exe.
>
>
> Year needs to be changed to the 10 that you have in your data. This
> will create a prev variable that I have called prev_test.
>
> Let me know if I've misunderstood something. You might want to clarify
> your logic. It was a little unclear.
>
> Thanks
> Matt Pirritano
>
> -----Original Message-----
> From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf
> Of David Wright
> Sent: Wednesday, November 24, 2010 4:01 AM
> To: [hidden email]
> Subject: determining most recent activity
>
> I have a denormalized database in which the previous institutional
> enrollment year is included (in slotted columns) for each applicant
> who
> may appear in multiple records.  I need to determine from these
> slotted
> previous entries which entry is the most recent in terms of the
> current
> record (less than or equal in time).  Listed below is an example.
>
> appy= application record year
> prev= most recent (<=) of all slotted prior institution years (this is
> the factor which I need to calculate)
> year#= the year of prior institution attendance (these are always
> slotted so that the most recent appears in year1, they slotted to 10
> possible years)
>
>
> ID appy prev year1 year2 year3 year4 year5...year10
> 12 1987 1983  2001  2000  1993  1983  1973
> 12 2003 2001  2001  2000  1993  1983  1973
> 43 2005 2005  2005
> 52 2007 2004  2008  2004  2001
> 52 2008 2008  2008  2004  2001
> 23 2001 1997  2004  2002  1999  1997  1983
>
> So, "prev" is created from the slotted years to the right & cannot be
> greater than the "appy" year.  The goal is to find which slotted year
> to
> use based on the "appy".  My current thoughts would be to do a series
> of
> if statements but it seems there must be an easier method.
>
>   Since this will be part of a syntax shared among many users, python
> cannot be used.  Any assistance would be greatly appreciated.
>
> David,
>
> =====================
> 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

=====================
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