Computing 10-second price difference

[PeterClemmensen]

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.

[Ksharp]

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;

[PeterClemmensen]

@Ksharp Cool 🙂

I think you should read from have instead of x though?

[Ksharp]

You mean using HAVE as table name ,not X ?

[Khaladdin]

I think it means we should do from HAVE instead of from X.

[PeterClemmensen]

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  . 

[Khaladdin]

It works. Many thanks indeed.