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.
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;
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;
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.
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
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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.