Sorting dates chronologically

[Hashmi, Syed S]

Hi all,

I've got this dataset with information of infections on a bunch of
patients.  Each patient was asked about the occurrence of an illness, and
if they said they had one, further questions were asked about start date,
stop date, etc.  Up to 6 possible illness episodes could be recorded.

Therefore, my dataset might look like the following (I've only put info on
4 illness episodes and the start dates here since that should be enough to
get the point across):

id ill startdt1 startdt startdt3 startdt4
1 1 02/05/04 03/12/04 03/31/04 .
2 0 . . . .
3 1 03/01/04 02/11/04 . .
4 1 04/25/04 12/15/03 06/01/04 .
5 0 . . . .
6 1 06/17/04 01/12/04 03/22/04 02/23/04
7 1 04/18/04 . . .

FYI: the date fields are in a date format.
FYI: "Ill" is a dichotomous variable denoting presence(1) or absence(0) of
any illness.

As you can see, the dates may not necessarily be in chronological order.
What I wanted to do was create a new variable that lists out the order the
episodes occured for each patients.

For example, a new variable "order" that results in:
id order
1 123
2 .
3 21
4 213
5 .
6 2431
7 1

So how would I go about doing this via code. I have 8000 cases so this
would be too tedious (and error-prone) to do by hand.  Also, I'm not too
familiar with python so that probably won't help.  However, if python is
the only way to do this, I guess no time like the present to learn.

Thanks a heap in advance.

- Shahrukh

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

[Peck, Jon]

You can probably write SPSS transformation code to do this, but here is a little Python program that does it in a few lines of code.  It handles any number of dates but assumes that the dataset is open and the date variables are any whose names begin with "startdt".  It creates a numeric variable named dateorder with the date order, so it would be good up to 9.  If you prefer a string, it is easy to change it.

To use these, you need the Python plugin and the spssaux, spssdata, and namedTuple modules, all downloadable from SPSS Developer Central (www.spss.com/devcentral).  Put them in c:/python24/lib/site-packages.  It requires SPSS 15 or later and, of course, Python 2.4.  You should not need to modify the program at all.

Since the listserve tends to mangle line wrapping, I can email you the program code separately if desired.

I won't explain this code in detail here, but note that all the real work takes place in the 6 lines between
for case in curs:
and
curs.CClose()


Regards,
Jon Peck

begin program.
import spss, spssaux, spssdata
from spssdata import vdef

datevars = spssaux.VariableDict(pattern="startdt.*").range()

curs = spssdata.Spssdata(indexes = "id ill " + " ".join(datevars), accessType="w")
curs.append(vdef("dateorder", vlabel="order of dates"))
curs.commitdict()

for case in curs:
    if case.ill:
        dates = sorted([(v, i+1) for i, v in enumerate(filter(None, case[2:]))])
        dateorder = float("".join([str(i) for v, i in dates]))
    else:
        dateorder = None
    curs.casevalues([dateorder])
curs.CClose()
end program.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Shahrukh Hashmi
Sent: Thursday, November 08, 2007 11:12 AM
To: [hidden email]
Subject: [SPSSX-L] Sorting dates chronologically

Hi all,

I've got this dataset with information of infections on a bunch of
patients.  Each patient was asked about the occurrence of an illness, and
if they said they had one, further questions were asked about start date,
stop date, etc.  Up to 6 possible illness episodes could be recorded.

Therefore, my dataset might look like the following (I've only put info on
4 illness episodes and the start dates here since that should be enough to
get the point across):

id ill startdt1 startdt startdt3 startdt4
1 1 02/05/04 03/12/04 03/31/04 .
2 0 . . . .
3 1 03/01/04 02/11/04 . .
4 1 04/25/04 12/15/03 06/01/04 .
5 0 . . . .
6 1 06/17/04 01/12/04 03/22/04 02/23/04
7 1 04/18/04 . . .

FYI: the date fields are in a date format.
FYI: "Ill" is a dichotomous variable denoting presence(1) or absence(0) of
any illness.

As you can see, the dates may not necessarily be in chronological order.
What I wanted to do was create a new variable that lists out the order the
episodes occured for each patients.

