dates in hours and blank hours

[monday89]

I have the following dataset. I need to subtract start and stop date in hours. If the stop date is blank, i need to make the time as midnight of that day. For example: subject 1 day 1, stop date is blank so new stop date should be: 1/1/2019 11:59 PM and then subtract two dates in hours. I am really stuck. Any help would be appreciated!

subject	formgroup	start date	stop date
1	day 1	1/1/19 13:55
1	day 2	1/2/19 14:34	1/2/19 19:32
1	day 3	1/3/19 19:33	1/3/19 23:44
1	day 4	1/4/19 21:49
2	day 1	2/3/19 21:33	2/3/19 23:44
2	day 2	2/4/19 23:33
2	day 3	2/5/19 12:45

Something like this:

subject	formgroup	start_date	stop_date	new_stop_date	hours
1	day 1	1/1/19 13:55		1/1/19 23:59
1	day 2	1/2/19 14:34	1/2/19 19:32	1/2/19 19:32
1	day 3	1/3/19 19:33	1/3/19 23:44	1/3/19 23:44
1	day 4	1/4/19 21:49		1/4/19 23:59
2	day 1	2/3/19 21:33	2/3/19 23:44	2/3/19 23:44
2	day 2	2/4/19 23:33		2/4/19 23:59
2	day 3	2/5/19 12:45		2/5/19 23:59

I have:

data test; set test;

if stop_ate = "" then new_stop_date = <not sure how to do end of the day midnight

else if stop_date is not null then new_stop_date = stop_date;

run;

data test; set test;

hours = new_stop_date-start_date;run;

[PaigeMiller]

There are a few ways to get the end of the day. I generally use the INTNX function with the 'e' option

if missing(stop_date) then stop_date=intnx('dtday',start_date,0,'e');

Then a subtraction gives you the length of time in seconds, and then you divide by 3600 to turn that into hours.

[Tom]

Sounds like you are using datetime values (number of seconds) and not date values (number of days).

Are your variables numeric with datetime values? Or character strings that look to humans like they mean datetime values.

To use INTNX() function to move a datetime value to the end of day you need to use the DTDAY interval (instead of the DAY interval which is for date values).  SAS will set the time part to '23:59:59't, so one second before midnight. To get a difference in datetime values in hours you just subtract them to get the number of seconds and then divide by one hour to convert to the number of hours.

data want;
  set have (rename=(start_date=start_dt stop_date=stop_dt));
  if missing(stop_dt) then stop_dt = intnx('dtday',stop_dt,0,'e');
  hours = (stop_dt - start_dt) / '01:00't ;
run;

PS I would avoid calling a datetime value a DATE.  That will cause confusion.

[mkeintz]

You've got good recommendations to use INTNX with the 'dtday' interval measure and the 'e' alignment, as in:

  stop_date=intnx('dtday',state_date,0,'e');

This will generate the 59th second of the 59th minute of 11PM.

But your example uses only whole minutes.  If you wish to preserve granularity by minute, just be aware that you can easily adjust the function by subtracting 59 seconds from the value generated above:

  stop_date=intnx('dtday',start_date,0,'e') - '00:00:59't;