TAQ_EVENT_WINDOWS

Retrieves trade or quote data from TAQ for user-specified SYMBOLS, each with a unique date and time range.

Example

/**  **************************************************************************/
/**  Program        : TAQ_EVENT_WINDOWS                                      **/
/**  Author         : Mark Keintz                                            **/
/**  Version        : 1.0                                                    **/
/**  Date Created   : 10/26/2008                                             **/
/**  Last Modified  : 10/26/2008                                             **/
/**                                                                          **/
/**  Description    : Retrieves trade or quote data from TAQ for             **/
/**                   user-specified SYMBOLS, each with a unique             **/
/**                   date and time range.                                   **/
/**                                                                          **/
/**  Usage: Two stages:                                                      **/
/**    Stage 1 :  Prepare a dataset with the following variables:            **/
/**                   SYMBOL BEGDATE BEGTIME ENDDATE ENDTIME                 **/
/**               For example:                                               **/
/**                                                                          **/
/**               data mysample;                                             **/
/**                 length symbol $10. ;                                     **/
/**                 informat begdate enddate yymmdd8. ;                      **/
/**                 informat begtime endtime time8.0 ;                       **/
/**                 input symbol begdate begtime enddate endtime;            **/
/**               datalines;                                                 **/
/**               AZTC 19990301 10:00:00 19990307 15:00:00                   **/
/**               UBID 19990505 10:30:00 19990505 14:30:00                   **/
/**               run;                                                       **/
/**                                                                          **/
/**    Stage 2 : Call the TAQ_EVENT_WINDOWS1 macro as here:                  **/
/**                                                                          **/
/**              %taq_event_windows1(type=CQ,keydataset=mysample             **/
/**                   ,keepvars=,sortord=SDT                                 **/
/**                   ,outdata=want,outtype=dataset,verbosity=some);         **/
/**                                                                          **/
/**              (See the beginning of the TAQ_EVENT_windows1 macro for an   **/
/**               explanation of the parameters).                            **/
/**                                                                          **/
/**                                                                          **/
/******************************************************************************/
 
 
%macro taq_event_windows1(type=CQ,keydataset=mysample,keepvars=,sortord=SDT
                   ,outdata=want,outtype=dataset,verbosity=some)
  /DES="Trades or Quotes for selected symbols, with distinct DATE/TIME ranges";
 
  %local       /* These macrovars have local scope only:                      */
    TYPE       /* Either CQ (quotes) or CT (trades).  CQ is default.          */
    KEYDATASET /* Dataset with SYMBOLS + DATE/TIME ranges (default MYSAMPLE)  */
    KEEPVARS   /* List of user-requested vars (if blank, keep all vars)       */
    SORTORD    /* Output order, either SDT (SYMBOL/DATE/TIME) or DST          */
    OUTDATA    /* Name of dataset or dataview to output                       */
    OUTTYPE    /* Type of output, either DATASET or DATAVIEW                  */
    VERBOSITY  /* ALL (All sas notes/mprint/msglevel=I), or SOME or NONE      */
    ;
 
 
  /*** STEP 1: Set up parameters and options ***/
 
  %let type=%upcase(&type);
  %let sortord=%upcase(&sortord);
  %let outtype=%upcase(&outtype);
  %let verbosity=%upcase(&verbosity);
 
  %let keeplist1 = &keepvars; 
  %* ** If KEEPLIST1 is not blank or null, prepend a "keep="  **;
  %* ** Also append SYMBOL DATE TIME, needed for prelim steps **;
  %if %length(&keeplist1) ^=0 %then
    %let keeplist1=KEEP%str(=)&keeplist1 SYMBOL DATE TIME;
 
  %* ** Save prior NOTES, MPRINT, and MSGLEVEL options, for later resetting **;
  %local prior_options;
  %let prior_options=%sysfunc(getoption(NOTES)) %sysfunc(getoption(MPRINT))
      MSGLEVEL=%sysfunc(getoption(MSGLEVEL));
 
  %* ** if VERBOSITY is SOME or NONE, turn off notes, etc. for steps 2 & 3 **;
  %if %index(SOME NONE,&verbosity) ^= 0 %then
     %str(options nonotes nomprint msglevel=N;);
  %else
     %str(options notes mprint msglevel=I;);
  ;
 
 
  /**** STEP 2: Determine list of TAQ datasets needed ****/
 
  %* ** 2a: Get minimum and maximum YYYYMMDD in KEYDATASET ;
  %local MIN_YYYYMMDD MAX_YYYYMMDD ;
  proc sql noprint;
    select min(begdate) format=yymmddn8. ,max(enddate) format=yymmddn8.
    into   : min_yyyymmdd                ,: max_yyyymmdd
    from &keydataset;
  quit;
 
  %* ** 2b: Convert min/max YYYYMMDD into begin/end taq dataset names **;
  %local BEG_DSN END_DSN ;
  %let beg_dsn=&type._&min_yyyymmdd;   %* e.g. 20081003 ==> CQ_20081003 **;
  %let end_dsn=&type._&max_yyyymmdd;
 
  %* ** 2c: Build list of all datasets from BEG_DSN through END_DSN **;
  %local DSLIST ;
  proc sql noprint;
    select distinct cats('TAQ.',trim(memname)) into :dslist
      separated by ' '
      from sashelp.vtable where
        libname="TAQ" and
        memname between "&beg_dsn" and "&end_dsn"
        ;
  quit;
 
  %* ** 2d: Get count of candidate dataset names **;
  %local N_DS ;
  %let n_ds=&sqlobs;
  %if &n_ds=0 %then %do;
    %put No &type TAQ datasets available for &min_yyyymmdd to &max_yyyymmdd ;
    %goto MACRODONE ;
  %end;
 
 
  /*** STEP 3: For each TAQ dataset (I=1 to N_DS), define a view  ***/
 
  %local I  YYYYMMDD  DATE;
  proc sql noprint;
  %do I=1 %to &n_ds;
    %local QSYMLIST&I /* List of quoted symbols for the Ith dataset   */
           NSYM&I     /* N SYMBOLS from Ith TAQ dataset               */
           WHENLIST&I /* "when" list (for SELECT statement) for Ith   */ ;
 
    %* ** Get the Ith date **;
    %let YYYYMMDD = %scan(&dslist,&i,%str( ));  %* Get Ith dsn **;
    %let YYYYMMDD = %scan(&yyyymmdd,2,_);     %* Part after the underscore **;
    %let DATE     = %sysfunc(inputn(&YYYYMMDD,yymmdd8.),date9.);
 
    select distinct quote(trim(symbol)) into : qsymlist&i
      separated by ' '
      from &keydataset
      where "&date"d between begdate and enddate;
 
    %let NSYM&I = &sqlobs;
 
    %* ** Make list of "when" lines like
        "when ('IBM') do; if '09:30:01't<=time<='13:00:00't then output; end;" ;
 
    select distinct cats("when (",quote(trim(symbol)),") do; if (","'"
        ,put(begtime,tod8.0),"'t","<=time<=","'",put(endtime,tod8.0)
        ,"'t) then output; end;")
      into :whenlist&i
      separated by ' '
      from &keydataset
      where "&date"d between begdate and enddate;
  %end;
  quit;
 
  %local VLIST   /* List of preliminary daily data views to use */ ;
  %let vlist=;
  %do I=1 %to &n_ds;
    %if &&nsym&i ^= 0 %then %do;
      data vtemp&i / view=vtemp&i;
        set %scan(&dslist,&I,%str( )) (&keeplist1) ;
        where symbol in (&&qsymlist&i);
        select (symbol);
          &&whenlist&i
        end;
 
      run;
      %let vlist=&vlist vtemp&i ;
    %end;
  %end;
 
  /** if VERBOSITY is SOME or ALL, turn on notes, etc. for the final step **/
  %if %index(SOME ALL,&verbosity) ^= 0 %then
    %str(options notes mprint msglevel=I;);
  %else
    %str(options nonotes nomprint msglevel=N;);
 
  /** STEP 4: Read all the data views into final data output **/
 
  data &outdata  %if &outtype=DATAVIEW %then %str(/ view=&outdata); ;
    set &vlist   %if &sortord=DST %then %str(open=defer)  ; ;
    /** See TAQ_BASIC for rationale on OPEN=DEFER **/
    %if &sortord=SDT %then
      %str(BY SYMBOL DATE TIME;);
    %if %length(&keepvars) ^= 0 %then
      %str(keep &keepvars;) ;
  run;
 
  %macrodone: options &prior_options ;
%mend taq_event_windows1 ;
 
 
/** STAGE 1: Make a "driver" file for the TAQ_EVENT_WINDOW macro **/
 
data mysample;
  length symbol $10.;
  informat begdate enddate date9.  begtime endtime time8.0;
  format   begdate enddate date9.  begtime endtime tod8.0 ;
  input symbol   begdate enddate   begtime endtime;
datalines;
DELL  01OCT2009 08OCT2009 09:30:00 14:00:00
IBM   07OCT2009 14OCT2009 10:30:00 15:00:00
MSFT  13OCT2009 20OCT2009 11:30:00 16:00:00
run;
 
/** STAGE 2: Run the TAQ_EVENT_WINDOW macro **/
 
%taq_event_windows1(verbosity=none,type=ct,keepvars=symbol date price size
    ,outdata=want,outtype=dataset,sortord=DST);
 
/* ********************************************************************************* */
/* *************  Material Copyright Wharton Research Data Services  *************** */
/* ****************************** All Rights Reserved ****************************** */
/* ********************************************************************************* */

Top of Section

Top