For example, a new variable "order" that results in:
id order
1 123
2 .
3 21
4 213
5 .
6 2431
7 1

So how would I go about doing this via code. I have 8000 cases so this
would be too tedious (and error-prone) to do by hand.  Also, I'm not too
familiar with python so that probably won't help.  However, if python is
the only way to do this, I guess no time like the present to learn.

Thanks a heap in advance.

- Shahrukh

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

[Richard Ristow]

At 01:12 PM 11/8/2007, Shahrukh Hashmi wrote:

>I've got this dataset with information of infections on a bunch of
>patients.  Each patient was asked about the occurrence of an
>illness, and if they said they had one, further questions were asked
>about start date, stop date, etc.  Up to 6 possible illness episodes
>could be recorded.
>
>Therefore, my dataset might look like the following (I've only put
>info on 4 illness episodes and the start dates here since that
>should be enough to get the point across):
>
>id ill startdt1 startdt startdt3 startdt4
>1 1 02/05/04 03/12/04 03/31/04 .
>2 0 . . . .
>3 1 03/01/04 02/11/04 . .
>4 1 04/25/04 12/15/03 06/01/04 .
>5 0 . . . .
>6 1 06/17/04 01/12/04 03/22/04 02/23/04
>7 1 04/18/04 . . .
>
>I want to create a new variable that lists out the order the
>episodes occurred for each patients:
>id order
>1 123
>2 .
>3 21
>4 213
>5 .
>6 2431
>7 1

(Grin) We have our instincts. Jon says 'Python', I say 'unroll' -
i.e., go to long data organization, with each episode in a separate
record. For many purposes that would be preferable altogether, and
you'd have no need for your 'order' variable. However, it can also be
used to create that variable.

  On your data, it produces the following:
|-----------------------------|---------------------------|
|Output Created               |08-NOV-2007 21:30:55       |
|-----------------------------|---------------------------|
[TestData]

  id ill    Order   startdt1   startdt2   startdt3   startdt4

   1   1      123 02/05/2004 03/12/2004 03/31/2004          .
   2   0        .          .          .          .          .
   3   1       21 03/01/2004 02/11/2004          .          .
   4   1      213 04/25/2004 12/15/2003 06/01/2004          .
   5   0        .          .          .          .          .
   6   1     2431 06/17/2004 01/12/2004 03/22/2004 02/23/2004
   7   1        1 04/18/2004          .          .          .

Number of cases read:  7    Number of cases listed:  7

But consider whether this would serve you better:
|-----------------------------|---------------------------|
|Output Created               |08-NOV-2007 21:30:53       |
|-----------------------------|---------------------------|
[Long]

  id ill NEpisode Episode    startdt

   1   1      3        1  02/05/2004
   1   1      3        2  03/12/2004
   1   1      3        3  03/31/2004
   3   1      2        2  02/11/2004
   3   1      2        1  03/01/2004
   4   1      3        2  12/15/2003
   4   1      3        1  04/25/2004
   4   1      3        3  06/01/2004
   6   1      4        2  01/12/2004
   6   1      4        4  02/23/2004
   6   1      4        3  03/22/2004
   6   1      4        1  06/17/2004
   7   1      1        1  04/18/2004

Number of cases read:  13    Number of cases listed:  13

...........................
Here's the process for both, starting with your posted test data. It
should also work for any number of episodes up to 9. This is SPSS 14
draft output; the code is listed after the output.
|-----------------------------|---------------------------|
|Output Created               |08-NOV-2007 21:30:51       |
|-----------------------------|---------------------------|
[TestData]

  id ill   startdt1   startdt2   startdt3   startdt4

   1   1 02/05/2004 03/12/2004 03/31/2004          .
   2   0          .          .          .          .
   3   1 03/01/2004 02/11/2004          .          .
   4   1 04/25/2004 12/15/2003 06/01/2004          .
   5   0          .          .          .          .
   6   1 06/17/2004 01/12/2004 03/22/2004 02/23/2004
   7   1 04/18/2004          .          .          .

Number of cases read:  7    Number of cases listed:  7


DATASET COPY     Long.
DATASET ACTIVATE Long     WINDOW=FRONT.

