Hello, I have ~20 columns of result dates which are formatted as numeric, continuous m/d/y h: m with width of 19. I want to see if these results occur on the same date ("CST_date") as another variable that is formatted as numeric, continuous, d/m/y width 12. I am trying to make a new column where if the result 1 date is equal to CST_date , put in value from result 1. Is it possible to do this without making a new column with the date from the results column extracted (no time)? CST_dateCST_timeResult Cortisol 1Date Cortisol 1Result Cortisol 2Date Cortisol 2Result Cortisol 3Date Cortisol 3Result Cortisol 430/04/20185:16:00 AM1804/30/2018 5:34 AM3004/30/2018 7:58 AM 22/06/20185:26:00 AM306/22/2018 5:14 AM1806/22/2018 6:14 AM 14/07/20168:11:00 AM1307/14/2016 7:45 AM4607/14/2016 8:47 AM5007/14/2016 9:17 AM 26/04/20188:48:00 AM804/25/2018 6:13 AM1204/26/2018 8:03 AM 23/02/20178:38:00 PM4002/23/2017 3:49 PM3502/23/2017 6:55 PM3602/23/2017 9:07 PM4225/12/20191:33:00 PM1012/25/2019 12:23 PM 28/05/20209:06:00 AM405/28/2020 6:58 AM2005/28/2020 10:26 AM 29/09/20177:45:00 PM1909/29/2017 7:04 PM3009/29/2017 8:53 PM 01/12/20172:47:00 PM1012/01/2017 2:45 PM2012/01/2017 3:54 PM 22/06/20171:49:00 PM206/22/2017 1:10 PM2406/22/2017 3:10 PM I tried the following but it is not working. data table ("cstlabs")<< new column ("basal_cst_cort", formula (if ( :cst_date == formula (:Date Cortisol 1 - time of day (:Date Cortisol 1)), :Result Cortisol 1, 0))); Even when I write data table ("cstlabs") << new column ("date1", time of day (:Date Cortisol 1)); it is not working. Any help would be greatly appreciated.

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

extract date from date time variable

CC86

Hello, I have ~20 columns of result dates which are formatted as numeric, continuous m/d/y h: m with width of 19. I want to see if these results occur on the same date ("CST_date") as another variable that is formatted as numeric, continuous, d/m/y width 12. I am trying to make a new column where if the result 1 date is equal to CST_date , put in value from result 1. Is it possible to do this without making a new column with the date from the results column extracted (no time)?

CST_date	CST_time	Result Cortisol 1	Date Cortisol 1	Result Cortisol 2	Date Cortisol 2	Result Cortisol 3	Date Cortisol 3	Result Cortisol 4
30/04/2018	5:16:00 AM	18	04/30/2018 5:34 AM	30	04/30/2018 7:58 AM
22/06/2018	5:26:00 AM	3	06/22/2018 5:14 AM	18	06/22/2018 6:14 AM
14/07/2016	8:11:00 AM	13	07/14/2016 7:45 AM	46	07/14/2016 8:47 AM	50	07/14/2016 9:17 AM
26/04/2018	8:48:00 AM	8	04/25/2018 6:13 AM	12	04/26/2018 8:03 AM
23/02/2017	8:38:00 PM	40	02/23/2017 3:49 PM	35	02/23/2017 6:55 PM	36	02/23/2017 9:07 PM	42
25/12/2019	1:33:00 PM	10	12/25/2019 12:23 PM
28/05/2020	9:06:00 AM	4	05/28/2020 6:58 AM	20	05/28/2020 10:26 AM
29/09/2017	7:45:00 PM	19	09/29/2017 7:04 PM	30	09/29/2017 8:53 PM
01/12/2017	2:47:00 PM	10	12/01/2017 2:45 PM	20	12/01/2017 3:54 PM
22/06/2017	1:49:00 PM	2	06/22/2017 1:10 PM	24	06/22/2017 3:10 PM

I tried the following but it is not working.

data table ("cstlabs")<< new column ("basal_cst_cort", formula (if (

:cst_date == formula (:Date Cortisol 1 - time of day (:Date Cortisol 1)), :Result Cortisol 1, 0)));

Even when I write

data table ("cstlabs") << new column ("date1", time of day (:Date Cortisol 1)); it is not working.

Any help would be greatly appreciated.

Accepted answers

txnelson

A JMP datetime value is just a numeric, continuous value, which is the number of seconds since midnight, January 1, 1904. A time value is just the number of seconds since midnight today. If you subtract one time value from another, you will get the a duration value, which is the number of seconds between the two times. As for determining the exact issue you may be having, you would have to attach the data table to this discussion

All comments

txnelson

Here is a script that should work

Names Default To Here( 1 );
Data Table( "cstlabs" ) << New Column( "basal_cst_cort",
	formula(
		If( :cst_date == :Date Cortisol 1 - Time Of Day( :Date Cortisol 1 )
		),
		:Result Cortisol 1,
		0
	)
);

CC86

Thank you so much! This worked perfectly.

CC86

Thank you very much. The above script works but I do not really understand what is going on with this dataset/ time variables. I have multiple time variables stored as h:m. When I subtract them from each other like:

data table ("cstlabs")<< new column ("test", formula (

time of day(:timecst) -time of day(:timecortisol1))); the variable comes out as an integer, but if I write like this:

data table ("cstlabs")<< new column ("test", formula (

:timecst -:timecortisol1)) --> this comes out as a very strange number.

I think I am having a very difficult time using these variables, because I do not understand why this happens. For example, with the following code my entire column is blank:

data table ("cstlabs")<< new column ("AM_cort", formula (

if(hour (:Date Cortisol 1)>= 5 & hour (:Date Cortisol 1) <=9 & time of day (:time) <= time of day (:timecortisol1) , :Result Cortisol 1, 0) ));

Thank you so much!

txnelson

CC86

Jim - thank you so much. I always struggle with time/date and mostly all of JMP and really find your answers helpful. Thank you!