That is because there is no price at that time . haikuo bian has already pointed out it . I make a TEMP dataset to hold every second price value. data have;
input date :date9. Co $ time : time9. price vol ;
format date date9. time time.;
cards;
01JAN2012 ABC 9:55:59 166.00 100
01JAN2012 ABC 9:56:02 165.40 50
01JAN2012 ABC 9:56:02 165.40 25
01JAN2012 ABC 9:56:03 166.45 700
01JAN2012 ABC 9:56:03 166.80 20
01JAN2012 ABC 9:56:03 167.00 280
01JAN2012 ABC 9:56:06 166.45 1
01JAN2012 ABC 9:56:06 166.05 24
01JAN2012 ABC 9:56:15 166.50 500
01JAN2012 ABC 9:56:16 166.50 25
01JAN2012 ABC 9:56:17 166.50 475
01JAN2012 ABC 9:56:22 166.25 10
01JAN2012 ABC 9:56:26 166.05 50
01JAN2012 ABC 9:56:30 166.25 20
01JAN2012 ABC 9:56:38 166.05 126
01JAN2012 ABC 9:56:40 166.00 100
01JAN2012 ABC 9:56:40 166.00 1
01JAN2012 ABC 9:56:40 166.00 1
01JAN2012 ABC 9:56:43 166.00 1
01JAN2012 ABC 9:56:46 166.00 2
;
run;
data temp;
merge have have(firstobs=2 keep=date time rename=(date=_date time=_time));
output;
if date eq _date then do;
do i=time+1 to _time-1;
time=i;output;
end;
end;
run;
proc sql;
create table want as
select *,log(price/(select price from temp where date=a.date and time+5=a.time)) as return
from have as a;
quit;
Xia Keshan
... View more