Many thanks for this. However, it is different from what I want. With this code, in want dataset, I know have only one observation for 10:02:16, which is volume-weighted average. I don't want to lose any observation from the data.
@Khaladdin If you go for the SQL approach, you have to read from have instead of x like this
proc sql;
create table want as
select *,
(price-(select price from x where time between a.time and a.time+10 having time=max(time)))/price as diff format = 8.4
from have as a;
quit;
Many thanks, @PeterClemmensen. It works well. However, now, the only issue is if we do not have any price within 10 seconds, then it returns 0. How can I fix it?
This code works well. The only issue is it returns 0 if you do not have value within 10 seconds.
I also need similar code. However, in my case, if I do not have observation within 10 seconds, then the code should return . (missing). Furthermore, I want to do the same computation for each day. Could you please how can I update this sql code to get what I want?
@AhmetHakan I suggest you open a new thread 🙂
Ok. Do you want the SQL code or data step?
Many thanks, much appreciated.
Both would work for me. However, I could not clearly understand data code, therefore, asked sql.
@Khaladdin Not much of an SQL guy, but a small correction to my data step code gives you what you want
data have;
input Time :time8. Volume Price;
format Time time8.;
datalines;
10:02:04 100 100.25
10:02:07 200 100.35
10:02:14 300 100.55
10:02:16 100 100.60
10:02:16 300 100.70
10:02:23 200 100.80
10:02:35 100 100.90
10:02:47 300 101.20
10:02:53 200 101.40
10:03:11 100 101.60
10:03:33 300 101.70
10:03:37 200 101.80
10:03:45 100 101.60
10:03:53 300 101.70
10:04:32 200 102.80
;
data want(keep = Time Volume Price dif);
if _N_ = 1 then do;
dcl hash h(multidata : "Y", ordered : "Y");
h.definekey("t");
h.definedata("t", "v", "p");
h.definedone();
dcl hiter hi("h");
dcl hash hh();
hh.definekey("t");
hh.definedata("t", "s");
hh.definedone();
do until (z);
set have end = z;
h.add(key : time, data : time, data : Volume, data : Price);
if hh.find(key : Time) ne 0 then s = Volume;
else s + Volume;
hh.replace(key : Time, data : Time, data : s);
end;
end;
set have;
t = .; v = .; p = .; s = .;
if h.find(key : Time + 10) = 0 then mt = t;
else do;
rc = hi.setcur(key : Time);
do i = 1 by 1 while(hi.next() = 0);
if t - time > 10 then do;
if i = 1 then mt = .;
leave;
end;
mt = t;
end;
end;
rc = hh.find(key : mt);
rc = h.reset_dup();
do while (h.do_over(key : mt) = 0);
pp = sum(pp, divide(v, s) * p);
end;
dif = (Price - pp) / Price;
format dif 8.4;
run;
Result:
Time Volume Price dif 10:02:04 100 100.25 -0.0030 10:02:07 200 100.35 -0.0032 10:02:14 300 100.55 -0.0025 10:02:16 100 100.60 -0.0020 10:02:16 300 100.70 -0.0010 10:02:23 200 100.80 . 10:02:35 100 100.90 . 10:02:47 300 101.20 -0.0020 10:02:53 200 101.40 . 10:03:11 100 101.60 . 10:03:33 300 101.70 -0.0010 10:03:37 200 101.80 0.0020 10:03:45 100 101.60 -0.0010 10:03:53 300 101.70 . 10:04:32 200 102.80 .
@
I think this is for me 🙂 Many thanks indeed. One final question (really sorry). I need to do this for each day.
What do you mean? There is no day here?
In this specific example, you only have time. However, in my data, I have days too. So, I need to compute this percentage change for each day. I am curious whether I need to update the code?
This raises new questions:
Are we allowed to cross days ?
Are the Dates in a separate variable or datetime values?
Again, I suggest you post a new question. Because this is a new question.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.