Period over Period calculation

[dukem]

I'm trying to calculate previous periods difference.

ref_period  Final_price

201707     10000
201708     20000
201709     50000
201710     5000
201711     8000
201712    40000
201801    30000
201802    100000
201803    2000
201804    1000

I would like calculate

1) Month-over-month % change  (ie. 201803-201804)

2) Year-over-year % change       (i.e 201703-2018-03)

I would like to do using final_price variable. I know I have to create a label, but not sure after that.

Thank you in advance

Duke

[Reeza]

Do you have data for every period?

If so, look into the LAG and LAG12() functions. 

---

@dukem wrote:

I'm trying to calculate previous periods difference.

ref_period  Final_price

201707     10000
201708     20000
201709     50000
201710     5000
201711     8000
201712    40000
201801    30000
201802    100000
201803    2000
201804    1000

I would like calculate

1) Month-over-month % change  (ie. 201803-201804)

2) Year-over-year % change       (i.e 201703-2018-03)

I would like to do using final_price variable. I know I have to create a label, but not sure after that.

Thank you in advance

Duke

---

[dukem]

m-o-m

data diff;
   set x;
   mchg = dif ( variable ) / lag( variable ) * 100;
   label mchg = "Monthly Percent Change";
run;

y-o-y

data diff;
   set x;
   ychng = dif12( variable) / lag12( variable ) * 100;
   label ychng = "Yearly Percent Change";
run;

The issue I'm have is:

1) The calculation must be done by unique ID (Roll_number). The m-o-m/y-o-y change of that roll_number.

2) Should I format date in any particular way?...currently YYYYMM i.e 201708.

3) The start period is 201707...therefore, this block should always be blank for very Roll_number

4)Is there any way to put this in a macro for future months since this a monthly updated product

I hope this makes sense.

[Reeza]

---

@dukem wrote:

1) The calculation must be done by unique ID (Roll_number). The m-o-m/y-o-y change of that roll_number.

---

Add a counter and reset at the top. Use FIRST/LAST to set the initial variables. https://stats.idre.ucla.edu/sas/faq/how-can-i-create-an-enumeration-variable-by-groups/

---

2) Should I format date in any particular way?...currently YYYYMM i.e 201708.

You should have a SAS date, with a date format. The actual format doesn't matter, the calculation uses the underlying data to do it.

---

3) The start period is 201707...therefore, this block should always be blank for very Roll_number

---

Not sure how this matters? Using First/Last and a counter will set it to blank/missing.

---

@dukem wrote:

4)Is there any way to put this in a macro for future months since this a monthly updated product

---

Not sure what value a macro adds since the number of dates don't matter here. It will do it regardless of the amount of data present. 

If you have SAS/ETS you can check PROC EXPAND which does this automatically.

Check the CONVERT statement and the transformation options.

https://documentation.sas.com/?docsetId=etsug&docsetTarget=etsug_expand_examples04.htm&docsetVersion...

[dukem]

I have the following program...however, something is wrong with my calculations. I would like the calculations to be based on roll_number within a ref_period. Therefore, the first roll_number (201707) should not have any value.

data p_diff;

      set x;

by Roll_number Ref_period;

if not first.Roll_number then do;

      mperchg= dif(FNL_PRICE) / lag(FNL_PRICE)* 100;

      yperchg= dif12(FNL_PRICE) / lag12(FNL_PRICE)* 100;

end;

format      mperchg comma8.2

               yperchg     comma8.2;

run;

[Reeza · Accepted solution]

You cant use LAG within an IF statement, it won’t work as expected because it’s a queue. You need to add a counter, as I mentioned. Use the enumerate to create the new variable and then if COUNTER>1 then calculate MoM and if counter>12 then YoY changes.


Untested:

If first.roll_number then counter=0;
Counter=1;

Lag_Month = lag1(fnl_price);
Lag_Year = lag12(fnl_price);

If counter>1 then MoM = (fnl_price/lag_month-1)*100;
If counter>12 then YoY = (fnl_price/lag_year-1)*100;

[s_lassen]

One possibility is to use keyed access, something like this:

proc sql;
  create index idx on have(roll_number,ref_period);
quit;

data want;
  set have;
  year_ago=ref_period-100; /* or use intnx if your variable is date type */
  month_ago=ref_period-1-88*(mod(ref_period,100)=1);
  set have(rename=(final_price=year_old_price ref_period=year_ago)) key=idx;
  if _iorc_ then */ no record found */
    _error_=0;
  else
    pct_chg_year=(final_price-year_old_price)/year_old_price;
  set have(rename=(final_price=month_old_price ref_period=month_ago)) key=idx;
  if _iorc_ then */ no record found */
    _error_=0;
  else
    pct_chg_month=(final_price-month_old_price)/month_old_price;
  format pct_chg: percent5.2;
run;   

If your ref_period variable is date type, make sure that all the dates are standardized (e.g. to the first daty of the month).