How to convert year-by-year changes to changes relative to a base year?

[deleted_user]

Hi, I am not sure if this forum is the appropriate one for my question. If it's not, my apology. I searched several forums for a similar question but found none. So I am posting my question here.

I have a data set that provides year-by-year changes. I want to create a new variable that takes the first year of each ID and accumulates the changes of all previous years. One factor that complicates the calculation is the year_by_year ratio may be reset to zero and then later to one. In this case, I wish the year it is reset to one is used as a new base. Also, the time-span of each ID may differ from others'. Like A's starts from 1997 but B's starts from 1995.

Can someone let me know what proc or macro to use? Thanks a bunch.

Below is an example:
..ID.....YEAR.....Q_Ratio.....What_I_Need.........My.Note
A...........1997.........1.02...........1.020
A...........1998..........0.9...........0.918................=.9.*.1.02
A...........1999.........0.88...........0.808................=.88.*..918
A...........2000.........1.32...........1.066...............=.1.066.*.0.808.
A...........2001.........1.11...........1.184...............etc.
B...........1995............1...............1
B...........1996.........1.05...........1.050
B...........1997.........0.89...........0.935
B...........1998............0...............0..................reset.to.zero
B...........1999............1...............1..................reset.to.one
B...........2000.........1.11...........1.110
B...........2001.........1.21...........1.343
B...........2002..........0.8...........1.074 Message was edited by: wtqn

[MaxW]

You can use by-group processing and first. to perform this task:

proc sort data=temp;
by ID Year;
run;
Data temp;
Retain What_I_Need;
set temp;
by id year;
if first.ID or Q_Ratio in (0,1) then What_I_Need=Q_Ratio;
else What_I_Need=What_I_Need*Q_Ratio;
run;

The retain statements keeps the previous value of What_I_Need, unless it is reset to Q_Ratio by a new value of ID or a Q_Ratio of 0 or 1.

Hope this helps.

[deleted_user]

thanks for your prompt reply. I will try it out. Best,

[Ksharp]

Hi.
There is also something uncertain ,Can you post some more data as it.
With my understanding , I code these:


[pre]
data temp;
input id $ year q_ratio ;
datalines;
A 1997 1.02
A 1998 0.9
A 1999 0.88
A 2000 1.32
A 2001 1.11
B 1995 1
B 1996 1.05
B 1997 0.89
B 1998 0
B 1999 1
B 2000 1.11
B 2001 1.21
B 2002 0.8
;
run;
proc sort data=temp;
by ID Year;
run;
data want;
set temp;
retain need;
if id ne lag(id) then need=q_ratio;
else need=need*q_ratio;
if id eq lag(id) and lag(q_ratio)=0 then need=q_ratio;
run;
[/pre]




Ksharp

[deleted_user]

Thanks. I don't have the data on hand right now. MaxW and you gave me a framework to work on. I will try it out. Whether I make it or not, I will let you guys know the outcome.

In fact the data is a bit more complicated. in some cases, the Q_ratio can be 1, meaning current year's Q is the same as last years. Sometimes Q_ratio is 1 following a year of 0, then this 1 means Q is reset. Also there may be a year where Q_ratio has missing value. Very annoying.

Anyway, I will be back with more questions or my code on the basis of yours.

Thanks again to you and MaxW.