Find a last daily TRACE record for each CUSIP
Example
/* ------------------------------------------------------------------------ */
/* Usage : Find a last daily TRACE record for each CUSIP */
/* */
/* Notes: */
/* */
/* 1. This program takes advantage of the fact that the TRACE data set is */
/* already sorted by CUSIP_ID TRD_EXCTN_DT TRD_EXCTN_TM (i.e. CUSIP, */
/* trade execution date and time). As a result, the last physical */
/* trace record for a given TRD_EXCTN_DT is also chronologically the */
/* last trade. */
/* */
/* 2. You will need to allow only one of the %LET statements below be */
/* operational, depending on your definition of "closing price", */
/* "closing trade", "last price", etc. */
/* */
/* %let timelimit=1; */
/* or */
/* %let timelimit=%str(trdexctn_tm<='18:29:59't); */
/* or */
/* %let timelimit=%str(trd_exctn_tm between '08:00't and '17:15:59't) */
/* */
/* The first %LET statement above tells the program to accept trades */
/* executed anytime during the day. The second says to accept only */
/* those that are reported during the opearting hours of the TRACE */
/* system (search for "system hours" in the TRACE users guide in the */
/* manual page of the TRACE web query. */
/* */
/* You may want to substitute your own time limits. For example, the */
/* normal market session hours are 8:00AM to 5:15PM (On Google search */
/* for "trace" "regular market session". So if you want price at the */
/* close of the market you would revise the TIMELIMIT macrovariable */
/* accordingly, as per the third %LET statement. */
/* */
/* 3. Note the WHERE ALSO statements enerate logical AND conditions, */
/* instead of requiring the construction of a single WHERE statement */
/* with numerous ANDs. The WHERE ALSO statement even works correctly */
/* when it is the first, or only WHERE statement. */
/* */
/* ------------------------------------------------------------------------ */
options nocenter nodate nonumber ls=max ps=max msglevel=i;
/* ************************************************************************ */
/* In the %LET statements below, */
/* use "%*" to make a statement into a comment */
/* and "%" to make the statement operational */
/* ************************************************************************ */
%*let timelimit=1; %** All day **;
%let timelimit=%str(trd_exctn_tm<='18:29:59't); %** Up to "trace system" closing **;
%*let timelimit=%str(trd_exctn_tm between '08:00't and '17:15:59't) %** Up to "market session" closing **;
/* ************************************************************************ */
/* Add and remove the WHERE ALSO statements as needed, but leave the */
/* "WHERE ALSO &timelimit" in place. Instead just modify the %LET */
/* statements above. Note that even the first WHERE statement can be */
/* a WHERE ALSO. */
/* ************************************************************************ */
data last_trace ;
set trace.trace;
where also company_symbol in ("DELL","XOM"); ** DELL, Exxon Mobil only **;
where also trd_exctn_dt between '01jan2006'd and '31dec2006'd; ** 2006 trades only **;
where also &timelimit; ** Timelimit, as appropriate **;
by cusip_id trd_exctn_dt trd_exctn_tm; ** Expects the incoming data to be sorted **;
if last.trd_exctn_dt; ** Keeps only the last record-of-the-day **;
run;