topic Re: Cumulative Sum for the current row and the 12 next rows in SAS Programming

Cumulative Sum for the current row and the 12 next rows

Nasser_DRMCP — Wed, 10 Apr 2019 13:36:47 GMT


data have ; 
  input product $1. month amount ;
datalines; 
A 1601 1
A 1602 2
A 1603 3
A 1604 4
A 1605 5
A 1606 6
A 1607 7
A 1608 8
A 1609 9
A 1610 10
A 1611 11
A 1612 12
A 1701 13
A 1702 14
A 1703 15
A 1704 16
A 1705 17
A 1706 18
A 1707 19
A 1708 20
A 1709 21
A 1710 22
A 1711 23
A 1712 24
B 1601 1
B 1602 2
B 1603 3
B 1604 4
B 1605 5
B 1606 6
B 1607 7
B 1608 8
B 1609 90
B 1610 100
B 1611 110
B 1612 120
B 1701 130
B 1702 140
B 1703 150
B 1704 160
B 1705 170
B 1706 180
B 1707 190
B 1708 200
B 1709 210
B 1710 220
B 1711 230
B 1712 240
;
run;

Hello,

this is my input dataset.

I try to get a column that woulb be the cumulative sum of the current row and the next 12.
And that for each product.

for example , for product A and month 201609 , the result should be 195 because that is the sum of 9,10,11.....20,21
for example , for product B and month 201609 , the result should be 2170 because that is the sum of 90,100....210,220
thanks in advance for your help

regards,

Nasser

Re: Cumulative Sum for the current row and the 12 next rows

Astounding — Wed, 10 Apr 2019 13:46:32 GMT

What would you like the results to be for

B 1710 220

when you only have a few more months of data?

Re: Cumulative Sum for the current row and the 12 next rows

Nasser_DRMCP — Wed, 10 Apr 2019 13:50:04 GMT

hello

whenever there are less than 12 month , no need so I would like get null

thanks

Re: Cumulative Sum for the current row and the 12 next rows

Ksharp — Wed, 10 Apr 2019 13:51:02 GMT

data have ; 
  input product $1. month : yymmn4. amount ;
  format month yymmn6.;
datalines; 
A 1601 1
A 1602 2
A 1603 3
A 1604 4
A 1605 5
A 1606 6
A 1607 7
A 1608 8
A 1609 9
A 1610 10
A 1611 11
A 1612 12
A 1701 13
A 1702 14
A 1703 15
A 1704 16
A 1705 17
A 1706 18
A 1707 19
A 1708 20
A 1709 21
A 1710 22
A 1711 23
A 1712 24
B 1601 1
B 1602 2
B 1603 3
B 1604 4
B 1605 5
B 1606 6
B 1607 7
B 1608 8
B 1609 90
B 1610 100
B 1611 110
B 1612 120
B 1701 130
B 1702 140
B 1703 150
B 1704 160
B 1705 170
B 1706 180
B 1707 190
B 1708 200
B 1709 210
B 1710 220
B 1711 230
B 1712 240
;
run;

proc sql;
create table want as
select *,(select sum(amount) from have where product=a.product and
month between a.month and intnx('month',a.month,12)) as want
 from have as a;
quit;

Re: Cumulative Sum for the current row and the 12 next rows

Nasser_DRMCP — Wed, 10 Apr 2019 14:14:51 GMT

Hello Ksharp

sorry but it does not seem to work.maybe my need was not clear enough.

in the output table want, the resut for product A and 1609 month is 264 but 195 is atended coming from sum of 9+10+11+12+13+14+15+16+17+18+19+20+21

Re: Cumulative Sum for the current row and the 12 next rows

novinosrin — Wed, 10 Apr 2019 14:23:31 GMT







data have ; 
  input product $1. month amount ;
datalines; 
A 1601 1
A 1602 2
A 1603 3
A 1604 4
A 1605 5
A 1606 6
A 1607 7
A 1608 8
A 1609 9
A 1610 10
A 1611 11
A 1612 12
A 1701 13
A 1702 14
A 1703 15
A 1704 16
A 1705 17
A 1706 18
A 1707 19
A 1708 20
A 1709 21
A 1710 22
A 1711 23
A 1712 24
B 1601 1
B 1602 2
B 1603 3
B 1604 4
B 1605 5
B 1606 6
B 1607 7
B 1608 8
B 1609 90
B 1610 100
B 1611 110
B 1612 120
B 1701 130
B 1702 140
B 1703 150
B 1704 160
B 1705 170
B 1706 180
B 1707 190
B 1708 200
B 1709 210
B 1710 220
B 1711 230
B 1712 240
;
run;

data want;
if _n_=1 then do;
if 0 then set have;
  dcl hash H (dataset:'have',ordered: "A") ;
   h.definekey  ("product","month") ;
   h.definedata ("amount") ;
   h.definedone () ;
   dcl hiter ih('h');
end;
set have(drop=amount);
by product month;
RC = IH.SETCUR() ;
_p=product;
do count = 1 to 13 while (RC = 0 and _p=product) ;
sum=sum(sum,amount);
  RC = IH.next() ;
 end ;
if count < 13 then sum=0;
drop rc count product;
rename _p=product;
run;