Alright. You can link to this thread for understanding.
Also, please post sample data in the form of a data step. Like in my code snippets.
OK. You want this ?
proc summary data=have nway; class Time ; var Price/weight=Volume; output out=x(drop=_:) mean=; run;proc sql; create table want as select *, (price-(select price from x where time between a.time+1 and a.time+10 having time=max(time)))/price as diff format = 8.4 from have as a; quit;
I think this is what you need.
Let me know if this works for you.
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 = t;
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 -0.0010 10:02:35 100 100.90 -0.0030 10:02:47 300 101.20 -0.0020 10:02:53 200 101.40 -0.0020 10:03:11 100 101.60 -0.0010 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 -0.0108 10:04:32 200 102.80 .
It works. Many thanks indeed.
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.