Summary of Minutes per Day Between Two DateTime values?

[shellp55]

Hello

I would like to calculate the minutes per day between two date/time values.  For the first card below, the resulting data with one line per day for each chartno and acctno:

4/1/19 - 111111 121212 960 minutes

4/2/19 - 111111 121212 1440 minutes

4/3/19 - 111111 121212  900 minutes

data grp1;
input @1  chartno 	$6.
	  @7  acctno  	$6.
	  @13 admdate   mmddyy10.
	  @23 disdate   mmddyy10.
	  @33 icuadmdttm  anydtdtm.
	  @49 icudisdttm  anydtdtm.
	  ;
	  
cards;
111111 12121204/01/201904/10/201901Apr19 08:00:0003Apr19 15:00:00
11111112121204/01/201904/10/201907Apr19 13:00:0010Apr19 13:00:00
55555516161608/01/201908/13/201901Aug19 08:00:0005Aug19 11:30:00
55555516161608/01/201908/13/201910Aug19 15:35:0013Aug19 10:00:00
run; 

Any and all assistance greatly appreciated, thanks.

[Reeza]

INTCK with minutes + LAG() to get previous value if it's across rows.

https://communities.sas.com/t5/General-SAS-Programming/INTCK-to-compute-minutes-between-dates/td-p/3...

If you have SAS/ETS PROC TIMESERIES is also an option.

[shellp55]

Hey Reeza!

Thanks for your response.  I am not sure how I would use the LAG because I'm not understanding why I would be accessing a previous value?  Can you please explain further?  Thanks.

[Reeza]

I don't know if you need it. If you're just doing difference between the variables in the same row you don't need it. If you are crossing rows you would need it.

The data kinda runs together in your post so I didn't do the mental arithmetic to see how your desired answer aligns with the data you provided (I'm being lazy). INTCK() is likely all you need 🙂

[shellp55]

I think you are right but not sure how to accomplish this.  I basically want an occupancy report but instead of just dates I would want date/times so that I can calculate total minutes per each day of occupancy in the ICU.

The original report for occupancy using ICU dates is a do loop where do=icuadmdate to icudisdate-1;  In this instance I assume I want to first create the datetime fields and then create the calculation for the minutes.  I'm just not clear how I do that.

[Reeza]

Are you doing this just so you can count days overall? You can do that or there's another approach if that's what you're doing. Or do you want a record per day for another reason?

This is an occupancy report or the 'medicine cabinet' problem - how many drugs (patients in your case) do you have at x time in the medicine cabinet (ICU or some location in your case).

[shellp55]

Hi Reeza

Finance wants to know how many minutes per day utilized by patients in the ICU per each day for a case costing exercise.   So whereas occupancy allows us to create a record per day between admit and discharge date, I would like minutes related to the icu admit date/time per each day.

So, for the first example, there would be 3 days showing (Apr 1 to 3) and the minutes would be calculated as 0800 hours to midnight (960 minutes) for April 1 because the patient entered the ICU at 0800, then the full day of April 2 = 1440 minutes, then day 3 would be 00:01 to 1500 hours (900 minutes) where 1500 is when they are discharged from ICU.  These would be 3 rows of data for the same chartno/acctno.  

Thanks.

[Reeza]

So the method I use typically is the same as yours, expand to daily and then summarize via PROC FREQ. But I think @freelanceReinhard has a solution that doesn't do that. Or maybe @novinsrin 🙂

That one is cumbersome and inefficient. I'm fairly certain he's posted one for days but not sure about day times.

[ballardw · Accepted solution]

Here is one way. Note that I changed the informat on the first date time value because with no delimiter and no specified length for the informat it was reading the day of the second datetime value as part of the seconds in the first.

data want;
   set grp1;
   basetime = icuadmdttm;
   do while (basetime < icudisdttm);
      minutes = intck('minute',basetime,min(intnx('dtday',basetime,1,'B'), icudisdttm));
      output;
      basetime=intnx('dtday',basetime,1,'B');
   end;
   format basetime datetime20.;
run;

It took be a bit to figure out that you want the individual days "contribution" as a separate record (I think) with the minute counts.

Key here is the INTNX to advance a current date time to the beginning of the next interval (if you use end of the current interval minutes are short by one) and using the earlier of the advanced datetime or the end of the period in the other variable. Then using the INTCK function to get the values between.

Basic "do while" stuff of resetting the loop control variable .

You can likely drop at least the basetime variable but this way you can look at how the values change. Since the OUTPUT comes before the basetime reassignment you see the value at the start of each loop in the dataset.

[shellp55]

Thanks to the both of you for your speedy response!  Ballardw's solution works exactly as I required, thank you so much!  Have a great weekend everyone!