Calculating monthly percent change

[parmis]

Hello,

I have the following dataset. Does anyone know how I can calculate monthly percent change by ID.

Thank you in advance!

Date	ID	Amount
Jan-18	A1	10
Jan-18	B1	27
Jan-18	C1	35
Jan-18	D1	34
Jan-18	F1	24
Feb-18	A1	2
Feb-18	B1	33
Feb-18	C1	0
Feb-18	D1	24
Feb-18	F1
Mar-18	A1	42
Mar-18	B1	67
Mar-18	C1	6
Mar-18	D1
Mar-18	F1	63
Mar-18	L1	36

[PeterClemmensen]

What does your desired result look like?

[parmis]

for example the desired outcome for February would be:

the percent should show the percent increase between Jan and February. The new amount is January Amount*(1+percent change)

Date	ID	percent change	new amount
Feb-18	A1	10%	44
Feb-18	B1	9%	53
Feb-18	C1	7%	57
Feb-18	D1	1%	57
Feb-18	F1	5%	22

[PGStats]

Those values don't seem related to your input data.

[mkeintz]

How do these A1 values ;

  Jan-18 A1 10
  Feb-18 A1  2
  Mar-18 A1 42

Produce this result?
  Date   ID percent change    new amount
  Feb-18 A1      10%              44

[ballardw]

---

@parmis wrote:

for example the desired outcome for February would be:

the percent should show the percent increase between Jan and February. The new amount is January Amount*(1+percent change)

Date	ID	percent change	new amount
Feb-18	A1	10%	44
Feb-18	B1	9%	53
Feb-18	C1	7%	57
Feb-18	D1	1%	57
Feb-18	F1	5%	22

---

And is the "new amount" supposed to be the value used in comparing with March?

[ballardw]

So what do you want to do when the value is missing? That will affect at least 2 calculations: the current month for the missing value and then the "percent change" for then month. Also, do you have any 0 amount values? The division for percent changes doesn't work with that either.

This assumes that you mean to do these calculations with ID in date order.

My take on this would be:

data have;
   informat date anydtdte. id $3. ;
   format date date9.;
   input Date ID Amount ;
datalines;
Jan-18 A1 10 
Jan-18 B1 27 
Jan-18 C1 35 
Jan-18 D1 34 
Jan-18 F1 24 
Feb-18 A1 2 
Feb-18 B1 33 
Feb-18 C1 0 
Feb-18 D1 24 
Feb-18 F1  . 
Mar-18 A1 42 
Mar-18 B1 67 
Mar-18 C1 6 
Mar-18 D1  . 
Mar-18 F1 63 
Mar-18 L1 36 
;
run;

proc sort data=have;
   by id date;
run;

data want;
   set have;
   by id date;
   difv = Dif(amount);
   lagv = lag(amount);
   if first.id then PctChange=0;
   else if not missing(lagv) and lagv>0 then PctChange = 100*(difv/lagv);
   /* drop difv lagv;*/
run;

Proc sort data=want;
   by date id;
run;

If your date variable is not a SAS date value then that would be something to do before sorting. The sort gets the ID values together in the correct date order. Otherwise Jan comes after Feb.

The last sort is to put things back in the apparent desired order. You can drop the difv and lagv variables after you are sure the result is what you want.

Please also note that providing data in the form of a data step allows proceed to testing code. Paste such code in a code box opened using the forum's {I} icon to preserve formatting and reduce the likelihood of odd characters being created when the forum reformats text as it does in the main window.

[parmis]

Thanks for your help. I'm trying to calculate a percent change for amount to forecast amount in long term. If the following code doesn't work with zero variables, is there any other solution?

[ballardw]

---

@parmis wrote:

Thanks for your help. I'm trying to calculate a percent change for amount to forecast amount in long term. If the following code doesn't work with zero variables, is there any other solution?

---

You have to decide what the "percent of change" would be with 0 or missing values should. That could be done with additional logic but I am not going to assume any rule works for your situation as I do not know your data or what the actual "forecast" methods might be or what different approaches to assigning the percent in those situations may have on the forecast approach.

Does you forecast method require that there be no missing data? If so you need to find some approach that is acceptable to determine numerators and denominators for these missing or 0 cases.

The question would relate to why are the data missing for those that it misses. There might be a clue in the definition of what "amount" measures. Is it a count or a different type of measure such as price?

If a count, is it an actual count or number of 1,000s or similar (i.e. 1= 1000 of something). In this last case then 0 might actually refer to a count less than 500 and has been rounded down. In which case 0 may not be 0 and a "small" value could be imputed to calculate percent of change.

And if the previous value is an actual 0 then what percent of change makes sense for a current value of 1, 10 or 100?