Point in time calculation #2

Question

Incident number	Received date	Closed Date	Time taken to close
111	01 Jan 2021	01 Feb 2021	31
222	01 Jan 2021	07 Feb 2021	37
333	01 Jan 2021
444	01 Jan 2021

I wanted to calculate the average number of days an incidents have been open at a point in time. So using the example above lets say at the end of Feb 2021 you would look at

1. Received date has to be less then the metric date (the metric date in this case being Feb 2021)
2. Closed date has to be either greater then metric date or empty (if the closed date is empty then the calculation for time taken to close would be from the received date to the metric date)

Using the example above the first two incidents would not been included, however the last two would be and so the different between 01 Jan 2021 and 28th Feb 2021 is 58 , divide that number by 2 as that’s the number of incidents included in the calculation to give you an average of 58. Using the same example the calculation for Jan 2021 would be 31 days for each incident as no incident was closed by 31st Jan, so its (31*4) / 4. I would be repeating this for Jan – Dec 2020 and 2021

Answer 1

The encoding of an unclosed incident with a missing value will require a case of if statement to properly compute the days open metric on a given asof date.

Example:

The days open average is computed for a variety of asof dates stored in a data set.

data have;
  call streaminit(2022);
  do id = 1 to 10;
    opened = '01jan2021'd + rand('integer', 60);
    closed = opened + rand('integer', 90);
    if rand('uniform') < 0.25 then call missing(closed);
    output;
  end;
  format opened closed yymmdd10.;
run;

data asof;
  do asof = '01jan2021'd to '01jun2021'd-1;
    output;
  end;
  format asof yymmdd10.;
run;

proc sql;
  create table averageDaysOpen_asof
  as
  select 
    asof
  , mean (days_open) as days_open_avg format=6.2
  , count(days_open) as id_count
  from
    ( select asof
    , opened
    , closed
    , case 
        when closed is not null and asof between opened and closed then asof-opened
        when closed is     null and asof > opened                  then asof-opened
        else .
      end as days_open
    from asof
    cross join have
    )
  group by asof
  ;
quit;