## How to calculate return based on first and last price in a time interval?

Solved
Highlighted
Regular Contributor
Posts: 166

# How to calculate return based on first and last price in a time interval?

Hello everybody,

I want to calculate the return or the growth of the price variable. Please consider the following table:

table A

 name price date time RETURN A 3 2-Apr-08 9:44:38 A 5 2-Apr-08 9:53:02 A 7 2-Apr-08 9:55:23 A 4 2-Apr-08 9:58:31 0.333333 A 6 2-Apr-08 11:17:33 A 2 2-Apr-08 11:18:33 A 1 2-Apr-08 11:19:33 -0.83333 A 4 2-Apr-08 11:57:33 A 8 7-Apr-08 9:08:39 A 4 7-Apr-08 9:10:22 A 2 7-Apr-08 9:13:41 -0.75 A 3 7-Apr-08 9:32:14 A 7 7-Apr-08 9:41:04 1.333333 A 8 17-Apr-08 9:15:51 A 2 17-Apr-08 10:24:15 B 4 22-Jul-08 10:16:19 B 7 22-Jul-08 10:20:51 0.75 B 8 22-Jul-08 11:12:19 B 4 22-Jul-08 11:15:27 B 2 22-Jul-08 11:17:30 B 5 22-Jul-08 11:18:36 -0.375 B 1 22-Jul-08 11:31:50 B 3 14-Oct-08 9:05:07 B 1 14-Oct-08 9:37:11 B 6 14-Oct-08 9:46:16 B 7 14-Oct-08 9:50:56 B 7 14-Oct-08 9:52:52 B 8 14-Oct-08 9:59:23 7 B 5 14-Oct-08 10:05:13 B 7 14-Oct-08 11:19:44 B 2 14-Oct-08 11:20:15 -0.71429

Now, I want to calculate the growth of price in half an hour for each name and date values. So, the RETURN variable is:

RETURNS variable = (Last price value in half an hour for each name and date variables) - (First price value in half an hour for each name and date variables) / (First price value in half an hour for each name and date variables)

Results have been shown in the last column of table A.

How can I calculate RETURNS variable in table A?

Accepted Solutions
Solution
‎12-25-2017 03:01 PM
PROC Star
Posts: 1,309

## Re: How to calculate return based on first and last price in a time interval?

Merry Christmas @art297  I wonder do we really need two passes once you have the 30 min interval grouping using intnx which you brilliantly coded. So, can't we use that x as a sub group and play with first.x and last.x like here:

Sorry if i am wrong. Just my 2 cents

data have;

infile cards dlm=',' truncover;

informat date date12.;

informat time time8.;

Input name \$ price date time want_return;

format date date10. time time12. ;

x=INTNX ( 'minutes30', time, 0, 'b'); /* I am taking this as a group*/

k=put(x,time12.);

cards;

A,3,2-Apr-08,9:44:38

A,5,2-Apr-08,9:53:02

A ,7,2-Apr-08,9:55:23

A,4,2-Apr-08,9:58:31,0.333333

A,6,2-Apr-08,11:17:33

A,2,2-Apr-08,11:18:33

A,1,2-Apr-08,11:19:33,-0.83333

A,4,2-Apr-08,11:57:33

A,8,7-Apr-08,9:08:39

A,4,7-Apr-08,9:10:22

A,2,7-Apr-08,9:13:41,-0.75

A,3,7-Apr-08,9:32:14

A,7,7-Apr-08,9:41:04,1.333333

A,8,17-Apr-08,9:15:51

A,2,17-Apr-08,10:24:15

B,4,22-Jul-08,10:16:19

B,7,22-Jul-08,10:20:51,0.75

B,8,22-Jul-08,11:12:19

B,4,22-Jul-08,11:15:27

B,2,22-Jul-08,11:17:30

B,5,22-Jul-08,11:18:36,-0.375

B,1,22-Jul-08,11:31:50

B,3,14-Oct-08,9:05:07

B,1,14-Oct-08,9:37:11

B,6,14-Oct-08,9:46:16

B,7,14-Oct-08,9:50:56

B,7,14-Oct-08,9:52:52

B,8,14-Oct-08,9:59:23,7

