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
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;
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.
@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 |
Another question: Are you also looking for a trade of the same stock (I suspect yes) or just any stock?
Yes I am looking for a trade of the same stock
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;
'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.
Thank you very much !!! That's exactly what I was looking for
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.
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.