How to calculate a security return variable?

Accepted Solution Solved
Reply
Regular Contributor
Posts: 166
Accepted Solution

How to calculate a security return variable?

Hello everybody;

I want to calculate returns of equities for specific name and date variables. Here is a sample of my data:

 

 

data have;
infile datalines expandtabs truncover ;
input name $    date :mmddyy10. time : time10.  Intraday;
format date mmddyy10. time time10.;
datalines;
A    12/1/2013  9:30:00    1   
A    12/1/2013  10:00:00   2  
A    12/2/2013  10:30:00   3   
A    12/2/2013  11:00:00   2  
A    12/3/2013  10:00:00   2   
A    12/3/2013  10:30:00   1   
A    12/3/2013  11:00:00   1  
A    12/4/2013  11:30:00   1   
A    12/4/2013  12:00:00   3   
A    12/4/2013  12:30:00   3  
A    12/7/2013  9:30:00    1   
A    12/7/2013  10:00:00   1  
A    12/8/2013  10:30:00   2   
A    12/8/2013  11:00:00   3   
A    12/17/2013 11:30:00   1   
A    12/17/2013 12:00:00   1   
A    12/18/2013 12:30:00   2   
A    12/28/2013 9:30:00    1   
A    12/28/2013 10:00:00   1  
B    12/8/2013  10:30:00   2   
B    12/8/2013  11:30:00   2   
B    12/8/2013  12:30:00   1  
B    12/14/2013 9:30:00    1   
B    12/14/2013 10:30:00   1  
B    12/15/2013 12:00:00   2   
B    12/15/2013 12:30:00   1  
B    12/24/2013 9:30:00    1  
B    12/25/2013 10:00:00   2   
B    12/25/2013 10:30:00   2  
B    12/26/2013 12:00:00   2  
B    12/30/2013 9:30:00    3   
B    12/30/2013 10:00:00   2   
B    12/30/2013 10:30:00   1  
B    1/3/2014   11:00:00   1   
B    1/3/2014   11:30:00   1   
B    1/13/2014  12:00:00   1  
;

 

 

There are two major numbers needed to calculate the rate of return:

Current value: the current value of the intraday variable.

Original value: the price at which you purchased the item (The intraday value of previous row).

Then, by applying these values to the rate of return formula:

((Current value of an intraday - original value of an intraday) / original value of an intraday) = rate of return

 

So the results is:

namedatetimeIntradayreturn
A12/1/20139:30:001 
A12/1/201310:00:0021
A12/2/201310:30:003 
A12/2/201311:00:002-0.33333
A12/3/201310:00:002 
A12/3/201310:30:001-0.5
A12/3/201311:00:0010
A12/4/201311:30:001 
A12/4/201312:00:0032
A12/4/201312:30:0030
A12/7/20139:30:001 
A12/7/201310:00:0010
A12/8/201310:30:002 
A12/8/201311:00:0030.5
A12/17/201311:30:001 
A12/17/201312:00:0010
A12/18/201312:30:002 
A12/28/20139:30:001 
A12/28/201310:00:0010
B12/8/201310:30:002 
B12/8/201311:30:0020
B12/8/201312:30:001-0.5
B12/14/20139:30:001 
B12/14/201310:30:0010
B12/15/201312:00:002 
B12/15/201312:30:001-0.5
B12/24/20139:30:001 
B12/25/201310:00:002 
B12/25/201310:30:0020
B12/26/201312:00:002 
B12/30/20139:30:003 
B12/30/201310:00:002-0.33333
B12/30/201310:30:001-0.5
B1/3/201411:00:001 
B1/3/201411:30:0010
B1/13/201412:00:001 

 

How can I do that?

Thanks in advance.


Accepted Solutions
Solution
‎12-16-2017 12:46 PM
Super User
Posts: 6,774

Re: How to calculate a security return variable?

Posted in reply to aminkarimid

It looks like these are the pieces you are asking for:

 

proc sort data=have;

by name date time;

run;

data want;

set have;

by name date;

return = dif(intraday) / lag(intraday);

if first.date then return = .;

run;

View solution in original post


All Replies
PROC Star
Posts: 1,283

Re: How to calculate a security return variable?

Posted in reply to aminkarimid

like this

 

proc sort data=have;
   by name date time;
run;

data want;
   set have;
   by name date;
   lag_Intraday=lag1(Intraday);
   if not first.date then return=(Intraday-lag_Intraday)/lag_Intraday;
run;
Solution
‎12-16-2017 12:46 PM
Super User
Posts: 6,774

Re: How to calculate a security return variable?

Posted in reply to aminkarimid

It looks like these are the pieces you are asking for:

 

proc sort data=have;

by name date time;

run;

data want;

set have;

by name date;

return = dif(intraday) / lag(intraday);

if first.date then return = .;

run;

Super User
Posts: 10,778

Re: How to calculate a security return variable?

Posted in reply to aminkarimid

Data   a;
Input  before   after;
Cards;
200     150
;
Run;

 

 

Data b;
Set a;
PCT_Change=(After-before)/before;
Format     PCT_Change     percentN9.2;
Run;
Super User
Posts: 10,778

Re: How to calculate a security return variable?

Posted in reply to aminkarimid
data want;
 set have;
 by name    date;
 lag=lag(Intraday);
 if not first.date then want=(Intraday-lag)/lag;
run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 576 views
  • 3 likes
  • 4 in conversation