WRDS_batch_ticker_cqm.sas

filename input '/home/yale/genli925/cqm_tsymbol_date.txt'; /* Change to your input file path */

data dictionary2;
	infile input dlm = "," dsd missover;
	input smbl $ dates yymmdd8.;
run;

/* Genrate a macro variable to loop through */

proc sql noprint;
  select distinct dates into :datesValsD separated by ' ' /* select unique dates that we want to extract from TAQ Monthly*/
  from work.dictionary2
  where dates >= '01Jan2013'd;
quit;  


%put &datesValsD;

/* A macro that autogenerated list of needed Monthly TAQ datasets */
%macro taq_monthly_dataset_list(type = );
      %let type=%lowcase(&type);
	  %let i = 1;
	  %let datesValsMi = %scan(&datesValsM, &i);
    /* Loop over each date in "datesVals" macro variable*/
        %do %while("&datesValsMi" ~= "");/** For each date in the "datesVals" */ 
            %let yyyymmdd=%sysfunc(putn(&datesValsMi,yymmddn8.));
            /*If the corresponding dataset exists, add it to the list */
            %if %sysfunc(exist(taq.&type._&yyyymmdd)) %then taq.&type._&yyyymmdd;
			%let i = %eval(&i + 1);	
			%let datesValsMi = %scan(&datesValsM, &i); 	
        %end;
%mend;

/* A macro that autogenerated list of needed Daily TAQ datasets */
%macro taq_daily_dataset_list(type = );
      %let type=%lowcase(&type);
	  %let i = 1;
	  %let datesValsDi = %scan(&datesValsD, &i);
    /* Loop over each date in "datesVals" macro variable*/
        %do %while("&datesValsDi" ~= "");/** For each date in the "datesVals" */ 
            %let yyyymmdd=%sysfunc(putn(&datesValsDi,yymmddn8.));
            /*If the corresponding dataset exists, add it to the list */
            %if %sysfunc(exist(taqmsec.&type._&yyyymmdd)) %then taqmsec.&type._&yyyymmdd;
			%let i = %eval(&i + 1);	
			%let datesValsDi = %scan(&datesValsD, &i); 	
        %end;
%mend;

* using this macro;

data outputD;
	set %taq_daily_dataset_list(type = cqm) open=defer; 	/* get TAQ Daily data for desired dates */ 
	where (TIME_M between '9:30:00't and '10:30:00't) or (TIME_M between '15:00:00't and '16:00:59't);
run;

proc sql;
	create table outputD as select a.*, b.*
		from (select * from work.dictionary2 where dates >= '01Jan2013'd) a, outputD b
		where a.smbl = b.sym_root and a.dates = b.date;
quit;



/* optional: export sas7bat file to dta file */
proc export data = outputD outfile= '/home/yale/genli925/ticker_cqm/cqm_ticker_date.dta'; run;