*________________________________________________________________________________________________________________________________
	taqExtract01	General extraction program for taqmsec data. Constructs nbbos, etc.
	(from ibm02. runs on wrds via remote)
	the wrds library for millisecond taq is taqmsec;
;;
dm log 'clear';
%let wrds=wrds-cloud.wharton.upenn.edu 4016;
options comamid=TCP remote=WRDS;
options nocenter orientation=landscape ls=max ps=max;
signon username=_prompt_;
*ods html;
%macro runquit;
%abort cancel;
%mend runquit;
rsubmit;
libname home '/home/nyu/jhasbrou';
libname temp '/scratch/nyu/agora';	*	you might have to make this, if its been a long time ...;
libname maclib '/home/nyu/jhasbrou/sasmacros';
options nocenter orientation=landscape ls=max ps=100 nolabel;
options mautosource append=(sasautos=('maclib'));	* this doesnt seem to work;
%include "/home/nyu/jhasbrou/sasmacros/printds.sas" /source2;
/*proc contents data=taqmsec.ctm_20160729;*/
/*run;*/
;
%macro taq_daily_dataset_list(type=ct,begyyyymmdd=,endyyyymmdd=)
 / des="Autogenerated list of needed Daily TAQ datasets";
 %let type=%lowcase(&type);
 /* Get SAS date values for date range endpoints */
 %let begdate = %sysfunc(inputn(&begyyyymmdd,yymmdd8.));
 %let enddate = %sysfunc(inputn(&endyyyymmdd,yymmdd8.));
 %put &begdate;
 %put &enddate;
 %do d=&begdate %to &enddate /** For each date in the DATE range */;
 %let yyyymmdd=%sysfunc(putn(&d,yymmddn8.));
 %put &d &yyyymmdd;
 /*If the corresponding dataset exists, add it to the list */
%if %sysfunc(exist(taqmsec.&type._&yyyymmdd)) %then taqmsec.&type._&yyyymmdd;
 %end;
%mend;
;
* using this macro;
options nomprint nosymbolgen;
/*data my_output;*/
/*%put %taq_daily_dataset_list(type=ctm,begyyyymmdd=20171001,endyyyymmdd=20171031);*/
/*run;*/
%macro setprec(timeVar,precVar);
	s = second(&timeVar);
	if not missing(s) then &precVar=0;
	if mod(s,.01)^=0 then &precVar=3;
	if mod(s,.00001)^=0 then &precVar=6;
	if mod(s,.00000001)^=0 then &precVar=9;
%mend;
%macro getTAQt;
data temp.taqt;
	set taqmsec.ctm_201610: taqmsec.ctm_201611: ;
	where sym_root in('IBM','NVDA');
	wkno=week(date);
	year=year(date);
	*	check precision of time stamps;
	%setprec(part_time,prec_part);
	%setprec(time_m, prec_m);
run;
%mend;
%*getTAQt;
%macro tablesTAQt;
ods rtf path='/scratch/nyu/agora' file='taqTab1.rtf';
/*ods rtf path='c:/' file=taqTab1.rtf;*/
proc tabulate data=temp.taqt noseps format=comma10. missing;
	class sym_root sym_suffix date ex year wkno prec_part prec_m;
	table sym_root*sym_suffix,n='' / box='root/suffix';
	table sym_root=''*year='',(all wkno*date),(all*f=comma9. ex*f=comma5.) / rts=40 box='exchange counts';
	table sym_root=''*year='',(all wkno*date),n*f=comma9. (all ex)*rowpctn=''*f=6.1 / rts=40 box='exchange %';
	table (all sym_root='')*year='',(all ex), n*(prec_part prec_m)*f=comma10. / rts=30 box='time precision';
	format date date.;
	title 'Trade date counts';
	run;
ods rtf close;
%mend tablesTAQt;
%tablesTAQt;
%runquit;
%macro getTAQq;
data temp.taqq;
	set taqmsec.cqm_201610: taqmsec.cqm_201611: ;
	where sym_root in('IBM','NVDA');
	wkno=week(date);
	year=year(date);
	*	check precision of time stamps;
	%setprec(part_time,prec_part);
	%setprec(time_m, prec_m);
