## How to calculate a security return variable?

Solved
Regular Contributor
Posts: 166

# 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:

 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?

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

## Re: How to calculate a security return variable?

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;

if first.date then return = .;

run;

All Replies
PROC Star
Posts: 1,283

## Re: How to calculate a security return variable?

like this

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

data want;
set have;
by name date;
run;``````
Solution
‎12-16-2017 12:46 PM
Super User
Posts: 6,774

## Re: How to calculate a security return variable?

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;

if first.date then return = .;

run;

Super User
Posts: 10,778

## Re: How to calculate a security return variable?

``````
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?

``````data want;
set have;
by name    date;