topic Re: Period over Period Differences (MoM, QoQ, and YoY) in SAS Enterprise Guide

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

B202 — Fri, 07 Jul 2017 01:42:26 GMT

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.

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.

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

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

Reeza — Fri, 07 Jul 2017 02:00:33 GMT

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

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

AhmedAl_Attar — Fri, 07 Jul 2017 05:12:55 GMT

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

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

B202 — Wed, 26 Jul 2017 21:40:36 GMT

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

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

B202 — Wed, 26 Jul 2017 21:47:04 GMT

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;

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

PaigeMiller — Wed, 26 Jul 2017 22:07:09 GMT

@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.

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

Reeza — Wed, 26 Jul 2017 22:51:55 GMT

@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.

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

B202 — Thu, 27 Jul 2017 18:57:01 GMT

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

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

Reeza — Thu, 27 Jul 2017 19:06:02 GMT

@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.