B,5,14-Oct-08,10:05:13

B,7,14-Oct-08,11:19:44

B,2,14-Oct-08,11:20:15,-0.71429

;

data want (drop= initial x start);

set have;

by name date x;

retain initial;

if first.x then initial=price;

else if last.x then return=(price-initial)/initial;

run;

All Replies
Frequent Contributor
Posts: 102

## Re: How to calculate return based on first and last price in a time interval?

data; infile cards delimiter='09'x;
Input @1 name \$1. @3 price: date: date12. time: time8.;
format date date10. time time12. ;
cards;
A 3 2-Apr-08 9:44:38
A 5 2-Apr-08 9:53:02
A 7 2-Apr-08 9:55:23
A 4 2-Apr-08 9:58:31 0.333333
A 6 2-Apr-08 11:17:33
A 2 2-Apr-08 11:18:33
A 1 2-Apr-08 11:19:33 -0.83333
A 4 2-Apr-08 11:57:33
A 8 7-Apr-08 9:08:39
A 4 7-Apr-08 9:10:22
A 2 7-Apr-08 9:13:41 -0.75
A 3 7-Apr-08 9:32:14
A 7 7-Apr-08 9:41:04 1.333333
A 8 17-Apr-08 9:15:51
A 2 17-Apr-08 10:24:15
B 4 22-Jul-08 10:16:19
B 7 22-Jul-08 10:20:51 0.75
B 8 22-Jul-08 11:12:19
B 4 22-Jul-08 11:15:27
B 2 22-Jul-08 11:17:30
B 5 22-Jul-08 11:18:36 -0.375
B 1 22-Jul-08 11:31:50
B 3 14-Oct-08 9:05:07
B 1 14-Oct-08 9:37:11
B 6 14-Oct-08 9:46:16
B 7 14-Oct-08 9:50:56
B 7 14-Oct-08 9:52:52
B 8 14-Oct-08 9:59:23 7
B 5 14-Oct-08 10:05:13
B 7 14-Oct-08 11:19:44
B 2 14-Oct-08 11:20:15 -0.71429
; * proc print; run;
data one; set; by name date ;
retain start; format start time12.;
if first.date then start=time;
else if time-start ge '1800' then halfhour+1;
*proc print; run;
data want; set; by name date halfhour;
retain initial;
if first.halfhour then initial=price;
else if last.halfhour then return=(price-initial)/initial;
proc print; run;

PROC Star
Posts: 1,309

## Re: How to calculate return based on first and last price in a time interval?

Your first example doesn't seem to calculate pct growth for 30 minute interval. The interval is not more than 15 mins at 9.58pm for the calculation (4-3)/3.

Regular Contributor
Posts: 166

## Re: How to calculate return based on first and last price in a time interval?

Hello @novinosrin.
Yes, the first interval is not more than 15 mins. However, it doesn't matter. Because half an hour intervals are:
9:00-9:29
9:30-9:59
10:00-10:29 and etc.
So, growth values are calculated in each half an hour interval.
PROC Star
Posts: 8,112

## Re: How to calculate return based on first and last price in a time interval?

Then a slight change to the code suggested by @Jim_G should suffice. The following uses the intnx function to adjust starting times to the beginning of each 30 minute period:

