SAS從以前的結果中減去最近的結果-有解無憂

我想修改函式代碼以減去結果，例如從前一個減去今天的 Run_date。但是，我的觀點是它應該只從相同的 POLICY_VIntage 中減去結果，即當前 (rund_date) 2021.01 - 先前 (2021.01)。不像下圖，例如 2021.08 - 2021.09 [![enter image description here][1]][1]

我想要一些這樣的 SAS 從以前的結果中減去最近的結果

  [1]: https://i.stack.imgur.com/T7k8Y.png


proc sql;   
create table policy_vintage_weekly as
  select 
    policy_vintage
    ,count(NRB) as number_policy
    ,today() as Run_Date format weeku.
  from PolisyEnd
  where POLIS= "W"
  group by policy_vintage
;
quit;

data policy_vintage_weekly_all;
set
  _work.policy_vintage_weekly (where=(run_date ne today()))
  policy_vintage_weekly
;
by policy_vintage run_date;
run;

%if &syscc. = 0
%then %do;


data _work.policy_vintage_weekly;
set policy_vintage_weekly_all;
by policy_vintage;
diff_value = dif(number_policy);
if not last.policy_vintage then diff_value = .;
run;
%end;
/* print report */
proc print data=_work.policy_vintage_weekly noobs;
var policy_vintage number_policy run_date diff_value;
run;

uj5u.com熱心網友回復：

使用 SQL 按策略年份連接當天的資料和最近前一天的資料，然后減去這兩個值。

示例資料：

data have;
    input run_date:date9. policy_vintage$ total;
    format run_date date9.;

    datalines;
02NOV2021 A 100
02NOV2021 B 200
02NOV2021 C 300
;
run;

data history;
    input run_date:date9. policy_vintage$ total;
    format run_date date9.;

    datalines;
01NOV2021 A 10
01NOV2021 B 20
01NOV2021 C 30
02NOV2021 A 100
02NOV2021 B 200
02NOV2021 C 300
;
run;

解決方案：

proc sql noprint;
    create table want as
        select today.policy_vintage
             , today.total as total_today
             , prior.total as total_prior
             , today.total - prior.total as diff
        from have as today
        LEFT JOIN
             (select *
              from history
              where run_date < today()
              having run_date = max(run_date)
             ) as prior
        ON today.policy_vintage = prior.policy_vintage
   ;
quit;

輸出：

policy_vintage  total_today total_yesterday diff
A               100         10              90
B               200         20              180
C               300         30              270

uj5u.com熱心網友回復：

這只需要在每個 policy_vintage 的第一個上完成。

data _work.policy_vintage_weekly;
set policy_vintage_weekly_all;
by policy_vintage;
diff_value = dif(number_policy);
if first.policy_vintage then diff_value = .;
run;

旁白：如果您的流程有一天中斷并且您需要在同一天重新運行它會發生什么？如果有部分負載會發生什么？

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/346150.html

標籤：sql SAS

上一篇：可以創建一個回傳多列的函式嗎？

下一篇：更好的SQL風格？