logical operations on dates/times
Locked
 
|
Hi everyone,
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. 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. ===================== 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 |
Re: logical operations on dates/times
|
At 06:56 PM 12/14/2009, Kylie Lange wrote:
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: In short form: the problem is, how to test for equality of two SPSS date-time values. First of all, it's normally possible. SPSS date-time values are in seconds. Values that include no fractional seconds are integers (1.35E10, for times near the present), and can be compared exactly. At 07:03 PM 12/14/2009, Kylie added: 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). Excel date-time values are in days. Values including hours, minutes, or seconds have fractional parts, and may not be converted to their exact SPSS values. Assuming you only need values to the nearest second (or minute, or hour), rounding to seconds should work: COMPUTE TIME = RND(TIME). COMPUTE V4_48_B = RND(V4_48_B). -Best of luck, Richard ===================== 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 Richard. For the time being I have gone back to the
original Excel data, extracted the hour and minute components of Time, imported
them into SPSS, and reconstructed Time in using TIME.HMS(). The two variables
now exactly match where expected. What I had found was that in about 10% of
cases there was a small fractional part to the dates imported from Excel
(though no seconds or fractions of seconds are present when the variable is displayed
in hh:mm:ss.ss format). I’ll try out the RND() function as well –
for some reason it didn’t occur to me that that was an option with times. Thanks, Kylie. From: Richard Ristow
[mailto:[hidden email]] At 06:56 PM 12/14/2009, Kylie Lange wrote: 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:
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,
|
| Free forum by Nabble | Edit this page |