Desktop productivity for business analysts and programmers

Period over Period Differences (MoM, QoQ, and YoY)

Reply
Frequent Learner
Posts: 1

Period over Period Differences (MoM, QoQ, and YoY)

I am trying to figure out how to add Month over Month, Quarter over Quarter, and Year over Year calculations columns to a SAS EG data table.

 

The green columns below is an example of the dataset I am working with.  We have month end balances for several products for the previous 13 months stacked on top of each other. I added a second view to provide an alternative.

 

I need to figure out how to add the orange colums with a calculation. For each product ID, I want to be able to calculate the difference between the current balance and the previous month, previous quarter, and previous year.  

 

Dataset.png

 

Also inlcuded is the formula that I would use in excel.  The formula starts with the current balance, finds the balance for the specific product ID and previous month, and then takes the difference between the two values.  

 

Formula.png

 

Let me know if you have any follow up questions regarding my issue. 

Grand Advisor
Posts: 17,332

Re: Period over Period Differences (MoM, QoQ, and YoY)

If you order your data by Product/Month you can use the LAG functions. I'm not sure how you're defining YoY and QoQ, ie summing/aggregating first or just a 3 month/12 month lag. If it is 3/12 month lag, use the respective lag3/lag12 functions. 

 

MoM -> Lag(variable)

 

If you want to use GUI look under Tasks>Time Series

 

 

Regular Contributor
Posts: 211

Re: Period over Period Differences (MoM, QoQ, and YoY)

Hi,

Not sure how big your data is, but here is how you can use the Hash Object to get the Period Differences

 

 

DATA have;
   length date 8 product_id 4 balance 8;
   input date :date9. product_id balance;
   format date date9.;
datalines;
30Apr2017 1 10000
30Apr2017 2 20000
31Mar2017 1 50000
31Mar2017 2 5000
28Feb2017 1 8000
28Feb2017 2 40000
31jan2017 1 30000
31jan2017 2 100000
30Apr2016 1 2000
30Apr2016 2 1000
;
RUN;

DATA want(KEEP=date product_id curr_bal MoM QoQ YoY RENAME=(curr_bal=balance));

	if (_n_=1) then
	do;
		dcl hash ho(dataset:'have');
		ho.defineKey('product_id','date');
		ho.defineData('balance');
		ho.defineDone();
	end;
	
	SET have;
	length curr_bal MoM QoQ YoY 8;
	format prev_q prev_m prev_y date9. curr_bal MoM QoQ YoY comma12.;
	
        /* Calculate Previous Periods */
	prev_m = intnx('month',date,-1,'E');
	prev_q = intnx('month',date,-3,'E');
	prev_y = intnx('year',date,-1,"sameday");
	curr_bal = balance;
	
	*put date= prev_m= prev_q= prev_y=;

        /* Search for Previous record in the Hash Object */
	rc=ho.find(key:product_id, key:prev_m);
	if (rc=0) then MoM = curr_bal - balance; else MoM = curr_bal;

	rc=ho.find(key:product_id, key:prev_q);
	if (rc=0) then QoQ = curr_bal - balance; else QoQ = curr_bal;

	rc=ho.find(key:product_id, key:prev_y);
	if (rc=0) then YoY = curr_bal - balance; else YoY = curr_bal;

RUN;

Here is the output

 

 

 
Obs date product_id balance MoM QoQ YoY
1 30APR2017 1 10,000 -40,000 -20,000 8,000
2 30APR2017 2 20,000 15,000 -80,000 19,000
3 31MAR2017 1 50,000 42,000 50,000 50,000
4 31MAR2017 2 5,000 -35,000 5,000 5,000
5 28FEB2017 1 8,000 -22,000 8,000 8,000
6 28FEB2017 2 40,000 -60,000 40,000 40,000
7 31JAN2017 1 30,000 30,000 30,000 30,000
8 31JAN2017 2 100,000 100,000 100,000 100,000
9 30APR2016 1 2,000 2,000 2,000 2,000
10 30APR2016 2 1,000 1,000 1,000 1,000
Ask a Question
Discussion stats
  • 2 replies
  • 140 views
  • 0 likes
  • 3 in conversation