topic Re: Record Count Per Hour in New SAS User

Record Count Per Hour

gbond21 — Tue, 30 Apr 2019 17:39:33 GMT

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!

Re: Record Count Per Hour

ballardw — Tue, 30 Apr 2019 19:14:47 GMT

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

Re: Record Count Per Hour

gbond21 — Tue, 30 Apr 2019 19:49:10 GMT

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.

Re: Record Count Per Hour

Reeza — Tue, 30 Apr 2019 20:03:44 GMT

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.

Re: Record Count Per Hour

gbond21 — Tue, 30 Apr 2019 20:18:53 GMT

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

Re: Record Count Per Hour

Reeza — Tue, 30 Apr 2019 20:36:35 GMT

How big is your original data set?

Re: Record Count Per Hour

gbond21 — Tue, 30 Apr 2019 20:41:05 GMT

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

Re: Record Count Per Hour

ballardw — Tue, 30 Apr 2019 22:07:24 GMT

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

Re: Record Count Per Hour

gbond21 — Wed, 01 May 2019 00:13:23 GMT

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.

Re: Record Count Per Hour

Reeza — Wed, 01 May 2019 14:52:07 GMT

Then the code in the link is correct for what you're trying to do here.

Re: Record Count Per Hour

ballardw — Wed, 01 May 2019 15:03:11 GMT

@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-data-AKA-generate/ta-p/258712 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.

Re: Record Count Per Hour

gbond21 — Wed, 01 May 2019 16:42:46 GMT

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:

Re: Record Count Per Hour

Reeza — Wed, 01 May 2019 16:49:32 GMT

Please provide sample data for us where that occurs.

Re: Record Count Per Hour

gbond21 — Wed, 01 May 2019 17:17:37 GMT

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

Re: Record Count Per Hour

gbond21 — Thu, 02 May 2019 13:21:40 GMT

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.

Re: Record Count Per Hour

Reeza — Thu, 02 May 2019 14:48:18 GMT

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

Re: Record Count Per Hour

gbond21 — Fri, 03 May 2019 13:18:44 GMT

Thanks @Reeza, you have been super helpful-appreciate the knowledge sharing!

Re: Record Count Per Hour

gbond21 — Fri, 03 May 2019 15:53:31 GMT

Hi Ball, quick question for you-in the code above what is the purpose of "day*n='' ? Thanks!