BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dukem
Fluorite | Level 6

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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;

View solution in original post

6 REPLIES 6
Reeza
Super User

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
Fluorite | Level 6

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
Super User

@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
Fluorite | Level 6

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
Super User
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
Meteorite | Level 14

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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