253248_en-US

liliconcom

With JMP 14.3 how can one match two date-time columns within =/- xhRe: 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?

Re: With JMP 14.3 how can one match two date-time columns within =/- xhSame data table. Trying to match serum lactate level in septic patients to a creatinine level at least within 6h

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

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="">inhours(2),dt:closest b time<<COLOR cells=""></COLOR></COLOR>

red flags near neighbors in time that are more than 2 hours apart

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.