Learning SAS? Welcome to the exclusive online community for all SAS learners.

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

Can anyone please help me with this?

Thanks in advance!


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

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,204

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: 85

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

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

Thanks Jerry! Thanks very much for your help.. I had to submit something very urgent yesterday and got stuck at this.. Really appreciate your help!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 447 views
  • 0 likes
  • 3 in conversation