VARSTOCASES
  /MAKE startdt FROM startdt1 startdt2 startdt3 startdt4
  /INDEX = Episode "Episode number"(4)
  /KEEP  = id ill
  /NULL  = DROP
  /COUNT = NEpisode "Number of episodes experienced" .


Variables to Cases
|-----------------------------|---------------------------|
|Output Created               |08-NOV-2007 21:30:52       |
|-----------------------------|---------------------------|
[Long]

Generated Variables
|--------|---------------|
|Name    |Label          |
|--------|---------------|
|NEpisode|Number of      |
|        |episodes       |
|        |experienced    |
|--------|---------------|
|Episode |Episode number |
|--------|---------------|
|startdt |<none>         |
|--------|---------------|

Processing Statistics
|-------------|-|
|Variables In |6|
|Variables Out|5|
|-------------|-|


SORT CASES BY id startdt.
*  .....  The 'long' organization may be your preferred form:  ..... .
LIST.

List
|-----------------------------|---------------------------|
|Output Created               |08-NOV-2007 21:30:53       |
|-----------------------------|---------------------------|
[Long]

  id ill NEpisode Episode    startdt

   1   1      3        1  02/05/2004
   1   1      3        2  03/12/2004
   1   1      3        3  03/31/2004
   3   1      2        2  02/11/2004
   3   1      2        1  03/01/2004
   4   1      3        2  12/15/2003
   4   1      3        1  04/25/2004
   4   1      3        3  06/01/2004
   6   1      4        2  01/12/2004
   6   1      4        4  02/23/2004
   6   1      4        3  03/22/2004
   6   1      4        1  06/17/2004
   7   1      1        1  04/18/2004

Number of cases read:  13    Number of cases listed:  13


*  .....  But you can create your "Order" variable:            ..... .

NEW FILE.
ADD FILES
    /FILE  = Long
    /BY id
    /FIRST = NewGuy.

NUMERIC Order (F8).
DO IF      NewGuy.
.  COMPUTE Order = Episode.
ELSE.
.  COMPUTE Order = 10*LAG(Order) + Episode.
END IF.

DATASET NAME     PreOrder WINDOW=FRONT.

.  /**/  LIST  /*-*/.

List
|-----------------------------|---------------------------|
|Output Created               |08-NOV-2007 21:30:53       |
|-----------------------------|---------------------------|
[PreOrder]

  id ill NEpisode Episode    startdt NewGuy    Order

   1   1      3        1  02/05/2004    1          1
   1   1      3        2  03/12/2004    0         12
   1   1      3        3  03/31/2004    0        123
   3   1      2        2  02/11/2004    1          2
   3   1      2        1  03/01/2004    0         21
   4   1      3        2  12/15/2003    1          2
   4   1      3        1  04/25/2004    0         21
   4   1      3        3  06/01/2004    0        213
   6   1      4        2  01/12/2004    1          2
   6   1      4        4  02/23/2004    0         24
   6   1      4        3  03/22/2004    0        243
   6   1      4        1  06/17/2004    0       2431
   7   1      1        1  04/18/2004    1          1

Number of cases read:  13    Number of cases listed:  13


AGGREGATE OUTFILE=*
   /BREAK = id
   /Order = LAST(Order).

DATASET NAME     Order    WINDOW=FRONT.


LIST.

List
|-----------------------------|---------------------------|
|Output Created               |08-NOV-2007 21:30:54       |
|-----------------------------|---------------------------|
[Order]

  id    Order

   1      123
   3       21
   4      213
   6     2431
   7        1

Number of cases read:  5    Number of cases listed:  5


*  .....  And attach "Order" to the original file:             ..... .

DATASET ACTIVATE TestData WINDOW=FRONT.
MATCH FILES
    /FILE = *
    /FILE = Order
    /BY id
    /KEEP = id ill Order all.

LIST.

List
|-----------------------------|---------------------------|
|Output Created               |08-NOV-2007 21:30:55       |
|-----------------------------|---------------------------|
[TestData]

  id ill    Order   startdt1   startdt2   startdt3   startdt4

   1   1      123 02/05/2004 03/12/2004 03/31/2004          .
   2   0        .          .          .          .          .
   3   1       21 03/01/2004 02/11/2004          .          .
   4   1      213 04/25/2004 12/15/2003 06/01/2004          .
   5   0        .          .          .          .          .
   6   1     2431 06/17/2004 01/12/2004 03/22/2004 02/23/2004
   7   1        1 04/18/2004          .          .          .

