BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
fongdo
Obsidian | Level 7

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? 

 

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

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;

View solution in original post

6 REPLIES 6
andreas_lds
Jade | Level 19
Use retain to preserve the value of Index during data step iteration, use set+by and if first.state_id to reset index.
fongdo
Obsidian | Level 7

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
Jade | Level 19

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
Obsidian | Level 7

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

ballardw
Super User

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
Obsidian | Level 7

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 6 replies
  • 814 views
  • 1 like
  • 3 in conversation