SAS SQL - How to get cumulative sum in past X months

Question

I have a table like this one:

I would like to calculate cumulative sum for column "total" for each cust_id in last x months, based on date_ini and today, so my output would be :

Could you help me doing this in sas / proc sql?

Thanks!

Answer 1

Cumulative results can be computed with SQL using a self-join with grouping and range limiting criteria.

Example:

data have;
  call streaminit(2021);
  do id = 1 to 10;
    do date = '01jan2017'd to '31dec2020'd;
      x = rand('integer', 10);
      if rand('uniform') < 0.20 then output;
    end;
  end;
  format date date9.;
run;

proc sql;
  create table want as
  select 
    self.id,
    self.date,
    self.x,
    count(earlier.date) as date_count,
    sum(earlier.x) as x_sum_36mo
  from 
    have as self
  left join 
    have as earlier
  on
    self.id = earlier.id and
    earlier.date between self.date and intnx('month', self.date, -36)
  group by
    self.id, self.date, self.x
  ;

A DOW loop in SAS DATA step is more performant, and multiple cumulative periods can be computed during a single pass through the data. Such code can be found in earlier questions.

Answer 2

It just looks like you want use CASE to decide whether the TOTAL contributes to your new sum.

So assuming your reference date is the current date you might use something like this.

create table want as
  select cust_id
       , sum(case
             when (date_ini >= intnx('month',today(),-1,'b') then total
             else 0 end) as total_last_month
       , sum(case
             when (date_ini >= intnx('month',today(),-36,'b') then total
             else 0 end) as total_last_36months
  from have
  group by cust_id
;

But I am not sure I would call those cumulative sums.