Number of cases read:  7    Number of cases listed:  7

============================
APPENDIX: Test data and code
============================
*  C:\Documents and Settings\Richard\My Documents                    .
*    \Technical\spssx-l\Z-2007d                                      .
*    \2007-11-08 Hashmi - Sorting dates chronologically.SPS          .

*  In response to posting                                            .
*  Date:    Thu, 8 Nov 2007 13:12:27 -0500                           .
*  From:    Shahrukh Hashmi <[hidden email]>            .
*  Subject: Sorting dates chronologically                            .
*  To:      [hidden email]                                 .


*  ................................................................. .
*  .................   Test data               ..................... .
*  Taken from the posting.                                           .
*  The code works, with copious warning messages for missing dates.  .

DATA LIST LIST/
     id ill startdt1 startdt2 startdt3 startdt4
    (N3, F2, 4ADATE8).
BEGIN DATA
     1 1 02/05/04 03/12/04 03/31/04 .
     2 0
     3 1 03/01/04 02/11/04
     4 1 04/25/04 12/15/03 06/01/04
     5 0
     6 1 06/17/04 01/12/04 03/22/04 02/23/04
     7 1 04/18/04
END DATA.
FORMATS startdt1 TO startdt4 (ADATE10).
DATASET NAME     TestData WINDOW=FRONT.


*  .................   Post after this point   ..................... .
*  ................................................................. .

LIST.

*  "I want to create a new variable that lists out the order the     .
*  episodes occurred for each patient:                               .
*  id order                                                          .
*  1  123                                                            .
*  2  .                                                              .
*  3  21                                                             .
*  4  213                                                            .
*  5  .                                                              .
*  6  2431                                                           .
*  7  1                               "                              .

DATASET COPY     Long.
DATASET ACTIVATE Long     WINDOW=FRONT.

VARSTOCASES
  /MAKE startdt FROM startdt1 startdt2 startdt3 startdt4
  /INDEX = Episode "Episode number"(4)
  /KEEP  = id ill
  /NULL  = DROP
  /COUNT = NEpisode "Number of episodes experienced" .

SORT CASES BY id startdt.

*  .....  The 'long' organization may be your preferred form:  ..... .
LIST.

*  .....  But you can create your "Order" variable:            ..... .


NEW FILE.
ADD FILES
    /FILE  = Long
    /BY id
    /FIRST = NewGuy.

NUMERIC Order (F8).
DO IF      NewGuy.
.  COMPUTE Order = Episode.
ELSE.
.  COMPUTE Order = 10*LAG(Order) + Episode.
END IF.

DATASET NAME     PreOrder WINDOW=FRONT.

.  /**/  LIST  /*-*/.

AGGREGATE OUTFILE=*
   /BREAK = id
   /Order = LAST(Order).

DATASET NAME     Order    WINDOW=FRONT.
LIST.

*  .....  And attach "Order" to the original file:             ..... .

DATASET ACTIVATE TestData WINDOW=FRONT.
MATCH FILES
    /FILE = *
    /FILE = Order
    /BY id
    /KEEP = id ill Order all.

LIST.

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

[Hashmi, Syed S]

Richard,

Thanks for the detailed reply.  I was initially thinking about some long
format 'unrolled' dataset as well.  I tried going the CASETOVARS route
but that didn't get me far.  I've got Jon's Python code as well and I
decided that I might as well learn some as well, so will be going over
his code this weekend.  I'll try out yours as well just to get a feel of
the code.

Thanks again to both you and Jon for your different yet educational
replies.  I guess there's always more than one way to skin a cat!

- Shahrukh