run;
;
%mend;
%*getTAQq;
%*printds(data=temp.taqq,contents=yes);
%macro tablesTAQq;
proc tabulate data=temp.taqq noseps format=comma11. missing;
	class sym_root sym_suffix date ex year wkno prec_part prec_m qu_cond natbbo_ind nasdbbo_ind finra_bbo_ind luld_bbo_cqs luld_bbo_ind luld_bbo_utp
		qu_cancel qu_source;
	table sym_root*sym_suffix,n='' / box='root/suffix';
	table sym_root=''*year='',(all wkno*date),(all*f=comma9. ex*f=comma5.) / rts=40 box='exchange counts';
	table sym_root=''*year='',(all wkno*date),n*f=comma9. (all ex)*rowpctn=''*f=6.1 / rts=40 box='exchange %';
	table (all sym_root='')*year='',(all ex), n*(prec_part prec_m)*f=comma10. / rts=30 box='time precision';
	table sym_root=''*year='',qu_source*n='';
	table sym_root=''*year='',qu_cond*n='';
	table sym_root=''*year='',qu_cancel*n='';
	table sym_root=''*year='',qu_source*natbbo_ind*n='';
	table sym_root=''*year='',qu_source*nasdbbo_ind*n='';
	table sym_root=''*year='',qu_source*finra_bbo_ind*n='';
	table sym_root=''*year='',luld_bbo_cqs*n='';
	table sym_root=''*year='',luld_bbo_ind*n='';
	table sym_root=''*year='',luld_bbo_utp*n='';
	title 'Quotes';
	run;
	title;
%mend;




*________________________________________________________________________________________________________________________________
	General routine to construct NBBOs from a set of exchange#s quotes.
;;
%macro buildNBBO(dsIn,dsOut,timeVar);
*	This routine uses a hash table to keep track of all exchanges# current quotes;
data &dsIn._2;
	set &dsIn;
	by date sym_root &timeVar;
	length bidm askm bidsizm asksizm 8;
	drop bidm askm bidsizm asksizm nbbPrev nboPrev;
	retain kRecord 0;	*	record counter;
	retain nbbPrev nboPrev;	*	prevailing/preceding the current record;
	kRecord = kRecord+1;
	length kex $1;		* k(ey) exchange;
	* setup and declarations for hash table;
	if _n_=1 then do;	
		declare hash quotes();
		rc = quotes.defineKey('kex');
		rc = quotes.defineData('bidm','askm','bidsizm','asksizm');
		rc = quotes.defineDone();
		declare hiter iq('quotes');	* the hash table iterator;
	end;
	drop rc1 rc bidm askm bidsizm asksizm kex x;
	length bid_ch ask_ch bidsiz_ch asksiz_ch nbb_ch nbo_ch 3;
	bid_ch = .;	*	indicator variable for a changed bid;
	ask_ch = .;	*	... and a changed ask;
	bidsiz_ch = .;
	asksiz_ch = .;
	nbb_ch = .;
	nbo_ch = .;
	kex = ex;
	if first.sym_root or first.date then do;
		rc = quotes.clear();
		nbb = .;
		nbo = .;
		nbbPrev = .;
		nboPrev = .;
	end;
	rc1 =quotes.find();	* look for ex in the quotes table;
	if rc1^=0 then do;	* if not found then add to table;
		add2Table = 0;
		drop add2Table;
		if bid>0 and bidsiz>0 then do;
			bid_ch=1;
			bidsiz_ch=1;
			bidm = bid;
			bidsizm = bidsiz;
			add2Table = 1;
		end;
		if ask>0 and asksiz>0 then do;
			ask_ch = -1;
			asksiz_ch = 1;
			askm = ask;
			asksizm = asksiz;
			add2Table = 1;
		end;
		if add2Table then rc = quotes.add();
	end;
	else do;	*	ex found. update the exchange record and set change flags;
		if bid=0 or bidsize=0 then do;
			if not missing(bidm) then bid_ch=-1;
			bidm = .;
			bidsizm = .;
		end;
		else do;
			if not missing(bidm) then do;
				bid_ch = sign(bid-bidm);
				bidsiz_ch = sign(bidsiz - bidsizm);
			end;
			bidm = bid;
			bidsizm = bidsiz;
		end;
		if ask=0 or asksize=0 then do;
			if not missing(askm) then ask_ch=+1;
			askm = .;
			asksizm = .;
		end;
		else do;
			if not missing(askm) then do;
				ask_ch = sign(askm-ask);
				asksiz_ch = sign(asksiz - asksizm);
			end;
			askm = ask;
			asksizm = asksiz;
		end;
		rc = quotes.replace();
	end;
	*	nbbo calculations;
	retain nbb nbo;
	if bid_ch^=0 then do;	* if the bid in the current record has changed, then recalculate the nbb;
		x = -1;
		rc = iq.first();
		do while (rc=0);
			if bidm>0 and bidm>x then x=bidm;
			rc = iq.next();
		end;
		if x>0 and (nbb=. or x^=nbb) then nbb=x;
	end;
	if ask_ch^=0 then do;	* if the ofr in the current record has changed, then recalculate the ofr;
		x = 1000000;
		rc = iq.first();
		do while (rc=0);
			if askm>0 and askm<x then x=askm;
			rc = iq.next();
		end;
		if x<1000000 and (nbo=. or x^=nbo) then nbo=x;
	end;
	if not (missing(nbb) or missing(nbbPrev)) then nbb_ch=sign(nbb-nbbPrev);
	if not (missing(nbo) or missing(nboPrev)) then nbo_ch=sign(nboPrev-nbo);
	output;
	nbbPrev = nbb;
	nboPrev = nbo;
	run;
