/******************************************************************************************** Latest Update: September 2026. Purpose: Construct compounded past stock returns over 3-, 6-, 12-, and 36-month horizons. For every horizon, the program creates: - a current-window return that includes month t; and - a lagged-window return that ends in month t-1. Main output: SAVE.CUMULATIVE_PAST_RETURNS Output definitions for an H-month horizon: CUM_RETH = compounded return from t-(H-1) through t CUM_RETH_LAGGED = compounded return from t-H through t-1 Local setup: Create one writable folder and point the SAVE LIBNAME below to it. The folder stores the final dataset, a compact WRDS checkpoint, diagnostics, and dated output backups. No separately downloaded CRSP file is required. Required access: - A WRDS account with SAS/CONNECT access - A CRSP subscription WRDS inputs: CRSP.MSF_V2 CRSP.STKSECURITYINFOHIST CRSP CIZ filters: - U.S.-incorporated ordinary common equity issued by corporations - Primary listing on NYSE, NYSE American/AMEX, or Nasdaq - CIZ conditional types RW or NW - Active trading status Design choices: - Monthly holding-period returns, including distributions, are compounded geometrically. - A missing return on an otherwise present monthly security record is replaced with zero and identified in SAVE.DIAG_MISSING_MONTHLY_RETURNS. - A cumulative return is created only when all H required calendar months are represented. A missing security-month observation therefore breaks the relevant window. - The program reads an additional 36 months before BEGIN_DATE so lagged 36-month returns can be constructed without leaking those buffer observations into the final sample. - 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 SAVE LIBNAME path before running the program. The directory must already exist. 2. The first run must use REFRESH_WRDS=YES. After a successful run, change it to NO to reuse the saved checkpoint without reconnecting to WRDS. 3. The program uses the monthly holding-period return MTHRET supplied by CRSP CIZ. It does not separately merge the legacy-format DLRET variable. ********************************************************************************************/ /************************************* 1. USER SETTINGS *************************************/ libname save 'C:\...\crsp\cum_ret'; /* Adjust to an existing writable folder for outputs, checkpoints, and diagnostics. */ /* YES: first run or deliberate WRDS refresh. NO : reuse a successfully completed local checkpoint. */ %let refresh_wrds=YES; /* Fixed sample dates for the public replication dataset. Store the bounds as numeric SAS dates so that macro comparisons and remote SQL use the same unambiguous values. */ %let begin_date=%sysfunc(inputn(01JAN1926,date9.)); %let end_date=%sysfunc(inputn(31DEC2025,date9.)); /* To update 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)); */ /* The longest lagged measure needs months t-1 through t-36. */ %let input_start_date=%sysfunc(intnx(month,&begin_date,-36,same)); %let begin_date_text=%sysfunc(putn(&begin_date,date9.)); %let end_date_text=%sysfunc(putn(&end_date,date9.)); %let input_start_date_text=%sysfunc(putn(&input_start_date,date9.)); %let output_data=save.cumulative_past_returns; %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 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; %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 stop_on_duplicate_keys(ds,diagnostic,label); %local duplicate_keys; proc sql; create table &diagnostic as select permno, date, count(*) as duplicate_count from &ds group by permno, date having calculated duplicate_count>1; select count(*) into :duplicate_keys trimmed from &diagnostic; quit; %if &duplicate_keys>0 %then %do; %put ERROR: &label contains &duplicate_keys nonunique PERMNO-month keys.; %put ERROR- See &diagnostic.. No existing final output was replaced.; %abort cancel; %end; %mend stop_on_duplicate_keys; /************************************* 3. DOWNLOAD OR REUSE MONTHLY CRSP CIZ RETURNS *************************************/ %macro refresh_monthly_returns; %if %upcase(&refresh_wrds)=YES %then %do; %let wrds=&wrds_server; options comamid=TCP remote=WRDS; signon WRDS username=_prompt_; %syslput input_start_date=&input_start_date / remote=WRDS; %syslput end_date=&end_date / remote=WRDS; /* Fail early with a readable message if WRDS access or the CIZ schema differs from the structure expected by this public program. */ rsubmit; %let msf_table_exists=0; %let security_table_exists=0; %let msf_variables_found=0; %let security_variables_found=0; proc sql noprint; select count(*) into :msf_table_exists trimmed from dictionary.tables where libname='CRSP' and memname='MSF_V2' and memtype in ('DATA','VIEW'); select count(*) into :security_table_exists trimmed from dictionary.tables where libname='CRSP' and memname='STKSECURITYINFOHIST' and memtype in ('DATA','VIEW'); select count(distinct name) into :msf_variables_found trimmed from dictionary.columns where libname='CRSP' and memname='MSF_V2' and upcase(name) in ('PERMNO','MTHCALDT','MTHRET','CONDITIONALTYPE','TRADINGSTATUSFLG'); select count(distinct name) into :security_variables_found trimmed from dictionary.columns where libname='CRSP' and memname='STKSECURITYINFOHIST' and upcase(name) in ('PERMNO','SECINFOSTARTDT','SECINFOENDDT','SHARETYPE','SECURITYTYPE', 'SECURITYSUBTYPE','USINCFLG','ISSUERTYPE','PRIMARYEXCH'); quit; %sysrput msf_table_exists=&msf_table_exists; %sysrput security_table_exists=&security_table_exists; %sysrput msf_variables_found=&msf_variables_found; %sysrput security_variables_found=&security_variables_found; endrsubmit; %if &msf_table_exists ne 1 %then %do; %put ERROR: CRSP.MSF_V2 is unavailable. Confirm the WRDS CRSP subscription.; signoff WRDS; %abort cancel; %end; %if &security_table_exists ne 1 %then %do; %put ERROR: CRSP.STKSECURITYINFOHIST is unavailable.; signoff WRDS; %abort cancel; %end; %if &msf_variables_found ne 5 %then %do; %put ERROR: CRSP.MSF_V2 contains only &msf_variables_found of 5 required fields.; %put ERROR- Expected PERMNO MTHCALDT MTHRET CONDITIONALTYPE TRADINGSTATUSFLG.; signoff WRDS; %abort cancel; %end; %if &security_variables_found ne 9 %then %do; %put ERROR: CRSP.STKSECURITYINFOHIST contains only &security_variables_found of 9 required fields.; %put ERROR- Review the source schema before changing this program.; signoff WRDS; %abort cancel; %end; rsubmit; options noovp nocenter ps=max ls=120 msglevel=i; /* CIZ replaces the legacy EXCHCD/SHRCD screen with historical security attributes. Joining on the effective security-information dates prevents current attributes from being applied retrospectively. */ proc sql; create table work.wrds_monthly_returns as select m.permno, m.mthcaldt as date format=date9., m.mthret as ret from crsp.msf_v2 as m inner join crsp.stksecurityinfohist as s on m.permno=s.permno and (missing(s.secinfostartdt) or s.secinfostartdt<=m.mthcaldt) and (missing(s.secinfoenddt) or m.mthcaldt<=s.secinfoenddt) where m.mthcaldt between &input_start_date and &end_date and s.sharetype='NS' and s.securitytype='EQTY' and s.securitysubtype='COM' and s.usincflg='Y' and s.issuertype in ('ACOR','CORP') and s.primaryexch in ('N','A','Q') and m.conditionaltype in ('RW','NW') and m.tradingstatusflg='A'; select count(*) into :remote_nobs trimmed from work.wrds_monthly_returns; quit; %sysrput remote_nobs=&remote_nobs; proc download data=work.wrds_monthly_returns out=work.new_wrds_monthly_returns; run; endrsubmit; signoff WRDS; /* Validate the staged download before replacing the durable checkpoint. */ %require_nonempty(work.new_wrds_monthly_returns,Staged CRSP monthly-return download); %require_variables(work.new_wrds_monthly_returns,permno date ret); proc sql noprint; select count(*) into :local_nobs trimmed from work.new_wrds_monthly_returns; quit; %if &local_nobs ne &remote_nobs %then %do; %put ERROR: WRDS created &remote_nobs rows but only &local_nobs rows were downloaded.; %put ERROR- The existing monthly-return checkpoint was not replaced.; %abort cancel; %end; %stop_on_duplicate_keys( work.new_wrds_monthly_returns, save.diag_download_duplicate_keys, Staged CRSP monthly-return download ); data save.wrds_monthly_returns; set work.new_wrds_monthly_returns; run; data save.wrds_monthly_returns_meta; length source_table $32; requested_start_date=&input_start_date; requested_end_date=&end_date; refresh_date=today(); complete=1; source_table='CRSP.MSF_V2'; format requested_start_date requested_end_date refresh_date date9.; run; %end; %else %if %upcase(&refresh_wrds)=NO %then %do; %put NOTE: REFRESH_WRDS=NO. Reusing the saved CRSP monthly-return checkpoint.; %end; %else %do; %put ERROR: REFRESH_WRDS must be YES or NO.; %abort cancel; %end; %mend refresh_monthly_returns; %refresh_monthly_returns; /************************************* 4. VALIDATE CHECKPOINT COVERAGE *************************************/ %require_nonempty(save.wrds_monthly_returns,CRSP monthly-return checkpoint); %require_variables(save.wrds_monthly_returns,permno date ret); %require_nonempty(save.wrds_monthly_returns_meta,CRSP monthly-return checkpoint metadata); %require_variables( save.wrds_monthly_returns_meta, requested_start_date requested_end_date refresh_date complete source_table ); proc sql noprint; select min(requested_start_date), max(requested_end_date), max(complete) into :checkpoint_start_date trimmed, :checkpoint_end_date trimmed, :checkpoint_complete trimmed from save.wrds_monthly_returns_meta; quit; %macro check_checkpoint_coverage; %if &checkpoint_complete ne 1 %then %do; %put ERROR: The saved CRSP monthly-return checkpoint is not marked complete.; %put ERROR- Set REFRESH_WRDS=YES and rerun the program.; %abort cancel; %end; %if %sysevalf(&checkpoint_start_date > &input_start_date) %then %do; %put ERROR: The checkpoint begins after the required buffer start date &input_start_date_text..; %put ERROR- Set REFRESH_WRDS=YES and rerun the program.; %abort cancel; %end; %if %sysevalf(&checkpoint_end_date < &end_date) %then %do; %put ERROR: The checkpoint ends before the requested sample end date &end_date_text..; %put ERROR- Set REFRESH_WRDS=YES and rerun the program.; %abort cancel; %end; %mend check_checkpoint_coverage; %check_checkpoint_coverage; /* Recheck uniqueness when an existing checkpoint is reused. A new checkpoint was already checked before replacement. */ %macro validate_reused_checkpoint; %if %upcase(&refresh_wrds)=NO %then %do; %stop_on_duplicate_keys( save.wrds_monthly_returns, save.diag_checkpoint_duplicate_keys, Saved CRSP monthly-return checkpoint ); %end; %mend validate_reused_checkpoint; %validate_reused_checkpoint; /************************************* 5. CLEAN MONTHLY RETURNS *************************************/ data work.monthly_returns save.diag_missing_monthly_returns; set save.wrds_monthly_returns; return_was_missing=missing(ret); if return_was_missing then do; output save.diag_missing_monthly_returns; ret=0; end; output work.monthly_returns; format date date9.; label ret='CRSP monthly holding-period return; missing values replaced with zero' return_was_missing='Original CRSP monthly return was missing'; run; proc sort data=work.monthly_returns; by permno date; run; /************************************* 6. CALCULATE ALL EIGHT ROLLING RETURNS IN ONE PASS *************************************/ data work.rolling_returns; set work.monthly_returns; by permno date; array gross[0:36] _temporary_; array present[0:36] _temporary_; retain prevdate; if first.permno then do; do i=0 to 36; gross[i]=1; present[i]=0; end; prevdate=.; end; if not first.permno then do; gap=intck('month',prevdate,date); if gap>36 then do; do i=0 to 36; gross[i]=1; present[i]=0; end; end; else if gap>0 then do; do i=36 to 0 by -1; if i>=gap then do; gross[i]=gross[i-gap]; present[i]=present[i-gap]; end; else do; gross[i]=1; present[i]=0; end; end; end; end; gross[0]=1+ret; present[0]=1; call missing( cum_ret3, cum_ret6, cum_ret12, cum_ret36, cum_ret3_lagged, cum_ret6_lagged, cum_ret12_lagged, cum_ret36_lagged ); prod_current=1; prod_lagged=1; n_current=0; n_lagged=0; do i=0 to 36; prod_current=prod_current*gross[i]; n_current=n_current+present[i]; if i>=1 then do; prod_lagged=prod_lagged*gross[i]; n_lagged=n_lagged+present[i]; end; if i=2 and n_current=3 then cum_ret3=prod_current-1; if i=5 and n_current=6 then cum_ret6=prod_current-1; if i=11 and n_current=12 then cum_ret12=prod_current-1; if i=35 and n_current=36 then cum_ret36=prod_current-1; if i=3 and n_lagged=3 then cum_ret3_lagged=prod_lagged-1; if i=6 and n_lagged=6 then cum_ret6_lagged=prod_lagged-1; if i=12 and n_lagged=12 then cum_ret12_lagged=prod_lagged-1; if i=36 and n_lagged=36 then cum_ret36_lagged=prod_lagged-1; end; prevdate=date; keep permno date cum_ret3 cum_ret6 cum_ret12 cum_ret36 cum_ret3_lagged cum_ret6_lagged cum_ret12_lagged cum_ret36_lagged; run; /************************************* 7. RESTRICT TO THE REQUESTED SAMPLE AND LABEL THE OUTPUT *************************************/ data work.cumulative_past_returns_new; set work.rolling_returns; /* Do not use a chained comparison in SAS. The explicit AND prevents the 36-month calculation buffer from appearing in the final dataset. */ if &begin_date<=date and date<=&end_date; /* Retain only months for which at least one requested measure is available. */ if n( cum_ret3, cum_ret6, cum_ret12, cum_ret36, cum_ret3_lagged, cum_ret6_lagged, cum_ret12_lagged, cum_ret36_lagged )>0; format date yymmddn8.; label permno='CRSP PERMNO' date='CRSP Month-End Date' cum_ret3='Compounded return over months t-2 through t' cum_ret6='Compounded return over months t-5 through t' cum_ret12='Compounded return over months t-11 through t' cum_ret36='Compounded return over months t-35 through t' cum_ret3_lagged='Compounded return over months t-3 through t-1' cum_ret6_lagged='Compounded return over months t-6 through t-1' cum_ret12_lagged='Compounded return over months t-12 through t-1' cum_ret36_lagged='Compounded return over months t-36 through t-1' ; run; proc sort data=work.cumulative_past_returns_new; by permno date; run; /************************************* 8. VALIDATE, BACK UP, AND SAVE THE FINAL DATASET *************************************/ %require_nonempty(work.cumulative_past_returns_new,Staged cumulative-return output); %require_variables( work.cumulative_past_returns_new, permno date cum_ret3 cum_ret6 cum_ret12 cum_ret36 cum_ret3_lagged cum_ret6_lagged cum_ret12_lagged cum_ret36_lagged ); %stop_on_duplicate_keys( work.cumulative_past_returns_new, save.diag_final_duplicate_keys, Staged cumulative-return output ); %let run_date=%sysfunc(today(),yymmddn8.); %let backup_data=save.cumret_backup_&run_date; %macro back_up_existing_output; %if %sysfunc(exist(&output_data)) %then %do; %if %sysfunc(exist(&backup_data))=0 %then %do; data &backup_data; set &output_data; run; %put NOTE: Existing &output_data backed up as &backup_data..; %end; %else %put NOTE: &backup_data already exists and was not overwritten.; %end; %mend back_up_existing_output; %back_up_existing_output; data &output_data( label='CRSP Cumulative Past Returns: Current and One-Month-Lagged Windows' ); set work.cumulative_past_returns_new; run; /************************************* 9. LOG DIAGNOSTIC COUNTS *************************************/ %put NOTE: ------------------------------------------------------------; %put NOTE: Requested final sample = &begin_date_text through &end_date_text; %put NOTE: WRDS input begins = &input_start_date_text; %put NOTE: Final output = &output_data; %put NOTE: ------------------------------------------------------------; %log_count(save.wrds_monthly_returns,Filtered CRSP monthly-return checkpoint); %log_count(save.diag_missing_monthly_returns,Missing monthly returns replaced with zero); %log_count(save.diag_download_duplicate_keys,Duplicate keys in latest WRDS download); %log_count(save.diag_checkpoint_duplicate_keys,Duplicate keys in reused checkpoint); %log_count(save.diag_final_duplicate_keys,Duplicate keys in staged final output); %log_count(&output_data,Final cumulative-return observations); proc contents data=&output_data varnum; run; /******************************************************************************************** OPTIONAL: CREATE ONE OF THE FORMER SINGLE-MEASURE FILES The public version saves all eight measures together to avoid duplicating the same PERMNO-date keys across eight permanent datasets. If a separate file is useful, the following pattern can be adapted to any measure: data save.cum_ret12_lagged(keep=permno date cum_return); set save.cumulative_past_returns( rename=(cum_ret12_lagged=cum_return) ); if not missing(cum_return); run; ********************************************************************************************/