topic Re: One Record Per Hour of Day? in SAS Programming

One Record Per Hour of Day?

shellp55 — Mon, 11 Apr 2016 18:11:19 GMT

Hello

I am using SAS 9.3. I have a data set where one row of data is one emergency department visit. I would like to calculate number of patients in the ED during each hour of the visit.

I believe this is referred to as "occupancy" calculation and I've done it before when looking for daily rates, not hourly, for inpatient stays as per below:

data want;
set mydata;
do date=admitdate to disdate;
format date ddmmyy10.;
output;
end;
run;

How would I do this hourly i.e. admitdatetime to dischdatetime but how do I specify only the hour i.e. if 13:30 then I want the hour to be 13? Thanks.

Re: One Record Per Hour of Day?

Reeza — Mon, 11 Apr 2016 18:57:51 GMT

You can use the hour() function to extract the hour.

If your data is datetime, you're best bet is to round the data to the hour, you decide on up/down and then use a similar loop.

Because datetimes are the number of seconds since Jan 1, 1960 you can loop by an hour = 60 sec* 60 mins = 360

Re: One Record Per Hour of Day?

ballardw — Mon, 11 Apr 2016 20:32:41 GMT

See if this gives you a starting point:

data example;
   admitdate = '01JAN2016 12:23:40'dt;
   disdate = '02JAN2016 08:15:00'dt;
   format admitdate disdate datetime20.;
   Do Occupanthour = (dhms(datepart(admitdate),hour(admitdate),0,0)) to dhms(datepart(disdate),hour(disdate),0,0) by 3600;
      output;
   end;
   format OccupantHour datetime20.;
run;

It creates a new variable indicating the start hour for each hour in the interval.

I suspect that you do not what to just look at time as admittance to discharge crossing the date boundary is common occurance.

You could also use Hour(Occupanthour) to get a single time of day with the hour for a report depending on your needs.

Re: One Record Per Hour of Day?

shellp55 — Mon, 11 Apr 2016 20:43:40 GMT

Thanks very much to both of you but I think Reeza's solution is the one I'm going with. As for round up round down, since a patient with a registration time of 06:27 and a left ED time of 1130 hours would be in the ED at 0600, 0700, 0800, 0900, 1000, and 1100 I think it is safe to produce a record per hour. Or do you see a downfall of that?

Re: One Record Per Hour of Day?

Reeza — Mon, 11 Apr 2016 20:49:06 GMT

You need to use the full datetime value to account for patients in ER after midnight and before midnight appropriately. Especially if you're going to do analysis by day of week.

Re: One Record Per Hour of Day?

shellp55 — Mon, 11 Apr 2016 21:01:29 GMT

Hi Reeza

Thanks but I'm not understanding your comments. What I was hoping to do was calculate volumes of ED patients by hour of day but not day itself. I would be interested in calculating before and after midnight as you suggested so can you please elaborate on what I have to do to for that to happen? Thanks.

Re: One Record Per Hour of Day?

ballardw — Mon, 11 Apr 2016 21:37:59 GMT

@Reeza and my comments involve proper assignment of the times in the incrementing process. If you do not consider the day of the week what happens when you increment from 10 at night (hour 22) to 8 in the morning (hour 8). Ignoring the date change would require much work to get appropriate assignments.

Here is a sort of worked example creating fake data to two different forms of reports.

data have (keep=admitdate disdate);
   /* generate some admission and discharge date time variables*/
   year=2015; /* for example all of the admits are in 2015*/
   format admitdate disdate datetime20.;
   do day= 1 to 20;
      do month=1 to 12;
         hour = floor(24*ranuni(4445));
         min  = floor(50*ranuni(1234));
         date = mdy(month,day,2015);
         admitdate=dhms(date,hour,min,0);
         /* random duration of stay*/
         duration = 60 + floor(3000*ranuni(7777));
         disdate = intnx('minute',admitdate,duration);
         output;
      end;
   end;
run;

data occupancy;
   set have;
   format admitdate disdate datetime20.;
   Do Occupanthour = (dhms(datepart(admitdate),hour(admitdate),0,0)) to dhms(datepart(disdate),hour(disdate),0,0) by 3600;
      HourOfDay = hour(OccupantHour);
      DayOfWeek = Weekday(datepart(OccupantHour));
      output;
   end;
   format OccupantHour datetime20.;
run;

Proc freq data=occupancy;
   Tables HourOfDay;
run;

proc tabulate data=occupancy;
   class DayOfWeek;
   class HourOfDay;
   tables HourOfDay,
          (DayOfWeek All)*n;
run;

Since this is fake data do not expect the result to match your actual distributions.

Re: One Record Per Hour of Day?

Reeza — Mon, 11 Apr 2016 22:10:25 GMT

Having done this analysis many times 😉 The next question is often day of week analysis by hour. Midnight on a Saturday is different than midnight on a Wednesday because of availability/access to Primary Care Providers.

Radar charts actually show this type of data well by the way, because of the cyclical nature of the data.

http://support.sas.com/documentation/cdl/en/graphref/63022/HTML/default/viewer.htm#a001572471.htm

Re: One Record Per Hour of Day?

Reeza — Mon, 11 Apr 2016 22:15:38 GMT

@ballardw code is appropriate...

http://www.health.alberta.ca/documents/PHC-Profile-EdmontonMillWoodsSouth-East.pdf

See page 21 of the report here for what we did, though I'm not sure I would recommend that again. Monday at 1 am for example is highest for that hour, but that's really Sunday morning where finding a doctor or Urgent Care would be very difficult.

Re: One Record Per Hour of Day?

shellp55 — Tue, 12 Apr 2016 13:50:32 GMT

Thank you so much for the response, Reeza and @bballard. Nice work on the publication you shared. The woman who is the head of the Alberta Health Authority used to be my Manager in Health Records!

Re: One Record Per Hour of Day?

shellp55 — Thu, 14 Apr 2016 00:53:02 GMT

Hello BallardW

Thanks for the code on this but I'm confused by the "Have" dataset because the returned dates were not what was in my dataset. For testing I extracted 1 day (April 1, 2015) of ED data. Running the "Have" dataset and the result was cases that had admit dates beyond April 1, 2015. Or should I just be running the occupancy portion?

Thanks.

Re: One Record Per Hour of Day?

Reeza — Thu, 14 Apr 2016 01:27:39 GMT

ER stays have been known to go up to 36 hours so you could go to April 2 for occupancy.