Sum column based on "date between"

[tsqpcs]

Hi everyone!

I am new to SAS and I am trying to build some query using proc sql.

I have the following code in sql (teradata syntax):

create multiset volatile table mvt_data, no log
( date1     date format 'yyyy-mm-dd'
, flag1     byteint
,cust_id    varchar(25)
)
primary index (date1)
on commit preserve rows;

insert into mvt_data values (date '2020-01-03', 1, 'A');
insert into mvt_data values (date '2020-02-04', 0, 'A');
insert into mvt_data values (date '2020-04-05', 0, 'B');
insert into mvt_data values (date '2020-01-19', 1, 'C');
insert into mvt_data values (date '2020-03-20', 1, 'B');
insert into mvt_data values (date '2020-06-20', 1, 'D');

I would like to have the following output:

For each cust_id, YYYYMM of date1 sum the Flag1 and sum Flag1 cumulative with respect to last 6 months.

In same syntax I would do :

with cte_data_ts (ts1, Flag1) as
(
select cast(date1 as timestamp(0)), Flag1
  from mvt_data
)
  ,  cte_gbt (YearMonth, SumFlag1, cust_id) as
(
  select cust_id                                          as cust_id
       ,to_char(begin($TD_TIMECODE_RANGE) at 0, 'yyyymm') as YearMonth
       , sum(flag1)                                       as SumFlag1
    from cte_data_ts
group by time(cal_months(1))
   using timecode(ts1)
    fill (0)
)
  select cust_id, YearMonth, SumFlag1
       , sum(SumFlag1) over(order by cust_id, YearMonth asc rows between 6 preceding and current row) as SumFlag1_last6Months
    from cte_gbt;

Unfortunatelly I know that over() does not exists in proc sql, so can anyone help me achieve the same result ?

[ballardw]

Almost anytime you have a need for something like "6months" related to date values the variables used should be dates and not character values so the SAS functions for determining intervals between values can be used.

But we would need an example SAS data set as the "insert" code you show won't allow us to build a data set.

This looks like possibly something Proc Expand might deal with. Do you have SAS/ETS available?

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

[tsqpcs]

Here's the data sample: 

data mvt_data;
  input date1 :yymmdd. flag1 cust_id;
  format date1 yymmdd10.;
cards;
2020-01-03 1 A
2020-02-04 0 A
2020-04-05 0 B
2020-01-19 1 C
2020-03-20 1 B
2020-06-20 1 D
;