COMPOUND (CIZ format) - Macro

The following example will compound returns weekly from daily returns for each security (IBM, AAPL and MSFT) in Data1 and will output them into Data2 dataset

proc sql;
  create table data1
  as select distinct permno, dlycaldt as date, dlyret
  from crsp.Stkdlysecuritydata
  where permno in (14593, 12490, 10107) and dlycaldt between '01JAN2020'd and '31DEC2020'd;
quit;
%COMPOUND (INSET=data1, OUTSET=data2, INFREQ=d, OUTFREQ=w, DATEVAR=date)

Top of Section

/* ********************************************************************************* */
/* ******************** W R D S   R E S E A R C H   M A C R O S ******************** */
/* ********************************************************************************* */
/* WRDS Macro: COMPOUND                                                              */
/* Summary   : Calculates continuosly compound returns                               */
/* Author    : Denys Glushkov, WRDS                                                  */
/* Date      : May 19, 2009                                                          */
/* Update    : November 2024 by Freda Drechsler for CRSP CIZ data format             */
/* Variables : - INSET and OUTSET are input and output datasets                      */
/*             - INFREQ frequency of returns in INSET dataset, m/d for monthly/daily */
/*             - OUTFREQ compounding interval,                                       */
/*                         a(annual)/s(semiannual)/q(quarterly)/m(monthly)/w(weekly) */
/*             - DATEVAR name of the date variable in the INSET dataset,             */
/*                         MUST BE a SAS date value                                  */
/* ********************************************************************************* */
 
 %MACRO COMPOUND (INSET=, OUTSET=, INFREQ=, OUTFREQ=, DATEVAR=);
  options nonotes nomprint;
  %local freq totret;

  %if %lowcase(&outfreq)=a %then %let freq='Year';
     %else %if %lowcase(&outfreq)=s %then %let freq='Semiyear';
  	 %else %if %lowcase(&outfreq)=q %then %let freq='Qtr';
     %else %if %lowcase(&outfreq)=m %then %let freq='Month';
     %else %if %lowcase(&outfreq)=w %then %let freq='Week';

  %if %lowcase(&infreq)=d %then %do; 
	%let totret = a.dlyret;
  %end;
  %else
   %if %lowcase(&infreq)=m %then %do;
	 %let totret = a.mthret;
  %end;
 
   %put ### START;
   %put ### Sorting... ;
   proc sort data=&inset out=_&inset; by permno &datevar;run;
 
   %put ### COMPOUNDING RETURNS FROM &INFREQ INTO &OUTFREQ FREQUENCY...;
   /*main part: compounding total returns within a specific date group*/
   proc sql;
     create table &outset
       as select distinct a.permno,
       year(a.&datevar)*100+intck(&freq,intnx('year',a.&datevar,0),
       a.&datevar)+1 as newdate label=&freq,
       exp(sum(log(1+&totret)))-1 as cret "Compound return",
       min(&totret) as minret "Min return", max(&totret) as maxret "Maximum return",
       nmiss(&totret) as nmiss "Number of total returns missing",
       n(a.&datevar) as nobs "Number of all observations in aggregation"
       from &inset as a 
       group by a.permno, newdate;
     quit;
 
   /*house cleaning*/
   proc sql; drop table  _&inset;quit;
  options notes mprint;
 %put ### DONE ;
 %put ### OUTPUT IN THE DATASET &outset;
 %MEND;
  
 /* ********************************************************************************* */
 /* *************  Material Copyright Wharton Research Data Services  *************** */
 /* ****************************** All Rights Reserved ****************************** */
 /* ********************************************************************************* */

Top of Section

Users should have access to CRSP daily and monthly stock files.

Top of Section