时间点计算＃2

发布于 2025-02-02 21:23:00 字数 724 浏览 2 评论 0原文

事件号	接收到的日期	截止日期至截止日期	为截止日期为
2021	年1月1日2011年	1月1日2021	31
222	01 2021年1	月7日2021年2月7日2021	37
333	01 2021
444	01 2021年1月1日，

我想计算平均天数时间点。因此，使用上面的示例，可以说在2021年2月底，您会查看

收到的日期必须少于公制日期（这种情况下的度量日期为2021年2月），
截止日期必须比度量日期更大，或者是公制日期（如果关闭日期为空，则收到的时间的计算将是从接收日期到度量日期）

使用上面的前两个事件的示例，但最后两个事件将是，因此，两者之间的不同之处2021年1月1日和2021年2月28日为58，除以2除以2 1月31日之前没有关闭事件，因此（31*4） / 4。我将在2020年1月至2021年1月重复此事

原文

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

Received date has to be less then the metric date (the metric date in this case being Feb 2021)
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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

深空失忆 2025-02-09 21:23:00

具有缺失值的未封闭事件的编码将需要IF语句的情况以正确计算给定的asof日期的日期。

示例：

针对数据集中存储的各种asof日期计算的日期。

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;

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;