Hi again. After some research, I found that intnx function is the another handy method to solve the problem. I hope it would be useful. Input Table ID COHORT ALLOCATE USAGE 1 1001 201601 15.049 6.766 2 1001 201602 13.507 5.088 3 1001 201603 11.872 9.352 4 1001 201604 13.791 7.404 5 1001 201605 0 8.866 6 1001 201606 16.906 8.897 7 1001 201607 13.706 0 8 1001 201608 11.543 0 9 1001 201609 12.457 0 10 1001 201610 14.250 0 11 1001 201611 11.148 0 12 1001 201612 19.648 0 Codes For the solution, DATE column was created for each observation in Cohort column. (201601=31.01.2016, 201602=29.02.2016 etc.) proc sql; create table work.vertical_sum as select ID, COHORT, DATE, ALLOCATE, SUM(USAGE) as TOTAL_USAGE from (select t1.ID, t1.COHORT, t1.DATE, t1.ALLOCATE, t2.USAGE from work.vertical as t1 left join work.vertical as t2 on (t1.ID=t2.ID and t2.DATE between intnx('month', t1.DATE, 0) and intnx('month', t1.DATE, 6))) group by ID, COHORT order by ID, COHORT; create table work.final_sum as select t1.ID, t1.COHORT, t1.ALLOCATE, t2.TOTAL_USAGE, Case When t1.ALLOCATE > 0 and t2.TOTAL_USAGE > 0 Then 1 Else 0 End as FINAL from work.vertical_sum as t1 inner join work.vertical_sum as t2 on t1.ID=t2.ID and t1.COHORT=t2.COHORT order by t1.ID, t1.COHORT; quit; proc sort data=work.final_sum out=work.final_sum nodupkey; by cohort; run; Result Table ID COHORT DATE ALLOCATE TOTAL_USAGE FINAL 1 1001 201601 31.01.2016 15.049 46.373 1 2 1001 201602 29.02.2016 13.507 39.607 1 3 1001 201603 31.03.2016 11.872 34.519 1 4 1001 201604 30.04.2016 13.791 25.167 1 5 1001 201605 31.05.2016 0 17.763 0 6 1001 201606 30.06.2016 16.906 8.897 1 7 1001 201607 31.07.2016 13.706 0 0 8 1001 201608 31.08.2016 11.543 0 0 9 1001 201609 30.09.2016 12.457 0 0 10 1001 201610 31.10.2016 14.25 0 0 11 1001 201611 30.11.2016 11.148 0 0 12 1001 201612 31.12.2016 19.648 0 0
... View more
