I would like to calculate return Index by state_id as example below. Could you kindly advise?
Have
State_id Year Return
1 2000 -0.00017
1 2001 0.00323
1 2002 -0.0161
1 2003 -0.00327
1 2004 0.009848
2 2000 -0.01373
2 2001 0.015687
2 2002 0.017782
2 2003 0.004842
2 2004 0.007863
Want
State_id Year Return Index Calculation
1 2000 -0.00017 0.9998 1*(1+ Return,t) = 1*(1- 0.00017)
1 2001 0.00323 1.0031 Index, t-1*(1+Return, t) = 0.9998*(1+0.00323)
1 2002 -0.0161 0.9869 Index, t-1*(1+Return, t)
1 2003 -0.00327 0.9837 Index, t-1*(1+Return, t)
1 2004 0.009848 0.9934 Index, t-1*(1+Return, t)
2 2000 -0.01373 0.9863 1*(1+Return, t) = 1*(1 -0.01373)
2 2001 0.015687 1.0017 Index, t-1*(1+Return, t) = 0.9863*(1+0.015687)
2 2002 0.017782 1.0196 Index, t-1*(1+Return, t)
2 2003 0.004842 1.0245 Index, t-1*(1+Return, t)
2 2004 0.007863 1.0325 Index, t-1*(1+Return, t)
P.s. each state 1) Return index at the first year is calculated by 1*(1+Return, t).
2) Return index from second to the last year are calculated by Index, t-1*(1+Return,t).
Thank you very much for your suggestions?
Try this:
data want;
set have;
by state_id;
length index 8;
retain index;
/* reset index when state_id changes */
if first.state_id then do;
index = 1;
end;
index = index * (1 + return);
run;
I have written the code below, however, the first year of each state_id is wrong. Could you please advise?
data want;
retain year return state_id;
set have; by state_id;
if first.state_id then Index=1*(1+return);
Index=Index*(1+return); retain Index;
run;
Try this:
data want;
set have;
by state_id;
length index 8;
retain index;
/* reset index when state_id changes */
if first.state_id then do;
index = 1;
end;
index = index * (1 + return);
run;
Your code is very efficient and effective. Thank you very much @andreas_lds.
and what is the value of "t" for the "beginning of the period"? Since you are using t in a calculation, 1*(1+ R,t), we need to know what value it may have. And I can't tell what calculation is indicated by : Index, t-1*(1+R,t) at all.
Minor question and may not be critical at this point, are your dates in day/month/year or month/day/year? It is not possible to tell by inspection.
I have changed date to year for easier understanding and showed using number for calculating Index.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.