/******************************************************************************************** Latest Update: September 2026. Purpose: Download Compustat North America short-interest and monthly shares-outstanding data, select one short-interest observation per security-month, and attach CRSP PERMNOs through the historical CRSP/Compustat Merged (CCM) link. Main output: SAVE.SHORT_INTEREST Unit of observation: One Compustat security (GVKEY-IID) per calendar month. When Compustat reports both a mid-month and month-end short-interest observation, the program retains the earlier observation. This preserves the convention used in the original public code and provides a consistent monthly series across periods with different reporting frequencies. Main variables: SIR_UNCAPPED = SHORTINT / SHROUT SIR = min(SIR_UNCAPPED, 1) SIR_ADJ_UNCAPPED = SHORTINTADJ / SHROUT SIR_ADJ = min(SIR_ADJ_UNCAPPED, 1) The key measure is SIR. The uncapped measure and the alternative based on Compustat's split-adjusted short-interest field are retained so users can make their own methodological choice. SIR_CAPPED_AT_ONE and SIR_ADJ_CAPPED_AT_ONE identify observations affected by the 100-percent caps. WRDS inputs: COMP.SEC_SHORTINT COMP.SEC_MSHARE CRSP.CCMXPF_LNKHIST Design choices: - The earliest short-interest date in each GVKEY-IID-month is retained. - The latest shares-outstanding observation in the same month is used as the denominator. - The CCM link is required to match GVKEY, issue ID, and the short-interest observation date. Only standard LC/LU links are accepted. LINKPRIM is retained for transparency, but secondary issues are not discarded merely because they are not the primary issue. - Observations with missing or nonpositive shares outstanding are excluded from the main output and retained in SAVE.DIAG_MISSING_SHARES. - Observations without an eligible CRSP link remain in the main output with PERMNO missing and are also retained in SAVE.DIAG_UNMATCHED_CCM. - Ratios below zero or above one are retained in SAVE.DIAG_RATIO_OUT_OF_RANGE. - The program performs the reductions and joins on WRDS and downloads only the compact finished table. Important: 1. Review the SAVE LIBNAME path before running. The directory must already exist. 2. The public replication sample ends on 31DEC2025. An optional annually updating setting is provided below but deliberately commented out. 3. The program replaces SAVE.SHORT_INTEREST only after the staged download passes source, variable, observation-count, nonempty, and uniqueness checks. 4. A dated backup of an existing final dataset is created before replacement. ********************************************************************************************/ /************************************* 1. USER SETTINGS *************************************/ libname save 'C:\...\data\short_interest'; /* Change to an existing writable folder on the local computer. */ /* Broad lower bound: the source data determine the first actual observation. */ %let begin_date=%sysfunc(inputn(01JAN1900,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)); */ %let begin_date_text=%sysfunc(putn(&begin_date,date9.)); %let end_date_text=%sysfunc(putn(&end_date,date9.)); %let output_data=save.short_interest; %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 gvkey, iid, yyyymm, count(*) as duplicate_count from &ds group by gvkey, iid, yyyymm 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 GVKEY-IID-month keys.; %put ERROR- See &diagnostic.. The existing final output was not replaced.; %abort cancel; %end; %mend stop_on_duplicate_keys; /************************************* 3. CONNECT TO WRDS AND CHECK THE SOURCE SCHEMA *************************************/ %let wrds=&wrds_server; options comamid=TCP remote=WRDS; signon WRDS username=_prompt_; %syslput begin_date=&begin_date / remote=WRDS; %syslput end_date=&end_date / remote=WRDS; rsubmit; options noovp nocenter ps=max ls=120 msglevel=i; %let short_table_exists=0; %let shares_table_exists=0; %let link_table_exists=0; %let short_variables_found=0; %let shares_variables_found=0; %let link_variables_found=0; proc sql noprint; select count(*) into :short_table_exists trimmed from dictionary.tables where libname='COMP' and memname='SEC_SHORTINT' and memtype in ('DATA','VIEW'); select count(*) into :shares_table_exists trimmed from dictionary.tables where libname='COMP' and memname='SEC_MSHARE' and memtype in ('DATA','VIEW'); select count(*) into :link_table_exists trimmed from dictionary.tables where libname='CRSP' and memname='CCMXPF_LNKHIST' and memtype in ('DATA','VIEW'); select count(distinct name) into :short_variables_found trimmed from dictionary.columns where libname='COMP' and memname='SEC_SHORTINT' and upcase(name) in ('GVKEY','IID','DATADATE','SHORTINT','SHORTINTADJ','SPLITADJDATE'); select count(distinct name) into :shares_variables_found trimmed from dictionary.columns where libname='COMP' and memname='SEC_MSHARE' and upcase(name) in ('GVKEY','IID','DATADATE','CSHOM'); select count(distinct name) into :link_variables_found trimmed from dictionary.columns where libname='CRSP' and memname='CCMXPF_LNKHIST' and upcase(name) in ('GVKEY','LIID','LPERMNO','LINKDT','LINKENDDT','LINKTYPE','LINKPRIM'); quit; %sysrput short_table_exists=&short_table_exists; %sysrput shares_table_exists=&shares_table_exists; %sysrput link_table_exists=&link_table_exists; %sysrput short_variables_found=&short_variables_found; %sysrput shares_variables_found=&shares_variables_found; %sysrput link_variables_found=&link_variables_found; endrsubmit; %macro validate_remote_schema; %if &short_table_exists ne 1 %then %do; %put ERROR: COMP.SEC_SHORTINT is unavailable. Confirm the WRDS Compustat subscription.; signoff WRDS; %abort cancel; %end; %if &shares_table_exists ne 1 %then %do; %put ERROR: COMP.SEC_MSHARE is unavailable. Confirm the WRDS Compustat subscription.; signoff WRDS; %abort cancel; %end; %if &link_table_exists ne 1 %then %do; %put ERROR: CRSP.CCMXPF_LNKHIST is unavailable. Confirm the WRDS CCM subscription.; signoff WRDS; %abort cancel; %end; %if &short_variables_found ne 6 %then %do; %put ERROR: COMP.SEC_SHORTINT contains only &short_variables_found of 6 required fields.; signoff WRDS; %abort cancel; %end; %if &shares_variables_found ne 4 %then %do; %put ERROR: COMP.SEC_MSHARE contains only &shares_variables_found of 4 required fields.; signoff WRDS; %abort cancel; %end; %if &link_variables_found ne 7 %then %do; %put ERROR: CRSP.CCMXPF_LNKHIST contains only &link_variables_found of 7 required fields.; signoff WRDS; %abort cancel; %end; %mend validate_remote_schema; %validate_remote_schema; /************************************* 4. BUILD THE MONTHLY DATASET ON WRDS *************************************/ rsubmit; /* Retain the first short-interest report in each security-month. */ data work.short_interest_raw; set comp.sec_shortint( keep=gvkey iid datadate shortint shortintadj splitadjdate ); if &begin_date<=datadate and datadate<=&end_date; if not missing(gvkey) and not missing(iid); yyyymm=year(datadate)*100+month(datadate); short_date=datadate; format short_date splitadjdate date9.; drop datadate; run; proc sort data=work.short_interest_raw; by gvkey iid yyyymm short_date; run; data work.short_interest_monthly; set work.short_interest_raw; by gvkey iid yyyymm; if first.yyyymm; run; /* Retain the latest shares-outstanding report in each security-month. */ data work.shares_raw; set comp.sec_mshare(keep=gvkey iid datadate cshom); if &begin_date<=datadate and datadate<=&end_date; if not missing(gvkey) and not missing(iid); yyyymm=year(datadate)*100+month(datadate); shares_date=datadate; format shares_date date9.; drop datadate; run; proc sort data=work.shares_raw; by gvkey iid yyyymm descending shares_date; run; data work.shares_monthly; set work.shares_raw; by gvkey iid yyyymm descending shares_date; if first.yyyymm; run; /* Match the denominator and attach the historical issue-level CCM link. The explicit date conditions replace the chained comparison used in the old program. */ proc sql; create table work.short_interest_linked as select distinct a.gvkey, a.iid, a.yyyymm, a.short_date, a.splitadjdate, a.shortint, a.shortintadj, b.shares_date, b.cshom as shrout, c.lpermno as permno, c.linktype, c.linkprim from work.short_interest_monthly as a left join work.shares_monthly as b on a.gvkey=b.gvkey and a.iid=b.iid and a.yyyymm=b.yyyymm left join crsp.ccmxpf_lnkhist as c on a.gvkey=c.gvkey and strip(a.iid)=strip(c.liid) and c.liid not in ('00X','99X') and c.linktype in ('LC','LU') and (missing(c.linkdt) or c.linkdt<=a.short_date) and (missing(c.linkenddt) or a.short_date<=c.linkenddt); quit; data work.wrds_short_interest; set work.short_interest_linked; length denominator_available crsp_match sir_capped_at_one sir_adj_capped_at_one 3; denominator_available=(not missing(shrout) and shrout>0); crsp_match=not missing(permno); if denominator_available and not missing(shortint) then do; sir_uncapped=shortint/shrout; sir=sir_uncapped; sir_capped_at_one=(sir_uncapped>1); if sir_capped_at_one then sir=1; end; if denominator_available and not missing(shortintadj) then do; sir_adj_uncapped=shortintadj/shrout; sir_adj=sir_adj_uncapped; sir_adj_capped_at_one=(sir_adj_uncapped>1); if sir_adj_capped_at_one then sir_adj=1; end; format short_date shares_date splitadjdate yymmddn8. sir sir_uncapped sir_adj sir_adj_uncapped 12.6; label gvkey='Compustat GVKEY' iid='Compustat Issue ID' yyyymm='Calendar Year and Month' short_date='Selected Short-Interest Observation Date' splitadjdate='Compustat Month-End Split-Adjustment Date' shortint='Shares Held Short' shortintadj='Shares Held Short, Compustat-Adjusted' shares_date='Shares-Outstanding Observation Date' shrout='Compustat Monthly Common Shares Outstanding' permno='CRSP PERMNO' linktype='CCM Link Type' linkprim='CCM Primary-Link Marker' denominator_available='Positive Shares-Outstanding Denominator Available' crsp_match='Eligible Historical CCM Link Found' sir_uncapped='SHORTINT Divided by SHROUT, Uncapped' sir='SHORTINT Divided by SHROUT, Capped at One' sir_capped_at_one='SIR_UNCAPPED Exceeded One' sir_adj_uncapped='SHORTINTADJ Divided by SHROUT, Uncapped' sir_adj='SHORTINTADJ Divided by SHROUT, Capped at One' sir_adj_capped_at_one='SIR_ADJ_UNCAPPED Exceeded One' ; run; proc sql noprint; select count(*) into :remote_nobs trimmed from work.wrds_short_interest; quit; %sysrput remote_nobs=&remote_nobs; proc download data=work.wrds_short_interest out=work.short_interest_download; run; endrsubmit; signoff WRDS; /************************************* 5. VALIDATE THE STAGED DOWNLOAD *************************************/ %require_nonempty(work.short_interest_download,Staged short-interest download); %require_variables( work.short_interest_download, gvkey iid yyyymm short_date splitadjdate shortint shortintadj shares_date shrout permno linktype linkprim denominator_available crsp_match sir_uncapped sir sir_capped_at_one sir_adj_uncapped sir_adj sir_adj_capped_at_one ); proc sql noprint; select count(*) into :local_nobs trimmed from work.short_interest_download; quit; %macro validate_download_count; %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 final output was not replaced.; %abort cancel; %end; %mend validate_download_count; %validate_download_count; %stop_on_duplicate_keys( work.short_interest_download, save.diag_duplicate_keys, Staged short-interest download ); /************************************* 6. CREATE DIAGNOSTICS AND FINAL SAMPLE *************************************/ data work.short_interest_new save.diag_missing_shares save.diag_unmatched_ccm save.diag_ratio_out_of_range; set work.short_interest_download; if not denominator_available then output save.diag_missing_shares; if not crsp_match then output save.diag_unmatched_ccm; if (not missing(sir_uncapped) and (sir_uncapped<0 or sir_uncapped>1)) or (not missing(sir_adj_uncapped) and (sir_adj_uncapped<0 or sir_adj_uncapped>1)) then output save.diag_ratio_out_of_range; /* Preserve the original final-sample rule: require a computable unadjusted SIR. */ if denominator_available and not missing(sir_uncapped) then output work.short_interest_new; run; proc sort data=work.short_interest_new; by gvkey iid yyyymm; run; %require_nonempty(work.short_interest_new,Staged final short-interest dataset); %stop_on_duplicate_keys( work.short_interest_new, save.diag_final_duplicate_keys, Staged final short-interest dataset ); /************************************* 7. BACK UP AND SAVE THE FINAL DATASET *************************************/ %let run_date=%sysfunc(today(),yymmddn8.); %let backup_data=save.short_interest_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='Compustat Monthly Short Interest and Shares Outstanding with Historical CRSP Link' ); set work.short_interest_new; run; /************************************* 8. LOG DIAGNOSTIC COUNTS *************************************/ %put NOTE: ------------------------------------------------------------; %put NOTE: Requested source dates = &begin_date_text through &end_date_text; %put NOTE: Final output = &output_data; %put NOTE: ------------------------------------------------------------; %log_count(save.diag_missing_shares,Observations without positive shares outstanding); %log_count(save.diag_unmatched_ccm,Observations without an eligible historical CCM link); %log_count(save.diag_ratio_out_of_range,Observations with an uncapped ratio outside zero to one); %log_count(save.diag_duplicate_keys,Duplicate keys in the staged WRDS download); %log_count(save.diag_final_duplicate_keys,Duplicate keys in the staged final output); %log_count(&output_data,Final short-interest observations); proc contents data=&output_data varnum; run;