Category: Data management

Author resource: Joost Impink


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)
		- 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\";
	%include "P:\projects\macros\";

/*	Include array function macros */

filename m1 url '';
%include m1;

/*	Include runquit macro */

filename m2 url '';
%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;

/*	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;

/*	Append industry-adjusted */

proc sql;

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


/*	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);

 /*  Clean up */     
proc datasets 
	delete a_ia1 - a_ia3;    



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;

/*	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.


Other Data management
SAS Winsorize macro


Latest forum posts
Fama French 49 YES 49 !!! by CA Miller
date format by Emily
Discretionary Accruals by jwhi121
question about discretionary accrual models by kerrida
Combining Global and North American data by CA Miller
File for Eventus or SAS Event Study by samme
quarterly accruals by lxt88
Financial Statement Comparability - De Franco, Kothari and Verdi (2011, JAR) Replication by rowing
question about discretionary accrual models by Zenghui
problem in perl by KZ

All rights reserved. © 2010-2014 [Copyright] [Privacy Statement] [Disclaimer] [About]