topic Re: Period over Period calculation in SAS Procedures

Period over Period calculation

dukem — Wed, 12 Sep 2018 15:19:14 GMT

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

Re: Period over Period calculation

Reeza — Wed, 12 Sep 2018 15:28:11 GMT

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

Re: Period over Period calculation

dukem — Wed, 12 Sep 2018 16:02:10 GMT

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.

Re: Period over Period calculation

Reeza — Wed, 12 Sep 2018 16:13:20 GMT

@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=14.2&locale=en

Re: Period over Period calculation

s_lassen — Thu, 13 Sep 2018 07:28:18 GMT

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

Re: Period over Period calculation

dukem — Thu, 13 Sep 2018 18:48:09 GMT

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;

Re: Period over Period calculation

Reeza — Thu, 13 Sep 2018 17:51:20 GMT

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;