/*************************************************************************************** Latest Update: September 2026. Purpose: Construct quarterly earnings surprises and three-trading-day raw announcement returns. Main outputs: IBES.SUECARS Final firm-quarter dataset SAVE.COMP_FINAL Combined local intermediate dataset (SUE1-SUE4) Local setup: Create two writable folders and point the IBES and SAVE LIBNAME statements below to them. IBES stores the final dataset and dated backups. SAVE stores checkpoints, diagnostics, and the combined intermediate dataset. No separately downloaded input file is required. Required access: - A WRDS account with SAS/CONNECT access - I/B/E/S, Compustat, and CRSP subscriptions WRDS inputs (when REFRESH_WRDS=YES, or exact CRSP pulls when RESUME): IBES.DETU_EPSUS IBES.ACTU_EPSUS Compustat FUNDQ, COMPANY, and SECURITY CRSP.CCMXPF_LNKHIST CRSP.DSF_V2 WRDS historical IBES-CRSP linking table (IBCRSPHIST) Design choices: - CRSP CIZ is used for the entire sample. - The ticker universe is obtained directly from WRDSAPPS.IBCRSPHIST using link scores 0-2; no local ticker-universe file is required. - One WRDS connection retrieves the exact CRSP factors and event returns needed; no local DSF_V2 copy is maintained. - Both median and mean forecasts are calculated in one pass. - Forecasts must be announced 1-90 days before the earnings announcement; FPI 6 and 7 are retained, and the latest forecast per analyst is used. - At least one valid analyst forecast is required for SUE3/SUE4. - Missing primary/diluted forecast-basis codes are treated as primary. Ties in the primary/diluted vote are assigned to diluted. - Forecast split factors and event returns are queried from CRSP CIZ on WRDS. - SUE1/SUE2 require the same fiscal quarter in the immediately preceding fiscal year. - The event return is a compounded raw buy-and-hold return over trading days [0,2]. - Missing stock returns on valid market trading days are set to zero. - The public replication sample ends on 31DEC2025. An optional annually updating END_DATE setting is shown below but is deliberately commented out. Important: 1. Review the two LIBNAME paths below before running this program. 2. The first run must use REFRESH_WRDS=YES. After a successful first run, use NO for offline reruns. Use RESUME only after an interrupted YES run that completed the core checkpoint downloads. 3. The historical output name SUECARS is retained, but BHAR02 and CRET03 are raw returns; the program does not subtract a market or factor-model benchmark. ***************************************************************************************/ /************************************* 1. USER SETTINGS *************************************/ libname ibes 'C:\...\ibes'; *Adjust to the existing folder for the final dataset and backups; libname save 'C:\...\ibes\tmp'; *Adjust to the existing folder for checkpoints, diagnostics, and intermediate datasets; /* YES : first run, or deliberately refresh the core WRDS checkpoints and exact CRSP observations. RESUME: reuse the saved core checkpoints and fetch only exact CRSP observations. NO : remain offline and use a previously completed set of checkpoints. */ %let refresh_wrds=YES; /* Fixed fiscal-period-end range for the public replication dataset. */ %let begin_date=%sysfunc(inputn(01JAN1980,date9.)); %let end_date=%sysfunc(inputn(31DEC2025,date9.)); /* To update the dataset automatically through the last completed calendar year, comment out the fixed END_DATE above and uncomment the following line. */ /* %let end_date=%sysfunc(intnx(year,%sysfunc(today()),-1,end)); */ %let end_date_text=%sysfunc(putn(&end_date,date9.)); /* Allow for forecasts made before the first fiscal-period end in the sample. */ %let ciz_start_buffer_days=180; /* WRDS SAS/CONNECT settings. */ %let wrds_server=wrds.wharton.upenn.edu 4016; options mprint msglevel=i; /************************************* 2. SMALL UTILITY MACROS *************************************/ %macro require_dataset(ds); %if %sysfunc(exist(&ds))=0 %then %do; %put ERROR: Required dataset &ds does not exist.; %abort cancel; %end; %mend require_dataset; %macro require_variable(ds,var); %local dsid varnum rc; %let dsid=%sysfunc(open(&ds,i)); %if &dsid=0 %then %do; %put ERROR: Cannot open required dataset &ds.; %abort cancel; %end; %let varnum=%sysfunc(varnum(&dsid,&var)); %let rc=%sysfunc(close(&dsid)); %if &varnum=0 %then %do; %put ERROR: Required variable &var is absent from &ds.; %abort cancel; %end; %mend require_variable; %macro require_variables(ds,vars); %local i var; %let i=1; %let var=%scan(&vars,&i,%str( )); %do %while(%length(&var)); %require_variable(&ds,&var); %let i=%eval(&i+1); %let var=%scan(&vars,&i,%str( )); %end; %mend require_variables; %macro log_count(ds,label); %local nobs; %if %sysfunc(exist(&ds)) %then %do; proc sql noprint; select count(*) into :nobs trimmed from &ds; quit; %put NOTE: &label = &nobs observations.; %end; %else %put NOTE: &label dataset &ds was not created.; %mend log_count; %macro require_nonempty(ds,label); %local dsid nobs rc; %require_dataset(&ds); %let dsid=%sysfunc(open(&ds,i)); %let nobs=%sysfunc(attrn(&dsid,nlobsf)); %let rc=%sysfunc(close(&dsid)); %if &nobs=0 %then %do; %put ERROR: &label (&ds) contains zero observations.; %abort cancel; %end; %mend require_nonempty; /************************************* 3. PREPARE AND OPTIONALLY REFRESH WRDS CHECKPOINTS *************************************/ %macro refresh_wrds_checkpoints; %if %upcase(&refresh_wrds)=YES %then %do; %let wrds=&wrds_server; options comamid=TCP remote=WRDS; signon WRDS username=_prompt_; /* Make the local sample bounds explicit in the remote macro environment. */ %syslput begin_date=&begin_date / remote=WRDS; %syslput end_date=&end_date / remote=WRDS; %syslput ciz_start_buffer_days=&ciz_start_buffer_days / remote=WRDS; rsubmit; options noovp nocenter ps=max ls=120 msglevel=i; /* Obtain the complete requested ticker universe directly from the current WRDS historical I/B/E/S-CRSP link. Restrict the link history to records that overlap the requested fiscal-period-end sample. */ proc sql; create table work.wrds_ibes_link as select distinct a.ticker, a.permno, a.sdate, a.edate, a.score from wrdsapps.ibcrsphist as a where a.score in (0,1,2) and not missing(a.ticker) and (missing(a.edate) or a.edate>=&begin_date) and (missing(a.sdate) or a.sdate<=&end_date); quit; proc sort data=work.wrds_ibes_link( keep=ticker where=(not missing(ticker))) out=work.tickers nodupkey; by ticker; run; /* Use WRDS's compact daily-index table as the official CRSP trading calendar. Do not scan the full security-day DSF_V2 table for dates. Exact split factors and returns are requested later, after their local PERMNO-date keys have been constructed. */ proc sql; create table work.wrds_crsp_trading_calendar as select dlycaldt as trade_date format=date9. from crsp.wrds_dailyindexret_query where dlycaldt between &begin_date-&ciz_start_buffer_days and today() order by trade_date; quit; /* Quarterly EPS actuals, including the announcement time used later. */ proc sql; create table work.wrds_ibes_actual as select a.ticker, a.pends, a.anndats, a.anntims, a.value, a.usfirm, a.measure, a.pdicity from ibes.actu_epsus as a inner join work.tickers as t on a.ticker=t.ticker where a.measure='EPS' and a.pdicity='QTR' and a.pends between &begin_date and &end_date; quit; /* Use a cleaned actual only to reduce detailed forecasts before download. The raw actual checkpoint is still downloaded so local diagnostics remain available. */ data work.wrds_ibes_actual_pre; set work.wrds_ibes_actual; missing_anndats=missing(anndats); missing_anntims=(missing(anntims) or anntims=0); run; proc sort data=work.wrds_ibes_actual_pre; by ticker pends usfirm missing_anndats anndats missing_anntims anntims; run; data work.wrds_ibes_actual_clean; set work.wrds_ibes_actual_pre; by ticker pends usfirm; if first.usfirm; run; /* Apply the forecast window and latest-per-analyst rule on WRDS. This is repeated locally as a defensive check but sharply reduces transfer size. */ proc sql; create table work.wrds_ibes_detail_window as select d.ticker, d.value, d.fpedats, d.anndats, d.revdats, d.estimator, d.analys, d.pdf, d.usfirm, d.measure, d.fpi from ibes.detu_epsus as d inner join work.tickers as t on d.ticker=t.ticker inner join work.wrds_ibes_actual_clean as a on d.ticker=a.ticker and d.fpedats=a.pends and d.usfirm=a.usfirm where d.measure='EPS' and d.fpi in ('6','7') and d.fpedats between &begin_date and &end_date and not missing(d.anndats) and not missing(a.anndats) and intck('day',d.anndats,a.anndats) between 1 and 90; quit; proc sort data=work.wrds_ibes_detail_window; by ticker fpedats usfirm estimator analys anndats revdats; run; data work.wrds_ibes_detail; set work.wrds_ibes_detail_window; by ticker fpedats usfirm estimator analys; if last.analys; run; /* CCM links needed for the PERMNOs in the requested universe. */ proc sql; create table work.wrds_ccm_link as select distinct c.gvkey, c.lpermno, c.linkdt, c.linkenddt, c.linktype, c.linkprim from crsp.ccmxpf_lnkhist as c inner join (select distinct permno from work.wrds_ibes_link) as p on c.lpermno=p.permno where c.linktype in ('LC','LU'); quit; /* Relevant GVKEYs only. */ proc sql; create table work.relevant_gvkeys as select distinct gvkey from work.wrds_ccm_link; quit; /* Compustat quarterly observations and the primary-issue IBTIC. */ proc sql; create table work.wrds_compq as select distinct f.gvkey, f.fyearq, f.fqtr, f.conm, f.datadate, f.rdq, f.epsfxq, f.epspxq, f.prccq, f.ajexq, f.spiq, f.cshoq, f.cshprq, f.cshfdq, f.saleq, f.atq, f.fyr, f.datafqtr, co.priusa, sec.ibtic, f.cshoq*f.prccq as mcap from comp.fundq as f inner join work.relevant_gvkeys as g on f.gvkey=g.gvkey left join comp.company as co on f.gvkey=co.gvkey left join comp.security as sec on f.gvkey=sec.gvkey and co.priusa=sec.iid where f.datadate between &begin_date and &end_date and f.fyr>0 and (f.saleq>0 or f.atq>0) and f.consol='C' and f.popsrc='D' and f.indfmt='INDL' and f.datafmt='STD' and not missing(f.datafqtr); quit; /* Stage every download in local WORK. Existing checkpoints are left intact if the connection fails before the full refresh is available. */ proc download data=work.wrds_ibes_link out=work.new_wrds_ibes_link; run; proc download data=work.wrds_ibes_detail out=work.new_wrds_ibes_detail; run; proc download data=work.wrds_ibes_actual out=work.new_wrds_ibes_actual; run; proc download data=work.wrds_ccm_link out=work.new_wrds_ccm_link; run; proc download data=work.wrds_compq out=work.new_wrds_compq; run; proc download data=work.wrds_crsp_trading_calendar out=work.new_wrds_crsp_trading_calendar; run; endrsubmit; /* Validate the complete staged refresh before replacing durable checkpoints. */ %require_nonempty(work.new_wrds_ibes_link,Staged WRDS IBES-CRSP link); %require_nonempty(work.new_wrds_ibes_detail,Staged WRDS detailed forecasts); %require_nonempty(work.new_wrds_ibes_actual,Staged WRDS actuals); %require_nonempty(work.new_wrds_ccm_link,Staged WRDS CCM links); %require_nonempty(work.new_wrds_compq,Staged WRDS Compustat quarters); %require_nonempty(work.new_wrds_crsp_trading_calendar,Staged WRDS trading calendar); %require_variables(work.new_wrds_ibes_link, ticker permno sdate edate score); %require_variables(work.new_wrds_ibes_detail, ticker value fpedats anndats revdats estimator analys pdf usfirm); %require_variables(work.new_wrds_ibes_actual, ticker pends anndats anntims value usfirm); %require_variables(work.new_wrds_ccm_link, gvkey lpermno linkdt linkenddt linktype linkprim); %require_variables(work.new_wrds_compq, gvkey fyearq fqtr conm datadate rdq epsfxq epspxq prccq ajexq spiq cshoq cshprq cshfdq saleq atq fyr datafqtr priusa ibtic mcap); %require_variable(work.new_wrds_crsp_trading_calendar,trade_date); data save.wrds_ibes_link; set work.new_wrds_ibes_link; run; data save.wrds_ibes_detail; set work.new_wrds_ibes_detail; run; data save.wrds_ibes_actual; set work.new_wrds_ibes_actual; run; data save.wrds_ccm_link; set work.new_wrds_ccm_link; run; data save.wrds_compq; set work.new_wrds_compq; run; data save.wrds_crsp_trading_calendar; set work.new_wrds_crsp_trading_calendar; run; data save.wrds_checkpoint_meta; length refresh_setting $3; begin_date=&begin_date; end_date=&end_date; refresh_date=today(); refresh_setting='YES'; complete=0; format begin_date end_date refresh_date date9.; run; %end; %else %if %upcase(&refresh_wrds)=RESUME %then %do; %put NOTE: REFRESH_WRDS=RESUME. Reusing core checkpoints and reconnecting only for exact CRSP requests.; %let wrds=&wrds_server; options comamid=TCP remote=WRDS; signon WRDS username=_prompt_; %end; %else %if %upcase(&refresh_wrds)=NO %then %do; %put NOTE: REFRESH_WRDS=NO. Using existing local WRDS checkpoint datasets.; %end; %else %do; %put ERROR: REFRESH_WRDS must be YES, RESUME, or NO.; %abort cancel; %end; %mend refresh_wrds_checkpoints; %refresh_wrds_checkpoints; %require_nonempty(save.wrds_ibes_link,WRDS IBES-CRSP checkpoint); %require_nonempty(save.wrds_ibes_detail,WRDS detailed-forecast checkpoint); %require_nonempty(save.wrds_ibes_actual,WRDS actuals checkpoint); %require_nonempty(save.wrds_ccm_link,WRDS CCM checkpoint); %require_nonempty(save.wrds_compq,WRDS Compustat checkpoint); %require_nonempty(save.wrds_crsp_trading_calendar,WRDS trading-calendar checkpoint); %require_nonempty(save.wrds_checkpoint_meta,WRDS checkpoint metadata); %require_variable(save.wrds_checkpoint_meta,begin_date); %require_variable(save.wrds_checkpoint_meta,end_date); %require_variable(save.wrds_checkpoint_meta,complete); proc sql noprint; select min(begin_date), max(end_date), max(complete) into :checkpoint_begin_date trimmed, :checkpoint_end_date trimmed, :checkpoint_complete trimmed from save.wrds_checkpoint_meta; quit; %macro check_checkpoint_coverage; %if %upcase(&refresh_wrds)=NO and &checkpoint_complete ne 1 %then %do; %put ERROR: The prior WRDS refresh did not complete all CRSP requests.; %put ERROR- Set REFRESH_WRDS=YES and rerun the program.; %abort cancel; %end; %if %sysevalf(&checkpoint_begin_date > &begin_date) %then %do; %put ERROR: WRDS checkpoints begin on %sysfunc(putn(&checkpoint_begin_date,date9.)), after requested %sysfunc(putn(&begin_date,date9.)).; %put ERROR- Set REFRESH_WRDS=YES to refresh them.; %abort cancel; %end; %if %sysevalf(&checkpoint_end_date < &end_date) %then %do; %put ERROR: WRDS checkpoints end on %sysfunc(putn(&checkpoint_end_date,date9.)), before requested &end_date_text..; %put ERROR- Set REFRESH_WRDS=YES to refresh them.; %abort cancel; %end; %mend check_checkpoint_coverage; %check_checkpoint_coverage; /* Fail at the checkpoint boundary if WRDS renamed or removed a required field. */ %require_variables(save.wrds_ibes_link, ticker permno sdate edate score); %require_variables(save.wrds_ibes_detail, ticker value fpedats anndats revdats estimator analys pdf usfirm); %require_variables(save.wrds_ibes_actual, ticker pends anndats anntims value usfirm); %require_variables(save.wrds_ccm_link, gvkey lpermno linkdt linkenddt linktype linkprim); %require_variables(save.wrds_compq, gvkey fyearq fqtr conm datadate rdq epsfxq epspxq prccq ajexq spiq cshoq cshprq cshfdq saleq atq fyr datafqtr priusa ibtic mcap); %require_variable(save.wrds_crsp_trading_calendar,trade_date); /* Reconstruct the local ticker list from the saved WRDS link checkpoint. */ proc sort data=save.wrds_ibes_link( keep=ticker where=(not missing(ticker))) out=work.tickers nodupkey; by ticker; run; %require_nonempty(work.tickers,Ticker universe reconstructed from WRDS link checkpoint); /************************************* 4. CHECK THE WRDS MARKET-CALENDAR CHECKPOINT *************************************/ proc sql noprint; select min(trade_date), max(trade_date) into :ciz_min_date trimmed, :ciz_max_date trimmed from save.wrds_crsp_trading_calendar; quit; %put NOTE: WRDS CRSP market-calendar checkpoint covers %sysfunc(putn(&ciz_min_date,date9.)) through %sysfunc(putn(&ciz_max_date,date9.)).; %macro check_ciz_coverage; %if %sysevalf(&ciz_min_date > &begin_date-&ciz_start_buffer_days) %then %do; %put ERROR: The CRSP checkpoint begins too late for the requested sample.; %put ERROR- Set REFRESH_WRDS=YES to rebuild it.; %abort cancel; %end; %if %sysevalf(&ciz_max_date < &end_date) %then %do; %put ERROR: The CRSP checkpoint ends before &end_date_text..; %put ERROR- Set REFRESH_WRDS=YES to rebuild it.; %abort cancel; %end; %mend check_ciz_coverage; %check_ciz_coverage; proc sort data=save.wrds_crsp_trading_calendar out=work.trading_calendar0 nodupkey; by trade_date; run; data work.trading_calendar; set work.trading_calendar0; td_index=_n_; run; /* Build a small, dense calendar-day lookup once. This turns every later calendar-to-trading-date assignment into a keyed merge rather than a many-to-many SQL range join. */ data work.calendar_date_map_seed; format calendar_date date9.; do calendar_date=&ciz_min_date to &ciz_max_date; output; end; run; data work.calendar_date_map_forward; merge work.calendar_date_map_seed(in=in_calendar_day) work.trading_calendar( rename=(trade_date=calendar_date)); by calendar_date; retain prev_trade_date; if in_calendar_day; if not missing(td_index) then prev_trade_date=calendar_date; format prev_trade_date date9.; run; proc sort data=work.calendar_date_map_forward out=work.calendar_date_map_reverse; by descending calendar_date; run; data work.calendar_date_map_reverse; set work.calendar_date_map_reverse; retain next_trade_date next_td_index; if not missing(td_index) then do; next_trade_date=calendar_date; next_td_index=td_index; end; format next_trade_date date9.; run; proc sort data=work.calendar_date_map_reverse out=work.calendar_date_map; by calendar_date; run; /************************************* 5. CLEAN QUARTERLY EPS ACTUALS *************************************/ data work.ibes_actual_pre; set save.wrds_ibes_actual; missing_anndats=missing(anndats); missing_anntims=(missing(anntims) or anntims=0); run; proc sort data=work.ibes_actual_pre; by ticker pends usfirm missing_anndats anndats missing_anntims anntims; run; data work.ibes_actual save.diag_actual_duplicates; set work.ibes_actual_pre; by ticker pends usfirm; if first.usfirm then output work.ibes_actual; else output save.diag_actual_duplicates; run; data work.ibes_actual; set work.ibes_actual; rename anndats=repdats value=act; drop missing_anndats missing_anntims measure pdicity; run; /************************************* 6. MATCH, FILTER, AND SELECT ANALYST FORECASTS *************************************/ proc sql; create table work.forecast_candidates as select d.*, a.repdats, a.anntims, a.act, l.permno, l.score as link_score, l.sdate, l.edate from save.wrds_ibes_detail as d inner join work.ibes_actual as a on d.ticker=a.ticker and d.fpedats=a.pends and d.usfirm=a.usfirm inner join save.wrds_ibes_link as l on d.ticker=l.ticker and (missing(l.sdate) or l.sdate<=d.fpedats) and (missing(l.edate) or d.fpedats<=l.edate) where not missing(d.anndats) and not missing(a.repdats) and intck('day',d.anndats,a.repdats) between 1 and 90; quit; proc sort data=work.forecast_candidates; by ticker fpedats usfirm permno estimator analys anndats revdats; run; data work.forecast_latest; set work.forecast_candidates; by ticker fpedats usfirm permno estimator analys; if last.analys; forecast_id+1; run; /************************************* 7. MAP FORECAST/REPORT DATES TO TRADING DATES AND SPLIT-ADJUST LOCALLY *************************************/ /* Forecast date: last market trading day on or before the forecast date. */ proc sort data=work.forecast_latest out=work.forecast_latest_by_anndats; by anndats; run; data work.forecast_est_dates; merge work.forecast_latest_by_anndats(in=in_forecast) work.calendar_date_map( keep=calendar_date prev_trade_date rename=(calendar_date=anndats prev_trade_date=est_trade_date)); by anndats; if in_forecast; format est_trade_date date9.; run; /* Report date: first market trading day on or after the announcement date. */ proc sort data=work.forecast_est_dates out=work.forecast_est_dates_by_repdats; by repdats; run; data work.forecast_trade_dates; merge work.forecast_est_dates_by_repdats(in=in_forecast) work.calendar_date_map( keep=calendar_date next_trade_date rename=(calendar_date=repdats next_trade_date=rep_trade_date)); by repdats; if in_forecast; format rep_trade_date date9.; run; /* Request each required PERMNO/date split factor only once. */ data work.crsp_factor_keys; set work.forecast_trade_dates( keep=permno est_trade_date rename=(est_trade_date=factor_date)) work.forecast_trade_dates( keep=permno rep_trade_date rename=(rep_trade_date=factor_date)); if not missing(permno) and not missing(factor_date); format factor_date date9.; run; proc sort data=work.crsp_factor_keys nodupkey; by permno factor_date; run; %macro refresh_crsp_factors; %if %upcase(&refresh_wrds) ne NO %then %do; rsubmit; proc upload data=work.crsp_factor_keys out=work.crsp_factor_keys; run; proc sql; create table work.wrds_crsp_factor_values as select f.permno, f.factor_date, r.dlycumfacshr as split_factor from work.crsp_factor_keys as f left join crsp.dsf_v2 as r on f.permno=r.permno and f.factor_date=r.dlycaldt and r.conditionaltype='RW' and r.tradingstatusflg='A'; quit; proc download data=work.wrds_crsp_factor_values out=work.new_wrds_crsp_factor_values; run; endrsubmit; %require_dataset(work.new_wrds_crsp_factor_values); %require_variables(work.new_wrds_crsp_factor_values, permno factor_date split_factor); proc sort data=work.new_wrds_crsp_factor_values out=work.crsp_factor_values nodupkey; by permno factor_date; run; proc sort data=work.forecast_trade_dates( keep=forecast_id permno est_trade_date rep_trade_date) out=work.forecast_factor_map_est; by permno est_trade_date; run; data work.crsp_factor_values_est; set work.crsp_factor_values; rename factor_date=est_trade_date split_factor=est_factor; run; proc sort data=work.crsp_factor_values_est; by permno est_trade_date; run; data work.forecast_factor_map_est; merge work.forecast_factor_map_est(in=in_forecast) work.crsp_factor_values_est; by permno est_trade_date; if in_forecast; run; proc sort data=work.forecast_factor_map_est; by permno rep_trade_date; run; data work.crsp_factor_values_rep; set work.crsp_factor_values; rename factor_date=rep_trade_date split_factor=rep_factor; run; proc sort data=work.crsp_factor_values_rep; by permno rep_trade_date; run; data save.wrds_crsp_factors; merge work.forecast_factor_map_est(in=in_forecast) work.crsp_factor_values_rep; by permno rep_trade_date; if in_forecast; keep forecast_id est_factor rep_factor; run; %end; %else %put NOTE: Reusing the saved exact-date CRSP split-factor checkpoint.; %require_dataset(save.wrds_crsp_factors); %require_variables(save.wrds_crsp_factors, forecast_id est_factor rep_factor); %mend refresh_crsp_factors; %refresh_crsp_factors; proc sort data=work.forecast_trade_dates out=work.forecast_trade_dates_by_id; by forecast_id; run; proc sort data=save.wrds_crsp_factors out=work.wrds_crsp_factors_by_id; by forecast_id; run; data work.forecast_factors; merge work.forecast_trade_dates_by_id(in=in_forecast) work.wrds_crsp_factors_by_id; by forecast_id; if in_forecast; run; data work.forecast_valid save.diag_missing_split_factors; set work.forecast_factors; if missing(est_factor) or missing(rep_factor) or est_factor<=0 or rep_factor<=0 then output save.diag_missing_split_factors; else do; new_value=(rep_factor/est_factor)*value; output work.forecast_valid; end; run; /************************************* 8. CALCULATE MEDIAN AND MEAN CONSENSUS IN ONE PASS *************************************/ proc sql; create table work.consensus0 as select ticker, fpedats, usfirm, permno, min(link_score) as link_score, min(repdats) as repdats format=date9., min(anntims) as anntims, min(act) as act, median(new_value) as medest, mean(new_value) as meanest, count(new_value) as numest, std(new_value) as dispersion, sum(case when pdf='P' or missing(pdf) then 1 else 0 end) as p_count, sum(case when pdf='D' then 1 else 0 end) as d_count from work.forecast_valid group by ticker, fpedats, usfirm, permno; create table work.consensus as select *, case when p_count>d_count then 'P' else 'D' end as basis length=1 from work.consensus0 where numest>=1; quit; /************************************* 9. CLEAN COMPUSTAT QUARTERS *************************************/ proc sort data=save.wrds_compq out=work.compq_sorted; by gvkey fyearq fqtr descending datadate; run; data work.compq save.diag_comp_duplicates; set work.compq_sorted; by gvkey fyearq fqtr; if first.fqtr then output work.compq; else output save.diag_comp_duplicates; run; data work.compq; set work.compq; fqenddt=datadate; format fqenddt date9.; run; /************************************* 10. LINK COMPUSTAT, CCM, IBES-CRSP, AND CONSENSUS DATA *************************************/ proc sql; create table work.comp_link_candidates0 as select a.*, c.lpermno, c.linkdt as ccm_linkdt format=date9., c.linkenddt as ccm_linkenddt format=date9., c.linkprim, c.linktype, i.ticker, i.score as link_score, i.sdate as ibes_link_sdate format=date9., i.edate as ibes_link_edate format=date9., s.fpedats, s.repdats, s.anntims, s.act, s.medest, s.meanest, s.numest, s.dispersion, s.p_count, s.d_count, s.basis, max(c.linkdt,i.sdate) as effective_link_date format=date9., abs(a.datadate-s.fpedats) as period_date_gap from work.compq as a inner join save.wrds_ccm_link as c on a.gvkey=c.gvkey and (missing(c.linkdt) or c.linkdt<=a.datadate) and (missing(c.linkenddt) or a.datadate<=c.linkenddt) inner join save.wrds_ibes_link as i on c.lpermno=i.permno and (missing(i.sdate) or i.sdate<=a.datadate) and (missing(i.edate) or a.datadate<=i.edate) and i.score in (0,1,2) left join work.consensus as s on i.ticker=s.ticker and c.lpermno=s.permno and intnx('month',a.datadate,0,'beginning') =intnx('month',s.fpedats,0,'beginning'); quit; /* Remove exact repetitions before applying the requested deterministic hierarchy. */ proc sort data=work.comp_link_candidates0 out=work.comp_link_candidates nodupkey; by gvkey fyearq fqtr lpermno ticker link_score linkprim linktype ccm_linkdt ccm_linkenddt ibes_link_sdate ibes_link_edate fpedats; run; data work.comp_link_ranked; set work.comp_link_candidates; score_rank=coalesce(link_score,999); if linkprim='P' then linkprim_rank=1; else if linkprim='C' then linkprim_rank=2; else linkprim_rank=9; if linktype='LC' then linktype_rank=1; else if linktype='LU' then linktype_rank=2; else linktype_rank=9; /* Prefer an available analyst consensus and the closest fiscal-period-end match only after applying the identifier-link quality hierarchy. */ consensus_missing=missing(fpedats); period_date_gap_rank=coalesce(period_date_gap,9999); run; proc sort data=work.comp_link_ranked; by gvkey fyearq fqtr score_rank linkprim_rank linktype_rank consensus_missing period_date_gap_rank descending effective_link_date lpermno ticker; run; data work.comp_linked save.diag_link_discarded; set work.comp_link_ranked; by gvkey fyearq fqtr; if first.fqtr then output work.comp_linked; else output save.diag_link_discarded; run; /************************************* 11. CALCULATE SUE1-SUE4 LOCALLY *************************************/ /* Require the same fiscal quarter in exactly the preceding fiscal year. */ proc sql; create table work.sue_base as select a.*, b.epspxq as lag_epspxq, b.epsfxq as lag_epsfxq, b.ajexq as lag_ajexq, b.spiq as lag_spiq, b.cshprq as lag_cshprq, b.cshfdq as lag_cshfdq from work.comp_linked as a /* Use the cleaned Compustat history for the lag. The prior-year quarter does not need analyst coverage or an active IBES ticker link. */ left join work.compq as b on a.gvkey=b.gvkey and a.fqtr=b.fqtr and b.fyearq=a.fyearq-1; quit; data work.comp_final_unfiltered; set work.sue_base; length selected_basis $1; if basis='D' then selected_basis='D'; else selected_basis='P'; /* SUE1: seasonal random walk. */ if selected_basis='D' then do; if ajexq ne 0 then actual_sue1=epsfxq/ajexq; if lag_ajexq ne 0 then expected_sue1=lag_epsfxq/lag_ajexq; end; else do; if ajexq ne 0 then actual_sue1=epspxq/ajexq; if lag_ajexq ne 0 then expected_sue1=lag_epspxq/lag_ajexq; end; if ajexq ne 0 then deflator_ts=prccq/ajexq; if n(actual_sue1,expected_sue1,deflator_ts)=3 and deflator_ts ne 0 then sue1=(actual_sue1-expected_sue1)/deflator_ts; /* SUE2: seasonal random walk excluding 65% of special items. */ current_spi_per_share=.; lag_spi_per_share=.; if selected_basis='D' then do; if not missing(spiq) and not missing(cshfdq) and cshfdq ne 0 then current_spi_per_share=-0.65*spiq/cshfdq; if not missing(lag_spiq) and not missing(lag_cshfdq) and lag_cshfdq ne 0 then lag_spi_per_share=-0.65*lag_spiq/lag_cshfdq; if ajexq ne 0 then actual_sue2=sum(epsfxq,current_spi_per_share)/ajexq; if lag_ajexq ne 0 then expected_sue2=sum(lag_epsfxq,lag_spi_per_share)/lag_ajexq; end; else do; if not missing(spiq) and not missing(cshprq) and cshprq ne 0 then current_spi_per_share=-0.65*spiq/cshprq; if not missing(lag_spiq) and not missing(lag_cshprq) and lag_cshprq ne 0 then lag_spi_per_share=-0.65*lag_spiq/lag_cshprq; if ajexq ne 0 then actual_sue2=sum(epspxq,current_spi_per_share)/ajexq; if lag_ajexq ne 0 then expected_sue2=sum(lag_epspxq,lag_spi_per_share)/lag_ajexq; end; if n(actual_sue2,expected_sue2,deflator_ts)=3 and deflator_ts ne 0 then sue2=(actual_sue2-expected_sue2)/deflator_ts; /* SUE3/SUE4: analyst median/mean forecasts. */ deflator_ibes=prccq; actual_sue3=act; expected_sue3=medest; actual_sue4=act; expected_sue4=meanest; if n(actual_sue3,expected_sue3,deflator_ibes)=3 and deflator_ibes ne 0 then sue3=(actual_sue3-expected_sue3)/deflator_ibes; if n(actual_sue4,expected_sue4,deflator_ibes)=3 and deflator_ibes ne 0 then sue4=(actual_sue4-expected_sue4)/deflator_ibes; /* Retain a plain numeric display rather than applying a percent format. */ format sue1-sue4 best12.; run; /* Preserve the Livnat-Mendenhall filters and the one-calendar-day date rule. */ data save.comp_final; set work.comp_final_unfiltered; if missing(rdq) then delete; if not (prccq>1 and mcap>5) then delete; if missing(repdats) then do; if missing(sue1) and missing(sue2) then delete; end; else if abs(intck('day',repdats,rdq))>1 then delete; /* Announcement timing variables used to assign event day 0. */ anndats_event=coalesce(repdats,rdq); missing_time=(missing(anntims) or anntims=0); after_hours=(not missing_time and anntims>=16*60*60); /* The trading-calendar join below finds the first valid market date on/after target. */ event_target_date=anndats_event+after_hours; format anndats_event event_target_date date9. anntims time8.; label anndats_event='Earnings announcement date used for event timing' anntims='I/B/E/S earnings announcement time' missing_time='Announcement time is missing or exactly 00:00' after_hours='Announcement occurred at or after 4:00 p.m.' event_target_date='Calendar target for event day 0' sue1='Earnings surprise: seasonal random walk' sue2='Earnings surprise: seasonal random walk excluding special items' sue3='Earnings surprise: analyst median forecast' sue4='Earnings surprise: analyst mean forecast' deflator_ts='SUE1/SUE2 deflator: PRCCQ/AJEXQ' deflator_ibes='SUE3/SUE4 deflator: PRCCQ'; drop score_rank linkprim_rank linktype_rank consensus_missing period_date_gap_rank; run; /************************************* 12. ASSIGN EVENT DAYS FROM THE MARKET CALENDAR *************************************/ data work.events; set save.comp_final; if not missing(lpermno) and not missing(event_target_date); event_id+1; run; proc sort data=work.events out=work.events_by_target_date; by event_target_date; run; data work.event_day0 save.diag_missing_event_dates; merge work.events_by_target_date(in=in_event) work.calendar_date_map( keep=calendar_date next_trade_date next_td_index rename=(calendar_date=event_target_date)); by event_target_date; if in_event; if not missing(next_trade_date) then do; rdq_t=next_trade_date; day0_index=next_td_index; output work.event_day0; end; else output save.diag_missing_event_dates; format rdq_t date9.; run; data work.event_grid_indices; set work.event_day0; do event_day=0 to 2; event_td_index=day0_index+event_day; output; end; run; proc sort data=work.event_grid_indices; by event_td_index; run; data work.trading_calendar_by_index; set work.trading_calendar; rename td_index=event_td_index trade_date=event_trade_date; run; proc sort data=work.trading_calendar_by_index; by event_td_index; run; data work.event_grid; merge work.event_grid_indices(in=in_event) work.trading_calendar_by_index( keep=event_td_index event_trade_date); by event_td_index; if in_event and not missing(event_trade_date); format event_trade_date date9.; run; proc sort data=work.event_grid; by event_id event_day; run; /************************************* 13. FETCH EXACT CIZ RETURNS FROM WRDS AND COMPOUND [0,2] *************************************/ data work.crsp_event_keys; set work.event_grid( keep=event_id event_day lpermno event_trade_date); run; %macro refresh_crsp_event_returns; %if %upcase(&refresh_wrds) ne NO %then %do; rsubmit; proc upload data=work.crsp_event_keys out=work.crsp_event_keys; run; proc sql; create table work.wrds_crsp_event_returns as select g.event_id, g.event_day, r.dlyret from work.crsp_event_keys as g left join crsp.dsf_v2 as r on g.lpermno=r.permno and g.event_trade_date=r.dlycaldt and r.conditionaltype='RW' and r.tradingstatusflg='A'; quit; proc download data=work.wrds_crsp_event_returns out=work.new_wrds_crsp_event_returns; run; endrsubmit; signoff WRDS; %require_dataset(work.new_wrds_crsp_event_returns); %require_variables(work.new_wrds_crsp_event_returns, event_id event_day dlyret); data save.wrds_crsp_event_returns; set work.new_wrds_crsp_event_returns; run; data work.wrds_checkpoint_meta_complete; set save.wrds_checkpoint_meta; complete=1; completion_date=today(); format completion_date date9.; run; data save.wrds_checkpoint_meta; set work.wrds_checkpoint_meta_complete; run; %end; %else %put NOTE: Reusing the saved exact-date CRSP event-return checkpoint.; %require_dataset(save.wrds_crsp_event_returns); %require_variables(save.wrds_crsp_event_returns, event_id event_day dlyret); %mend refresh_crsp_event_returns; %refresh_crsp_event_returns; proc sql; create table work.event_returns_daily as select g.*, r.dlyret, missing(r.dlyret) as return_was_missing from work.event_grid as g left join save.wrds_crsp_event_returns as r on g.event_id=r.event_id and g.event_day=r.event_day order by g.event_id, g.event_day; quit; data work.event_returns_daily save.diag_missing_event_returns; set work.event_returns_daily; if return_was_missing then do; output save.diag_missing_event_returns; ret_filled=0; end; else ret_filled=dlyret; output work.event_returns_daily; run; data work.event_bhar02; set work.event_returns_daily; by event_id event_day; retain wealth ret0 n_event_days n_missing_returns; if first.event_id then do; wealth=1; ret0=.; n_event_days=0; n_missing_returns=0; end; wealth=wealth*(1+ret_filled); n_event_days+1; n_missing_returns+return_was_missing; if event_day=0 then ret0=ret_filled; if last.event_id then do; if n_event_days=3 then bhar02=wealth-1; else bhar02=.; cret03=bhar02; /* Backward-compatible alias; definition is now compounded [0,2]. */ output; end; keep event_id rdq_t ret0 bhar02 cret03 n_event_days n_missing_returns; label rdq_t='Event day 0: first assigned CRSP trading day' ret0='Raw return on event day 0; missing set to zero' bhar02='Compounded raw buy-and-hold return [0,2]' cret03='Backward-compatible alias for compounded raw return [0,2]' n_event_days='Number of market trading days in event window' n_missing_returns='Daily stock returns replaced with zero'; run; data save.diag_incomplete_event_calendar; set work.event_bhar02; if n_event_days<3; run; /************************************* 14. CREATE, BACK UP, AND SAVE THE FINAL DATASET *************************************/ proc sql; create table work.suecars_new as select e.*, r.rdq_t, r.ret0, r.bhar02, r.cret03, r.n_event_days, r.n_missing_returns from work.events as e inner join work.event_bhar02 as r on e.event_id=r.event_id; quit; proc sort data=work.suecars_new; by gvkey fyearq fqtr; run; %require_nonempty(work.suecars_new,Rebuilt final dataset); /* A nonunique firm-quarter would make the replacement ambiguous, so retain the duplicate keys for inspection and stop before touching the existing output. */ proc sql; create table save.diag_final_duplicate_keys as select gvkey, fyearq, fqtr, count(*) as duplicate_count from work.suecars_new group by gvkey, fyearq, fqtr having calculated duplicate_count>1; quit; %macro stop_on_final_duplicates; %local duplicate_keys; proc sql noprint; select count(*) into :duplicate_keys trimmed from save.diag_final_duplicate_keys; quit; %if &duplicate_keys>0 %then %do; %put ERROR: WORK.SUECARS_NEW has &duplicate_keys nonunique firm-quarter keys.; %put ERROR- See SAVE.DIAG_FINAL_DUPLICATE_KEYS. Existing IBES.SUECARS was not replaced.; %abort cancel; %end; %mend stop_on_final_duplicates; %stop_on_final_duplicates; %let run_date=%sysfunc(today(),yymmddn8.); %let backup_ds=ibes.suecars_backup_&run_date; %macro back_up_existing_output; %if %sysfunc(exist(ibes.suecars)) %then %do; %if %sysfunc(exist(&backup_ds))=0 %then %do; data &backup_ds; set ibes.suecars; run; %put NOTE: Existing IBES.SUECARS backed up as &backup_ds..; %end; %else %put NOTE: &backup_ds already exists and was not overwritten.; %end; %mend back_up_existing_output; %back_up_existing_output; data ibes.suecars; set work.suecars_new; run; /************************************* 15. LOG DIAGNOSTIC COUNTS *************************************/ %log_count(work.tickers,Unique WRDS-linked tickers); %log_count(save.wrds_ibes_detail,Downloaded detailed forecasts); %log_count(save.wrds_crsp_factors,Exact-date CRSP split-factor requests); %log_count(save.wrds_crsp_event_returns,Exact-date CRSP event-return requests); %log_count(work.forecast_latest,Latest valid analyst forecasts before split-factor checks); %log_count(save.diag_missing_split_factors,Forecasts excluded for missing split factors); %log_count(work.consensus,Quarterly analyst consensuses); %log_count(save.diag_actual_duplicates,Duplicate quarterly actuals discarded); %log_count(save.diag_comp_duplicates,Duplicate Compustat quarters discarded); %log_count(save.diag_link_discarded,Identifier-link candidates discarded by tie-break hierarchy); %log_count(save.diag_missing_event_dates,Events lacking a valid day-0 market date); %log_count(save.diag_incomplete_event_calendar,Events with fewer than three market dates); %log_count(save.diag_missing_event_returns,Daily event returns replaced with zero); %log_count(save.diag_final_duplicate_keys,Nonunique final firm-quarter keys); %log_count(ibes.suecars,Final IBES.SUECARS observations);