Grouping Dates
Locked
 
|
Hi everyone. I've spent several days on this and I'm stuck. I have tried
computing an aggregate end date to use as a reference and if statements using lag variables. I'm aggravated. And I don't know macros. I am trying to determine which dates belong to a range of time which will determine their placement in a corresponding group. One rule is if a start date and the lag of the end date is 4 days or less they are in the same group. Another rule is if a start date is within the range of date range, it is in the same group. For example id 5987 Rank 1 should be in its own group ID 5987 rank 2-7 in group2 ID 5987 rank 8-9 in group 3 id 5987 rank 10-19 in group 4. This last is because the date range 2/19/2006-5/24/2009 (Rank 15) either includes all subsequent dates through Rank 19 or is connected backward consecutively to previous dates back to rank 10 For ID 2756 there are 2 groups: Rank 1-5 Rank 5-11 The data is as follows: ID Rank StartDate EndDate 5987 1 5/3/2004 5/9/2004 5987 2 10/12/2004 10/27/2004 5987 3 10/27/2004 12/1/2004 5987 4 12/1/2004 12/2/2004 5987 5 12/2/2004 1/9/2005 5987 6 1/7/2005 1/9/2005 5987 7 1/9/2005 3/1/2005 5987 8 6/19/2005 6/20/2005 5987 9 6/20/2005 7/2/2005 5987 10 7/27/2005 8/24/2005 5987 11 8/24/2005 12/20/2005 5987 12 12/19/2005 1/8/2006 5987 13 1/8/2006 2/19/2006 5987 14 2/16/2006 2/19/2006 5987 15 2/19/2006 5/24/2009 5987 16 3/30/2006 4/16/2006 5987 17 11/21/2006 11/27/2006 5987 18 12/19/2006 3/21/2007 5987 19 8/12/2008 8/20/2008 2756 1 11/16/2005 1/5/2006 2756 2 1/5/2006 1/8/2006 2756 3 1/8/2006 1/17/2006 2756 4 1/17/2006 5/14/2007 2756 5 4/27/2008 3/23/2009 2756 6 12/11/2008 12/14/2008 2756 7 12/11/2008 12/19/2008 2756 8 12/19/2008 3/23/2009 2756 9 3/23/2009 3/23/2009 2756 10 3/23/2009 7/19/2009 2756 11 7/19/2009 11/15/2009 Thanks Brandon ===================== 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 |
Administrator
|
Brandon,
Absolutely no need for a macro here. I believe this works to your specifications although your first ID has an odd year for the last record. HTH, David BTW: Always nice to post whatever syntax you have attempted and what ensues . Even if it is a complete flustercluck, it saves other's time in starting from scratch ;-) ---- DATA LIST LIST/ ID (F4) Rank (F2) StartDate (ADATE) EndDate (ADATE). BEGIN DATA 5987 1 5/3/2004 5/9/2004 5987 2 10/12/2004 10/27/2004 5987 3 10/27/2004 12/1/2004 5987 4 12/1/2004 12/2/2004 5987 5 12/2/2004 1/9/2005 5987 6 1/7/2005 1/9/2005 5987 7 1/9/2005 3/1/2005 5987 8 6/19/2005 6/20/2005 5987 9 6/20/2005 7/2/2005 5987 10 7/27/2005 8/24/2005 5987 11 8/24/2005 12/20/2005 5987 12 12/19/2005 1/8/2006 5987 13 1/8/2006 2/19/2006 5987 14 2/16/2006 2/19/2006 5987 15 2/19/2006 5/24/2009 5987 16 3/30/2006 4/16/2006 5987 17 11/21/2006 11/27/2006 5987 18 12/19/2006 3/21/2007 5987 19 8/12/2008 8/20/2008 2756 1 11/16/2005 1/5/2006 2756 2 1/5/2006 1/8/2006 2756 3 1/8/2006 1/17/2006 2756 4 1/17/2006 5/14/2007 2756 5 4/27/2008 3/23/2009 2756 6 12/11/2008 12/14/2008 2756 7 12/11/2008 12/19/2008 2756 8 12/19/2008 3/23/2009 2756 9 3/23/2009 3/23/2009 2756 10 3/23/2009 7/19/2009 2756 11 7/19/2009 11/15/2009 END DATA. IF ( ($CASENUM EQ 1) OR (ID NE LAG(ID)) ) GROUP=1. DO IF (ID EQ LAG(ID)) AND CTIME.DAYS(StartDate - LAG(ENDDATE)) GT 4 . + COMPUTE GROUP=LAG(GROUP) + 1. ELSE . + IF ID=LAG(ID) GROUP=LAG(GROUP). END IF. LIST. ID RANK STADATE ENDDATE GROUP 5987 1 05/03/2004 05/09/2004 1.00 5987 2 10/12/2004 10/27/2004 2.00 5987 3 10/27/2004 12/01/2004 2.00 5987 4 12/01/2004 12/02/2004 2.00 5987 5 12/02/2004 01/09/2005 2.00 5987 6 01/07/2005 01/09/2005 2.00 5987 7 01/09/2005 03/01/2005 2.00 5987 8 06/19/2005 06/20/2005 3.00 5987 9 06/20/2005 07/02/2005 3.00 5987 10 07/27/2005 08/24/2005 4.00 5987 11 08/24/2005 12/20/2005 4.00 5987 12 12/19/2005 01/08/2006 4.00 5987 13 01/08/2006 02/19/2006 4.00 5987 14 02/16/2006 02/19/2006 4.00 5987 15 02/19/2006 05/24/2009 4.00 5987 16 03/30/2006 04/16/2006 4.00 5987 17 11/21/2006 11/27/2006 5.00 5987 18 12/19/2006 03/21/2007 6.00 5987 19 08/12/2008 08/20/2008 7.00 2756 1 11/16/2005 01/05/2006 1.00 2756 2 01/05/2006 01/08/2006 1.00 2756 3 01/08/2006 01/17/2006 1.00 2756 4 01/17/2006 05/14/2007 1.00 2756 5 04/27/2008 03/23/2009 2.00 2756 6 12/11/2008 12/14/2008 2.00 2756 7 12/11/2008 12/19/2008 2.00 2756 8 12/19/2008 03/23/2009 2.00 2756 9 03/23/2009 03/23/2009 2.00 2756 10 03/23/2009 07/19/2009 2.00 2756 11 07/19/2009 11/15/2009 2.00 Number of cases read: 30 Number of cases listed: 30
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
|
|
In reply to this post by Brandon Lassiter
David,
This did exactly what I needed. And I will include syntax next time - I appreciate the heads up. I have a follow up question, if you don't mind. I want to understand the syntax better. The first line IF ( ($CASENUM EQ 1) OR (ID NE LAG(ID)) ) GROUP=1. What is the $CASENUM EQ 1 doing? And why GT 4? CTIME.DAYS(StartDate - LAG(ENDDATE)) GT 4 I think I get the rest. Thanks for the help, it's really appreciate. Brandon. On Fri, 23 Aug 2013 16:08:44 -0700, David Marso <[hidden email]> wrote: >Brandon, >Absolutely no need for a macro here. >I believe this works to your specifications although your first ID has an >odd year for the last record. >HTH, David >BTW: Always nice to post whatever syntax you have attempted and what ensues >. >Even if it is a complete flustercluck, it saves other's time in starting >from scratch ;-) >---- >DATA LIST LIST/ ID (F4) Rank (F2) StartDate (ADATE) EndDate (ADATE). >BEGIN DATA >5987 1 5/3/2004 5/9/2004 >5987 2 10/12/2004 10/27/2004 >5987 3 10/27/2004 12/1/2004 >5987 4 12/1/2004 12/2/2004 >5987 5 12/2/2004 1/9/2005 >5987 6 1/7/2005 1/9/2005 >5987 7 1/9/2005 3/1/2005 >5987 8 6/19/2005 6/20/2005 >5987 9 6/20/2005 7/2/2005 >5987 10 7/27/2005 8/24/2005 >5987 11 8/24/2005 12/20/2005 >5987 12 12/19/2005 1/8/2006 >5987 13 1/8/2006 2/19/2006 >5987 14 2/16/2006 2/19/2006 >5987 15 2/19/2006 5/24/2009 >5987 16 3/30/2006 4/16/2006 >5987 17 11/21/2006 11/27/2006 >5987 18 12/19/2006 3/21/2007 >5987 19 8/12/2008 8/20/2008 >2756 1 11/16/2005 1/5/2006 >2756 2 1/5/2006 1/8/2006 >2756 3 1/8/2006 1/17/2006 >2756 4 1/17/2006 5/14/2007 >2756 5 4/27/2008 3/23/2009 >2756 6 12/11/2008 12/14/2008 >2756 7 12/11/2008 12/19/2008 >2756 8 12/19/2008 3/23/2009 >2756 9 3/23/2009 3/23/2009 >2756 10 3/23/2009 7/19/2009 >2756 11 7/19/2009 11/15/2009 >END DATA. > >IF ( ($CASENUM EQ 1) OR (ID NE LAG(ID)) ) GROUP=1. >DO IF (ID EQ LAG(ID)) AND CTIME.DAYS(StartDate - LAG(ENDDATE)) GT 4 . >+ COMPUTE GROUP=LAG(GROUP) + 1. >ELSE . >+ IF ID=LAG(ID) GROUP=LAG(GROUP). >END IF. >LIST. > > > ID RANK STADATE ENDDATE GROUP > >5987 1 05/03/2004 05/09/2004 1.00 >5987 2 10/12/2004 10/27/2004 2.00 >5987 3 10/27/2004 12/01/2004 2.00 >5987 4 12/01/2004 12/02/2004 2.00 >5987 5 12/02/2004 01/09/2005 2.00 >5987 6 01/07/2005 01/09/2005 2.00 >5987 7 01/09/2005 03/01/2005 2.00 >5987 8 06/19/2005 06/20/2005 3.00 >5987 9 06/20/2005 07/02/2005 3.00 >5987 10 07/27/2005 08/24/2005 4.00 >5987 11 08/24/2005 12/20/2005 4.00 >5987 12 12/19/2005 01/08/2006 4.00 >5987 13 01/08/2006 02/19/2006 4.00 >5987 14 02/16/2006 02/19/2006 4.00 >5987 15 02/19/2006 05/24/2009 4.00 >5987 16 03/30/2006 04/16/2006 4.00 >5987 17 11/21/2006 11/27/2006 5.00 >5987 18 12/19/2006 03/21/2007 6.00 >5987 19 *08/12/2008 08/20/2008* 7.00 >2756 1 11/16/2005 01/05/2006 1.00 >2756 2 01/05/2006 01/08/2006 1.00 >2756 3 01/08/2006 01/17/2006 1.00 >2756 4 01/17/2006 05/14/2007 1.00 >2756 5 04/27/2008 03/23/2009 2.00 >2756 6 12/11/2008 12/14/2008 2.00 >2756 7 12/11/2008 12/19/2008 2.00 >2756 8 12/19/2008 03/23/2009 2.00 >2756 9 03/23/2009 03/23/2009 2.00 >2756 10 03/23/2009 07/19/2009 2.00 >2756 11 07/19/2009 11/15/2009 2.00 > > >Number of cases read: 30 Number of cases listed: 30 > > > >Brandon-2 wrote >> Hi everyone. I've spent several days on this and I'm stuck. I have tried >> computing an aggregate end date to use as a reference and if statements >> using lag variables. I'm aggravated. And I don't know macros. >> >> I am trying to determine which dates belong to a range of time which will >> determine their placement in a corresponding group. >> One rule is if a start date and the lag of the end date is 4 days or less >> they are in the same group. >> Another rule is if a start date is within the range of date range, it is >> in >> the same group. >> For example id 5987 Rank 1 should be in its own group >> ID 5987 rank 2-7 in group2 >> ID 5987 rank 8-9 in group 3 >> id 5987 rank 10-19 in group 4. This last is because the date range >> 2/19/2006-5/24/2009 (Rank 15) >> either includes all subsequent dates through Rank 19 or is connected >> backward consecutively >> to previous dates back to rank 10 >> For ID 2756 there are 2 groups: >> Rank 1-5 >> Rank 5-11 >> >> >> The data is as follows: >> >> ID Rank StartDate EndDate >> 5987 1 5/3/2004 5/9/2004 >> 5987 2 10/12/2004 10/27/2004 >> 5987 3 10/27/2004 12/1/2004 >> 5987 4 12/1/2004 12/2/2004 >> 5987 5 12/2/2004 1/9/2005 >> 5987 6 1/7/2005 1/9/2005 >> 5987 7 1/9/2005 3/1/2005 >> 5987 8 6/19/2005 6/20/2005 >> 5987 9 6/20/2005 7/2/2005 >> 5987 10 7/27/2005 8/24/2005 >> 5987 11 8/24/2005 12/20/2005 >> 5987 12 12/19/2005 1/8/2006 >> 5987 13 1/8/2006 2/19/2006 >> 5987 14 2/16/2006 2/19/2006 >> 5987 15 2/19/2006 5/24/2009 >> 5987 16 3/30/2006 4/16/2006 >> 5987 17 11/21/2006 11/27/2006 >> 5987 18 12/19/2006 3/21/2007 >> 5987 19 8/12/2008 8/20/2008 >> 2756 1 11/16/2005 1/5/2006 >> 2756 2 1/5/2006 1/8/2006 >> 2756 3 1/8/2006 1/17/2006 >> 2756 4 1/17/2006 5/14/2007 >> 2756 5 4/27/2008 3/23/2009 >> 2756 6 12/11/2008 12/14/2008 >> 2756 7 12/11/2008 12/19/2008 >> 2756 8 12/19/2008 3/23/2009 >> 2756 9 3/23/2009 3/23/2009 >> 2756 10 3/23/2009 7/19/2009 >> 2756 11 7/19/2009 11/15/2009 >> >> >> Thanks >> >> Brandon >> >> ===================== >> To manage your subscription to SPSSX-L, send a message to > >> LISTSERV@.UGA > >> (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 > > > > > >----- >Please reply to the list and not to my personal email. >Those desiring my consulting or training services please feel free to email >--- >"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." >Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" >-- >View this message in context: http://spssx- discussion.1045642.n5.nabble.com/Grouping-Dates-tp5721709p5721713.html >Sent from the SPSSX Discussion mailing list archive at Nabble.com. > >===================== >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 |
|
|
In reply to this post by Brandon Lassiter
Thanks David, this worked perfectly. (If this message goes comes through
twice, I apologize, I did reply yesterday but it didn't seem to make it through). Also, thanks for the heads up about providing syntax - I'll do that next time. I have a follow up question if you don't mind, I want to be able to understand the syntax better. What does "IF ( ($CASENUM EQ 1)" do? And why GT 4 in this line? DO IF (ID EQ LAG(ID)) AND CTIME.DAYS(StartDate - LAG(ENDDATE)) GT 4 Thanks again for your help. Brandon. On Fri, 23 Aug 2013 16:08:44 -0700, David Marso <[hidden email]> wrote: >Brandon, >Absolutely no need for a macro here. >I believe this works to your specifications although your first ID has an >odd year for the last record. >HTH, David >BTW: Always nice to post whatever syntax you have attempted and what ensues >. >Even if it is a complete flustercluck, it saves other's time in starting >from scratch ;-) >---- >DATA LIST LIST/ ID (F4) Rank (F2) StartDate (ADATE) EndDate (ADATE). >BEGIN DATA >5987 1 5/3/2004 5/9/2004 >5987 2 10/12/2004 10/27/2004 >5987 3 10/27/2004 12/1/2004 >5987 4 12/1/2004 12/2/2004 >5987 5 12/2/2004 1/9/2005 >5987 6 1/7/2005 1/9/2005 >5987 7 1/9/2005 3/1/2005 >5987 8 6/19/2005 6/20/2005 >5987 9 6/20/2005 7/2/2005 >5987 10 7/27/2005 8/24/2005 >5987 11 8/24/2005 12/20/2005 >5987 12 12/19/2005 1/8/2006 >5987 13 1/8/2006 2/19/2006 >5987 14 2/16/2006 2/19/2006 >5987 15 2/19/2006 5/24/2009 >5987 16 3/30/2006 4/16/2006 >5987 17 11/21/2006 11/27/2006 >5987 18 12/19/2006 3/21/2007 >5987 19 8/12/2008 8/20/2008 >2756 1 11/16/2005 1/5/2006 >2756 2 1/5/2006 1/8/2006 >2756 3 1/8/2006 1/17/2006 >2756 4 1/17/2006 5/14/2007 >2756 5 4/27/2008 3/23/2009 >2756 6 12/11/2008 12/14/2008 >2756 7 12/11/2008 12/19/2008 >2756 8 12/19/2008 3/23/2009 >2756 9 3/23/2009 3/23/2009 >2756 10 3/23/2009 7/19/2009 >2756 11 7/19/2009 11/15/2009 >END DATA. > >IF ( ($CASENUM EQ 1) OR (ID NE LAG(ID)) ) GROUP=1. >DO IF (ID EQ LAG(ID)) AND CTIME.DAYS(StartDate - LAG(ENDDATE)) GT 4 . >+ COMPUTE GROUP=LAG(GROUP) + 1. >ELSE . >+ IF ID=LAG(ID) GROUP=LAG(GROUP). >END IF. >LIST. > > > ID RANK STADATE ENDDATE GROUP > >5987 1 05/03/2004 05/09/2004 1.00 >5987 2 10/12/2004 10/27/2004 2.00 >5987 3 10/27/2004 12/01/2004 2.00 >5987 4 12/01/2004 12/02/2004 2.00 >5987 5 12/02/2004 01/09/2005 2.00 >5987 6 01/07/2005 01/09/2005 2.00 >5987 7 01/09/2005 03/01/2005 2.00 >5987 8 06/19/2005 06/20/2005 3.00 >5987 9 06/20/2005 07/02/2005 3.00 >5987 10 07/27/2005 08/24/2005 4.00 >5987 11 08/24/2005 12/20/2005 4.00 >5987 12 12/19/2005 01/08/2006 4.00 >5987 13 01/08/2006 02/19/2006 4.00 >5987 14 02/16/2006 02/19/2006 4.00 >5987 15 02/19/2006 05/24/2009 4.00 >5987 16 03/30/2006 04/16/2006 4.00 >5987 17 11/21/2006 11/27/2006 5.00 >5987 18 12/19/2006 03/21/2007 6.00 >5987 19 *08/12/2008 08/20/2008* 7.00 >2756 1 11/16/2005 01/05/2006 1.00 >2756 2 01/05/2006 01/08/2006 1.00 >2756 3 01/08/2006 01/17/2006 1.00 >2756 4 01/17/2006 05/14/2007 1.00 >2756 5 04/27/2008 03/23/2009 2.00 >2756 6 12/11/2008 12/14/2008 2.00 >2756 7 12/11/2008 12/19/2008 2.00 >2756 8 12/19/2008 03/23/2009 2.00 >2756 9 03/23/2009 03/23/2009 2.00 >2756 10 03/23/2009 07/19/2009 2.00 >2756 11 07/19/2009 11/15/2009 2.00 > > >Number of cases read: 30 Number of cases listed: 30 > > > >Brandon-2 wrote >> Hi everyone. I've spent several days on this and I'm stuck. I have tried >> computing an aggregate end date to use as a reference and if statements >> using lag variables. I'm aggravated. And I don't know macros. >> >> I am trying to determine which dates belong to a range of time which will >> determine their placement in a corresponding group. >> One rule is if a start date and the lag of the end date is 4 days or less >> they are in the same group. >> Another rule is if a start date is within the range of date range, it is >> in >> the same group. >> For example id 5987 Rank 1 should be in its own group >> ID 5987 rank 2-7 in group2 >> ID 5987 rank 8-9 in group 3 >> id 5987 rank 10-19 in group 4. This last is because the date range >> 2/19/2006-5/24/2009 (Rank 15) >> either includes all subsequent dates through Rank 19 or is connected >> backward consecutively >> to previous dates back to rank 10 >> For ID 2756 there are 2 groups: >> Rank 1-5 >> Rank 5-11 >> >> >> The data is as follows: >> >> ID Rank StartDate EndDate >> 5987 1 5/3/2004 5/9/2004 >> 5987 2 10/12/2004 10/27/2004 >> 5987 3 10/27/2004 12/1/2004 >> 5987 4 12/1/2004 12/2/2004 >> 5987 5 12/2/2004 1/9/2005 >> 5987 6 1/7/2005 1/9/2005 >> 5987 7 1/9/2005 3/1/2005 >> 5987 8 6/19/2005 6/20/2005 >> 5987 9 6/20/2005 7/2/2005 >> 5987 10 7/27/2005 8/24/2005 >> 5987 11 8/24/2005 12/20/2005 >> 5987 12 12/19/2005 1/8/2006 >> 5987 13 1/8/2006 2/19/2006 >> 5987 14 2/16/2006 2/19/2006 >> 5987 15 2/19/2006 5/24/2009 >> 5987 16 3/30/2006 4/16/2006 >> 5987 17 11/21/2006 11/27/2006 >> 5987 18 12/19/2006 3/21/2007 >> 5987 19 8/12/2008 8/20/2008 >> 2756 1 11/16/2005 1/5/2006 >> 2756 2 1/5/2006 1/8/2006 >> 2756 3 1/8/2006 1/17/2006 >> 2756 4 1/17/2006 5/14/2007 >> 2756 5 4/27/2008 3/23/2009 >> 2756 6 12/11/2008 12/14/2008 >> 2756 7 12/11/2008 12/19/2008 >> 2756 8 12/19/2008 3/23/2009 >> 2756 9 3/23/2009 3/23/2009 >> 2756 10 3/23/2009 7/19/2009 >> 2756 11 7/19/2009 11/15/2009 >> >> >> Thanks >> >> Brandon >> >> ===================== >> To manage your subscription to SPSSX-L, send a message to > >> LISTSERV@.UGA > >> (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 > > > > > >----- >Please reply to the list and not to my personal email. >Those desiring my consulting or training services please feel free to email >--- >"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." >Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" >-- >View this message in context: http://spssx- discussion.1045642.n5.nabble.com/Grouping-Dates-tp5721709p5721713.html >Sent from the SPSSX Discussion mailing list archive at Nabble.com. > >===================== >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 |
|
|
In reply to this post by Brandon Lassiter
Thanks David, this worked perfectly. (If this message goes comes through
twice, I apologize, I did reply on Friday but it didn't seem to make it through). Also, thanks for the heads up about providing syntax - I'll do that next time. I have a follow up question if you don't mind, I want to be able to understand the syntax better. What does "IF ( ($CASENUM EQ 1)" do? And why GT 4 in this line? DO IF (ID EQ LAG(ID)) AND CTIME.DAYS(StartDate - LAG(ENDDATE)) GT 4 Thanks again for your help. Brandon On Fri, 23 Aug 2013 16:08:44 -0700, David Marso <[hidden email]> wrote: >Brandon, >Absolutely no need for a macro here. >I believe this works to your specifications although your first ID has an >odd year for the last record. >HTH, David >BTW: Always nice to post whatever syntax you have attempted and what ensues >. >Even if it is a complete flustercluck, it saves other's time in starting >from scratch ;-) >---- >DATA LIST LIST/ ID (F4) Rank (F2) StartDate (ADATE) EndDate (ADATE). >BEGIN DATA >5987 1 5/3/2004 5/9/2004 >5987 2 10/12/2004 10/27/2004 >5987 3 10/27/2004 12/1/2004 >5987 4 12/1/2004 12/2/2004 >5987 5 12/2/2004 1/9/2005 >5987 6 1/7/2005 1/9/2005 >5987 7 1/9/2005 3/1/2005 >5987 8 6/19/2005 6/20/2005 >5987 9 6/20/2005 7/2/2005 >5987 10 7/27/2005 8/24/2005 >5987 11 8/24/2005 12/20/2005 >5987 12 12/19/2005 1/8/2006 >5987 13 1/8/2006 2/19/2006 >5987 14 2/16/2006 2/19/2006 >5987 15 2/19/2006 5/24/2009 >5987 16 3/30/2006 4/16/2006 >5987 17 11/21/2006 11/27/2006 >5987 18 12/19/2006 3/21/2007 >5987 19 8/12/2008 8/20/2008 >2756 1 11/16/2005 1/5/2006 >2756 2 1/5/2006 1/8/2006 >2756 3 1/8/2006 1/17/2006 >2756 4 1/17/2006 5/14/2007 >2756 5 4/27/2008 3/23/2009 >2756 6 12/11/2008 12/14/2008 >2756 7 12/11/2008 12/19/2008 >2756 8 12/19/2008 3/23/2009 >2756 9 3/23/2009 3/23/2009 >2756 10 3/23/2009 7/19/2009 >2756 11 7/19/2009 11/15/2009 >END DATA. > >IF ( ($CASENUM EQ 1) OR (ID NE LAG(ID)) ) GROUP=1. >DO IF (ID EQ LAG(ID)) AND CTIME.DAYS(StartDate - LAG(ENDDATE)) GT 4 . >+ COMPUTE GROUP=LAG(GROUP) + 1. >ELSE . >+ IF ID=LAG(ID) GROUP=LAG(GROUP). >END IF. >LIST. > > > ID RANK STADATE ENDDATE GROUP > >5987 1 05/03/2004 05/09/2004 1.00 >5987 2 10/12/2004 10/27/2004 2.00 >5987 3 10/27/2004 12/01/2004 2.00 >5987 4 12/01/2004 12/02/2004 2.00 >5987 5 12/02/2004 01/09/2005 2.00 >5987 6 01/07/2005 01/09/2005 2.00 >5987 7 01/09/2005 03/01/2005 2.00 >5987 8 06/19/2005 06/20/2005 3.00 >5987 9 06/20/2005 07/02/2005 3.00 >5987 10 07/27/2005 08/24/2005 4.00 >5987 11 08/24/2005 12/20/2005 4.00 >5987 12 12/19/2005 01/08/2006 4.00 >5987 13 01/08/2006 02/19/2006 4.00 >5987 14 02/16/2006 02/19/2006 4.00 >5987 15 02/19/2006 05/24/2009 4.00 >5987 16 03/30/2006 04/16/2006 4.00 >5987 17 11/21/2006 11/27/2006 5.00 >5987 18 12/19/2006 03/21/2007 6.00 >5987 19 *08/12/2008 08/20/2008* 7.00 >2756 1 11/16/2005 01/05/2006 1.00 >2756 2 01/05/2006 01/08/2006 1.00 >2756 3 01/08/2006 01/17/2006 1.00 >2756 4 01/17/2006 05/14/2007 1.00 >2756 5 04/27/2008 03/23/2009 2.00 >2756 6 12/11/2008 12/14/2008 2.00 >2756 7 12/11/2008 12/19/2008 2.00 >2756 8 12/19/2008 03/23/2009 2.00 >2756 9 03/23/2009 03/23/2009 2.00 >2756 10 03/23/2009 07/19/2009 2.00 >2756 11 07/19/2009 11/15/2009 2.00 > > >Number of cases read: 30 Number of cases listed: 30 > > > >Brandon-2 wrote >> Hi everyone. I've spent several days on this and I'm stuck. I have tried >> computing an aggregate end date to use as a reference and if statements >> using lag variables. I'm aggravated. And I don't know macros. >> >> I am trying to determine which dates belong to a range of time which will >> determine their placement in a corresponding group. >> One rule is if a start date and the lag of the end date is 4 days or less >> they are in the same group. >> Another rule is if a start date is within the range of date range, it is >> in >> the same group. >> For example id 5987 Rank 1 should be in its own group >> ID 5987 rank 2-7 in group2 >> ID 5987 rank 8-9 in group 3 >> id 5987 rank 10-19 in group 4. This last is because the date range >> 2/19/2006-5/24/2009 (Rank 15) >> either includes all subsequent dates through Rank 19 or is connected >> backward consecutively >> to previous dates back to rank 10 >> For ID 2756 there are 2 groups: >> Rank 1-5 >> Rank 5-11 >> >> >> The data is as follows: >> >> ID Rank StartDate EndDate >> 5987 1 5/3/2004 5/9/2004 >> 5987 2 10/12/2004 10/27/2004 >> 5987 3 10/27/2004 12/1/2004 >> 5987 4 12/1/2004 12/2/2004 >> 5987 5 12/2/2004 1/9/2005 >> 5987 6 1/7/2005 1/9/2005 >> 5987 7 1/9/2005 3/1/2005 >> 5987 8 6/19/2005 6/20/2005 >> 5987 9 6/20/2005 7/2/2005 >> 5987 10 7/27/2005 8/24/2005 >> 5987 11 8/24/2005 12/20/2005 >> 5987 12 12/19/2005 1/8/2006 >> 5987 13 1/8/2006 2/19/2006 >> 5987 14 2/16/2006 2/19/2006 >> 5987 15 2/19/2006 5/24/2009 >> 5987 16 3/30/2006 4/16/2006 >> 5987 17 11/21/2006 11/27/2006 >> 5987 18 12/19/2006 3/21/2007 >> 5987 19 8/12/2008 8/20/2008 >> 2756 1 11/16/2005 1/5/2006 >> 2756 2 1/5/2006 1/8/2006 >> 2756 3 1/8/2006 1/17/2006 >> 2756 4 1/17/2006 5/14/2007 >> 2756 5 4/27/2008 3/23/2009 >> 2756 6 12/11/2008 12/14/2008 >> 2756 7 12/11/2008 12/19/2008 >> 2756 8 12/19/2008 3/23/2009 >> 2756 9 3/23/2009 3/23/2009 >> 2756 10 3/23/2009 7/19/2009 >> 2756 11 7/19/2009 11/15/2009 >> >> >> Thanks >> >> Brandon >> >> ===================== >> To manage your subscription to SPSSX-L, send a message to > >> LISTSERV@.UGA > >> (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 > > > > > >----- >Please reply to the list and not to my personal email. >Those desiring my consulting or training services please feel free to email >--- >"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." >Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" >-- >View this message in context: http://spssx- discussion.1045642.n5.nabble.com/Grouping-Dates-tp5721709p5721713.html >Sent from the SPSSX Discussion mailing list archive at Nabble.com. > >===================== >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 Thanks David, this worked perfectly. (If this message goes comes through twice, I apologize, I did reply on Friday but it didn't seem to make it through). Also, thanks for the heads up about providing syntax - I'll do that next time. I have a follow up question if you don't mind, I want to be able to understand the syntax better. What does "IF ( ($CASENUM EQ 1)" do? And why GT 4 in this line? DO IF (ID EQ LAG(ID)) AND CTIME.DAYS(StartDate - LAG(ENDDATE)) GT 4 Thanks again for your help. Brandon ===================== 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 |
Administrator
|
See below.
If the case number (i.e., row number in the data file) is equal to (EQ) 1... LAG pulls the value of the specified variable from the previous row. GT means greater than. You said, "One rule is if a start date and the lag of the end date is 4 days or less they are in the same group." HTH.
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
|
|
In reply to this post by Brandon Lassiter
Sorry for the duplicate emails. I'm not sure what happened. Anyway, This
makes sense. I really appreciate the help. Brandon. On Fri, 23 Aug 2013 20:10:37 -0400, Brandon <[hidden email]> wrote: >David, > >This did exactly what I needed. And I will include syntax next time - I >appreciate the heads up. I have a follow up question, if you don't mind. I >want to understand the syntax better. The first line >IF ( ($CASENUM EQ 1) OR (ID NE LAG(ID)) ) GROUP=1. > >What is the $CASENUM EQ 1 doing? > >And why GT 4? >CTIME.DAYS(StartDate - LAG(ENDDATE)) GT 4 > >I think I get the rest. > >Thanks for the help, it's really appreciate. > >Brandon. > >On Fri, 23 Aug 2013 16:08:44 -0700, David Marso <[hidden email]> >wrote: > >>Brandon, >>Absolutely no need for a macro here. >>I believe this works to your specifications although your first ID has an >>odd year for the last record. >>HTH, David >>BTW: Always nice to post whatever syntax you have attempted and what >ensues >>. >>Even if it is a complete flustercluck, it saves other's time in starting >>from scratch ;-) >>---- >>DATA LIST LIST/ ID (F4) Rank (F2) StartDate (ADATE) EndDate (ADATE). >>BEGIN DATA >>5987 1 5/3/2004 5/9/2004 >>5987 2 10/12/2004 10/27/2004 >>5987 3 10/27/2004 12/1/2004 >>5987 4 12/1/2004 12/2/2004 >>5987 5 12/2/2004 1/9/2005 >>5987 6 1/7/2005 1/9/2005 >>5987 7 1/9/2005 3/1/2005 >>5987 8 6/19/2005 6/20/2005 >>5987 9 6/20/2005 7/2/2005 >>5987 10 7/27/2005 8/24/2005 >>5987 11 8/24/2005 12/20/2005 >>5987 12 12/19/2005 1/8/2006 >>5987 13 1/8/2006 2/19/2006 >>5987 14 2/16/2006 2/19/2006 >>5987 15 2/19/2006 5/24/2009 >>5987 16 3/30/2006 4/16/2006 >>5987 17 11/21/2006 11/27/2006 >>5987 18 12/19/2006 3/21/2007 >>5987 19 8/12/2008 8/20/2008 >>2756 1 11/16/2005 1/5/2006 >>2756 2 1/5/2006 1/8/2006 >>2756 3 1/8/2006 1/17/2006 >>2756 4 1/17/2006 5/14/2007 >>2756 5 4/27/2008 3/23/2009 >>2756 6 12/11/2008 12/14/2008 >>2756 7 12/11/2008 12/19/2008 >>2756 8 12/19/2008 3/23/2009 >>2756 9 3/23/2009 3/23/2009 >>2756 10 3/23/2009 7/19/2009 >>2756 11 7/19/2009 11/15/2009 >>END DATA. >> >>IF ( ($CASENUM EQ 1) OR (ID NE LAG(ID)) ) GROUP=1. >>DO IF (ID EQ LAG(ID)) AND CTIME.DAYS(StartDate - LAG(ENDDATE)) GT 4 . >>+ COMPUTE GROUP=LAG(GROUP) + 1. >>ELSE . >>+ IF ID=LAG(ID) GROUP=LAG(GROUP). >>END IF. >>LIST. >> >> >> ID RANK STADATE ENDDATE GROUP >> >>5987 1 05/03/2004 05/09/2004 1.00 >>5987 2 10/12/2004 10/27/2004 2.00 >>5987 3 10/27/2004 12/01/2004 2.00 >>5987 4 12/01/2004 12/02/2004 2.00 >>5987 5 12/02/2004 01/09/2005 2.00 >>5987 6 01/07/2005 01/09/2005 2.00 >>5987 7 01/09/2005 03/01/2005 2.00 >>5987 8 06/19/2005 06/20/2005 3.00 >>5987 9 06/20/2005 07/02/2005 3.00 >>5987 10 07/27/2005 08/24/2005 4.00 >>5987 11 08/24/2005 12/20/2005 4.00 >>5987 12 12/19/2005 01/08/2006 4.00 >>5987 13 01/08/2006 02/19/2006 4.00 >>5987 14 02/16/2006 02/19/2006 4.00 >>5987 15 02/19/2006 05/24/2009 4.00 >>5987 16 03/30/2006 04/16/2006 4.00 >>5987 17 11/21/2006 11/27/2006 5.00 >>5987 18 12/19/2006 03/21/2007 6.00 >>5987 19 *08/12/2008 08/20/2008* 7.00 >>2756 1 11/16/2005 01/05/2006 1.00 >>2756 2 01/05/2006 01/08/2006 1.00 >>2756 3 01/08/2006 01/17/2006 1.00 >>2756 4 01/17/2006 05/14/2007 1.00 >>2756 5 04/27/2008 03/23/2009 2.00 >>2756 6 12/11/2008 12/14/2008 2.00 >>2756 7 12/11/2008 12/19/2008 2.00 >>2756 8 12/19/2008 03/23/2009 2.00 >>2756 9 03/23/2009 03/23/2009 2.00 >>2756 10 03/23/2009 07/19/2009 2.00 >>2756 11 07/19/2009 11/15/2009 2.00 >> >> >>Number of cases read: 30 Number of cases listed: 30 >> >> >> >>Brandon-2 wrote >>> Hi everyone. I've spent several days on this and I'm stuck. I have tried >>> computing an aggregate end date to use as a reference and if statements >>> using lag variables. I'm aggravated. And I don't know macros. >>> >>> I am trying to determine which dates belong to a range of time which >>> determine their placement in a corresponding group. >>> One rule is if a start date and the lag of the end date is 4 days or less >>> they are in the same group. >>> Another rule is if a start date is within the range of date range, it is >>> in >>> the same group. >>> For example id 5987 Rank 1 should be in its own group >>> ID 5987 rank 2-7 in group2 >>> ID 5987 rank 8-9 in group 3 >>> id 5987 rank 10-19 in group 4. This last is because the date range >>> 2/19/2006-5/24/2009 (Rank 15) >>> either includes all subsequent dates through Rank 19 or is connected >>> backward consecutively >>> to previous dates back to rank 10 >>> For ID 2756 there are 2 groups: >>> Rank 1-5 >>> Rank 5-11 >>> >>> >>> The data is as follows: >>> >>> ID Rank StartDate EndDate >>> 5987 1 5/3/2004 5/9/2004 >>> 5987 2 10/12/2004 10/27/2004 >>> 5987 3 10/27/2004 12/1/2004 >>> 5987 4 12/1/2004 12/2/2004 >>> 5987 5 12/2/2004 1/9/2005 >>> 5987 6 1/7/2005 1/9/2005 >>> 5987 7 1/9/2005 3/1/2005 >>> 5987 8 6/19/2005 6/20/2005 >>> 5987 9 6/20/2005 7/2/2005 >>> 5987 10 7/27/2005 8/24/2005 >>> 5987 11 8/24/2005 12/20/2005 >>> 5987 12 12/19/2005 1/8/2006 >>> 5987 13 1/8/2006 2/19/2006 >>> 5987 14 2/16/2006 2/19/2006 >>> 5987 15 2/19/2006 5/24/2009 >>> 5987 16 3/30/2006 4/16/2006 >>> 5987 17 11/21/2006 11/27/2006 >>> 5987 18 12/19/2006 3/21/2007 >>> 5987 19 8/12/2008 8/20/2008 >>> 2756 1 11/16/2005 1/5/2006 >>> 2756 2 1/5/2006 1/8/2006 >>> 2756 3 1/8/2006 1/17/2006 >>> 2756 4 1/17/2006 5/14/2007 >>> 2756 5 4/27/2008 3/23/2009 >>> 2756 6 12/11/2008 12/14/2008 >>> 2756 7 12/11/2008 12/19/2008 >>> 2756 8 12/19/2008 3/23/2009 >>> 2756 9 3/23/2009 3/23/2009 >>> 2756 10 3/23/2009 7/19/2009 >>> 2756 11 7/19/2009 11/15/2009 >>> >>> >>> Thanks >>> >>> Brandon >>> >>> ===================== >>> To manage your subscription to SPSSX-L, send a message to >> >>> LISTSERV@.UGA >> >>> (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 >> >> >> >> >> >>----- >>Please reply to the list and not to my personal email. >>Those desiring my consulting or training services please feel free to >me. >>--- >>"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos >ne forte conculcent eas pedibus suis." >>Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in >abyssum?" >>-- >>View this message in context: http://spssx- >discussion.1045642.n5.nabble.com/Grouping-Dates-tp5721709p5721713.html >>Sent from the SPSSX Discussion mailing list archive at Nabble.com. >> >>===================== >>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 |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |