Solved
Contributor
Posts: 26

Compute the number of times that an order from investor i at time t is followed by an order from investor j at time t+ τ

Hello everyone,

I'm currently writing my thesis and I'm facing a serious issue. It would be awesome if you could help me with it.

My database is a limit order book. In there you can see the orders sumbitted by traders accross time.Here is what my database look like

My goal : Let's take the example of a trader i who submits an order. I want the number of times - per day - that another trader j submitted an order within an hour after the submission of the order of the trader i.

I don't know if was clear enough  so  if you want I can rewrite it better.

Accepted Solutions
Solution
‎06-27-2014 06:32 PM
Super User
Posts: 13,583

Re: Compute the number of times that an order from investor i at time t is followed by an order from investor j at time t+ τ

I think this may do what you're looking for. The dataset intrade can then be used in proc freq.

Note: This may take a  long time to run as there are lots of joins and comparisons going.

I include the datetimes of the trades so you can eyeball whether it's working correctly and you may want to segment you data on dates or times later.

input ID_stock   ID_Traders Date :ddmmyy10. Hour  :time10.;
datetime = dhms(date,0,0,hour);
format date ddmmyy10. hour time10. datetime datetime.;
datalines;
1 10 1/02/2014 09:00:12
1 14 1/02/2014 09:01:45
1 15 1/02/2014 09:40:32
1 10 1/02/2014 09:52:33
1 12 1/02/2014 10:01:23
1 13 1/02/2014 10:02:32
1 46 1/02/2014 10:23:32
1 65 1/02/2014 10:32:09
1 43 1/02/2014 10:50:23
1 23 1/02/2014 11:23:12
1 90 1/02/2014 11:34:12
1 96 1/02/2014 11:35:32
1 102 1/02/2014 11:42:09
1 243 1/02/2014 11:43:32
1 32 1/02/2014 11:54:56
;
run;

proc sql;
quit;

All Replies
Super User
Posts: 23,771

Re: Compute the number of times that an order from investor i at time t is followed by an order from investor j at time t+ τ

Its best to post sample data and output.

Super User
Posts: 13,583

Re: Compute the number of times that an order from investor i at time t is followed by an order from investor j at time t+ τ

Step one: Create a sas datetime variable from your date and times. There are lots of comparison functions that will work much better with a single variable.

Contributor
Posts: 26

Re: Compute the number of times that an order from investor i at time t is followed by an order from investor j at time t+ τ

@Reeza : Hereunder is one illustration of the Database I have. In my real database I have about 180different trades over 60 trading days and 100 different stocks. I think a great output would be similar to what we obtain with a PROC FREQ. That is to say a kind of frequency table with the ID of the traders on both axis x and y.

@Ballardw: I'm looking to  cross all traders in my data. Thanks for step one, I'll do it right away.

 ID_stock ID_Traders Date Hour 1 10 1/02/2014 09:00:12 1 14 1/02/2014 09:01:45 1 15 1/02/2014 09:40:32 1 10 1/02/2014 09:52:33 1 12 1/02/2014 10:01:23 1 13 1/02/2014 10:02:32 1 46 1/02/2014 10:23:32 1 65 1/02/2014 10:32:09 1 43 1/02/2014 10:50:23 1 23 1/02/2014 11:23:12 1 90 1/02/2014 11:34:12 1 96 1/02/2014 11:35:32 1 102 1/02/2014 11:42:09 1 243 1/02/2014 11:43:32 1 32 1/02/2014 11:54:56
Super User
Posts: 13,583

Re: Compute the number of times that an order from investor i at time t is followed by an order from investor j at time t+ τ

Another question: Are you also looking for a trade of the same stock (I suspect yes) or just any stock?

Contributor
Posts: 26

Re: Compute the number of times that an order from investor i at time t is followed by an order from investor j at time t+ τ

Yes I am looking for a trade of the same stock

Solution
‎06-27-2014 06:32 PM
Super User
Posts: 13,583

Re: Compute the number of times that an order from investor i at time t is followed by an order from investor j at time t+ τ

I think this may do what you're looking for. The dataset intrade can then be used in proc freq.

Note: This may take a  long time to run as there are lots of joins and comparisons going.

I include the datetimes of the trades so you can eyeball whether it's working correctly and you may want to segment you data on dates or times later.

input ID_stock   ID_Traders Date :ddmmyy10. Hour  :time10.;
datetime = dhms(date,0,0,hour);
format date ddmmyy10. hour time10. datetime datetime.;
datalines;
1 10 1/02/2014 09:00:12
1 14 1/02/2014 09:01:45
1 15 1/02/2014 09:40:32
1 10 1/02/2014 09:52:33
1 12 1/02/2014 10:01:23
1 13 1/02/2014 10:02:32
1 46 1/02/2014 10:23:32
1 65 1/02/2014 10:32:09
1 43 1/02/2014 10:50:23
1 23 1/02/2014 11:23:12
1 90 1/02/2014 11:34:12
1 96 1/02/2014 11:35:32
1 102 1/02/2014 11:42:09
1 243 1/02/2014 11:43:32
1 32 1/02/2014 11:54:56
;
run;

proc sql;
quit;

Posts: 3,167

Re: Compute the number of times that an order from investor i at time t is followed by an order from investor j at time t+ τ

's suggestion is spot on,  it could  get messy when inter-days. So HDMS function is worth taking a look here. For example your have this newdttm in date time format, and the trader of interest is 11. the code has NOT been tested :

Proc SQL ;

Create table want as

Select *, (select count(*) from have where stock_id=a.stock_id and newdttm between a.newdttm and a.newdttm +3600 and trader_id='11') as ct11  from have a

;

Quit;

This will give you the number of trades 'trader 11' does for a particular stock within one hour of range  after  any trade done by anyone.

Contributor
Posts: 26

Re: Compute the number of times that an order from investor i at time t is followed by an order from investor j at time t+ τ

Thank you very much !!! That's exactly what I was looking for

🔒 This topic is solved and locked.