2 weeks ago
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.
2 weeks ago
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
2 weeks ago
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