topic Re: Summary of Minutes per Day Between Two DateTime values? in SAS Programming

Summary of Minutes per Day Between Two DateTime values?

shellp55 — Fri, 12 Jun 2020 17:41:37 GMT

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.

Re: Summary of Minutes per Day Between Two DateTime values?

Reeza — Fri, 12 Jun 2020 17:48:41 GMT

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/386324

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

Re: Summary of Minutes per Day Between Two DateTime values?

shellp55 — Fri, 12 Jun 2020 19:18:23 GMT

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.

Re: Summary of Minutes per Day Between Two DateTime values?

Reeza — Fri, 12 Jun 2020 19:25:13 GMT

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 🙂

Re: Summary of Minutes per Day Between Two DateTime values?

shellp55 — Fri, 12 Jun 2020 19:35:43 GMT

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.

Re: Summary of Minutes per Day Between Two DateTime values?

Reeza — Fri, 12 Jun 2020 19:52:52 GMT

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).

Re: Summary of Minutes per Day Between Two DateTime values?

shellp55 — Fri, 12 Jun 2020 20:15:41 GMT

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.

Re: Summary of Minutes per Day Between Two DateTime values?

Reeza — Fri, 12 Jun 2020 20:25:58 GMT

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.

Re: Summary of Minutes per Day Between Two DateTime values?

ballardw — Fri, 12 Jun 2020 20:27:47 GMT

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.

Re: Summary of Minutes per Day Between Two DateTime values?

shellp55 — Fri, 12 Jun 2020 20:46:25 GMT

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!