Dear all, I hope you are doing well despite the pandemic. The content of the question is copied from https://communities.sas.com/t5/SAS-Programming/Computing-10-second-price-difference/td-p/716368 and then minor adjustments have been made. I have the following dataset: Time Volume Price Date 10:02:04 100 100.25 10.01.2021 10:02:07 200 100.35 10.01.2021 10:02:14 300 100.55 10.01.2021 10:02:16 100 100.60 10.01.2021 10:02:16 300 100.70 10.01.2021 10:02:23 200 100.80 10.01.2021 10:02:04 100 100.25 11.01.2021 10:02:07 200 100.35 11.01.2021 10:02:14 300 100.55 11.01.2021 10:02:16 100 100.60 11.01.2021 10:02:16 300 100.70 11.01.2021 10:02:23 200 100.80 11.01.2021 And here is what I want: Time Volume Price Date Diff 10:02:04 100 100.25 10.01.2021 -0.0029 = (100.25 - 100.55)/100.25 = (price(t)-price(t+10))/price(t) 10:02:07 200 100.35 10.01.2021 -0.0032 = (100.35 - 100.675)/100.35 10:02:14 300 100.55 10.01.2021 -0.0012 = (100.55 - 100.675)/100.55 10:02:16 100 100.60 10.01.2021 . (because there is not observation within 10 seconds window) 10:02:16 300 100.70 10.01.2021 10:02:33 200 100.80 10.01.2021 The same computations for the next day In short, I need to find price change over 10 seconds windows with some modifications: 1. .If I do not have p(t+10), then I need to select the price that is closest to p(t+10). If I do not have any value within 10 second windows then, I should get ".". 2. If I have two prices in one second, I need to compute the volume-weighted average price for this second and then use it. For example, in my data, I have an interval of 10:02:07. I do not have a price at 10:02:17 and the closest one is the price at 10:02:16. However, in this interval, I have two prices. Therefore, I am computing the volume-weighted average price for this interval (((100/400)*100.60 + (300/400)*100.70) = 100.675) and then use this one. 3. I need to do same computation for each day. Cross days are not allowed. So, each day will have a new start. I have separate date and time variable. Date format is DDMMYYN8. and time format is TOD. Thank you!
... View more
