BookmarkSubscribeRSS Feed
B202
Calcite | Level 5

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. 

8 REPLIES 8
Reeza
Super User

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

 

 

B202
Calcite | Level 5

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

 

 

PaigeMiller
Diamond | Level 26

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

--
Paige Miller
AhmedAl_Attar
Rhodochrosite | Level 12

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
B202
Calcite | Level 5

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;

Reeza
Super User

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

 

 

 

B202
Calcite | Level 5

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

Reeza
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 5067 views
  • 1 like
  • 4 in conversation