topic Re: How to calculate return index by state_id? in New SAS User

How to calculate return index by state_id?

fongdo — Tue, 26 Mar 2019 08:03:26 GMT

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?

Re: How to calculate return index by state_id?

andreas_lds — Sun, 24 Mar 2019 06:08:49 GMT

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

Re: How to calculate return index by state_id?

ballardw — Mon, 25 Mar 2019 15:33:22 GMT

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.

Re: How to calculate return index by state_id?

fongdo — Tue, 26 Mar 2019 07:58:13 GMT

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;

Re: How to calculate return index by state_id?

fongdo — Tue, 26 Mar 2019 09:37:09 GMT

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

Re: How to calculate return index by state_id?

andreas_lds — Tue, 26 Mar 2019 12:45:21 GMT

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;

Re: How to calculate return index by state_id?

fongdo — Tue, 26 Mar 2019 13:14:29 GMT

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