I have an economic data series of positive and negative numbers and i am having difficulty computing the percentage change. I compute percentage change as: [(current - prior)/prior]*100 (I am using the lag function for prior). This works fine if the numbers are positive but errors occur in some cases with 2 negative numbers or a positive and a negative number. For example:
prior 4
current 2 -50% OK
prior 2
current 4 100% OK
prior -4
current -2 - 50% error
prior -2
current -4 100% error
prior 4
current -2 -150% OK
prior -4
current 2 -150% error
prior 2
current -4 -300% OK
prior -2
current 4 -300% error
I think you are concerned about the sign of the pct. From -4 to -2 you get -50%, but I think you want +50%. I.e. you want the direction of the change to determine sign of pct.
data series;
input current @@;
pct= 100*sign(dif(current))*abs(dif(current)/lag(current));
datalines;
4 2 4 -2 -4 -2 2 -4 4
run;
Look at proc expand for creating the new variable.
Could you post your code? I've tried this example and it works fine:
23 data _null_ ;
24 prior = -2 ;
25 current = -4 ;
26 dif = ((current - prior)/prior)*100 ;
27 put dif= ;
28 run ;
dif=100
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
You will have to describe WHY you consider some of the results "errors".
whith prior=-4 and current = -2 then your formula resolves to:
-2 - (-4) = 2
2/ -4 = -0.5
-0.5*100 = -50
So the -50% is correct from your formula. So either your formula is incorrect or your belief that the value is incorrect is wrong or you have not completely stated the problem.
As an aside, there is a companion function to LAG called DIF that is the equivalent of Var - lag(var). So you can use Dif(var).
I think you are concerned about the sign of the pct. From -4 to -2 you get -50%, but I think you want +50%. I.e. you want the direction of the change to determine sign of pct.
data series;
input current @@;
pct= 100*sign(dif(current))*abs(dif(current)/lag(current));
datalines;
4 2 4 -2 -4 -2 2 -4 4
run;
Yes, this is exactly what I wanted to do. ( I.e. you want the direction of the change to determine sign of pct.)
Thanks for your help. rfortin
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.