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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 166
Accepted Solution

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

namepricedatetimeRETURN
A32-Apr-089:44:38 
A52-Apr-089:53:02 
A72-Apr-089:55:23 
A42-Apr-089:58:310.333333
A62-Apr-0811:17:33 
A22-Apr-0811:18:33 
A12-Apr-0811:19:33-0.83333
A42-Apr-0811:57:33 
A87-Apr-089:08:39 
A47-Apr-089:10:22 
A27-Apr-089:13:41-0.75
A37-Apr-089:32:14 
A77-Apr-089:41:041.333333
A817-Apr-089:15:51 
A217-Apr-0810:24:15 
B422-Jul-0810:16:19 
B722-Jul-0810:20:510.75
B822-Jul-0811:12:19 
B422-Jul-0811:15:27 
B222-Jul-0811:17:30 
B522-Jul-0811:18:36-0.375
B122-Jul-0811:31:50 
B314-Oct-089:05:07 
B114-Oct-089:37:11 
B614-Oct-089:46:16 
B714-Oct-089:50:56 
B714-Oct-089:52:52 
B814-Oct-089:59:237
B514-Oct-0810:05:13 
B714-Oct-0811:19:44 
B214-Oct-0811: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.


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

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;

View solution in original post


All Replies
Frequent Contributor
Posts: 102

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

Posted in reply to aminkarimid

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,592

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

Posted in reply to aminkarimid

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?

Posted in reply to novinosrin
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,149

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

Posted in reply to aminkarimid

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,592

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,149

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

Posted in reply to novinosrin

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 236 views
  • 3 likes
  • 4 in conversation