BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
aminkarimid
Lapis Lazuli | Level 10

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.

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

7 REPLIES 7
Jim_G
Pyrite | Level 9

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;

novinosrin
Tourmaline | Level 20

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. 

 

aminkarimid
Lapis Lazuli | Level 10
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.
art297
Opal | Level 21

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

 

novinosrin
Tourmaline | Level 20

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;

art297
Opal | Level 21

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

 

 

aminkarimid
Lapis Lazuli | Level 10
Thank you so much @art297,
Merry Christmas to everyone.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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