Category: Data management
Author resource: Joost Impink
/* 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;
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.
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 |