seeking help in calculating number of days
Locked
seeking help in calculating number of days
 
|
Dear List Members
I would be grateful if anyone could help me calculate the number of days between the 2 dates - "Eventdate" and "Intimidation date". I have tried using the date diff function and it works but it only calculates where there are values in the two columns. What I need to calculate is the number of days between each eventdate and intimidationdate for each personcni. Personcni eventdate intimidationdate xyz 13-APR-2004 13-APR-2004 abc 06-JUN-2006 06-JUN-2006 hij 22-JUL-2003 . hij 22-JUL-2003 . hij 23-NOV-2003 . hij 27-NOV-2004 . hij 24-OCT-2005 24-OCT-2005 hij 17-MAR-2006 . hij 17-MAR-2006 . lop 01-OCT-2005 . lop 14-DEC-2005 . lop 08-APR-2006 08-APR-2006 efg 06-SEP-2005 . efg 06-SEP-2005 06-SEP-2005 aks 05-FEB-2005 . aks 05-FEB-2005 05-FEB-2005 aks 19-APR-2005 . stu 27-APR-2004 27-APR-2004 stu 27-FEB-2006 . stu 02-NOV-2003 02-NOV-2003 Thanks regards Thara Vardhan Senior Statistician Planning & Results Organisation Review and Support NSW Police Tel: (02) 8835-8526 Thara Vardhan Senior Statistician Planning & Results Organisation Review and Support NSW Police Tel: (02) 8835-8526 This message and any attachment is confidential and may be privileged or otherwise protected from disclosure. If you have received it by mistake, please let us know by reply and then delete it from your system; you should not copy the message or disclose its contents to anyone. |
Re: seeking help in calculating number of days
|
|
The trick here is to create a variable that does have a value in each row.
It appears there are multiple events but only one intimidationdate for each personci. If that is always the case, you can create the variable you need (call it intimidationdate2) with the command Aggregate /break=personci/ intimidationdate2 = first(intimidationdate). Jonathan Fry SPSS Inc. -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Thara Vardhan Sent: Thursday, September 06, 2007 12:55 AM To: [hidden email] Subject: seeking help in calculating number of days Dear List Members I would be grateful if anyone could help me calculate the number of days between the 2 dates - "Eventdate" and "Intimidation date". I have tried using the date diff function and it works but it only calculates where there are values in the two columns. What I need to calculate is the number of days between each eventdate and intimidationdate for each personcni. Personcni eventdate intimidationdate xyz 13-APR-2004 13-APR-2004 abc 06-JUN-2006 06-JUN-2006 hij 22-JUL-2003 . hij 22-JUL-2003 . hij 23-NOV-2003 . hij 27-NOV-2004 . hij 24-OCT-2005 24-OCT-2005 hij 17-MAR-2006 . hij 17-MAR-2006 . lop 01-OCT-2005 . lop 14-DEC-2005 . lop 08-APR-2006 08-APR-2006 efg 06-SEP-2005 . efg 06-SEP-2005 06-SEP-2005 aks 05-FEB-2005 . aks 05-FEB-2005 05-FEB-2005 aks 19-APR-2005 . stu 27-APR-2004 27-APR-2004 stu 27-FEB-2006 . stu 02-NOV-2003 02-NOV-2003 Thanks regards Thara Vardhan Senior Statistician Planning & Results Organisation Review and Support NSW Police Tel: (02) 8835-8526 Thara Vardhan Senior Statistician Planning & Results Organisation Review and Support NSW Police Tel: (02) 8835-8526 This message and any attachment is confidential and may be privileged or otherwise protected from disclosure. If you have received it by mistake, please let us know by reply and then delete it from your system; you should not copy the message or disclose its contents to anyone. |
Re: seeking help in calculating number of days
|
|
In reply to this post by thara vardhan-2
Oops. I see the assumption I made in my post of a few minutes ago is wrong. Some personci's have more than one intimidatindate.
Assuming now that you want "." to mean "ditto" within personci, you could use if (sysmis(intimidationdate) and personci = lag(personci)) intimidationdate = lag(intimdationdate). Jonathan Fry SPSS Inc. -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Thara Vardhan Sent: Thursday, September 06, 2007 12:55 AM To: [hidden email] Subject: seeking help in calculating number of days Dear List Members I would be grateful if anyone could help me calculate the number of days between the 2 dates - "Eventdate" and "Intimidation date". I have tried using the date diff function and it works but it only calculates where there are values in the two columns. What I need to calculate is the number of days between each eventdate and intimidationdate for each personcni. Personcni eventdate intimidationdate xyz 13-APR-2004 13-APR-2004 abc 06-JUN-2006 06-JUN-2006 hij 22-JUL-2003 . hij 22-JUL-2003 . hij 23-NOV-2003 . hij 27-NOV-2004 . hij 24-OCT-2005 24-OCT-2005 hij 17-MAR-2006 . hij 17-MAR-2006 . lop 01-OCT-2005 . lop 14-DEC-2005 . lop 08-APR-2006 08-APR-2006 efg 06-SEP-2005 . efg 06-SEP-2005 06-SEP-2005 aks 05-FEB-2005 . aks 05-FEB-2005 05-FEB-2005 aks 19-APR-2005 . stu 27-APR-2004 27-APR-2004 stu 27-FEB-2006 . stu 02-NOV-2003 02-NOV-2003 Thanks regards Thara Vardhan Senior Statistician Planning & Results Organisation Review and Support NSW Police Tel: (02) 8835-8526 Thara Vardhan Senior Statistician Planning & Results Organisation Review and Support NSW Police Tel: (02) 8835-8526 This message and any attachment is confidential and may be privileged or otherwise protected from disclosure. If you have received it by mistake, please let us know by reply and then delete it from your system; you should not copy the message or disclose its contents to anyone. |
Re: seeking help in calculating number of days
|
In reply to this post by thara vardhan-2
At 01:55 AM 9/6/2007, Thara Vardhan wrote:
>I would be grateful if anyone could help me calculate the number of >days between the 2 dates - "Eventdate" and "Intimidation date". |-----------------------------|---------------------------| |Output Created |06-SEP-2007 08:14:19 | |-----------------------------|---------------------------| Personcni eventdate intimidationdate xyz 13-APR-2004 13-APR-2004 abc 06-JUN-2006 06-JUN-2006 hij 22-JUL-2003 . hij 22-JUL-2003 . hij 23-NOV-2003 . hij 27-NOV-2004 . hij 24-OCT-2005 24-OCT-2005 hij 17-MAR-2006 . hij 17-MAR-2006 . lop 01-OCT-2005 . lop 14-DEC-2005 . lop 08-APR-2006 08-APR-2006 efg 06-SEP-2005 . efg 06-SEP-2005 06-SEP-2005 aks 05-FEB-2005 . aks 05-FEB-2005 05-FEB-2005 aks 19-APR-2005 . stu 27-APR-2004 27-APR-2004 stu 27-FEB-2006 . stu 02-NOV-2003 02-NOV-2003 Number of cases read: 20 Number of cases listed: 20 >I have tried using the date diff function and it works but it only >calculates where there are values in the two columns. Right. That's true of any transformation that doesn't explicitly deal with multiple cases. DateDiff has no notion that there's any value except in the immediate record it's looking at. >What I need to calculate is the number of days between each eventdate >and intimidationdate for each personcni. It looks like a person ('Personcni') has one 'intimidationdate' and any number of 'eventdate' values. You need to get the 'intimidationdate' value into *all* the person's records; then, DateDiff will work. Like this - SPSS 15 draft output (WRR:not saved separately): AGGREGATE OUTFILE=* MODE=ADDVARIABLES OVERWRITE=YES /BREAK=Personcni /intimidationdate = MAX(intimidationdate). LIST. List |-----------------------------|---------------------------| |Output Created |06-SEP-2007 08:18:01 | |-----------------------------|---------------------------| Personcni eventdate intimidationdate xyz 13-APR-2004 13-APR-2004 abc 06-JUN-2006 06-JUN-2006 hij 22-JUL-2003 24-OCT-2005 hij 22-JUL-2003 24-OCT-2005 hij 23-NOV-2003 24-OCT-2005 hij 27-NOV-2004 24-OCT-2005 hij 24-OCT-2005 24-OCT-2005 hij 17-MAR-2006 24-OCT-2005 hij 17-MAR-2006 24-OCT-2005 lop 01-OCT-2005 08-APR-2006 lop 14-DEC-2005 08-APR-2006 lop 08-APR-2006 08-APR-2006 efg 06-SEP-2005 06-SEP-2005 efg 06-SEP-2005 06-SEP-2005 aks 05-FEB-2005 05-FEB-2005 aks 05-FEB-2005 05-FEB-2005 aks 19-APR-2005 05-FEB-2005 stu 27-APR-2004 27-APR-2004 stu 27-FEB-2006 27-APR-2004 stu 02-NOV-2003 27-APR-2004 Number of cases read: 20 Number of cases listed: 20 =================== APPENDIX: Test data =================== (Works, but gives many warning messages) DATA LIST LIST SKIP=2 /Personcni eventdate intimidationdate (A3, DATE11, DATE11). BEGIN DATA Personcni eventdate intimidationdate ----|---10----|---20----|---30----|---40----|---50----|---60----| xyz 13-APR-2004 13-APR-2004 abc 06-JUN-2006 06-JUN-2006 hij 22-JUL-2003 . hij 22-JUL-2003 . hij 23-NOV-2003 . hij 27-NOV-2004 . hij 24-OCT-2005 24-OCT-2005 hij 17-MAR-2006 . hij 17-MAR-2006 . lop 01-OCT-2005 . lop 14-DEC-2005 . lop 08-APR-2006 08-APR-2006 efg 06-SEP-2005 . efg 06-SEP-2005 06-SEP-2005 aks 05-FEB-2005 . aks 05-FEB-2005 05-FEB-2005 aks 19-APR-2005 . stu 27-APR-2004 27-APR-2004 stu 27-FEB-2006 . stu 02-NOV-2003 02-NOV-2003 END DATA. LIST. |
Re: seeking help in calculating number of days
|
|
In reply to this post by thara vardhan-2
An alternative (if you have an older version of SPSS that doesn't do
MODE=ADDVARIABLES would be to Sort cases by personcni intimidationdate (D). If (personcni=lag(personcni)) intimidationdate=lag(intimidationdate). By sorting in descending order, the blank intimidationdate values will sort after the valid intimidationdate value. The lag function will then copy the valid intimidationdate value down to the blank rows for the same personcni. Melissa -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Richard Ristow Sent: Thursday, September 06, 2007 7:23 AM To: [hidden email] Subject: Re: [SPSSX-L] seeking help in calculating number of days At 01:55 AM 9/6/2007, Thara Vardhan wrote: >I would be grateful if anyone could help me calculate the number of >days between the 2 dates - "Eventdate" and "Intimidation date". |-----------------------------|---------------------------| |Output Created |06-SEP-2007 08:14:19 | |-----------------------------|---------------------------| Personcni eventdate intimidationdate xyz 13-APR-2004 13-APR-2004 abc 06-JUN-2006 06-JUN-2006 hij 22-JUL-2003 . hij 22-JUL-2003 . hij 23-NOV-2003 . hij 27-NOV-2004 . hij 24-OCT-2005 24-OCT-2005 hij 17-MAR-2006 . hij 17-MAR-2006 . lop 01-OCT-2005 . lop 14-DEC-2005 . lop 08-APR-2006 08-APR-2006 efg 06-SEP-2005 . efg 06-SEP-2005 06-SEP-2005 aks 05-FEB-2005 . aks 05-FEB-2005 05-FEB-2005 aks 19-APR-2005 . stu 27-APR-2004 27-APR-2004 stu 27-FEB-2006 . stu 02-NOV-2003 02-NOV-2003 Number of cases read: 20 Number of cases listed: 20 >I have tried using the date diff function and it works but it only >calculates where there are values in the two columns. Right. That's true of any transformation that doesn't explicitly deal with multiple cases. DateDiff has no notion that there's any value except in the immediate record it's looking at. >What I need to calculate is the number of days between each eventdate >and intimidationdate for each personcni. It looks like a person ('Personcni') has one 'intimidationdate' and any number of 'eventdate' values. You need to get the 'intimidationdate' value into *all* the person's records; then, DateDiff will work. Like this - SPSS 15 draft output (WRR:not saved separately): AGGREGATE OUTFILE=* MODE=ADDVARIABLES OVERWRITE=YES /BREAK=Personcni /intimidationdate = MAX(intimidationdate). LIST. List |-----------------------------|---------------------------| |Output Created |06-SEP-2007 08:18:01 | |-----------------------------|---------------------------| Personcni eventdate intimidationdate xyz 13-APR-2004 13-APR-2004 abc 06-JUN-2006 06-JUN-2006 hij 22-JUL-2003 24-OCT-2005 hij 22-JUL-2003 24-OCT-2005 hij 23-NOV-2003 24-OCT-2005 hij 27-NOV-2004 24-OCT-2005 hij 24-OCT-2005 24-OCT-2005 hij 17-MAR-2006 24-OCT-2005 hij 17-MAR-2006 24-OCT-2005 lop 01-OCT-2005 08-APR-2006 lop 14-DEC-2005 08-APR-2006 lop 08-APR-2006 08-APR-2006 efg 06-SEP-2005 06-SEP-2005 efg 06-SEP-2005 06-SEP-2005 aks 05-FEB-2005 05-FEB-2005 aks 05-FEB-2005 05-FEB-2005 aks 19-APR-2005 05-FEB-2005 stu 27-APR-2004 27-APR-2004 stu 27-FEB-2006 27-APR-2004 stu 02-NOV-2003 27-APR-2004 Number of cases read: 20 Number of cases listed: 20 =================== APPENDIX: Test data =================== (Works, but gives many warning messages) DATA LIST LIST SKIP=2 /Personcni eventdate intimidationdate (A3, DATE11, DATE11). BEGIN DATA Personcni eventdate intimidationdate ----|---10----|---20----|---30----|---40----|---50----|---60----| xyz 13-APR-2004 13-APR-2004 abc 06-JUN-2006 06-JUN-2006 hij 22-JUL-2003 . hij 22-JUL-2003 . hij 23-NOV-2003 . hij 27-NOV-2004 . hij 24-OCT-2005 24-OCT-2005 hij 17-MAR-2006 . hij 17-MAR-2006 . lop 01-OCT-2005 . lop 14-DEC-2005 . lop 08-APR-2006 08-APR-2006 efg 06-SEP-2005 . efg 06-SEP-2005 06-SEP-2005 aks 05-FEB-2005 . aks 05-FEB-2005 05-FEB-2005 aks 19-APR-2005 . stu 27-APR-2004 27-APR-2004 stu 27-FEB-2006 . stu 02-NOV-2003 02-NOV-2003 END DATA. LIST. PRIVILEGED AND CONFIDENTIAL INFORMATION This transmittal and any attachments may contain PRIVILEGED AND CONFIDENTIAL information and is intended only for the use of the addressee. If you are not the designated recipient, or an employee or agent authorized to deliver such transmittals to the designated recipient, you are hereby notified that any dissemination, copying or publication of this transmittal is strictly prohibited. If you have received this transmittal in error, please notify us immediately by replying to the sender and delete this copy from your system. You may also call us at (309) 827-6026 for assistance. |
AW: seeking help in calculating number of days
|
|
In reply to this post by Richard Ristow
Richard
it looks like you loos some information on the very last row where the date in the variable intimidationdate (02-NOV-2003) gets overwritten with 27-APR-2004. I myself tried to send a solution here but stumbled over the Personcni ('stu') with more than one entry for intimidationdate. I am not 100% sure what Thara wants, but I think there is not enough information to solve the problem. Christian -----Ursprungliche Nachricht----- Von: SPSSX(r) Discussion [mailto:[hidden email]]Im Auftrag von Richard Ristow Gesendet: Donnerstag, 6. September 2007 14:23 An: [hidden email] Betreff: Re: seeking help in calculating number of days At 01:55 AM 9/6/2007, Thara Vardhan wrote: >I would be grateful if anyone could help me calculate the number of >days between the 2 dates - "Eventdate" and "Intimidation date". |-----------------------------|---------------------------| |Output Created |06-SEP-2007 08:14:19 | |-----------------------------|---------------------------| Personcni eventdate intimidationdate xyz 13-APR-2004 13-APR-2004 abc 06-JUN-2006 06-JUN-2006 hij 22-JUL-2003 . hij 22-JUL-2003 . hij 23-NOV-2003 . hij 27-NOV-2004 . hij 24-OCT-2005 24-OCT-2005 hij 17-MAR-2006 . hij 17-MAR-2006 . lop 01-OCT-2005 . lop 14-DEC-2005 . lop 08-APR-2006 08-APR-2006 efg 06-SEP-2005 . efg 06-SEP-2005 06-SEP-2005 aks 05-FEB-2005 . aks 05-FEB-2005 05-FEB-2005 aks 19-APR-2005 . stu 27-APR-2004 27-APR-2004 stu 27-FEB-2006 . stu 02-NOV-2003 02-NOV-2003 Number of cases read: 20 Number of cases listed: 20 >I have tried using the date diff function and it works but it only >calculates where there are values in the two columns. Right. That's true of any transformation that doesn't explicitly deal with multiple cases. DateDiff has no notion that there's any value except in the immediate record it's looking at. >What I need to calculate is the number of days between each eventdate >and intimidationdate for each personcni. It looks like a person ('Personcni') has one 'intimidationdate' and any number of 'eventdate' values. You need to get the 'intimidationdate' value into *all* the person's records; then, DateDiff will work. Like this - SPSS 15 draft output (WRR:not saved separately): AGGREGATE OUTFILE=* MODE=ADDVARIABLES OVERWRITE=YES /BREAK=Personcni /intimidationdate = MAX(intimidationdate). LIST. List |-----------------------------|---------------------------| |Output Created |06-SEP-2007 08:18:01 | |-----------------------------|---------------------------| Personcni eventdate intimidationdate xyz 13-APR-2004 13-APR-2004 abc 06-JUN-2006 06-JUN-2006 hij 22-JUL-2003 24-OCT-2005 hij 22-JUL-2003 24-OCT-2005 hij 23-NOV-2003 24-OCT-2005 hij 27-NOV-2004 24-OCT-2005 hij 24-OCT-2005 24-OCT-2005 hij 17-MAR-2006 24-OCT-2005 hij 17-MAR-2006 24-OCT-2005 lop 01-OCT-2005 08-APR-2006 lop 14-DEC-2005 08-APR-2006 lop 08-APR-2006 08-APR-2006 efg 06-SEP-2005 06-SEP-2005 efg 06-SEP-2005 06-SEP-2005 aks 05-FEB-2005 05-FEB-2005 aks 05-FEB-2005 05-FEB-2005 aks 19-APR-2005 05-FEB-2005 stu 27-APR-2004 27-APR-2004 stu 27-FEB-2006 27-APR-2004 stu 02-NOV-2003 27-APR-2004 Number of cases read: 20 Number of cases listed: 20 =================== APPENDIX: Test data =================== (Works, but gives many warning messages) DATA LIST LIST SKIP=2 /Personcni eventdate intimidationdate (A3, DATE11, DATE11). BEGIN DATA Personcni eventdate intimidationdate ----|---10----|---20----|---30----|---40----|---50----|---60----| xyz 13-APR-2004 13-APR-2004 abc 06-JUN-2006 06-JUN-2006 hij 22-JUL-2003 . hij 22-JUL-2003 . hij 23-NOV-2003 . hij 27-NOV-2004 . hij 24-OCT-2005 24-OCT-2005 hij 17-MAR-2006 . hij 17-MAR-2006 . lop 01-OCT-2005 . lop 14-DEC-2005 . lop 08-APR-2006 08-APR-2006 efg 06-SEP-2005 . efg 06-SEP-2005 06-SEP-2005 aks 05-FEB-2005 . aks 05-FEB-2005 05-FEB-2005 aks 19-APR-2005 . stu 27-APR-2004 27-APR-2004 stu 27-FEB-2006 . stu 02-NOV-2003 02-NOV-2003 END DATA. LIST. |
Re: seeking help in calculating number of days
|
|
In reply to this post by Richard Ristow
At 01:55 AM 9/6/2007, Thara Vardhan had asked:
>>I would be grateful if anyone could help me calculate the number of >>days between the 2 dates - "Eventdate" and "Intimidation date". At 08:22 AM 9/6/2007, I wrote: >It looks like a person ('Personcni') has one 'intimidationdate' and >any >number of 'eventdate' values. Jonathan Fry and Christian Schmidhauser ("la volta statistics") have pointed out that a person can have more than one 'intimidationdate', so the logic I posted is inappropriate. The question then is, what 'intimidationdate' should be compared with each 'Eventdate'? I suppose the latest preceding 'intimidationdate' makes sense; is that it? -Onward, Richard > You need to get the 'intimidationdate' >value into *all* the person's records; then, DateDiff will work. Like >this - SPSS 15 draft output (WRR:not saved separately): > >AGGREGATE OUTFILE=* MODE=ADDVARIABLES OVERWRITE=YES > /BREAK=Personcni > /intimidationdate = MAX(intimidationdate). >LIST. > >List >|-----------------------------|---------------------------| >|Output Created |06-SEP-2007 08:18:01 | >|-----------------------------|---------------------------| >Personcni eventdate intimidationdate > >xyz 13-APR-2004 13-APR-2004 >abc 06-JUN-2006 06-JUN-2006 >hij 22-JUL-2003 24-OCT-2005 >hij 22-JUL-2003 24-OCT-2005 >hij 23-NOV-2003 24-OCT-2005 >hij 27-NOV-2004 24-OCT-2005 >hij 24-OCT-2005 24-OCT-2005 >hij 17-MAR-2006 24-OCT-2005 >hij 17-MAR-2006 24-OCT-2005 >lop 01-OCT-2005 08-APR-2006 >lop 14-DEC-2005 08-APR-2006 >lop 08-APR-2006 08-APR-2006 >efg 06-SEP-2005 06-SEP-2005 >efg 06-SEP-2005 06-SEP-2005 >aks 05-FEB-2005 05-FEB-2005 >aks 05-FEB-2005 05-FEB-2005 >aks 19-APR-2005 05-FEB-2005 >stu 27-APR-2004 27-APR-2004 >stu 27-FEB-2006 27-APR-2004 >stu 02-NOV-2003 27-APR-2004 > >Number of cases read: 20 Number of cases listed: 20 > > >=================== >APPENDIX: Test data >=================== >(Works, but gives many warning messages) > >DATA LIST LIST SKIP=2 > /Personcni eventdate intimidationdate > (A3, DATE11, DATE11). >BEGIN DATA > Personcni eventdate intimidationdate >----|---10----|---20----|---30----|---40----|---50----|---60----| > xyz 13-APR-2004 13-APR-2004 > abc 06-JUN-2006 06-JUN-2006 > hij 22-JUL-2003 . > hij 22-JUL-2003 . > hij 23-NOV-2003 . > hij 27-NOV-2004 . > hij 24-OCT-2005 24-OCT-2005 > hij 17-MAR-2006 . > hij 17-MAR-2006 . > lop 01-OCT-2005 . > lop 14-DEC-2005 . > lop 08-APR-2006 08-APR-2006 > efg 06-SEP-2005 . > efg 06-SEP-2005 06-SEP-2005 > aks 05-FEB-2005 . > aks 05-FEB-2005 05-FEB-2005 > aks 19-APR-2005 . > stu 27-APR-2004 27-APR-2004 > stu 27-FEB-2006 . > stu 02-NOV-2003 02-NOV-2003 >END DATA. >LIST. > > > >-- >No virus found in this incoming message. >Checked by AVG Free Edition. Version: 7.5.485 / Virus Database: >269.13.6/991 - Release Date: 9/5/2007 2:55 PM |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |