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.
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;
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;
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;
Data a;
Input before after;
Cards;
200 150
;
Run;
Data b;
Set a;
PCT_Change=(After-before)/before;
Format PCT_Change percentN9.2;
Run;
data want;
set have;
by name date;
lag=lag(Intraday);
if not first.date then want=(Intraday-lag)/lag;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.