SPSSX Discussion

Recoding Date and time into antoher variable

classic Classic list List threaded Threaded
♦
♦
 2 messages Options
Sjoerd van den Berg
Reply | Threaded
Open this post in threaded view
♦
♦
|

Recoding Date and time into antoher variable

Sjoerd van den Berg
Dear all,

For an comprehensive laboratory animal monitoring system I would like to
recode the follwing variables in to tw onew variables:

The alod variables are

Date            Time            AM_PM
______________________________________
2006/05/02 10:18:09,00 AM
2006/05/02 10:25:39,00 AM

And I would like to generate

One variable:

All data points deriven from one date ( e.g. 2006/05/02 ) as a "day"
this would look something like

2006/05/02 = 1
2006/05/03 = 2
2006/05/04 = 3

In which I can add the labels that 1 = tuesday, 2 = wednesday etc.

The other variable I would like to derive is

2006/05/02 - 06.00.00 am to 06.00.00 pm = 1
2006/05/02 - 06.00.00 pm to 2006/05/03 - 06.00.00 am = 2
2006/05/03 - 06.00.00 am to 06.00.00 pm = 3
2006/05/03 - 06.00.00 pm to 2006/05/04 - 06.00.00 am = 4

So in this case all "half days" ( or in other words, day and night periods,
are stated.

Later, I can add the labels that 1 = tuesday, 2 = wednesday etc.

Since this data set will be used by other researcher too, I would really
like to have both variables.

Data and Time variables are in "Date" format, the AM_PM is a string variable.

Thank you very much in advance,

kind regards

Sjoerd

___________________________________

Drs. ing. Sjoerd van den Berg
Departments of  Human and Clinical Genetics
Leiden University Medical Center
PO Box 9503
2300 RA Leiden
The Netherlands

tel: -31-(0)71-5276410
Richard Ristow
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Recoding Date and time into antoher variable

Richard Ristow
At 01:20 PM 11/26/2006, Sjoerd van den Berg wrote:

>For an comprehensive laboratory animal monitoring system I would like
>to recode the [variables Date, Time, and AM_PM] into two new
>variables. Date and Time variables are in "Date" format, the AM_PM is
>a string variable.
>
>One variable: All data points deriven from one date ( e.g. 2006/05/02
>) as a "day"; this would look something like
>
>2006/05/02 = 1
>2006/05/03 = 2
>2006/05/04 = 3
>
>[The second variable:]
>
>2006/05/02 - 06.00.00 am to 06.00.00 pm = 1
>2006/05/02 - 06.00.00 pm to 2006/05/03 - 06.00.00 am = 2
>2006/05/03 - 06.00.00 am to 06.00.00 pm = 3
>2006/05/03 - 06.00.00 pm to 2006/05/04 - 06.00.00 am = 4
>
>So in this case all "half days" ( or in other words, day and night
>periods, are stated.
>
>Later, I can add the labels that 1 = tuesday, 2 = wednesday etc.

I will guess that you want your 'day' and 'half-day' numbers to run
sequentially, not to go up to 7 (days) and start over. As for the
weekday, as Melissa Ives pointed out, function XDATE.WKDAY will give
you that, if you have the date. (For night half-days, the date and
weekday are ambiguous, since the period spans two days. I'll take the
date as the earlier of the two, the one corresponding to the evening
rather than the early-morning hours. Finally, half-day 1 begins at
6:00am of the earliest date in the file; so some events may fall in
half-day 0. The following is tested; SPSS draft output. For final
display, it uses function VALUELABEL, added in SPSS 14.

*  ........................................................... .
LIST.

List
|-----------------------------|---------------------------|
|Output Created               |04-DEC-2006 20:57:24       |
|-----------------------------|---------------------------|
EVENT#       DATE         TIME AM_PM

     1  2006/05/02   2:18:09.00 AM
     2  2006/05/02   2:34:19.12 AM
     3  2006/05/02   4:39:39.09 AM
     4  2006/05/02   8:00:22.20 AM
     5  2006/05/02   3:25:10.58 PM
     6  2006/05/02   4:14:16.38 PM
     7  2006/05/02   5:20:57.57 PM
     8  2006/05/02   6:10:43.99 PM
     9  2006/05/03   1:14:20.05 PM
    10  2006/05/03   2:30:42.65 PM
    11  2006/05/03   6:53:46.43 PM
    12  2006/05/03  11:19:26.08 PM
    13  2006/05/04   3:00:05.39 AM
    14  2006/05/04   1:38:48.78 PM
    15  2006/05/04   8:53:03.50 PM
    16  2006/05/05   8:38:52.30 AM
    17  2006/05/05  11:44:30.15 AM
    18  2006/05/05   7:58:38.01 PM
    19  2006/05/06  12:53:32.23 AM
    20  2006/05/06   2:50:04.37 AM
    21  2006/05/06   5:28:55.46 AM
    22  2006/05/06   5:32:27.74 AM
    23  2006/05/06   6:23:01.55 AM
    24  2006/05/06   9:52:17.22 AM
    25  2006/05/07  12:49:40.26 AM

Number of cases read:  25    Number of cases listed:  25


*  .......... Combine date and time to a single date-time value.

NUMERIC DATETIME (DATETIME23.2).

NUMERIC #TIME24(TIME12.2).
COMPUTE #TIME24 = TIME.
DO IF   AM_PM EQ 'AM'.
.  IF TIME GE TIME.HMS(12)
         #TIME24  = #TIME24 - TIME.HMS(12,0,0).
ELSE IF AM_PM EQ 'PM'.
.  IF TIME LT TIME.HMS(12)
         #TIME24  = #TIME24 + TIME.HMS(12,0,0).
END IF.

COMPUTE DATETIME = DATE + #TIME24.
.  NUMERIC #DAY1    (SDATE10)
           /#HLFDAY1 (DATETIME17).
.  COMPUTE #DAY1    = XDATE.DATE(DATETIME).
.  COMPUTE #HLFDAY1 = #DAY1 + TIME.HMS(6,0,0).
LIST.

List
|-----------------------------|---------------------------|
|Output Created               |04-DEC-2006 20:57:25       |
|-----------------------------|---------------------------|
EVENT#       DATE         TIME AM_PM                DATETIME

     1  2006/05/02   2:18:09.00 AM    02-MAY-2006 02:18:09.00
     2  2006/05/02   2:34:19.12 AM    02-MAY-2006 02:34:19.12
     3  2006/05/02   4:39:39.09 AM    02-MAY-2006 04:39:39.09
     4  2006/05/02   8:00:22.20 AM    02-MAY-2006 08:00:22.20
     5  2006/05/02   3:25:10.58 PM    02-MAY-2006 15:25:10.58
     6  2006/05/02   4:14:16.38 PM    02-MAY-2006 16:14:16.38
     7  2006/05/02   5:20:57.57 PM    02-MAY-2006 17:20:57.57
     8  2006/05/02   6:10:43.99 PM    02-MAY-2006 18:10:43.99
     9  2006/05/03   1:14:20.05 PM    03-MAY-2006 13:14:20.05
    10  2006/05/03   2:30:42.65 PM    03-MAY-2006 14:30:42.65
    11  2006/05/03   6:53:46.43 PM    03-MAY-2006 18:53:46.43
    12  2006/05/03  11:19:26.08 PM    03-MAY-2006 23:19:26.08
    13  2006/05/04   3:00:05.39 AM    04-MAY-2006 03:00:05.39
    14  2006/05/04   1:38:48.78 PM    04-MAY-2006 13:38:48.78
    15  2006/05/04   8:53:03.50 PM    04-MAY-2006 20:53:03.50
    16  2006/05/05   8:38:52.30 AM    05-MAY-2006 08:38:52.30
    17  2006/05/05  11:44:30.15 AM    05-MAY-2006 11:44:30.15
    18  2006/05/05   7:58:38.01 PM    05-MAY-2006 19:58:38.01
    19  2006/05/06  12:53:32.23 AM    06-MAY-2006 00:53:32.23
    20  2006/05/06   2:50:04.37 AM    06-MAY-2006 02:50:04.37
    21  2006/05/06   5:28:55.46 AM    06-MAY-2006 05:28:55.46
    22  2006/05/06   5:32:27.74 AM    06-MAY-2006 05:32:27.74
    23  2006/05/06   6:23:01.55 AM    06-MAY-2006 06:23:01.55
    24  2006/05/06   9:52:17.22 AM    06-MAY-2006 09:52:17.22
    25  2006/05/07  12:49:40.26 AM    07-MAY-2006 00:49:40.26

Number of cases read:  25    Number of cases listed:  25


*  .......... Get the startpoints for day 1 and half-day 1.    .
*  These values are stored in scratch variables, so they carry .
*  through to all later cases in the file.                     .
DO IF   $CASENUM EQ 1.
.  NUMERIC #DAY1    (SDATE10)
           /#HLFDAY1 (DATETIME17).
.  COMPUTE #DAY1    = XDATE.DATE(DATETIME).
.  COMPUTE #HLFDAY1 = #DAY1 + TIME.HMS(6,0,0).
END IF.


*  .......... Declare the output variables          .......... .
NUMERIC  DAY#     (F4)
         /DAY_DT   (SDATE10)
         /HLFDY#   (F4)
         /HLFDY_DT (SDATE10)
         /HLFDY_DN (F2).
VAR LABELS
          DAY#     'Day number, sequential in period'
          DAY_DT   'Date corresponding to day number'
          HLFDY#   'Half-day, sequential in period'
          HLFDY_DT 'Date of the beginning of the half-day'
          HLFDY_DN 'Half-day: Day, or night'.
VAL LABELS
          HLFDY_DN 1 'Day'   2 'Night'.

*  .......... The computations                      .......... .
*  These are somewhat less direct than they might be;          .
*  for example, DAY_DT is calculated separately from DAY#,     .
*  although its value should be the same as variable DATE.     .

COMPUTE DAY#     = TRUNC(1+CTIME.DAYS(DATETIME-#DAY1)).
COMPUTE DAY_DT   = #DAY1    + TIME.DAYS(DAY#-1).

COMPUTE HLFDY#   = TRUNC(1+CTIME.DAYS(DATETIME-#HLFDAY1)*2).
COMPUTE HLFDY_DT = XDATE.DATE
                   (#HLFDAY1 + TIME.DAYS((HLFDY#-1)/2)).
COMPUTE HLFDY_DN = 2-MOD(HLFDY#,2).

TEMPORARY.
STRING  DAY_NT (A5).
COMPUTE DAY_NT = VALUELABEL(HLFDY_DN).
LIST VARIABLES = EVENT# TO AM_PM, DAY# TO HLFDY_DT, DAY_NT.

List
|-----------------------------|---------------------------|
|Output Created               |04-DEC-2006 20:57:27       |
|-----------------------------|---------------------------|
EVENT#       DATE         TIME AM_PM DAY#     DAY_DT HLFDY#   HLFDY_DT
DAY_NT

     1  2006/05/02   2:18:09.00 AM       1 2006/05/02     0  2006/05/01
Night
     2  2006/05/02   2:34:19.12 AM       1 2006/05/02     0  2006/05/01
Night
     3  2006/05/02   4:39:39.09 AM       1 2006/05/02     0  2006/05/01
Night
     4  2006/05/02   8:00:22.20 AM       1 2006/05/02     1  2006/05/02
Day
     5  2006/05/02   3:25:10.58 PM       1 2006/05/02     1  2006/05/02
Day
     6  2006/05/02   4:14:16.38 PM       1 2006/05/02     1  2006/05/02
Day
     7  2006/05/02   5:20:57.57 PM       1 2006/05/02     1  2006/05/02
Day
     8  2006/05/02   6:10:43.99 PM       1 2006/05/02     2  2006/05/02
Night
     9  2006/05/03   1:14:20.05 PM       2 2006/05/03     3  2006/05/03
Day
    10  2006/05/03   2:30:42.65 PM       2 2006/05/03     3  2006/05/03
Day
    11  2006/05/03   6:53:46.43 PM       2 2006/05/03     4  2006/05/03
Night
    12  2006/05/03  11:19:26.08 PM       2 2006/05/03     4  2006/05/03
Night
    13  2006/05/04   3:00:05.39 AM       3 2006/05/04     4  2006/05/03
Night
    14  2006/05/04   1:38:48.78 PM       3 2006/05/04     5  2006/05/04
Day
    15  2006/05/04   8:53:03.50 PM       3 2006/05/04     6  2006/05/04
Night
    16  2006/05/05   8:38:52.30 AM       4 2006/05/05     7  2006/05/05
Day
    17  2006/05/05  11:44:30.15 AM       4 2006/05/05     7  2006/05/05
Day
    18  2006/05/05   7:58:38.01 PM       4 2006/05/05     8  2006/05/05
Night
    19  2006/05/06  12:53:32.23 AM       5 2006/05/06     8  2006/05/05
Night
    20  2006/05/06   2:50:04.37 AM       5 2006/05/06     8  2006/05/05
Night
    21  2006/05/06   5:28:55.46 AM       5 2006/05/06     8  2006/05/05
Night
    22  2006/05/06   5:32:27.74 AM       5 2006/05/06     8  2006/05/05
Night
    23  2006/05/06   6:23:01.55 AM       5 2006/05/06     9  2006/05/06
Day
    24  2006/05/06   9:52:17.22 AM       5 2006/05/06     9  2006/05/06
Day
    25  2006/05/07  12:49:40.26 AM       6 2006/05/07    10  2006/05/06
Night

Number of cases read:  25    Number of cases listed:  25

============================
APPENDIX: GENERATE TEST DATA
============================
NEW FILE.
INPUT PROGRAM.
.  NUMERIC EVENT#   (F3)
           /DATE     (SDATE10)
           /TIME     (TIME12.2).
.  STRING  AM_PM    (A2).

*  Time density of events             .
.  NUMERIC #EV_DENS(E15.4).
.  COMPUTE #EV_DENS = 1/TIME.HMS(4).
*--LEAVE   #EV_DENS.

*  Number of events to simulate       .
.  COMPUTE #N_EVTS  = 25.

.  NUMERIC #EVT_DTM (DATETIME23.2).
*--LEAVE   #EVT_DTM.

*  Time of first event                .
.  COMPUTE #EVT_DTM = DATE.DMY(02,05,2006)
                     + TIME.HMS(02,18,09).

.  LOOP    EVENT# = 1 TO #N_EVTS.
*     # After the first event, advance to next event .
.     NUMERIC #MN_INTV #INTERVL (TIME40.16).
.     DO IF EVENT# GT 1.
.        COMPUTE #INTERVL = RV.EXP(#EV_DENS).
.        COMPUTE #EVT_DTM = #EVT_DTM + #INTERVL.
.     END IF.
*     # Convert event time into date, time, and AM/PM.
.     COMPUTE DATE = XDATE.DATE(#EVT_DTM).
.     COMPUTE TIME = XDATE.TIME(#EVT_DTM).
.     DO IF   TIME LT TIME.HMS(01).
.        COMPUTE TIME  = TIME + TIME.HMS(12).
.        COMPUTE AM_PM = 'AM'.
.     ELSE IF TIME LT TIME.HMS(12).
.        COMPUTE AM_PM = 'AM'.
.     ELSE IF TIME LT TIME.HMS(13).
.        COMPUTE AM_PM = 'PM'.
.     ELSE.
.        COMPUTE TIME  = TIME - TIME.HMS(12).
.        COMPUTE AM_PM = 'PM'.
.     END IF.
.     END CASE.
.  END LOOP.

END FILE.
END INPUT PROGRAM.
Free forum by Nabble Edit this page