1 The SAS System 14:40 Friday, September 2, 2011 NOTE: Copyright (c) 2002-2008 by SAS Institute Inc., Cary, NC, USA. NOTE: SAS (r) Proprietary Software 9.2 (TS2M3) Licensed to UNIVERSITY OF PENNSYLVANIA - T&R, Site 70001358. NOTE: This session is executing on the SunOS 5.10 (SUN 64) platform. ***************************************************************** * The WRDS Unix server will be undergoing maintenance from * * 6:00am - 9:00am EDT on Tuesday, August 23. During this time, * * we will be patching our server and upgrading our network * * infrastructure to better serve your research needs. The WRDS * * website will not be affected by the maintenance. * * * * Users will not be able to access WRDS through UNIX or PC SAS * * Connect while the maintenance is in progress. All jobs and * * user sessions that are active when it begins will be * * interrupted and will have to be restarted and run again * * when the system is back online. * * * * We understand how important access to data through the WRDS * * system is to your research and we appreciate your patience * * during this time. Our system administrators will be on-site * * until the maintenance is complete. In the meantime, if you * * have any questions or concerns, please contact us at WRDS * * support * ***************************************************************** * On Monday, August 22, the Thomson Reuters data on WRDS will * * be reorganized. Mutual Fund Holdings (s12), 13f Holdings * * (s34) and Insider Filings will each be moved into a separate * * directory. All three currently reside in /wrds/tfn/sasdata. * * The new directories will be * * * * /wrds/tfn/sasdata/s12 * * /wrds/tfn/sasdata/s34 * * /wrds/tfn/sasdata/insiders * * * * All three will continue to be included in the SAS libname * * "tfn". Web users will not be affected. * * * ***************************************************************** NOTE: SAS initialization used: real time 0.11 seconds cpu time 0.10 seconds NOTE: AUTOEXEC processing beginning; file is /home/nyu/jhasbrou/autoexec.sas. NOTE: Libref CRLCMACR was successfully assigned as follows: Engine: V9 Physical Name: /wrds/eventus/pgm/crlcmacr NOTE: Libref VOLINDEX was successfully assigned as follows: Engine: V9 Physical Name: /wrds/eventus/sasdata NOTE: Libref AUDIT was successfully assigned as follows: Levels: 2 Engine(1): V9 Physical Name(1): /wrds/audit/sasdata/audit_comp Engine(2): V9 Physical Name(2): /wrds/audit/sasdata/corp_legal NOTE: Libref BANK was successfully assigned as follows: Levels: 3 Engine(1): V9 Physical Name(1): /wrds/bank/sasdata Engine(2): V9 Physical Name(2): /wrds/bank/sasdata/commercial Engine(3): V9 Physical Name(3): /wrds/bank/sasdata/holding NOTE: Libref BLOCK was successfully assigned as follows: Engine: V9 Physical Name: /wrds/blockholders/sasdata NOTE: Libref BVD was successfully assigned as follows: Levels: 4 Engine(1): V9 Physical Name(1): /wrds/bvd/sasdata/ama Engine(2): V9 Physical Name(2): /wrds/bvd/sasdata/bs Engine(3): V9 Physical Name(3): /wrds/bvd/sasdata/is Engine(4): V9 Physical Name(4): /wrds/bvd/sasdata/os NOTE: Libref CBOE was successfully assigned as follows: Engine: V9 Physical Name: /wrds/cboe/sasdata NOTE: Libref CENTRIS was successfully assigned as follows: Engine: V9 Physical Name: /wrds/centris/sasdata NOTE: Libref CIQ was successfully assigned as follows: Levels: 4 Engine(1): V9 Physical Name(1): /wrds/capitaliq/sasdata/capstructure Engine(2): V9 Physical Name(2): /wrds/capitaliq/sasdata/helper Engine(3): V9 Physical Name(3): /wrds/capitaliq/sasdata/keydev Engine(4): V9 Physical Name(4): /wrds/capitaliq/sasdata/pplintel NOTE: Libref CIQSAMP was successfully assigned as follows: Levels: 4 Engine(1): V9 Physical Name(1): /wrds/ciqsamp/sasdata/keydev Engine(2): V9 Physical Name(2): /wrds/ciqsamp/sasdata/capstructure Engine(3): V9 Physical Name(3): /wrds/ciqsamp/sasdata/helper Engine(4): V9 Physical Name(4): /wrds/ciqsamp/sasdata/pplintel NOTE: Libref CISDM was successfully assigned as follows: Engine: V9 Physical Name: /wrds/cisdm/sasdata NOTE: Libref COMPB was successfully assigned as follows: Levels: 2 Engine(1): V9 Physical Name(1): /wrds/comp/sasdata/bank Engine(2): V9 Physical Name(2): /wrds/comp/sasdata/bank/company NOTE: Libref COMPG was successfully assigned as follows: Levels: 7 Engine(1): V9 Physical Name(1): /wrds/comp/sasdata/global Engine(2): V9 Physical Name(2): /wrds/comp/sasdata/global/company Engine(3): V9 Physical Name(3): /wrds/comp/sasdata/global/currency Engine(4): V9 Physical Name(4): /wrds/comp/sasdata/global/dictionary Engine(5): V9 Physical Name(5): /wrds/comp/sasdata/global/economy Engine(6): V9 Physical Name(6): /wrds/comp/sasdata/global/index Engine(7): V9 Physical Name(7): /wrds/comp/sasdata/global/security NOTE: Libref COMPA was successfully assigned as follows: Levels: 14 Engine(1): V9 Physical Name(1): /wrds/comp/sasdata/naa Engine(2): V9 Physical Name(2): /wrds/comp/sasdata/naa/company Engine(3): V9 Physical Name(3): /wrds/comp/sasdata/naa/currency Engine(4): V9 Physical Name(4): /wrds/comp/sasdata/naa/dictionary Engine(5): V9 Physical Name(5): /wrds/comp/sasdata/naa/economy Engine(6): V9 Physical Name(6): /wrds/comp/sasdata/naa/index Engine(7): V9 Physical Name(7): /wrds/comp/sasdata/naa/industry Engine(8): V9 Physical Name(8): /wrds/comp/sasdata/naa/insider Engine(9): V9 Physical Name(9): /wrds/comp/sasdata/naa/institution Engine(10): V9 Physical Name(10): /wrds/comp/sasdata/naa/pension Engine(11): V9 Physical Name(11): /wrds/comp/sasdata/naa/rating Engine(12): V9 Physical Name(12): /wrds/comp/sasdata/naa/security Engine(13): V9 Physical Name(13): /wrds/comp/sasdata/naa/segments_current Engine(14): V9 Physical Name(14): /wrds/comp/sasdata/naa/transparency NOTE: Libref COMPM was successfully assigned as follows: Levels: 14 Engine(1): V9 Physical Name(1): /wrds/comp/sasdata/nam Engine(2): V9 Physical Name(2): /wrds/comp/sasdata/nam/company Engine(3): V9 Physical Name(3): /wrds/comp/sasdata/nam/currency Engine(4): V9 Physical Name(4): /wrds/comp/sasdata/nam/dictionary Engine(5): V9 Physical Name(5): /wrds/comp/sasdata/nam/economy Engine(6): V9 Physical Name(6): /wrds/comp/sasdata/nam/index Engine(7): V9 Physical Name(7): /wrds/comp/sasdata/nam/industry Engine(8): V9 Physical Name(8): /wrds/comp/sasdata/nam/insider Engine(9): V9 Physical Name(9): /wrds/comp/sasdata/nam/institution Engine(10): V9 Physical Name(10): /wrds/comp/sasdata/nam/pension Engine(11): V9 Physical Name(11): /wrds/comp/sasdata/nam/rating Engine(12): V9 Physical Name(12): /wrds/comp/sasdata/nam/security Engine(13): V9 Physical Name(13): /wrds/comp/sasdata/nam/segments_current Engine(14): V9 Physical Name(14): /wrds/comp/sasdata/nam/transparency NOTE: Libref EXECCOMP was successfully assigned as follows: Engine: V9 Physical Name: /wrds/comp/sasdata/execcomp NOTE: Libref COMPSEG was successfully assigned as follows: Engine: V9 Physical Name: /wrds/comp/sasdata/seghist NOTE: Libref COMP was successfully assigned as follows: Levels: 27 Engine(1): V9 Physical Name(1): /wrds/comp/sasdata/nam Engine(2): V9 Physical Name(2): /wrds/comp/sasdata/nam/company Engine(3): V9 Physical Name(3): /wrds/comp/sasdata/nam/currency Engine(4): V9 Physical Name(4): /wrds/comp/sasdata/nam/dictionary Engine(5): V9 Physical Name(5): /wrds/comp/sasdata/nam/economy Engine(6): V9 Physical Name(6): /wrds/comp/sasdata/nam/index Engine(7): V9 Physical Name(7): /wrds/comp/sasdata/nam/industry Engine(8): V9 Physical Name(8): /wrds/comp/sasdata/nam/insider Engine(9): V9 Physical Name(9): /wrds/comp/sasdata/nam/institution Engine(10): V9 Physical Name(10): /wrds/comp/sasdata/nam/pension Engine(11): V9 Physical Name(11): /wrds/comp/sasdata/nam/rating Engine(12): V9 Physical Name(12): /wrds/comp/sasdata/nam/security Engine(13): V9 Physical Name(13): /wrds/comp/sasdata/nam/segments_current Engine(14): V9 Physical Name(14): /wrds/comp/sasdata/nam/transparency Engine(15): V9 Physical Name(15): /wrds/comp/sasdata/bank Engine(16): V9 Physical Name(16): /wrds/comp/sasdata/bank/company Engine(17): V9 Physical Name(17): /wrds/comp/sasdata/global Engine(18): V9 Physical Name(18): /wrds/comp/sasdata/global/company Engine(19): V9 Physical Name(19): /wrds/comp/sasdata/global/currency Engine(20): V9 Physical Name(20): /wrds/comp/sasdata/global/dictionary Engine(21): V9 Physical Name(21): /wrds/comp/sasdata/global/economy Engine(22): V9 Physical Name(22): /wrds/comp/sasdata/global/index Engine(23): V9 Physical Name(23): /wrds/comp/sasdata/global/security Engine(24): V9 Physical Name(24): /wrds/comp/sasdata/execcomp Engine(25): V9 Physical Name(25): /wrds/comp/sasdata/seghist Engine(26): V9 Physical Name(26): /wrds/comp/sasdata/filings Engine(27): V9 Physical Name(27): /wrds/comp/sasdata/marginaltax NOTE: Libref COMPH was successfully assigned as follows: Levels: 4 Engine(1): V9 Physical Name(1): /wrds/comp/sasdata/ph Engine(2): V9 Physical Name(2): /wrds/comp/sasdata/pit Engine(3): V9 Physical Name(3): /wrds/comp/sasdata/urq Engine(4): V9 Physical Name(4): /wrds/comp/sasdata NOTE: Libref COMPSAMP was successfully assigned as follows: Engine: V9 Physical Name: /wrds/compsamp/sasdata NOTE: Libref COMSCORE was successfully assigned as follows: Engine: V9 Physical Name: /wrds/comscore/sasdata NOTE: Libref CORPLIB was successfully assigned as follows: Engine: V9 Physical Name: /wrds/corplib/sasdata NOTE: Libref CRSP was successfully assigned as follows: Levels: 9 Engine(1): V9 Physical Name(1): /wrds/crsp/sasdata/sm Engine(2): V9 Physical Name(2): /wrds/crsp/sasdata/sd Engine(3): V9 Physical Name(3): /wrds/crsp/sasdata/ix Engine(4): V9 Physical Name(4): /wrds/crsp/sasdata/bm Engine(5): V9 Physical Name(5): /wrds/crsp/sasdata/bd Engine(6): V9 Physical Name(6): /wrds/crsp/sasdata/mf Engine(7): V9 Physical Name(7): /wrds/crsp/sasdata/cc Engine(8): V9 Physical Name(8): /wrds/crsp/sasdata/zr Engine(9): V9 Physical Name(9): /wrds/crsp/sasdata/cc/CST_FTP_LEGACY NOTE: Libref CRSPQ was successfully assigned as follows: Levels: 6 Engine(1): V9 Physical Name(1): /wrds/crspq/sasdata/sm Engine(2): V9 Physical Name(2): /wrds/crspq/sasdata/sd Engine(3): V9 Physical Name(3): /wrds/crspq/sasdata/ix Engine(4): V9 Physical Name(4): /wrds/crspq/sasdata/bm Engine(5): V9 Physical Name(5): /wrds/crspq/sasdata/bd Engine(6): V9 Physical Name(6): /wrds/crspq/sasdata/cc NOTE: Libref CRSPSAMP was successfully assigned as follows: Engine: V9 Physical Name: /wrds/crspsamp/sasdata NOTE: Libref CSMAR was successfully assigned as follows: Levels: 4 Engine(1): V9 Physical Name(1): /wrds/csmar/sasdata/trading Engine(2): V9 Physical Name(2): /wrds/csmar/sasdata/financial Engine(3): V9 Physical Name(3): /wrds/csmar/sasdata/openfund Engine(4): V9 Physical Name(4): /wrds/csmar/sasdata/closedfund NOTE: Libref CUSIPM was successfully assigned as follows: Engine: V9 Physical Name: /wrds/cusip/sasdata NOTE: Libref DEALSCAN was successfully assigned as follows: Engine: V9 Physical Name: /wrds/tfn/sasdata/dealscan NOTE: Libref DJONES was successfully assigned as follows: Engine: V9 Physical Name: /wrds/djones/sasdata NOTE: Libref DMEF was successfully assigned as follows: Engine: V9 Physical Name: /wrds/dmef/sasdata NOTE: Libref DOE was successfully assigned as follows: Engine: V9 Physical Name: /wrds/doe/sasdata NOTE: Libref EMDB was successfully assigned as follows: Levels: 2 Engine(1): V9 Physical Name(1): /wrds/comp/sasdata/emdbd Engine(2): V9 Physical Name(2): /wrds/comp/sasdata/emdbm NOTE: Libref FDIC was successfully assigned as follows: Engine: V9 Physical Name: /wrds/fdic/sasdata NOTE: Libref FF was successfully assigned as follows: Engine: V9 Physical Name: /wrds/ff/sasdata NOTE: Libref FC was successfully assigned as follows: Engine: V9 Physical Name: /wrds/firstcall/sasdata NOTE: Libref FISD was successfully assigned as follows: Levels: 2 Engine(1): V9 Physical Name(1): /wrds/mergent/sasdata/fisd Engine(2): V9 Physical Name(2): /wrds/mergent/sasdata/naic NOTE: Libref FRB was successfully assigned as follows: Engine: V9 Physical Name: /wrds/frb/sasdata NOTE: Libref GINSIGHT was successfully assigned as follows: Levels: 3 Engine(1): V9 Physical Name(1): /wrds/ginsight/sasdata/all Engine(2): V9 Physical Name(2): /wrds/ginsight/sasdata/basic Engine(3): V9 Physical Name(3): /wrds/ginsight/sasdata/common NOTE: Libref GSI was successfully assigned as follows: Engine: V9 Physical Name: /wrds/gsi/sasdata NOTE: Libref GOVPX was successfully assigned as follows: Engine: V9 Physical Name: /wrds/govpx/sasdata NOTE: Libref IBES was successfully assigned as follows: Engine: V9 Physical Name: /wrds/ibes/sasdata NOTE: Libref IRI was successfully assigned as follows: Engine: V9 Physical Name: /wrds/iri/sasdata NOTE: Libref ISSM was successfully assigned as follows: Engine: V9 Physical Name: /wrds/issm/sasdata NOTE: Libref KLD was successfully assigned as follows: Engine: V9 Physical Name: /wrds/kld/sasdata NOTE: Libref MFL was successfully assigned as follows: Engine: V9 Physical Name: /wrds/mfl/sasdata NOTE: Library NASTRAQ does not exist. NOTE: Libref OPTIONM was successfully assigned as follows: Engine: V9 Physical Name: /wrds/optionm/sasdata NOTE: Libref PACAP was successfully assigned as follows: Levels: 8 Engine(1): V9 Physical Name(1): /wrds/pacap/sasdata/hk Engine(2): V9 Physical Name(2): /wrds/pacap/sasdata/id Engine(3): V9 Physical Name(3): /wrds/pacap/sasdata/ja Engine(4): V9 Physical Name(4): /wrds/pacap/sasdata/ks Engine(5): V9 Physical Name(5): /wrds/pacap/sasdata/my Engine(6): V9 Physical Name(6): /wrds/pacap/sasdata/sn Engine(7): V9 Physical Name(7): /wrds/pacap/sasdata/tw Engine(8): V9 Physical Name(8): /wrds/pacap/sasdata/th NOTE: Libref PHLX was successfully assigned as follows: Engine: V9 Physical Name: /wrds/phlx/sasdata NOTE: Libref PWT was successfully assigned as follows: Engine: V9 Physical Name: /wrds/pwt/sasdata NOTE: Libref RENT was successfully assigned as follows: Engine: V9 Physical Name: /wrds/rent/sasdata NOTE: Libref RISK was successfully assigned as follows: Engine: V9 Physical Name: /wrds/riskmetrics/sasdata NOTE: Libref SPRAT was successfully assigned as follows: Engine: V9 Physical Name: /wrds/comp/sasdata/sprat NOTE: Libref TAQ was successfully assigned as follows: Engine: V9 Physical Name: /wrds/taq/sasdata NOTE: Libref TAQSAMP was successfully assigned as follows: Engine: V9 Physical Name: /wrds/taqsamp/sasdata NOTE: Libref TFN was successfully assigned as follows: Levels: 3 Engine(1): V9 Physical Name(1): /wrds/tfn/sasdata/s12 Engine(2): V9 Physical Name(2): /wrds/tfn/sasdata/s34 Engine(3): V9 Physical Name(3): /wrds/tfn/sasdata/insiders NOTE: Libref TRACE was successfully assigned as follows: Engine: V9 Physical Name: /wrds/trace/sasdata NOTE: AUTOEXEC processing completed. 1 * 2 _____________________________________________________________________ 2 ! ___________________________________________________ 3 4 crspSelect 5 Joel Hasbrouck 6 September 2011 7 8 This program runs on wrds, and builds a small trade and quote dataset 8 ! ('ctqAll') for student analysis. 9 It uses macros contained in the directory 'sasmacros'. 10 11 The principal steps are: 12 13 1. From the crsp daily stock file, compute average daily volumes for 13 ! all stocks in the last quarter of 2010. 14 2. Compute volume deciles. 15 3. Determine which stocks had (on at least one day in the quarter) 15 ! an absolute return of at least ten percent 16 on positive volume with a price of at least $5. 17 4. Take stocks in the volume decile 1 (next to lowest) that also 17 ! satisfy the return criterion. 18 5. Match these to the TAQ master file on cusip and ticker symbol. 19 6. Take the first ten stocks that match. 20 21 For each stock and date in the quarter: 22 1. Construct an nbbo series from the cq file. 23 2. Merge with the trades. 24 25 Notes: the NBBO is computed using the nbbofast macro. This is a 25 ! minimal computation. It filters the quotes 26 (see the cqfilter macro) but does not keep a record of which 26 ! exchanges are at the nbbo, nor does it compute 27 the sizes at the bid and offer. The nbbo is computed using all cq 27 ! records in a given second under the assumption 28 that they are correctly sequenced. An nbbo record is generated only 28 ! when the nbbo changes. 29 30 _____________________________________________________________________ 30 ! ___________________________________________________ 31 ; 32 options source nodate nocenter nonumber ps=max ls=72; 33 options nocenter ps=70 ls=110 sasautos=('$HOME/sasmacros','.') mprint; The SAS System 34 libname this '.'; NOTE: Libref THIS was successfully assigned as follows: Engine: V9 Physical Name: /home/nyu/jhasbrou/phd2011Fall 35 * Make and set a library reference to a temporary directory. 36 *x 'mkdir /sastemp2/agora'; 37 libname temp '/sastemp2/agora'; NOTE: Libref TEMP was successfully assigned as follows: Engine: V9 Physical Name: /sastemp2/agora 37 ! * Set this to some temporary directory; 38 39 /*_________________________________________________________________________________________________ 39 ! ______________________ 40 41 Use the wrds crspmerge macro to retrieve data from the crsp dse and dsf files. 42 ___________________________________________________________________________________________________ 42 ! _____________________; 43 44 %crspmerge(s=d,start=01Sep2010,end=30Dec2010,sfvars=ret prc vol,final_ds=temp.c, 45 filters=shrcd in(10,11,12) and exchcd in(1,2,3)); 46 *__________________________________________________________________________________________________ 46 ! _____________________ 47 48 Construct average volume deciles 49 ___________________________________________________________________________________________________ 49 ! _____________________; 50 proc means data=temp.c noprint; 51 var vol; 52 class permno; 53 types permno; 54 id ncusip; 55 output mean= out=temp.mv (drop=_type_); 56 run; 57 proc rank data=temp.mv out=temp.mvr groups=10; 58 var vol; 59 ranks volRank; 60 run; 61 proc tabulate data=temp.mvr noseps format=comma12.0; 62 class volRank; 63 var vol; 64 table (all volRank), (n mean median min max)*vol; 65 run; 66 67 *__________________________________________________________________________________________________ 67 ! _____________________ 68 69 Make list of stocks with large returns on at least one day. 70 ___________________________________________________________________________________________________ 70 ! _____________________; 71 data temp.r; 72 set temp.c; 73 where abs(prc)>5 and abs(ret)>0.10 and date>'02sep2010'd and vol>0; 74 absRet = abs(ret); 75 run; 76 proc sort data=temp.r; 77 by permno descending absret; 78 run; 79 data temp.r2; 80 set temp.r; 81 by permno descending absret; 82 if first.permno then output; 83 run; 84 85 *__________________________________________________________________________________________________ 85 ! _____________________ 86 87 Subset to stocks in volume decile 1 (and do not have a change of listing exchange) The SAS System 88 ___________________________________________________________________________________________________ 88 ! _____________________; 89 proc sql; 90 create table temp.crsp1 as select c.permno, c.ncusip, c.ticker, c.exchcd, count(*) as n 91 from temp.c as c, temp.mvr as m where c.permno=m.permno and volRank=1 92 group by c.permno, c.ncusip, c.ticker, c.exchcd; 93 create table temp.crsp2 as select permno, count(*) as npermno from temp.crsp1 group by permno; 94 create table temp.reject as select c.* from temp.crsp1 as c, temp.crsp2 as d where 94 ! c.permno=d.permno and d.npermno>1 95 order by c.permno, c.ncusip, c.ticker, c.exchcd; 96 create table this.accept as select c.* from temp.crsp1 as c, temp.crsp2 as d where 96 ! c.permno=d.permno and d.npermno=1 97 order by c.permno, c.ncusip, c.ticker, c.exchcd; 98 quit; 99 run;*/ 100 * Subset to stocks acceptable by the return criterion.; 101 /*proc sql; 102 create table this.accept2 as select * from this.accept as a, temp.r2 as r where a.permno=r.permno 103 order by permno; 104 quit; 105 %printds(data=this.accept2); 106 107 *__________________________________________________________________________________________________ 107 ! _____________________ 108 109 Merge to TAQ master file. 110 ___________________________________________________________________________________________________ 110 ! _____________________; 111 %let begDate='01Sep2010'd; 112 %let endDate='30Dec2010'd; 113 %let file=mast_; 114 data dsMastNames; 115 length dsnames $ 32000; 116 date=&begdate; 117 dsnames=''; 118 do until (date>&enddate); 119 dsnames=catx(" ",dsnames,cats("taq.","&file",put(date,yymmn6.))); 120 date=intnx('month',date,1,'begin'); 121 end; 122 call symput ('dsnames',dsnames); 123 run; 124 data mast / view=mast; 125 set &dsnames; 126 run; 127 proc sql; 128 create table tmatch as select m.*, permno, ncusip, ticker from mast as m, this.accept2 as c where 128 ! m.symbol like c.ticker 129 and c.ncusip=substr(m.cusip,1,8); 130 quit; 131 * Eliminate duplicates; 132 proc sort data=tmatch out=temp.tmatch2 nodupkey; 133 by symbol permno ncusip ticker name uot shrout denom; 134 run; 135 * limit to the first twenty; 136 data this.get; 137 set temp.tmatch2; 138 if _n_<=20 then output; 139 run;*/ 140 141 *__________________________________________________________________________________________________ 141 ! _____________________ 142 143 MACRO oneSymbolDay pulls off the taq data for one symbol, one day. 144 ___________________________________________________________________________________________________ 144 ! _____________________; 145 146 %macro oneSymbolDay(symbol, taqDate, dsOut=ctqOut / view=ctqOut, permno=0); 147 proc sql; The SAS System 148 drop view cq1, cq2, ct1; 149 quit; 150 %let ndate= %sysfunc(putn(&taqDate,yymmddn8)); 151 %let dct = taq.ct_&ndate. ; 152 %let dcq = taq.cq_&ndate. ; 153 %if %sysfunc(exist(&dct)) and %sysfunc(exist(&dcq)) %then %do; 154 %cqFilter(&dcq (where=(symbol="&symbol")), cq1 / view=cq1); 155 %*printds(data=cq1,obs=200); 156 %nbboFast(cq1, cq2 / view=cq2); 157 %*printds(data=cq2, obs=100); 158 %ctFilter(&dct (where=(symbol="&symbol")), ct1 / view=ct1); 159 *%printds(data=ct1,obs=100); 160 data &dsOut; 161 retain symbol permno date seqno time BBid BOfr price size ex cond corr condFlag g127; 162 merge ct1 cq2 (rename=(BBid=BBid0 BOfr=BOfr0) in=cq); 163 by symbol time; 164 retain seqNo BBid BOfr; 165 permno = &permno; 166 if first.symbol then seqNo=0; 167 seqNo = seqNo+1; 168 if cq then do; 169 BBid=BBid0; 170 BOfr=BOfr0; 171 end; 172 drop BBid0 BOfr0; 173 run; 174 %end; 175 %mend; 176 177 178 /*%oneSymbolDay(SPY, "02sep2010"d); 179 %oneSymbolDay(SPY, "02sep2010"d, dsOut=temp.spyDay); 180 %printds(data=temp.spyDay,obs=100); 181 endsas;*/ 182 183 184 185 *__________________________________________________________________________________________________ 185 ! _____________________ 186 187 MACRO doAll loops over all symbols and days 188 ___________________________________________________________________________________________________ 188 ! _____________________; 189 %macro doAll(symbolFile, fdate, ldate); 190 proc datasets nolist lib=temp; 191 delete ctqAll; 192 run; 193 %getnobs(&symbolFile, nSymbols); 194 %do iSymbol=1 %to &nSymbols; 195 data _null_; 196 i = &iSymbol; 197 set &symbolFile point=i; 198 call symput('symbol',symbol); 199 call symput('permno',permno); 200 stop; 201 run; 202 %let fdated = %sysfunc(InputN(&fdate,yymmdd8.)); 203 %let ldated = %sysfunc(InputN(&ldate,yymmdd8.)); 204 %do date = &fdated %to &ldated; 205 %put Starting analysis for &symbol &fdate &fdated &ldate &ldated &date; 206 %oneSymbolDay(&symbol, &date, dsOut=ctqsd, permno=&permno); 207 proc append base=temp.ctqAll data=ctqsd; 208 run; 209 %end; 210 %end; 211 %mend; 212 213 options nonotes nosource nomprint; The SAS System 216 217 proc freq data=temp.ctqAll; 218 tables symbol date; 219 run; NOTE: There were 1399404 observations read from the data set TEMP.CTQALL. NOTE: The PROCEDURE FREQ printed pages 1-3. NOTE: PROCEDURE FREQ used (Total process time): real time 1.00 seconds cpu time 0.80 seconds 220 proc sort data=temp.ctqAll; 221 by permno date; 222 run; NOTE: Input data set is already sorted, no sorting done. NOTE: PROCEDURE SORT used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 223 *__________________________________________________________________________________________________ 223 ! _____________________ 224 225 Export data for transfer: MUST RUN WITH -NOTERMINAL OPTION 226 ___________________________________________________________________________________________________ 226 ! _____________________; 227 228 proc export data=temp.ctqAll outfile="/sastemp2/agora/ctqAll.csv" replace; 229 run; 230 /********************************************************************** 231 * PRODUCT: SAS 232 * VERSION: 9.2 233 * CREATOR: External File Interface 234 * DATE: 02SEP11 235 * DESC: Generated SAS Datastep Code 236 * TEMPLATE SOURCE: (None Specified.) 237 ***********************************************************************/ 238 data _null_; 239 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */ 240 %let _EFIREC_ = 0; /* clear export record count macro variable */ 241 file '/sastemp2/agora/ctqAll.csv' delimiter=',' DSD DROPOVER lrecl=32767; 242 if _n_ = 1 then /* write column names or labels */ 243 do; 244 put 245 "symbol" 246 ',' 247 "permno" 248 ',' 249 "date" 250 ',' 251 "seqno" 252 ',' 253 "time" 254 ',' 255 "BBid" 256 ',' 257 "BOfr" 258 ',' 259 "price" 260 ',' 261 "size" 262 ',' 263 "ex" 264 ',' 265 "cond" The SAS System 266 ',' 267 "corr" 268 ',' 269 "condFlag" 270 ',' 271 "g127" 272 ; 273 end; 274 set TEMP.CTQALL end=EFIEOD; 275 format symbol $10. ; 276 format permno best12. ; 277 format date yymmddn8. ; 278 format seqno best12. ; 279 format time time8. ; 280 format BBid best12. ; 281 format BOfr best12. ; 282 format price best12. ; 283 format size best12. ; 284 format ex $1. ; 285 format cond $2. ; 286 format corr best12. ; 287 format condFlag best12. ; 288 format g127 best12. ; 289 do; 290 EFIOUT + 1; 291 put symbol $ @; 292 put permno @; 293 put date @; 294 put seqno @; 295 put time @; 296 put BBid @; 297 put BOfr @; 298 put price @; 299 put size @; 300 put ex $ @; 301 put cond $ @; 302 put corr @; 303 put condFlag @; 304 put g127 ; 305 ; 306 end; 307 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */ 308 if EFIEOD then call symputx('_EFIREC_',EFIOUT); 309 run; NOTE: The file '/sastemp2/agora/ctqAll.csv' is: Filename=/sastemp2/agora/ctqAll.csv, Owner Name=jhasbrou,Group Name=nyu, Access Permission=rw-------, Last Modified=Fri Sep 2 14:40:07 2011 NOTE: 1399405 records were written to the file '/sastemp2/agora/ctqAll.csv'. The minimum record length was 37. The maximum record length was 77. NOTE: There were 1399404 observations read from the data set TEMP.CTQALL. NOTE: DATA statement used (Total process time): real time 10.62 seconds cpu time 10.62 seconds 1399404 records created in /sastemp2/agora/ctqAll.csv from TEMP.CTQALL. NOTE: "/sastemp2/agora/ctqAll.csv" file was successfully created. NOTE: PROCEDURE EXPORT used (Total process time): real time 12.60 seconds cpu time 11.01 seconds The SAS System 310 NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414 NOTE: The SAS System used: real time 14.88 seconds cpu time 12.17 seconds