Desktop productivity for business analysts and programmers

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

Reply
New Contributor
Posts: 4

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. 

Super User
Posts: 19,034

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

 

 

New Contributor
Posts: 4

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

The variance columns are calculating the difference between the current month balance and depending on which column the previous month, quarter, and year.

 

Current month - Month-1

Current month - Month-3

Current month - Month-12

 

 

Trusted Advisor
Posts: 1,783

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


Reeza wrote:

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

 

 


Similarly, if you order your data by product month and you have access to PROC EXPAND, then you can easily compute the differences by month, quarter or year.

Regular Contributor
Posts: 214

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
New Contributor
Posts: 4

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

Can I take this approach if I am importing a table?

 

My experience in the SAS code is limited but it seems like it may have more flexibiliy vs the GUI. I currently have a SAS EG program which imports the dataset I am using and contains 10k lines of historical data.  I can create a table with the imported table and write code to perform calculations.  I'm not sure how I can run your process given that I already have my dataset and don't need to define it.

 

Example of what I currently have:

 

%_eg_conditional_dropds(WORK.HistoricalData);

proc SQL;
create table RWA_Historical as
select t1.ID,
t1.Entity,
t1.Description,
t1.Period,
t1.Balance

FROM WORK.HistoricalData t1;

quit;

Super User
Posts: 19,034

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


B202 wrote:

I'm not sure how I can run your process given that I already have my dataset and don't need to define it.

 

 

 

The first step defines the sample data, where @AhmedAl_Attar took the time to type it out from the picture, I wouldn't have.

To implement this you would work from teh second step and point it at your table instead of using the sample data he created.  

 

 

 

New Contributor
Posts: 4

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

Can you explicity illustrate how this could be accomplished? My coding knowledge is very limited.

Super User
Posts: 19,034

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


B202 wrote:

Can you explicity illustrate how this could be accomplished? My coding knowledge is very limited.


How about if you try, I posted a basic idea, and we can help you debug or solve further issues? If you need dedicated coding support, I would suggest either technical support or a consultant.

 

To implement this you would work from the (sp) second step and point it at your table instead of using the sample data he created.  

Ask a Question
Discussion stats
  • 8 replies
  • 295 views
  • 1 like
  • 4 in conversation