Computing 10-second price difference

[Khaladdin]

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. 

[PeterClemmensen · Accepted solution]

@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;

[Khaladdin]

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?

[Khaladdin]

This code works well. The only issue is it returns 0 if you do not have value within 10 seconds.

[AhmetHakan]

Hi @PeterClemmensen, @Ksharp 

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?

[PeterClemmensen]

@AhmetHakan I suggest you open a new thread 🙂

[AhmetHakan]

@PeterClemmensen ,

Thanks. I opened, however, @Ksharp directed me here.

[PeterClemmensen]

Ok. Do you want the SQL code or data step?

[AhmetHakan]

@PeterClemmensen ,

Many thanks, much appreciated.

Both would work for me. However, I could not clearly understand data code, therefore, asked sql. 

[PeterClemmensen]

@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   . 

@ 

[AhmetHakan]

@PeterClemmensen ,

I think this is for me 🙂 Many thanks indeed. One final question (really sorry). I need to do this for each day. 

[PeterClemmensen]

What do you mean? There is no day here?

[AhmetHakan]

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?

[PeterClemmensen]

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. 

[AhmetHakan]

Thanks @PeterClemmensen,

I am opening a new thread.