Record Count Per Hour

[gbond21]

I'd like to calculate the number of patients currently within an Emergency Room by hour and I'm having trouble conceptualizing an efficient code.

I have two time variables, 'Check In Time' and 'Release Time'. These date/time variables are obviously arbitrary and the 'release time' variable will come after the 'check in time variable'. So, I'm taking into consideration check in time and release time to figure out the counts per hour, not just check in time. 

Example data looks like this: 

 Patient_ID    'Check In Time'                 'Release Time'
 01           2015-08-01 02:49:00         2015-08-01 08:29:00
 02           2015-08-02 01:30:00         2015-08-02 14:29:00
 03           2015-08-02 21:30:00         2015-09-02 01:20:00

I would like the output for a given day to look something like this:

    Hour        Midnight   1am   2am   3am    4am.....
    # of Pts      34       56     89    23     29

So for example, at 1am there were 56 patients currently in the ED -when considering both checkin and release times.

My initial thought is to: 1) round the time variables 2) Write a code a code the looks something like this...

  data EDTimesl;
  set  EDDATA;
  if checkin = '1am' and release = '2am' then OneAMToTwoAM = 1;
  if checkin = '1am' and release = '3am' then OneAMToTwoAM = 1;
  if checkin = '1am' and release = '3am' then TwoAMToThreeAM = 1;
  ....
  run; 

This, however, gives me pause because I feel there is a more efficient method!

I see there is a previous post that touches on this topic but I'm having trouble checking if that code is correct, as it applies to my data. 

https://communities.sas.com/t5/SAS-Programming/One-Record-Per-Hour-of-Day/td-p/262983

Thanks in advance!

[ballardw]

Please describe how you are " considering both checkin and release times" when determining your count as it relates to any particular "hour".

If your values are indeed SAS datetime values then you have to consider the continuous nature of time and I am not sure what you would actually intend by :

if checkin = '1am' and release = '2am' then OneAMToTwoAM = 1;

What times of day would be counted in your '1am' or '2am' value?

The functions INTNX or INTCK are used to build or count intervals of time but an idea of exactly what sort of comparisons you want are needed.

And I don't see anything that comes close to addressing the date portion. What if the release is two days later? How would that affect your "count".

[gbond21]

Hi, 

The Checkin and Release times are date/time variables and I would need to determine the time span within the two variables per record. The code I provided, is something I do not intend to use-I was just pointing it out because it's an option but not a sustainable option. 

I would like to round times of day to the nears hour e.g. 1:20AM would equal 1am; 3:45PM would equal 4PM. 

If release date is more than 48 hours I would count that data as missing. 

[Reeza]

SAS date and times are numeric, time is stored as # of seconds, dates as # of days. So you can use the floor function to get each date/time point to be for a specific hour using some basic math operations. 

You still need to create a record for each hour or you can loop through and do the calculation iteratively, updating an array for example. Do you care about the date portion? Or do you only need hours of the day, in general. 

For your three record data set there please show the exact output you're expecting. 

---

@gbond21 wrote:

Hi, 

The Checkin and Release times are date/time variables and I would need to determine the time span within the two variables per record. The code I provided, is something I do not intend to use-I was just pointing it out because it's an option but not a sustainable option. 

I would like to round times of day to the nears hour e.g. 1:20AM would equal 1am; 3:45PM would equal 4PM. 

If release date is more than 48 hours I would count that data as missing. 

---

[gbond21]

Hi Reeza, 

Thank you!

Only hours of the day; however it would be useful to aggregate the data by day of the week. Id also like to be able to query by date, so for example if I have a data set for 2019-add a if statement that would would only contain dates in specific months. 

Greg 

The exact output would look like this if making a table by day is possible. It would show the number of patients currently in the ED by hour.

Hour      Monday    Tuesday   Wednesday ....

0                  345       490               342

1am             245       250               362

2am             146       680               742

3am             375       100               241

4am             348       250               347

[Reeza]

How big is your original data set?

[gbond21]

I have roughly 80,000 records within the dataset, thanks again. 

[ballardw]

---

@gbond21 wrote:

Hi Reeza, 

Thank you!

Only hours of the day; however it would be useful to aggregate the data by day of the week. Id also like to be able to query by date, so for example if I have a data set for 2019-add a if statement that would would only contain dates in specific months. 

Greg 

The exact output would look like this if making a table by day is possible. It would show the number of patients currently in the ED by hour.

Hour      Monday    Tuesday   Wednesday ....

0                  345       490               342

1am             245       250               362

2am             146       680               742

3am             375       100               241

4am             348       250               347

---

Consider:

data example;
   input x datetime19.;
   format x datetime19.;
   /* round to hour: 3600 seconds*/
   y= round(x,3600);
   format y datetime19.;
datalines;
21APR2019:12:25:15
21APR2019:12:30:15
21APR2019:12:00:00
21APR2019:23:45:10
;
run;

proc freq data=example;
   table x y;
   format x dtwkdatx9. y timeampm5.;
run;

Formats will actually round values as well but I am still not sure if I understand all you needs. Choice of format for display is very flexible with date, time and datetime values. Plus you can usually role your own with at custom Picture format.

[gbond21]

Thanks. 

There are two time variables checkin and release. For example, a patient checks in in at 10am and leaves at 4pm. So that patient would be counted at 10am, 11am, noon, 1pm, 2pm, and 3pm. I am not looking just at registered visits, rather the entirety of a patients stay. 

[ballardw · Accepted solution]

---

@gbond21 wrote:

Thanks. 

There are two time variables checkin and release. For example, a patient checks in in at 10am and leaves at 4pm. So that patient would be counted at 10am, 11am, noon, 1pm, 2pm, and 3pm. I am not looking just at registered visits, rather the entirety of a patients stay. 

---

Quite a different bit of information that was missing in your original problem description.

data have;
   informat patient_id $2. checkintime releasetime datetime18.;
   format checkintime releasetime datetime18.;
   input patient_id checkintime releasetime   ;
datalines;
01 01AUG2015:02:49:00        01AUG2015:08:29:00
02 01AUG2015:01:30:00        02AUG2015:14:29:00
03 02AUG2015:21:30:00        02SEP2015:01:20:00
;
run;

data want;
   set have;
   counttime = round(checkintime,3600);
   format counttime datetime18.;
   do while (counttime le round(releasetime,3600) );
      hr = timepart(counttime);
      day = weekday(datepart(counttime));
      output;
      counttime= intnx('hour',counttime,1,'B');
   end;
run;


proc tabulate data=want;
   class hr day;
   format hr timeampm5.;
   table hr='', day*n=''
         / box=hr
   ;
   label hr='Hour'
         day= 'Day of week'
   ;
run;

There is no "hour 0" basically that is midnight or 12AM.

This shows the weekday number , 1= Sunday. You could use a custom format to show words.

Or we could play some games with date functions.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

The time values you showed are not in a standard SAS datetime format so I have to assume that the pasted values were from some other program.

[gbond21]

Thanks again. Sorry in advance for my lack of understanding but...

When I run that code, the output doesn't necessarily align with the data. For example, there is only one record which covers 12 AM; however, the output shows that there were 5 records on day 1-4 which had data for 12AM:

[Reeza]

Please provide sample data for us where that occurs.

[gbond21]

NVM disregard that response, I did not see one of the release dates was in September!

[gbond21]

This code worked beautifully, thank you!

My next question would be how to adapt this to show averages. For example, if I ran this code for April 2019, I would like to know on average, how many patients were in the ED at 12am on Monday's, Tuesday's etc. 

[Reeza]

Use PROC MEANS on the resulting table with day/week/hour as your class or by variables.