* ________________________________________________________________________________________________________________________ crspSelect Joel Hasbrouck September 2011 This program runs on wrds, and builds a small trade and quote dataset ('ctqAll') for student analysis. It uses macros contained in the directory 'sasmacros'. The principal steps are: 1. From the crsp daily stock file, compute average daily volumes for all stocks in the last quarter of 2010. 2. Compute volume deciles. 3. Determine which stocks had (on at least one day in the quarter) an absolute return of at least ten percent on positive volume with a price of at least $5. 4. Take stocks in the volume decile 1 (next to lowest) that also satisfy the return criterion. 5. Match these to the TAQ master file on cusip and ticker symbol. 6. Take the first ten stocks that match. For each stock and date in the quarter: 1. Construct an nbbo series from the cq file. 2. Merge with the trades. Notes: the NBBO is computed using the nbbofast macro. This is a minimal computation. It filters the quotes (see the cqfilter macro) but does not keep a record of which exchanges are at the nbbo, nor does it compute the sizes at the bid and offer. The nbbo is computed using all cq records in a given second under the assumption that they are correctly sequenced. An nbbo record is generated only when the nbbo changes. ________________________________________________________________________________________________________________________ ; options source nodate nocenter nonumber ps=max ls=72; options nocenter ps=70 ls=110 sasautos=('$HOME/sasmacros','.') mprint; libname this '.'; * Make and set a library reference to a temporary directory. *x 'mkdir /sastemp2/agora'; libname temp '/sastemp2/agora'; * Set this to some temporary directory; /*_______________________________________________________________________________________________________________________ Use the wrds crspmerge macro to retrieve data from the crsp dse and dsf files. ________________________________________________________________________________________________________________________; %crspmerge(s=d,start=01Sep2010,end=30Dec2010,sfvars=ret prc vol,final_ds=temp.c, filters=shrcd in(10,11,12) and exchcd in(1,2,3)); *_______________________________________________________________________________________________________________________ Construct average volume deciles ________________________________________________________________________________________________________________________; proc means data=temp.c noprint; var vol; class permno; types permno; id ncusip; output mean= out=temp.mv (drop=_type_); run; proc rank data=temp.mv out=temp.mvr groups=10; var vol; ranks volRank; run; proc tabulate data=temp.mvr noseps format=comma12.0; class volRank; var vol; table (all volRank), (n mean median min max)*vol; run; *_______________________________________________________________________________________________________________________ Make list of stocks with large returns on at least one day. ________________________________________________________________________________________________________________________; data temp.r; set temp.c; where abs(prc)>5 and abs(ret)>0.10 and date>'02sep2010'd and vol>0; absRet = abs(ret); run; proc sort data=temp.r; by permno descending absret; run; data temp.r2; set temp.r; by permno descending absret; if first.permno then output; run; *_______________________________________________________________________________________________________________________ Subset to stocks in volume decile 1 (and do not have a change of listing exchange) ________________________________________________________________________________________________________________________; proc sql; create table temp.crsp1 as select c.permno, c.ncusip, c.ticker, c.exchcd, count(*) as n from temp.c as c, temp.mvr as m where c.permno=m.permno and volRank=1 group by c.permno, c.ncusip, c.ticker, c.exchcd; create table temp.crsp2 as select permno, count(*) as npermno from temp.crsp1 group by permno; create table temp.reject as select c.* from temp.crsp1 as c, temp.crsp2 as d where c.permno=d.permno and d.npermno>1 order by c.permno, c.ncusip, c.ticker, c.exchcd; create table this.accept as select c.* from temp.crsp1 as c, temp.crsp2 as d where c.permno=d.permno and d.npermno=1 order by c.permno, c.ncusip, c.ticker, c.exchcd; quit; run;*/ * Subset to stocks acceptable by the return criterion.; /*proc sql; create table this.accept2 as select * from this.accept as a, temp.r2 as r where a.permno=r.permno order by permno; quit; %printds(data=this.accept2); *_______________________________________________________________________________________________________________________ Merge to TAQ master file. ________________________________________________________________________________________________________________________; %let begDate='01Sep2010'd; %let endDate='30Dec2010'd; %let file=mast_; data dsMastNames; length dsnames $ 32000; date=&begdate; dsnames=''; do until (date>&enddate); dsnames=catx(" ",dsnames,cats("taq.","&file",put(date,yymmn6.))); date=intnx('month',date,1,'begin'); end; call symput ('dsnames',dsnames); run; data mast / view=mast; set &dsnames; run; proc sql; create table tmatch as select m.*, permno, ncusip, ticker from mast as m, this.accept2 as c where m.symbol like c.ticker and c.ncusip=substr(m.cusip,1,8); quit; * Eliminate duplicates; proc sort data=tmatch out=temp.tmatch2 nodupkey; by symbol permno ncusip ticker name uot shrout denom; run; * limit to the first twenty; data this.get; set temp.tmatch2; if _n_<=20 then output; run;*/ *_______________________________________________________________________________________________________________________ MACRO oneSymbolDay pulls off the taq data for one symbol, one day. ________________________________________________________________________________________________________________________; %macro oneSymbolDay(symbol, taqDate, dsOut=ctqOut / view=ctqOut, permno=0); proc sql; drop view cq1, cq2, ct1; quit; %let ndate= %sysfunc(putn(&taqDate,yymmddn8)); %let dct = taq.ct_&ndate. ; %let dcq = taq.cq_&ndate. ; %if %sysfunc(exist(&dct)) and %sysfunc(exist(&dcq)) %then %do; %cqFilter(&dcq (where=(symbol="&symbol")), cq1 / view=cq1); %*printds(data=cq1,obs=200); %nbboFast(cq1, cq2 / view=cq2); %*printds(data=cq2, obs=100); %ctFilter(&dct (where=(symbol="&symbol")), ct1 / view=ct1); *%printds(data=ct1,obs=100); data &dsOut; retain symbol permno date seqno time BBid BOfr price size ex cond corr condFlag g127; merge ct1 cq2 (rename=(BBid=BBid0 BOfr=BOfr0) in=cq); by symbol time; retain seqNo BBid BOfr; permno = &permno; if first.symbol then seqNo=0; seqNo = seqNo+1; if cq then do; BBid=BBid0; BOfr=BOfr0; end; drop BBid0 BOfr0; run; %end; %mend; /*%oneSymbolDay(SPY, "02sep2010"d); %oneSymbolDay(SPY, "02sep2010"d, dsOut=temp.spyDay); %printds(data=temp.spyDay,obs=100); endsas;*/ *_______________________________________________________________________________________________________________________ MACRO doAll loops over all symbols and days ________________________________________________________________________________________________________________________; %macro doAll(symbolFile, fdate, ldate); proc datasets nolist lib=temp; delete ctqAll; run; %getnobs(&symbolFile, nSymbols); %do iSymbol=1 %to &nSymbols; data _null_; i = &iSymbol; set &symbolFile point=i; call symput('symbol',symbol); call symput('permno',permno); stop; run; %let fdated = %sysfunc(InputN(&fdate,yymmdd8.)); %let ldated = %sysfunc(InputN(&ldate,yymmdd8.)); %do date = &fdated %to &ldated; %put Starting analysis for &symbol &fdate &fdated &ldate &ldated &date; %oneSymbolDay(&symbol, &date, dsOut=ctqsd, permno=&permno); proc append base=temp.ctqAll data=ctqsd; run; %end; %end; %mend; options nonotes nosource nomprint; %*doAll(this.get, 20100901,20101230); options notes source mprint; proc freq data=temp.ctqAll; tables symbol date; run; proc sort data=temp.ctqAll; by permno date; run; *_______________________________________________________________________________________________________________________ Export data for transfer: MUST RUN WITH -NOTERMINAL OPTION ________________________________________________________________________________________________________________________; proc export data=temp.ctqAll outfile="/sastemp2/agora/ctqAll.csv" replace; run;