Macro to compute industry-year adjusted values

Category: Data management

Author resource: Joost Impink

Explanation

This macro creates new variables where the industry-median for the year is subtracted (industry-year adjustment). The data is sorted by the industry-year groups, and for each group the median values are computed. These are subtracted from the original (firm) values to compute industry-adjusted numbers.

/*	Macro to create industry adjusted variables

	For a list of variables, the industry median is subtracted

	variables required:
		dsin			dataset in
		dsout			dataset out
		industry		variable that holds industry (sic, gics, naics)
		year			variable that holds period to group on 
		vars			list of vars, space seperated (vars=roa roe)
				
	dependencies:
		- this macro uses the %runquit macro
		- this macro uses the %DO_OVER macro from Clay

	Note: this macro creates the variable keyYrInd, and will overwrite it if it exists

*/

/*	Include macro files used (%ARRAY, %DO_OVER, %runquit) 
	It is recommended to download these files, and include like this:
	%include "P:\projects\macros\array_functions.sas";
	%include "P:\projects\macros\run_quit.sas";
*/

/*	Include array function macros */

filename m1 url 'http://www.wrds.us/macros/array_functions.sas';
%include m1;

/*	Include runquit macro */

filename m2 url 'http://www.wrds.us/macros/runquit.sas';
%include m2;

/* 	Helper macro to compute industry adjusted numbers */
%macro computedIAdj(var);    &var._IA = &var - &var._median;%mend;

%macro industryAdjust(dsin=, dsout=, industry=, year=, vars=);

/*	Create variable to group by: industry-year */

data work.a_ia1;
set &dsin;
keyYrInd = &industry || &year;
%runquit;


/*	Compute industry medians */

proc sort data = work.a_ia1; by keyYrInd;run;

proc means data=work.a_ia1 n mean median noprint;
OUTPUT OUT=work.a_ia2
        median= /autoname;
var &vars;
by keyYrInd;
%runquit;

/*	Append industry-adjusted */

proc sql;

	create table work.a_ia3 as
		select a.*, %DO_OVER(VALUES=&vars, PHRASE=b.?_median, BETWEEN=COMMA ) 
		from
			work.a_ia1 a,
			work.a_ia2 b
		where
			a.keyYrInd = b.keyYrInd;

%runquit;


/*	Create output dataset and Drop keyYrInd and medians */

data &dsout (drop =keyYrInd %DO_OVER(VALUES=&vars, PHRASE=?_median));
set work.a_ia3;
%DO_OVER(VALUES=&vars, MACRO = computedIAdj);
%runquit;

 /*  Clean up */     
proc datasets 
library=work;      
	delete a_ia1 - a_ia3;    
%runquit;

%mend;

Usage

Sample usage:

/*	Sample set Funda */

data work.sample (keep = gvkey fyear sich roa roe);
set comp.funda;
if indfmt='INDL' and datafmt='STD' and popsrc='D' and consol='C' ;
roa = ni / at;
roe = ni / ceq;
if at > 0;
if ceq > 0;
run;

/*	Invoke macro */

%industryAdjust(dsin=work.sample, dsout=work.sample_ia, industry=sich, year=fyear, vars=roa roe);

Dataset ‘sample_ia’ will contain roa and roe, as well as the industry-year adjusted variables roa_ia and roe_ia.