Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- With JMP 14.3 how can one match two date-time columns within =/- xh

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Mar 19, 2020 3:09 PM
(652 views)

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

```
dt = New Table( "Untitled 3",
Add Rows( 20 ),
New Column( "atime",
Numeric,
"Continuous",
Format( "ddMonyyyy h:m", 19 ),
Input Format( "ddMonyyyy h:m" ),
Formula( As Date( Random Uniform( Today() - 100000, Today() + 100000 ) ) ),
Set Display Width( 250 )
),
New Column( "closest b time",
Numeric,
"Continuous",
Format( "ddMonyyyy h:m", 19 ),
Input Format( "ddMonyyyy h:m" ),
Set Display Width( 250 )
),
New Column( "btime",
Numeric,
"Continuous",
Format( "ddMonyyyy h:m", 19 ),
Input Format( "ddMonyyyy h:m" ),
Formula( As Date( Random Uniform( Today() - 100000, Today() + 100000 ) ) ),
Set Display Width( 250 )
),
New Column( "closest a time",
Numeric,
"Continuous",
Format( "ddMonyyyy h:m", 19 ),
Input Format( "ddMonyyyy h:m" ),
Set Display Width( 250 )
)
);
dt << runformulas;
atime << deleteformula;
btime << deleteformula;
// here it is: find both backwards and forwards nearest neighbor
kdtA = KDTable( dt:atime << getvalues );
kdtB = KDTable( dt:btime << getvalues );
For( irow = 1, irow <= N Rows( dt ), irow += 1,
// look up the row of the nearest A time to this row's B time
closeA = (kdtA << knearestrows( 1, Matrix( dt:btime[irow] ) ))[1][1];
// get the actual A time from that row and keep it in this row.
dt:closest a time[irow] = dt:atime[closeA];
// flag times that are too far away
if(abs(dt:closest a time[irow]-btime[irow])>inhours(2),dt:closest a time<<color cells("red",irow));
// repeat for the other direction. These are NOT going to be "the same, but backwards!"
// because you might not be the nearest neighbor of your nearest neighbor.
closeB = (kdtB << knearestrows( 1, Matrix( dt:atime[irow] ) ))[1][1];
dt:closest b time[irow] = dt:btime[closeB];
if(abs(dt:closest b time[irow]-atime[irow])>inhours(2),dt:closest b time<<color cells("red",irow));
);
```

the [1][1] in the knearest rows call is getting the first item from the returned list, which is a matrix of one element, then getting that element as a scalar value. It is the row number.

This technique will not work if you need an asymmetric window (like B time must be within 2 hours *after* A time). You could adapt it by getting all the rows within 2 hours, then picking the first one that works.

Craige

3 REPLIES 3

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: With JMP 14.3 how can one match two date-time columns within =/- xh

Let me state, what I believe you are asking.

You want to know, how to match 2 date columns within a range of plus or minus X number of Hours.

Is this correct?

Are the columns in the same data table, or are you looking to match across data tables, so the data can be merged together?

If more than one row is found within the +/- hour range, do you want the closest value chosen? What if there is a tie?

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: With JMP 14.3 how can one match two date-time columns within =/- xh

Same data table. Trying to match serum lactate level in septic patients to a creatinine level at least within 6h

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

```
dt = New Table( "Untitled 3",
Add Rows( 20 ),
New Column( "atime",
Numeric,
"Continuous",
Format( "ddMonyyyy h:m", 19 ),
Input Format( "ddMonyyyy h:m" ),
Formula( As Date( Random Uniform( Today() - 100000, Today() + 100000 ) ) ),
Set Display Width( 250 )
),
New Column( "closest b time",
Numeric,
"Continuous",
Format( "ddMonyyyy h:m", 19 ),
Input Format( "ddMonyyyy h:m" ),
Set Display Width( 250 )
),
New Column( "btime",
Numeric,
"Continuous",
Format( "ddMonyyyy h:m", 19 ),
Input Format( "ddMonyyyy h:m" ),
Formula( As Date( Random Uniform( Today() - 100000, Today() + 100000 ) ) ),
Set Display Width( 250 )
),
New Column( "closest a time",
Numeric,
"Continuous",
Format( "ddMonyyyy h:m", 19 ),
Input Format( "ddMonyyyy h:m" ),
Set Display Width( 250 )
)
);
dt << runformulas;
atime << deleteformula;
btime << deleteformula;
// here it is: find both backwards and forwards nearest neighbor
kdtA = KDTable( dt:atime << getvalues );
kdtB = KDTable( dt:btime << getvalues );
For( irow = 1, irow <= N Rows( dt ), irow += 1,
// look up the row of the nearest A time to this row's B time
closeA = (kdtA << knearestrows( 1, Matrix( dt:btime[irow] ) ))[1][1];
// get the actual A time from that row and keep it in this row.
dt:closest a time[irow] = dt:atime[closeA];
// flag times that are too far away
if(abs(dt:closest a time[irow]-btime[irow])>inhours(2),dt:closest a time<<color cells("red",irow));
// repeat for the other direction. These are NOT going to be "the same, but backwards!"
// because you might not be the nearest neighbor of your nearest neighbor.
closeB = (kdtB << knearestrows( 1, Matrix( dt:atime[irow] ) ))[1][1];
dt:closest b time[irow] = dt:btime[closeB];
if(abs(dt:closest b time[irow]-atime[irow])>inhours(2),dt:closest b time<<color cells("red",irow));
);
```

the [1][1] in the knearest rows call is getting the first item from the returned list, which is a matrix of one element, then getting that element as a scalar value. It is the row number.

This technique will not work if you need an asymmetric window (like B time must be within 2 hours *after* A time). You could adapt it by getting all the rows within 2 hours, then picking the first one that works.

Craige

Article Labels

There are no labels assigned to this post.