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
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
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.
@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.
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;
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 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.