BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
crikriek
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

8 REPLIES 8
ballardw
Super User

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.

crikriek
Calcite | Level 5

@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
ballardw
Super User

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

ballardw
Super User

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;

Haikuo
Onyx | Level 15

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

crikriek
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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