How to calculate return index by state_id?

[fongdo]

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? 

[andreas_lds]

Use retain to preserve the value of Index during data step iteration, use set+by and if first.state_id to reset index.

[fongdo]

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;

[andreas_lds · Accepted solution]

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;

[fongdo]

Your code is very efficient and effective. Thank you very much @andreas_lds. 

[ballardw]

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.

[fongdo]

I have changed date to year for easier understanding and showed using number for calculating Index.