> -----Original Message-----
> From: Richard Ristow [mailto:[hidden email]]
> Sent: Thursday, November 08, 2007 8:45 PM
> To: Hashmi, Syed S; [hidden email]
> Cc: Peck, Jon
> Subject: Re: Sorting dates chronologically
>
> At 01:12 PM 11/8/2007, Shahrukh Hashmi wrote:
>
> >I've got this dataset with information of infections on a bunch of
> >patients.  Each patient was asked about the occurrence of an
> >illness, and if they said they had one, further questions were asked
> >about start date, stop date, etc.  Up to 6 possible illness episodes
> >could be recorded.
> >
> >Therefore, my dataset might look like the following (I've only put
> >info on 4 illness episodes and the start dates here since that
> >should be enough to get the point across):
> >
> >id ill startdt1 startdt startdt3 startdt4
> >1 1 02/05/04 03/12/04 03/31/04 .
> >2 0 . . . .
> >3 1 03/01/04 02/11/04 . .
> >4 1 04/25/04 12/15/03 06/01/04 .
> >5 0 . . . .
> >6 1 06/17/04 01/12/04 03/22/04 02/23/04
> >7 1 04/18/04 . . .
> >
> >I want to create a new variable that lists out the order the
> >episodes occurred for each patients:
> >id order
> >1 123
> >2 .
> >3 21
> >4 213
> >5 .
> >6 2431
> >7 1
>
> (Grin) We have our instincts. Jon says 'Python', I say 'unroll' -
> i.e., go to long data organization, with each episode in a separate
> record. For many purposes that would be preferable altogether, and
> you'd have no need for your 'order' variable. However, it can also be
> used to create that variable.
>
>   On your data, it produces the following:
> |-----------------------------|---------------------------|
> |Output Created               |08-NOV-2007 21:30:55       |
> |-----------------------------|---------------------------|
> [TestData]
>
>   id ill    Order   startdt1   startdt2   startdt3   startdt4
>
>    1   1      123 02/05/2004 03/12/2004 03/31/2004          .
>    2   0        .          .          .          .          .
>    3   1       21 03/01/2004 02/11/2004          .          .
>    4   1      213 04/25/2004 12/15/2003 06/01/2004          .
>    5   0        .          .          .          .          .
>    6   1     2431 06/17/2004 01/12/2004 03/22/2004 02/23/2004
>    7   1        1 04/18/2004          .          .          .
>
> Number of cases read:  7    Number of cases listed:  7
>
> But consider whether this would serve you better:
> |-----------------------------|---------------------------|
> |Output Created               |08-NOV-2007 21:30:53       |
> |-----------------------------|---------------------------|
> [Long]
>
>   id ill NEpisode Episode    startdt
>
>    1   1      3        1  02/05/2004
>    1   1      3        2  03/12/2004
>    1   1      3        3  03/31/2004
>    3   1      2        2  02/11/2004
>    3   1      2        1  03/01/2004
>    4   1      3        2  12/15/2003
>    4   1      3        1  04/25/2004
>    4   1      3        3  06/01/2004
>    6   1      4        2  01/12/2004
>    6   1      4        4  02/23/2004
>    6   1      4        3  03/22/2004
>    6   1      4        1  06/17/2004
>    7   1      1        1  04/18/2004
>
> Number of cases read:  13    Number of cases listed:  13
>
> ...........................
> Here's the process for both, starting with your posted test data. It
> should also work for any number of episodes up to 9. This is SPSS 14
> draft output; the code is listed after the output.
> |-----------------------------|---------------------------|
> |Output Created               |08-NOV-2007 21:30:51       |
> |-----------------------------|---------------------------|
> [TestData]
>
>   id ill   startdt1   startdt2   startdt3   startdt4
>
>    1   1 02/05/2004 03/12/2004 03/31/2004          .
>    2   0          .          .          .          .
>    3   1 03/01/2004 02/11/2004          .          .
>    4   1 04/25/2004 12/15/2003 06/01/2004          .
>    5   0          .          .          .          .
>    6   1 06/17/2004 01/12/2004 03/22/2004 02/23/2004
>    7   1 04/18/2004          .          .          .
>
> Number of cases read:  7    Number of cases listed:  7
>
>
> DATASET COPY     Long.
> DATASET ACTIVATE Long     WINDOW=FRONT.
>
> VARSTOCASES
>   /MAKE startdt FROM startdt1 startdt2 startdt3 startdt4
>   /INDEX = Episode "Episode number"(4)
>   /KEEP  = id ill
>   /NULL  = DROP
>   /COUNT = NEpisode "Number of episodes experienced" .
>
>
> Variables to Cases
> |-----------------------------|---------------------------|
> |Output Created               |08-NOV-2007 21:30:52       |
> |-----------------------------|---------------------------|
> [Long]
>
> Generated Variables
> |--------|---------------|
> |Name    |Label          |
> |--------|---------------|
> |NEpisode|Number of      |
> |        |episodes       |
> |        |experienced    |
> |--------|---------------|
> |Episode |Episode number |
> |--------|---------------|
> |startdt |<none>         |
> |--------|---------------|
>
> Processing Statistics
> |-------------|-|
> |Variables In |6|
> |Variables Out|5|
> |-------------|-|
>
>
> SORT CASES BY id startdt.
> *  .....  The 'long' organization may be your preferred form:  ..... .
> LIST.
>
> List
> |-----------------------------|---------------------------|
> |Output Created               |08-NOV-2007 21:30:53       |
> |-----------------------------|---------------------------|
> [Long]
>
>   id ill NEpisode Episode    startdt
>
>    1   1      3        1  02/05/2004
>    1   1      3        2  03/12/2004
>    1   1      3        3  03/31/2004
>    3   1      2        2  02/11/2004
>    3   1      2        1  03/01/2004
>    4   1      3        2  12/15/2003
>    4   1      3        1  04/25/2004
>    4   1      3        3  06/01/2004
>    6   1      4        2  01/12/2004
>    6   1      4        4  02/23/2004
>    6   1      4        3  03/22/2004
>    6   1      4        1  06/17/2004
>    7   1      1        1  04/18/2004
>
> Number of cases read:  13    Number of cases listed:  13
>
>
> *  .....  But you can create your "Order" variable:            ..... .
>
> NEW FILE.
> ADD FILES
>     /FILE  = Long
>     /BY id
>     /FIRST = NewGuy.
>
> NUMERIC Order (F8).
> DO IF      NewGuy.
> .  COMPUTE Order = Episode.
> ELSE.
> .  COMPUTE Order = 10*LAG(Order) + Episode.
> END IF.
>
> DATASET NAME     PreOrder WINDOW=FRONT.
>
> .  /**/  LIST  /*-*/.
>
> List
> |-----------------------------|---------------------------|
> |Output Created               |08-NOV-2007 21:30:53       |
> |-----------------------------|---------------------------|
> [PreOrder]
>
>   id ill NEpisode Episode    startdt NewGuy    Order
>
>    1   1      3        1  02/05/2004    1          1
>    1   1      3        2  03/12/2004    0         12
>    1   1      3        3  03/31/2004    0        123
>    3   1      2        2  02/11/2004    1          2
>    3   1      2        1  03/01/2004    0         21
>    4   1      3        2  12/15/2003    1          2
>    4   1      3        1  04/25/2004    0         21
>    4   1      3        3  06/01/2004    0        213
>    6   1      4        2  01/12/2004    1          2
>    6   1      4        4  02/23/2004    0         24
>    6   1      4        3  03/22/2004    0        243
>    6   1      4        1  06/17/2004    0       2431
>    7   1      1        1  04/18/2004    1          1
>
> Number of cases read:  13    Number of cases listed:  13
>
>
> AGGREGATE OUTFILE=*
>    /BREAK = id
>    /Order = LAST(Order).
>
> DATASET NAME     Order    WINDOW=FRONT.
>
>
> LIST.
>
> List
> |-----------------------------|---------------------------|
> |Output Created               |08-NOV-2007 21:30:54       |
> |-----------------------------|---------------------------|
> [Order]
>
>   id    Order
>
>    1      123
>    3       21
>    4      213
>    6     2431
>    7        1
>
> Number of cases read:  5    Number of cases listed:  5
>
>
> *  .....  And attach "Order" to the original file:             ..... .
>
> DATASET ACTIVATE TestData WINDOW=FRONT.
> MATCH FILES
>     /FILE = *
>     /FILE = Order
>     /BY id
>     /KEEP = id ill Order all.
>
> LIST.
>
> List
> |-----------------------------|---------------------------|
> |Output Created               |08-NOV-2007 21:30:55       |
> |-----------------------------|---------------------------|
> [TestData]
>
>   id ill    Order   startdt1   startdt2   startdt3   startdt4
>
>    1   1      123 02/05/2004 03/12/2004 03/31/2004          .
>    2   0        .          .          .          .          .
>    3   1       21 03/01/2004 02/11/2004          .          .
>    4   1      213 04/25/2004 12/15/2003 06/01/2004          .
>    5   0        .          .          .          .          .
>    6   1     2431 06/17/2004 01/12/2004 03/22/2004 02/23/2004
>    7   1        1 04/18/2004          .          .          .
>
> Number of cases read:  7    Number of cases listed:  7
>
> ============================
> APPENDIX: Test data and code
> ============================
> *  C:\Documents and Settings\Richard\My Documents                    .
> *    \Technical\spssx-l\Z-2007d                                      .
> *    \2007-11-08 Hashmi - Sorting dates chronologically.SPS          .
>
> *  In response to posting                                            .
> *  Date:    Thu, 8 Nov 2007 13:12:27 -0500                           .
> *  From:    Shahrukh Hashmi <[hidden email]>            .
> *  Subject: Sorting dates chronologically                            .
> *  To:      [hidden email]                                 .
>
>
> *  ................................................................. .
> *  .................   Test data               ..................... .
> *  Taken from the posting.                                           .
> *  The code works, with copious warning messages for missing dates.  .
>
> DATA LIST LIST/
>      id ill startdt1 startdt2 startdt3 startdt4
>     (N3, F2, 4ADATE8).
> BEGIN DATA
>      1 1 02/05/04 03/12/04 03/31/04 .
>      2 0
>      3 1 03/01/04 02/11/04
>      4 1 04/25/04 12/15/03 06/01/04
>      5 0
>      6 1 06/17/04 01/12/04 03/22/04 02/23/04
>      7 1 04/18/04
> END DATA.
> FORMATS startdt1 TO startdt4 (ADATE10).
> DATASET NAME     TestData WINDOW=FRONT.
>
>
> *  .................   Post after this point   ..................... .
> *  ................................................................. .
>
> LIST.
>
> *  "I want to create a new variable that lists out the order the     .
> *  episodes occurred for each patient:                               .
> *  id order                                                          .
> *  1  123                                                            .
> *  2  .                                                              .
> *  3  21                                                             .
> *  4  213                                                            .
> *  5  .                                                              .
> *  6  2431                                                           .
> *  7  1                               "                              .
>
> DATASET COPY     Long.
> DATASET ACTIVATE Long     WINDOW=FRONT.
>
> VARSTOCASES
>   /MAKE startdt FROM startdt1 startdt2 startdt3 startdt4
>   /INDEX = Episode "Episode number"(4)
>   /KEEP  = id ill
>   /NULL  = DROP
>   /COUNT = NEpisode "Number of episodes experienced" .
>
> SORT CASES BY id startdt.
>
> *  .....  The 'long' organization may be your preferred form:  ..... .
> LIST.
>
> *  .....  But you can create your "Order" variable:            ..... .
>
>
> NEW FILE.
> ADD FILES
>     /FILE  = Long
>     /BY id
>     /FIRST = NewGuy.
>
> NUMERIC Order (F8).
> DO IF      NewGuy.
> .  COMPUTE Order = Episode.
> ELSE.
> .  COMPUTE Order = 10*LAG(Order) + Episode.
> END IF.
>
> DATASET NAME     PreOrder WINDOW=FRONT.
>
> .  /**/  LIST  /*-*/.
>
> AGGREGATE OUTFILE=*
>    /BREAK = id
>    /Order = LAST(Order).
>
> DATASET NAME     Order    WINDOW=FRONT.
> LIST.
>
> *  .....  And attach "Order" to the original file:             ..... .
>
> DATASET ACTIVATE TestData WINDOW=FRONT.
> MATCH FILES
>     /FILE = *
>     /FILE = Order
>     /BY id
>     /KEEP = id ill Order all.
>
> LIST.

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