logical operations on dates/times
Kylie
I am currently working on a file where I have to compare date
variables and
flag those cases that are a match. I am running into problems due to
the
internal precision of the variables, which means that cases that I
want to
be considered equal, are not. Here is what I have:
Name Visit Period24 Time V4_48_B
HC d 2 9:25 9:40
HC d 2 9:30 9:40
HC d 2 9:35 9:40
HC d 2 9:40 9:40
HC d 2 9:45 9:40
TD d 2 7:58 8:10
TD d 2 8:03 8:10
TD d 2 8:08 8:10
TD d 2 8:13 8:10
TD d 2 8:18 8:10
V4_48_B holds the time that the subject had breakfast at during that
period
of that visit. I want to create a variable that flags the row where
Time
occurs at, or the first one after, breakfast (V4_48_B and Time are
both Date
type variables). Ie, I want:
Name Visit Period24 Time V4_48_B V4_48_B_flag
HC d 2 9:25 9:40 0
HC d 2 9:30 9:40 0
HC d 2 9:35 9:40 0
HC d 2 9:40 9:40 1
HC d 2 9:45 9:40 0
TD d 2 7:58 8:10 0
TD d 2 8:03 8:10 0
TD d 2 8:08 8:10 0
TD d 2 8:13 8:10 1
TD d 2 8:18 8:10 0
Here is the syntax I have been using:
COMPUTE V4_48_B_flag=0.
IF (Name=LAG(Name) & Visit=LAG(Visit) & Visit='d' & Period24=2 &
(V4_48_B GT
LAG(Time)) & (V4_48_B LE Time)) V4_48_B_flag=1.
However, the fourth row is not being flagged as a match because there
is a
small precision difference between Time and V4_48_B. Computing the
difference of these two variables shows that the difference for the
fourth
row is -7.276e-12. Thus the flag variable isn't being set to 1 until
it
reaches the following row, with Time=9:45, as the final LE clause in
the IF
statement isn't true for the Time=9:40 row.
Time was imported from Excel where it was in h:mm format. V4_48_B was
computed in SPSS using the TIME.HMS function on two separate numeric
variables also imported from Excel. Ie, COMPUTE
V4_48_B=TIME.HMS(V4_48_B_hr, V4_24_48_min, 0).
Can anyone suggest what I need to do to get the behaviour that I want?
Can I
round/truncate the Time or V4_48_B variables? Or change the IF
statement in
some way? (This is of course just a subset of the total data file,
which has
data from 4 visits of 3 periods each for each subject, each period
being 24
hours long, and I am doing this matching/flagging process for three
meals in
each 24 hour period.)
Thanks for any suggestions,
Kylie.
Bruce Weaver
I don't have SPSS here to test, but how about doing this before
computing your flag?
compute #h = xdate.hour(time).
compute #m = xdate.minute(time).
compute time = time.hms(#h,#m,0).
compute #h = xdate.hour(V4_48_B).
compute #m = xdate.minute(V4_48_B).
compute V4_48_B = time.hms(#h,#m,0).
exe.
--
Bruce Weaver
bwe...@lakeheadu.ca
http://sites.google.com/a/lakeheadu.ca/bweaver/
"When all else fails, RTFM."
Kylie
>
> I don't have SPSS here to test, but how about doing this before
> computing your flag?
>
> compute #h = xdate.hour(time).
> compute #m = xdate.minute(time).
> compute time = time.hms(#h,#m,0).
> compute #h = xdate.hour(V4_48_B).
> compute #m = xdate.minute(V4_48_B).
> compute V4_48_B = time.hms(#h,#m,0).
> exe.
>
Thanks for the suggestion Bruce - this was really interesting. For the
particular data mentioned above, the 9:40 value in Time, once
reconstructed using your code, comes back as 9:39. There are no
seconds or fractions of seconds present in the original Time variable
when it is displayed in the hh:mm:ss.ss format, but I guess there is
just something small hanging around (from the original Excel data I
assume) in there somewhere that is causing this. Calculating the
difference between my original Time variable and your suggested
reconstruction of time, shows that about 10% of cases are out by
approx 1 minute (with difference values of 59.9999999999xxxxx).
I think I'm going to go back to the original Excel data and try
importing the hours and minutes as separate numeric variables, and
piece them together into Time once in SPSS - in the same way that
V4_48_B was computed. Hopefully that will get rid of these fractional
values.
Thanks,
Kylie.
Kylie
As mentioned, I went back to the Excel data, split Time into it's hour
and minute parts, imported them into SPSS and constructed a SPSS Time
variable. This has appeared to have worked - I am now getting an exact
match where expected.
Thanks again,
Kylie.
Bruce Weaver
--- snip ---
> As mentioned, I went back to the Excel data, split Time into it's hour
> and minute parts, imported them into SPSS and constructed a SPSS Time
> variable. This has appeared to have worked - I am now getting an exact
> match where expected.
>
> Thanks again,
> Kylie.
Very sensible. I've had enough troubles with Excel Time variables in
the past that if I ever have to set up data entry for someone in
Excel, I always split date & time variables into components, and stick
the pieces together in SPSS.
Cheers,
Bruce
p.s. - I got booted out of my Google Groups session when attempting to
send this, so I apologize if it appears twice.
--
Bruce Weaver
bwe...@lakeheadu.ca
http://sites.google.com/a/lakeheadu.ca/bweaver/Home