SAS subtracting the most recent results from the previous ones-CodePudding

I would like to modify the function code so as to Subtract the result, e.g. today's Run_date from the previous one. However, my point is that it should only subtract the results from the same POLICY_VIntage, i.e. current (rund_date) 2021.01 - previous (2021.01). Not like the picture below where 2021.08 - 2021.09 for example [![enter image description here][1]][1]

i would like some like that

  [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;

CodePudding user response：

This only needs to be done on the first of each 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;

Aside: What happens if your process breaks one day and you need to re-run it the same day? What happens if there's a partial load?

CodePudding user response：

Use SQL to join the current day's data and most recent prior day's data by policy vintage, then subtract the two values.

Example data:

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;

Solution:

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;

Output:

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