Generating sum of rolling data Using the Lag function2 min read

Rolling Data also known as Moving average is a time-based calculations to get an insight of trends for a defined period of time.

If time frame for moving average is 12 months, on each month the data which is 13 month old be dropped and the new months data will be added.

In the below example, we will be calculating rolling 3 months of sales data.

data raw_Data;
do i=1 to 100;
    transaction_Date=a+floor((b-a)* rand("uniform"));

format transaction_Date mmddyy10.;
drop a b i;
  • I have prepared the data by generating random dates (between 01JAN2019 to 30NOV2019) and numbers between 100 to 100 which represents sales amount.

proc Summary data=raw_Data nway;
	class month;
	var sales;
	output out=trends(drop=_TYPE_ _FREQ_) sum=sales;
  • The sum of sales amount has is grouped so that we get the total sales data for each month.
Lag Function Example
data Rolling_data;
	set trends;
	rolling_total + sales - coalesce(lag3(sales),0);
Generating sum of rolling data Using the Lag function 1
  • Lag of Sales is calculated for looking back 3 observation.
  • Running total is calculated by adding the current sales and subtraction the first sales of the window period. In this case the window period is 3.
  • Once, the 4th sales is added to the running total , the 1st sales is subtracted.
  • Coalesce function computes, the first non missing values and assigns 0. This sis required as the 1st 3 sales will have missing values.
by Subhro Kar
Been in the realm with the professionals of the IT industry. I am passionate about Coding, Blogging, Web Designing and deliver creative and useful content for a wide array of audience.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share via
Copy link
Powered by Social Snap