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
... View more
