Skip to content
9to5sas
  • Index
  • Glossary
Facebook Twitter Instagram Email RSS

  • Start Here
  • Base SAS
  • Advanced SASExpand
    • SAS Macros
    • PROC SQL
  • SAS/STATSExpand
    • SAS Analytics
    • Statistics
  • SAS Programs
9to5sas

9to5sas » SAS PROGRAMS » Generating sum of rolling data using the Lag function

Generating sum of rolling data using the Lag function

BySubhro Posted onDecember 20, 2019September 10, 2022 Last Updated:September 10, 2022
0 Comments

Rolling Data also known as Moving average is a time-based calculation to get an insight into trends for a defined period of time.
If the time frame for the moving average is 12 months, the data that is 13 months old be dropped and the new month’s data will be added.

Rolling Data, known as Moving average, is a time-based calculation to get an insight into trends for a defined period.

If the time frame for the moving average is 12 months, the data that is 13 months old be dropped, and the new month’s data will be added.

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

data raw_Data;
	a="01JAN2019"d;
	b="30NOV2019"d;
do i=1 to 100;
    sales=rand("Integer",100,10000);
    transaction_Date=a+floor((b-a)* rand("uniform"));
    month=month(transaction_Date);
    output;
end;

format transaction_Date mmddyy10.;
drop a b i;
run;

We have prepared the data by generating random dates (between 01JAN2019 to 30NOV2019) and numbers between 1 to 100, which represent sales amount.

proc Summary data=raw_Data nway;
	class month;
	var sales;
	output out=trends(drop=_TYPE_ _FREQ_) sum=sales;
run;

The sum of the sales amount 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);
run;

Output:

Generating sum of rolling data using the Lag function
  • Lag of Sales is calculated by looking back at three observations.
  • The running total is calculated by adding the current sales and subtracting the first sales of the window period. In this case, the window period is 3.
  • Once the 4th sale is added to the running total, the 1st sale is subtracted.
  • Coalesce function computes the first non-missing values and assigns 0. This is required as the 1st three sales will have missing values.

Every week we'll send you SAS tips and in-depth tutorials

JOIN OUR COMMUNITY OF SAS Programmers!

Check your inbox or spam folder to confirm your subscription.

Post Tags: #lag#rolling total
Subhro

Subhro Kar is an Analyst with over five years of experience. As a programmer specializing in SAS (Statistical Analysis System), Subhro also offers tutorials and guides on how to approach the coding language. His website, 9to5sas, offers students and new programmers useful easy-to-grasp resources to help them understand the fundamentals of SAS. Through this website, he shares his passion for programming while giving back to up-and-coming programmers in the field. Subhro’s mission is to offer quality tips, tricks, and lessons that give SAS beginners the skills they need to succeed.

Facebook Twitter Linkedin

Post navigation

Previous Previous
Using PROC MEANS for detailed analysis of data
NextContinue
How do you convert characters to numeric or numeric to characters in SAS?

SAS Tips in your inbox

Subscribe to 9to5sas for timely SAS tips and news delivered each month.
Learn about the latest articles, and code samples to keep your SAS skills fresh!

Your subscription is successful!

Recent Posts

  • Concatenate strings in SAS: The CAT Functions Demystified
  • 5 Techniques for Quickly Removing Leading Zeros in SAS
  • Troubleshoot Your Proc SQL Code Like a Pro with These 7 Automatic Macro Variables
  • 7 PROC SQL Options You Should Use to Debug Queries
  • How To Use The SAS Proc SQL Order By Statement?
9to5sas
  • Privacy Policy
  • Disclaimer
  • About
  • Contact
Facebook Twitter Instagram RSS Email Pinterest

Copyright © 2023 9TO5SAS, All rights reserved.

Scroll to top
  • 9to5sas Blueprint
  • About
  • About
  • Acceptable use policy
  • calculator
  • Confirm email
  • Contact
  • Contact
  • Cookie Policy
  • DISCLAIMER
  • Getting Started with SAS
  • Glossary
  • Index
  • Post #13801
  • Privacy Policy
  • Privacy policy
  • SAS Programs
  • Styles
  • Subscription confirmed
  • Terms and conditions
  • Thank You