Solved
New Contributor
Posts: 3

# How do I create a rolling sum for the last 30 days?

Hi.. I would require to create a rolling sum of sales in the last 7 days for each product type as of each date.

For example,

prod_group      date           sales

AAAA          01jan2015     10

AAAA          03jan2015     7

AAAA          04jan2015     16

AAAA          08jan2015     12

The data goes on like this.

The output I desire is as follows:

prod_group      date           sales     sales_in_last7days

AAAA          01jan2015     10          0 (because no sales before 01jan2015)

AAAA          03jan2015     7          10 (only 1 day available before 03jan2015, that is the 01jan2015)

AAAA          04jan2015     16        17 (2 days are available so 10 + 7 = 17)

AAAA          08jan2015     12        33

Accepted Solutions
Solution
‎07-16-2015 10:02 PM
Frequent Contributor
Posts: 88

## Re: How do I create a rolling sum for the last 30 days?

I'm assuming you mean the last 7 day rather than the 30 days in your title.

If you can ensure records for every day, unlike your example, a solution is relatively easy, and based on an example in the doco for the LAG function:

data have;
input prod_group \$
date :date9.
sales ;
format date date9.;
cards;
AAAA          01jan2015     10
AAAA          02jan2015     7
AAAA          03jan2015     1
AAAA          04jan2015     0
AAAA          05jan2015     4
AAAA          06jan2015     6
AAAA          07jan2015     2
AAAA          08jan2015     7
AAAA          09jan2015     6
AAAA          10jan2015     2
BAAA          01jan2015     1
BAAA          02jan2015     2
BAAA          03jan2015     3
;
run;
data want (keep=prod_group date sales sales_last7days);
set have;
by prod_group date;
retain sales_last7days  0;
array x(*) sale1-sale7;

sale1 = lag1(sales);
sale2 = lag2(sales);
sale3 = lag3(sales);
sale4 = lag4(sales);
sale5 = lag5(sales);
sale6 = lag6(sales);
sale7 = lag7(sales);
if first.prod_group then
day=1;
do i=day to dim(x);
x(i)=.;
end;
day + 1;

sales_last7days = sum(of sale1-sale7);
run;

All Replies
Posts: 1,270

## Re: How do I create a rolling sum for the last 30 days?

data want;

set have;

by prod_group;

cum+lag(sales);

if first.prod_group then cum=0;

run;

New Contributor
Posts: 3

## Re: How do I create a rolling sum for the last 30 days?

Thanks Stat@sas for the help!

Solution
‎07-16-2015 10:02 PM
Frequent Contributor
Posts: 88

## Re: How do I create a rolling sum for the last 30 days?

I'm assuming you mean the last 7 day rather than the 30 days in your title.

If you can ensure records for every day, unlike your example, a solution is relatively easy, and based on an example in the doco for the LAG function:

data have;
input prod_group \$
date :date9.
sales ;
format date date9.;
cards;
AAAA          01jan2015     10
AAAA          02jan2015     7
AAAA          03jan2015     1
AAAA          04jan2015     0
AAAA          05jan2015     4
AAAA          06jan2015     6
AAAA          07jan2015     2
AAAA          08jan2015     7
AAAA          09jan2015     6
AAAA          10jan2015     2
BAAA          01jan2015     1
BAAA          02jan2015     2
BAAA          03jan2015     3
;
run;
data want (keep=prod_group date sales sales_last7days);
set have;
by prod_group date;
retain sales_last7days  0;
array x(*) sale1-sale7;

sale1 = lag1(sales);
sale2 = lag2(sales);
sale3 = lag3(sales);
sale4 = lag4(sales);
sale5 = lag5(sales);
sale6 = lag6(sales);
sale7 = lag7(sales);
if first.prod_group then
day=1;
do i=day to dim(x);
x(i)=.;
end;
day + 1;

sales_last7days = sum(of sale1-sale7);
run;

New Contributor
Posts: 3