Help using Base SAS procedures

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+ τ

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

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

ID_stock     ID_Traders  Date   Hour

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.

Could you please help me ?

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

Thanks in advance


Accepted Solutions
Solution
‎06-27-2014 06:32 PM
Super User
Posts: 11,343

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.

data trades;
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;
create table intrade as
select a.ID_stock, a.ID_Traders as FirstTrader, b.ID_Traders as SecondTrader,
    a.datetime as FirstTrade, b.datetime as SecondTrade
from trades as a join trades as b on a.ID_stock=B.ID_stock
where (a.ID_Traders ne b.ID_Traders) and (a.datetime ge intnx('hour',b.datetime,-1));
quit;

View solution in original post


All Replies
Super User
Posts: 19,799

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: 11,343

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+ τ

Are  asking about a specific pair of traders that you will code or are you looking to cross all traders in your data?

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_TradersDateHour
1101/02/201409:00:12
1141/02/201409:01:45
1151/02/201409:40:32
1101/02/201409:52:33
1121/02/201410:01:23
1131/02/201410:02:32
1461/02/201410:23:32
1651/02/201410:32:09
1431/02/201410:50:23
1231/02/201411:23:12
1901/02/201411:34:12
1961/02/201411:35:32
11021/02/201411:42:09
12431/02/201411:43:32
1321/02/201411:54:56
Super User
Posts: 11,343

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: 11,343

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.

data trades;
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;
create table intrade as
select a.ID_stock, a.ID_Traders as FirstTrader, b.ID_Traders as SecondTrader,
    a.datetime as FirstTrade, b.datetime as SecondTrade
from trades as a join trades as b on a.ID_stock=B.ID_stock
where (a.ID_Traders ne b.ID_Traders) and (a.datetime ge intnx('hour',b.datetime,-1));
quit;

Respected Advisor
Posts: 3,156

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 Smiley Happy

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 341 views
  • 3 likes
  • 4 in conversation