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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.