Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Re: Period over Period calculation

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 09-12-2018 11:19 AM
(2507 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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**;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

**Available on demand!**

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

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.