## Period over Period calculation

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.

Duke

1 ACCEPTED SOLUTION

Accepted Solutions

## Re: Period over Period calculation

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;

6 REPLIES 6

## Re: Period over Period calculation

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.

Duke

## Re: Period over Period calculation

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

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

## Re: Period over Period calculation

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

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;

## Re: Period over Period calculation

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

Discussion stats
• 6 replies
• 2222 views
• 2 likes
• 3 in conversation