Extracting Intraday Price Quotes for Multiple Days from TAQ WRDS Millisecond Files using SAS
5 minute intervals

Notes 
• This example program below may be used to extract variables from TAQ Trades or other data files on WRDS. 
• You should copy the whole code into SAS and run the whole program. You can use WRDS SAS Studio.  Please be sure to modify the program as necessary (e.g., library name, interval length, date and symbol.) 
• Text in between asterisks in the document below indicates comments and will not be executed by SAS. 
• This program is based upon a sample code provided by WRDS (http://wrds.wharton.upenn.edu/), then modified by Ofira Schwartz-Soicher, then by Mary Carter and Copilot (Dec. 2025)


/* ============================================
   Build 5-minute close prices from WRDS TAQ MS (CTM)
   - Day-specific date range input (e.g., 1/1/2025-2/28/2025 or 2025-01-01 to 2025-02-28)
   - Parameterized ticker
   - Exports times as character strings (Excel-friendly)
   - Optional ISO datetime and Excel serial columns
   - Uses WRDS home path: /home/<inst>/<sysuserid>/
   ============================================*/

/* Session options (read-only warnings are OK on WRDS) */
options nonotes nostimer nosource nosyntaxcheck;
options validvarname=any;     /* May be locked on WRDS */
options validmemname=extend;  /* May be locked on WRDS */

/* WRDS TAQ MS Trades library (CTM) */
libname taqct '/wrds/nyse/sasdata/taqms/ct';  /* Daily TAQ MS trades (CTM) */

/* ------------ Main macro ------------ */
%macro run_taq_close(
    ticker=SPY,
    date_range=1/1/2025-2/28/2025,
    start_time='09:30:00.000't,
    end_time='16:00:00.000't,
    interval_sec=300,
    inst=princeton,
    include_iso_dt=Y,          /* Adds YYYY-MM-DD HH:MM:SS.sss column */
    include_excel_serial=N     /* Adds Excel serial datetime (numeric) */
  );

  /* Normalize ticker (trim, uppercase) for consistency */
  %let _ticker = %upcase(%sysfunc(strip(&ticker)));

  /* ------------------------------------------
     Parse date_range (supports "start-end" or "start to end")
     Accepts many formats via ANYDTDTE., e.g.:
       1/1/2025-2/28/2025
       2025-01-01 to 2025-02-28
       01JAN2025-28FEB2025
     ------------------------------------------*/
  /* Normalize "to" -> "-" and compress spaces */
  %let _dr_norm = %sysfunc(compbl(%sysfunc(tranwrd(%upcase(&date_range),%str( TO ),-))));
  /* Remove extra spaces around dash if present */
  %let _dr_norm = %sysfunc(tranwrd(&_dr_norm,%str( - ),-));

  /* Split start/end strings */
  %let _dr_start_str = %scan(&_dr_norm,1,-);
  %let _dr_end_str   = %scan(&_dr_norm,2,-);

  /* Guard: ensure both parts exist */
  %if %superq(_dr_end_str) = %then %do;
    %put ERROR: DATE_RANGE="&date_range" must contain a start and end (e.g., 1/1/2025-2/28/2025).;
    %return;
  %end;

  /* Convert to SAS dates using ANYDTDTE informat */
  %let _start_date = %sysfunc(inputn(%superq(_dr_start_str), anydtdte.));
  %let _end_date   = %sysfunc(inputn(%superq(_dr_end_str),   anydtdte.));

  /* Validate numeric missing using %SYSEVALF(...,boolean) */
  %if %sysevalf(&_start_date = ., boolean) or %sysevalf(&_end_date = ., boolean) %then %do;
    %put ERROR: Could not parse DATE_RANGE="&date_range". Use formats like 1/1/2025-2/28/2025.;
    %return;
  %end;

  /* Validate order (start <= end) */
  %if %sysevalf(&_start_date > &_end_date, boolean) %then %do;
    %put ERROR: Start date must be <= end date. Got &_dr_start_str to &_dr_end_str.;
    %return;
  %end;

  /* Tags for filenames and logging */
  %let _start_tag = %sysfunc(putn(&_start_date, yymmdd10.));  /* e.g., 2025-01-01 */
  %let _end_tag   = %sysfunc(putn(&_end_date,   yymmdd10.));  /* e.g., 2025-02-28 */

  %let _start_year = %sysfunc(year(&_start_date));
  %let _end_year   = %sysfunc(year(&_end_date));

  %put NOTE: &=sysuserid;
  %put NOTE: TICKER=&_ticker DATE_RANGE=&_dr_start_str to &_dr_end_str (&_start_tag to &_end_tag) INST=&inst;

  /* --------------------------
     Read CTM tables for covered years and filter by:
       - sym_root = ticker
       - date between start/end (inclusive)
       - time_m within session window
     ---------------------------*/
  data work.trades_&_ticker._range;
    set
      %do y = &_start_year %to &_end_year;
        taqct.ctm_&y:
      %end;
    ;
    where sym_root = "&_ticker"
          and date between &_start_date and &_end_date
          and time_m between &start_time and &end_time;
    keep date time_m sym_root price;
    format time_m time12.3;
  run;

  /* --------------------------
     Assign each trade to its 5-minute bin start (itime)
     ---------------------------*/
  data work.trades_binned_&_ticker._range;
    set work.trades_&_ticker._range;

    /* Numeric bin start (seconds since midnight) */
    itime = floor((time_m - &start_time)/&interval_sec)*&interval_sec + &start_time;

    /* Character for CSV (Excel-friendly) */
    itime_char = put(itime, time8.);       /* 09:30:00, 09:35:00, ... */

    format itime time12.3;                 /* visible in SAS; CSV won’t honor this */
  run;

  /* --------------------------
     Close = last trade in the interval (BY-group)
     ---------------------------*/
  proc sort data=work.trades_binned_&_ticker._range;
    by date itime time_m;
  run;

  data work.&_ticker._close_5min_raw;
    set work.trades_binned_&_ticker._range;
    by date itime time_m;
    if last.itime then do;
      /* Numeric time of last trade (seconds since midnight) */
      last_trade_time = time_m;

      /* Combine date + time to SAS datetime (seconds since 01JAN1960) */
      last_trade_dt = dhms(date, 0, 0, time_m);

      /* Character strings for export */
      date_char            = put(date, yymmdd10.);     /* YYYY-MM-DD */
      last_trade_time_char = put(time_m, time12.3);    /* HH:MM:SS.sss */

      /* Optional ISO datetime (Excel-friendly) */
      %if %upcase(&include_iso_dt) = Y %then %do;
        datetime_char = put(last_trade_dt, E8601DT23.3); /* 2025-01-02 09:54:59.969 */
      %end;

      close_price = price;
      output;
    end;
  run;

  /* --------------------------
     Optional: Excel serial datetime (numeric)
     Excel stores datetimes as days since 1899-12-30.
     Convert SAS datetime (seconds since 1960-01-01) to Excel serial:
       ExcelSerial = (SASDatetime / 86400) + 21916
  ---------------------------*/
  %if %upcase(&include_excel_serial) = Y %then %do;
    data work.&_ticker._close_5min_raw;
      set work.&_ticker._close_5min_raw;
      excel_serial = last_trade_dt / 86400 + 21916;
    run;
  %end;

  /* --------------------------
     Build export dataset
     ---------------------------*/
  %let _keep_list = date_char itime_char last_trade_time_char close_price;
  %if %upcase(&include_iso_dt) = Y %then %let _keep_list = &_keep_list datetime_char;
  %if %upcase(&include_excel_serial) = Y %then %let _keep_list = &_keep_list excel_serial;

  data work.&_ticker._close_5min_export;
    set work.&_ticker._close_5min_raw;
    keep &_keep_list;
  run;

  /* --------------------------
     Export directly from WORK to CSV
     ---------------------------*/
  %let outfile = /home/&inst/&sysuserid/spy_close_&_ticker._&_start_tag._to_&_end_tag._5min.csv;

  proc export data=work.&_ticker._close_5min_export
      outfile="&outfile"
      dbms=csv replace;
  run;

  /* --------------------------
     Verify the CSV exists
     ---------------------------*/
  data _null_;
    if fileexist("&outfile") then
      put "NOTE: CSV found at &outfile";
    else
      put "WARNING: CSV not found at &outfile";
  run;

%mend;

/* ======== Example run ======== */
%run_taq_close(ticker=SPY, date_range=1/1/2025-2/28/2025, include_iso_dt=Y);

/* Restore default note settings if desired */
options notes;