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 ?
... View more