%let dsnbb=&dsOut._nbb;
%let dsnbo=&dsOut._nbo;
data 	&dsnbb (keep=date sym_root timeMid nbb)
		&dsnbo (keep=date sym_root timeMid nbo);
	set &dsIn._2;
	by date sym_root &timeVar;
	timeMid = &timeVar;
	format timeMid 20.9;
	if nbb_ch then output &dsnbb;
	if nbo_ch then output &dsnbo;
	run;
%mend buildNBBO;
*________________________________________________________________________________________________________________________________
	nbbo from participant timestamps;
;;
%macro buildNBBOPartSIP;
proc sort data=temp.taqq out=temp.cqPart;
	by date sym_root part_time;
	run;
%buildNBBO(temp.cqPart, temp.Part, part_time);
proc sort data=temp.taqq out=temp.cqSIP;
	by date sym_root time_m;
	run;
%buildNBBO(temp.cqSIP, temp.SIP, time_m);
%mend;
options mprint;
%*buildNBBOPartSIP;
/*%printds(data=temp.cqPart_2,contents=yes);*/
/*ods rtf file='taqTables2.rtf';*/
/*proc tabulate data=temp.cqPart_2 format=comma10. missing noseps;*/
/*	class sym_root date nbb_ch nbo_ch bid_ch ask_ch bidsiz_ch asksiz_ch;*/
/*	table sym_root*(all nbb_ch),(all nbo_ch)*n='' / rts=18 misstext=' ';*/
/*	table sym_root*(all nbb_ch),(all bid_ch)*n='' / rts=18 misstext=' ';*/
/*	table sym_root*(all nbo_ch),(all ask_ch)*n='' / rts=18 misstext=' ';*/
/*	table sym_root*(all nbb_ch),(all bidsiz_ch)*n='' / rts=18 misstext=' ';*/
/*	table sym_root*(all nbo_ch),(all asksiz_ch)*n='' / rts=18 misstext=' ';*/
/*	title 'nbbo changes';*/
/*	run;*/
/*ods rtf close;*/
/*proc print data=temp.cqPart_2 (obs=5);*/
/*proc print data=temp.cqPart_2 (where=(sym_root='IBM' and bid_ch=1 and nbb_ch=-1) obs=10);*/
/*run;*/
/*%printds(data=temp.part_nbb);*/
/*proc tabulate data=temp.part_nbb noseps format=11.0;*/
/*	class date sym_root;*/
/*	table sym_root*date,n='' / rts=18;*/
/*	run;*/
/*endrsubmit;*/
/*%runquit;*/

*________________________________________________________________________________________________________________________________
	nbbo from listing exchange and other exchanges.
;;
%macro buildNBBOlex;
proc sort data=temp.taqq out=temp.cqPart;
	by date sym_root part_time;
	run;
data temp.cqLEX temp.cqOthers;
	set temp.cqPart;
	if (sym_root='IBM' and ex='N') or (sym_root='NVDA' and ex in ('T','Q')) then output temp.cqLEX;
	else output temp.cqOthers;
	run;
%buildNBBO(temp.cqLEX, temp.LEX, part_time);
%buildNBBO(temp.cqOthers, temp.EXL, part_time);
%mend;
%*buildNBBOlex;
/*proc tabulate data=temp.cqLEX noseps format=comma10.;*/
/*	class date sym_root;*/
/*	table date,sym_root / rts=15;*/
/*	title 'cqLEX';*/
/*	run;*/
/*proc tabulate data=temp.cqPart noseps format=comma10.;*/
/*	class date sym_root ex;*/
/*	table sym_root,date,(all ex) / rts=15;*/
/*	title temp.cqPart;*/
/*	run;*/
*________________________________________________________________________________________________________________________________
	trades, dark and lit.
;;
%*printds(data=temp.taqt);
%macro buildTrades;
proc sort data=temp.taqt out=temp.ctPart;
	by date sym_root part_time;
	run;
data temp.ctDark temp.ctLit;
	set temp.ctPart;
	by date sym_root;
	format part_time 20.9;
	if ex='D' then output temp.ctDark;
	else output temp.ctLit;
	keep date sym_root part_time ex price size;
	run; 
%mend;
%*buildTrades;
endrsubmit;
*signoff;
