topic How to calculate return based on first and last price in a time interval? in SAS Programming

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

aminkarimid — Mon, 25 Dec 2017 10:37:22 GMT

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?

Thanks in advance.

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

Jim_G — Mon, 25 Dec 2017 13:49:30 GMT

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;

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

novinosrin — Mon, 25 Dec 2017 16:03:05 GMT

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.

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

aminkarimid — Mon, 25 Dec 2017 17:20:35 GMT

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.

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

art297 — Mon, 25 Dec 2017 17:26:12 GMT

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

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

novinosrin — Mon, 25 Dec 2017 19:16:07 GMT

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;

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

art297 — Mon, 25 Dec 2017 19:40:18 GMT

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

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

aminkarimid — Mon, 25 Dec 2017 20:00:58 GMT

Thank you so much @art297,
Merry Christmas to everyone.