```data;
infile cards dlm=',' truncover;
informat date date12.;
informat time time8.;
Input name \$ price date time want_return;
format date date10. time time12. ;
x=INTNX ( 'minutes30', time, 0, 'b');
cards;
A,3,2-Apr-08,9:44:38
A,5,2-Apr-08,9:53:02
A ,7,2-Apr-08,9:55:23
A,4,2-Apr-08,9:58:31,0.333333
A,6,2-Apr-08,11:17:33
A,2,2-Apr-08,11:18:33
A,1,2-Apr-08,11:19:33,-0.83333
A,4,2-Apr-08,11:57:33
A,8,7-Apr-08,9:08:39
A,4,7-Apr-08,9:10:22
A,2,7-Apr-08,9:13:41,-0.75
A,3,7-Apr-08,9:32:14
A,7,7-Apr-08,9:41:04,1.333333
A,8,17-Apr-08,9:15:51
A,2,17-Apr-08,10:24:15
B,4,22-Jul-08,10:16:19
B,7,22-Jul-08,10:20:51,0.75
B,8,22-Jul-08,11:12:19
B,4,22-Jul-08,11:15:27
B,2,22-Jul-08,11:17:30
B,5,22-Jul-08,11:18:36,-0.375
B,1,22-Jul-08,11:31:50
B,3,14-Oct-08,9:05:07
B,1,14-Oct-08,9:37:11
B,6,14-Oct-08,9:46:16
B,7,14-Oct-08,9:50:56
B,7,14-Oct-08,9:52:52
B,8,14-Oct-08,9:59:23,7
B,5,14-Oct-08,10:05:13
B,7,14-Oct-08,11:19:44
B,2,14-Oct-08,11:20:15,-0.71429
;

data one;
set;
by name date ;
retain start;
format start time12.;
if first.date then start=x;
else if time-start ge '1800' then do;
halfhour+1;
start=x;
end;
run;

data want (drop=halfhour initial x start);
set;
by name date halfhour;
retain initial;
if first.halfhour then initial=price;
else if last.halfhour then return=(price-initial)/initial;
run;
```

Art, CEO, AnalystFinder.com

Solution
‎12-25-2017 03:01 PM
PROC Star
Posts: 1,309

## Re: How to calculate return based on first and last price in a time interval?

Merry Christmas @art297  I wonder do we really need two passes once you have the 30 min interval grouping using intnx which you brilliantly coded. So, can't we use that x as a sub group and play with first.x and last.x like here:

Sorry if i am wrong. Just my 2 cents

data have;

infile cards dlm=',' truncover;

informat date date12.;

informat time time8.;

Input name \$ price date time want_return;

format date date10. time time12. ;

x=INTNX ( 'minutes30', time, 0, 'b'); /* I am taking this as a group*/

k=put(x,time12.);

cards;

A,3,2-Apr-08,9:44:38

A,5,2-Apr-08,9:53:02

A ,7,2-Apr-08,9:55:23

A,4,2-Apr-08,9:58:31,0.333333

A,6,2-Apr-08,11:17:33

A,2,2-Apr-08,11:18:33

A,1,2-Apr-08,11:19:33,-0.83333

A,4,2-Apr-08,11:57:33

A,8,7-Apr-08,9:08:39

A,4,7-Apr-08,9:10:22

A,2,7-Apr-08,9:13:41,-0.75

A,3,7-Apr-08,9:32:14

A,7,7-Apr-08,9:41:04,1.333333

A,8,17-Apr-08,9:15:51

A,2,17-Apr-08,10:24:15

B,4,22-Jul-08,10:16:19

B,7,22-Jul-08,10:20:51,0.75

B,8,22-Jul-08,11:12:19

B,4,22-Jul-08,11:15:27

B,2,22-Jul-08,11:17:30

B,5,22-Jul-08,11:18:36,-0.375

B,1,22-Jul-08,11:31:50

B,3,14-Oct-08,9:05:07

B,1,14-Oct-08,9:37:11

B,6,14-Oct-08,9:46:16

B,7,14-Oct-08,9:50:56

B,7,14-Oct-08,9:52:52

B,8,14-Oct-08,9:59:23,7

B,5,14-Oct-08,10:05:13

B,7,14-Oct-08,11:19:44

B,2,14-Oct-08,11:20:15,-0.71429

;

data want (drop= initial x start);

set have;

by name date x;

retain initial;

if first.x then initial=price;

else if last.x then return=(price-initial)/initial;

run;

PROC Star
Posts: 8,112

## Re: How to calculate return based on first and last price in a time interval?

Merry Christmas (and/or Happy Holidays) to you and everyone else. In answer to your question: of course you could .. as long as you remove the variable 'start' from the drop list. However, you'd still need two passes if you didn't create the variable 'x' in the initial datastep.

I wasn't trying to optimize the code but, rather, simply modify @Jim_G's code so that it met the newly stated condition.

Art, CEO, AnalystFinder.com

Regular Contributor
Posts: 166

## Re: How to calculate return based on first and last price in a time interval?

Thank you so much @art297,
Merry Christmas to everyone.
☑ This topic is solved.