How to calculate a security return variable?

[aminkarimid]

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:

name	date	time	Intraday	return
A	12/1/2013	9:30:00	1
A	12/1/2013	10:00:00	2	1
A	12/2/2013	10:30:00	3
A	12/2/2013	11:00:00	2	-0.33333
A	12/3/2013	10:00:00	2
A	12/3/2013	10:30:00	1	-0.5
A	12/3/2013	11:00:00	1	0
A	12/4/2013	11:30:00	1
A	12/4/2013	12:00:00	3	2
A	12/4/2013	12:30:00	3	0
A	12/7/2013	9:30:00	1
A	12/7/2013	10:00:00	1	0
A	12/8/2013	10:30:00	2
A	12/8/2013	11:00:00	3	0.5
A	12/17/2013	11:30:00	1
A	12/17/2013	12:00:00	1	0
A	12/18/2013	12:30:00	2
A	12/28/2013	9:30:00	1
A	12/28/2013	10:00:00	1	0
B	12/8/2013	10:30:00	2
B	12/8/2013	11:30:00	2	0
B	12/8/2013	12:30:00	1	-0.5
B	12/14/2013	9:30:00	1
B	12/14/2013	10:30:00	1	0
B	12/15/2013	12:00:00	2
B	12/15/2013	12:30:00	1	-0.5
B	12/24/2013	9:30:00	1
B	12/25/2013	10:00:00	2
B	12/25/2013	10:30:00	2	0
B	12/26/2013	12:00:00	2
B	12/30/2013	9:30:00	3
B	12/30/2013	10:00:00	2	-0.33333
B	12/30/2013	10:30:00	1	-0.5
B	1/3/2014	11:00:00	1
B	1/3/2014	11:30:00	1	0
B	1/13/2014	12:00:00	1

How can I do that?

Thanks in advance.

[PeterClemmensen]

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;

[Astounding · Accepted solution]

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;

[Ksharp]

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

 

 

Data b;
Set a;
PCT_Change=(After-before)/before;
Format     PCT_Change     percentN9.2;
Run;

[Ksharp]

data want;
 set have;
 by name    date;
 lag=lag(Intraday);
 if not first.date then want=(Intraday-lag)/lag;
run;