******************************************************************************* * Begin with data from the eeoclitigation.wustl.edu website ** 2013_01_master.dta ** 2013_01_injunctive-relief.dta ** 2013_01_events-and-orders.dta ** 2013_01_motions.dta ** 2013_01_eeoc-administrative /******************************************************************************* Setting up the main dataset by merging data from the website. *******************************************************************************/ ***Merging Data from eeoclitigation.wustl.edu set logtype text set linesize 200 set more off * Use the cd command to name your working directory, where the datasets are stored *cd "working directory"; *cd "M:\Margo\Injunctive Relief\work\v3\work" *cd "L:\Users\mschlan\Data\EEOC\" cd "C:\Users\kim\Desktop\Injunctive Relief Paper Final\" use "2013_01_master.dta", clear merge 1:1 caseCode using "2013_01_injunctive-relief.dta", generate (_merge) drop if _merge==2 drop _merge saveold "master_injunctive.dta", replace *** Convert all 2s to 0s, all 3s to .s foreach var of varlist hiringIssue-otherIssue { replace `var' = 0 if `var' == 2 replace `var' = . if `var' == 3 } foreach var of varlist injReliefOnly consCase eeocComplAvailable-fullDocInjAvailable appealNotice eeocIntervened-plaintiffPubIntCounsel ADABasis-allegedRaceDiscrim allegedColorDiscrim allegedNatOrigDiscrim allegedReligDiscrim allegedSexDiscrim allegedPregDiscrim-allegedDisabDiscrim allegedAssocDiscrim retaliationComplaint-otherIssue payRelief-otherFedPCause stateDiscPCause-reliefObtained monetaryRelief injunctiveRelief { replace `var' = 0 if `var' == 2 replace `var' = . if `var' == 3 } foreach var of varlist ICInj-otherICInj defProhDiscrim-defProhOther defReqDiscPolicy-defReqOtherHiring defReqPostRights-defTrainingSpecified defReqDRP-defResolutionSpecified defReqElse termTime termSub goals-quantOutcomeGoals quantOutcomeRep quantOutcomeOther measures consequence-ltdExtSanction otherSanction reportsRequired-reportsReqOther complReptMgr-complianceReqOther complianceReptMgr-recordRequired auditingRequired accessRequired accessReqMgr-otherRolePrvt { replace `var' = 0 if `var' == 2 replace `var' = . if `var' == 3 } merge 1:1 caseCode using "2013_01_eeoc-administrative.dta" drop if _merge == 2 drop _merge gen basis_race = 0 *Basis=race includes color and national origin discrimination replace basis_race = 1 if allegedRaceDiscrim == 1 | allegedColorDiscrim == 1 | allegedNatOrigDiscrim == 1 *if allegedRaceDiscrim is missing, so is allegedColor and allegedNatOrig replace basis_race = . if allegedRaceDiscrim == . *no changes result from the following line: replace basis_race = 1 if discrimProtectedCat == 2 | discrimProtectedCat == 4 | discrimProtectedCat == 6 *identify which cases have missing values for these variables and import basis from EEOC data replace basis_race = 1 if EEbasisRaceEth == 1 & basis_race == . gen basis_sex = 0 replace basis_sex = 1 if allegedSexDiscrim == 1 | allegedPregDiscrim == 1 * missingness is same for allegedSex and allegedPreg replace basis_sex = . if allegedSexDiscrim == . *no changes result from the following line: replace basis_sex = 1 if discrimProtectedCat == 5 | discrimProtectedCat == 8 replace basis_sex = 1 if EEbasisGender == 1 & basis_sex == . gen basis_religion = 0 replace basis_religion = 1 if allegedReligDiscrim == 1 replace basis_religion = . if allegedReligDiscrim == . *no changes result from the following line: replace basis_religion = 1 if discrimProtectedCat == 7 *identify which cases have missing values for these variables and import basis from EEOC data replace basis_religion = 1 if EEbasisRelig == 1 & basis_religion == . gen basis_disability = 0 replace basis_disability = 1 if allegedDisabDiscrim == 1 replace basis_disability = . if allegedDisabDiscrim == . replace basis_disability = 1 if discrimProtectedCat == 3 *identify which cases have missing values for these variables and import basis from EEOC data replace basis_disability = 1 if EEbasisDisab == 1 & basis_disability == . gen basis_age = 0 replace basis_age = 1 if allegedAgeDiscrim == 1 replace basis_age = . if allegedAgeDiscrim == . replace basis_age = 1 if discrimProtectedCat == 1 *identify which cases have missing values for these variables and import basis from EEOC data replace basis_age = 1 if EEbasisAge == 1 & basis_age == . gen basis_retal = 0 replace basis_retal = 1 if retaliationComplaint == 1 | retaliationComplaint3p == 1 | retaliationCooperating == 1 *no changes result from the following line: replace basis_retal = . if retaliationComplaint == 3 *identify which cases have missing values for these variables and import basis from EEOC data *no changes result from the following line: replace basis_retal = 1 if EEbasisRetal == 1 & basis_retal == . * create variables to identify systemic cases and 7 criteria for inclusion gen systemic=0 gen sysPP=0 gen sysDI=0 gen sysGoals=0 gen sysBenPersEEOC=0 gen sysNumCompl=0 gen sysNumRecMon=0 gen sysDamages=0 * if complaint alleged a pattern and practice of discrim replace sysPP=1 if practiceAlleged==1 * if complaint alleged disparate impact replace sysDI=1 if impactAlleged==1 * if relief included affirmative action replace sysGoals=1 if goals==1 * if the number of benefited persons was 20 or more replace sysBenPersEEOC=1 if benefitPersonsEEOC>=20 & benefitPersonsEEOC~=. * if the number of complainants identified in the complaint was 20 or more replace sysNumCompl=1 if numCompl>=20 & numCompl~=. * if the number of complainants receiving monetary relief was 20 or more replace sysNumRecMon=1 if numRecMon>=20 & numRecMon~=. * create variable for total damages using either amtDefPays or EEOC relief variable gen damages_sum = amtDefPays gen source_damages_sum = "amtDefPays" if amtDefPays ~= . replace source_damages_sum = "amtReliefEEOC" if damages_sum == . & amtReliefEEOC ~= . & amtReliefEEOC ~= 0 replace damages_sum = amtReliefEEOC if damages_sum == . & amtReliefEEOC ~= 0 * adjust for inflation using CPI / new variable damages_adj has relief in 2008 dollars gen cpi = . replace cpi = 0.756874096 if year(finalResDate) == 1996 replace cpi = 0.774240232 if year(finalResDate) == 1997 replace cpi = 0.786300048 if year(finalResDate) == 1998 replace cpi = 0.803666184 if year(finalResDate) == 1999 replace cpi = 0.830680174 if year(finalResDate) == 2000 replace cpi = 0.854317414 if year(finalResDate) == 2001 replace cpi = 0.867824409 if year(finalResDate) == 2002 replace cpi = 0.887602508 if year(finalResDate) == 2003 replace cpi = 0.911239749 if year(finalResDate) == 2004 replace cpi = 0.94211288 if year(finalResDate) == 2005 replace cpi = 0.972503618 if year(finalResDate) == 2006 replace cpi = 1 if year(finalResDate) == 2007 replace cpi = 1 if year(finalResDate) == 2008 gen damages_adj = . replace damages_adj = damages_sum/cpi * > $1 million as cut off] replace sysDamages=1 if damages_adj>=1000000 & damages_adj~=. * identify case as systemic if any one criteria met replace systemic=1 if sysPP==1 | sysDI==1 | sysGoals==1 replace systemic=1 if sysBenPersEEOC==1 | sysNumCompl==1 | sysNumRecMon==1 replace systemic=1 if sysDamages==1 * see extent to which criteria overlap gen criteria=0 replace criteria=sysP + sysDI + sysG + sysB + sysNumC + sysNumR + sysDam tab criteria ***Syntax for creating variables used in Tables:*** *setup for table 1: gen fyFiling = year(dateFiled) replace fyFiling = year(dateFiled)+1 if month(dateFiled) == 10 replace fyFiling = year(dateFiled)+1 if month(dateFiled) == 11 replace fyFiling = year(dateFiled)+1 if month(dateFiled) == 12 *Setup for table 4: *Value Labels for finalResType: *consent judgment 1 *voluntary dismissal-settlement 2 *voluntary dismissal-non-settlement 3 *involuntary dismissal-failure to prosec 4 *involuntary dismissal-failure to state 5 *involuntary dismissal-other 7 *default judgment 8 *judgment on pleadings 9 *plaintiffs summary judgment 10 *defendants summary judgment 11 *defendants judgment as a matter of law 13 *plaintiffs jury verdict 14 *defendants jury verdict 15 *plaintiffs bench verdict 16 *none as of 04/22/08 18 gen resSum = . label define resSum 1 "settlement" 2 "withdrawal by EEOC" 3 "default judgment" 4 "litigated for D" 5 "litigated for EEOC" label values resSum resSum replace resSum = 1 if finalResType == 1 | finalResType == 2 replace resSum = 2 if finalResType == 3 | finalResType == 4 replace resSum = 3 if finalResType == 8 replace resSum = 4 if finalResType == 5 | finalResType == 7 | finalResType == 11 | finalResType == 13 | finalResType == 15 replace resSum = 5 if finalResType == 10 | finalResType == 14 | finalResType == 16 *** What about finalResType == 9? (Judgment on Pleadings) Need to do this by hand, because it doesn't say for whom. *** EE-OH-0039 -- first judgment was for Deft *** EE-CA-0132 -- first judgment was for both (for deft on damages, for eeoc on injunction). *** Since this piece is about injunctions, we'll count that as for the EEOC. replace resSum = 4 if caseCode == "EE-OH-0039" replace resSum = 5 if caseCode == "EE-CA-0132" *setup for table 6: gen days = finalResDate - dateFiled *setup for table 8: *** Getting means. *** Detect all use of consultants, special masters, monitors gen outsider = 0 replace outsider = 1 if complReptPeer == 1 replace outsider = 1 if complReptUnion == 1 replace outsider = 1 if complReptAdvoc == 1 replace outsider = 1 if complReptConsult == 1 replace outsider = 1 if complReptMonitor == 1 replace outsider = 1 if complReptPrvt == 1 replace outsider = 1 if complianceReptPeer == 1 replace outsider = 1 if complianceReptUnion == 1 replace outsider = 1 if complianceReptAdvoc == 1 replace outsider = 1 if complianceReptConsult == 1 replace outsider = 1 if complianceReptMonitor == 1 replace outsider = 1 if complianceReptPrvt == 1 replace outsider = 1 if accessReqPeer == 1 replace outsider = 1 if accessReqUnion == 1 replace outsider = 1 if accessReqAdvoc == 1 replace outsider = 1 if accessReqConsult == 1 replace outsider = 1 if accessReqMonitor == 1 replace outsider = 1 if accessReqPrvt == 1 replace outsider = 1 if otherRolePeer == 1 replace outsider = 1 if otherRoleUnion == 1 replace outsider = 1 if otherRoleAdvoc == 1 replace outsider = 1 if otherRoleConsult == 1 replace outsider = 1 if otherRoleMonitor == 1 replace outsider = 1 if otherRolePrvt == 1 saveold "master_injunctive.dta", replace ******************************************************************************* * Setting up the events counts. ******************************************************************************* use "2013_01_events-and-orders.dta", clear *merge in needed variables from master brick merge m:1 caseCode using "master_injunctive.dta", keepusing(finalResType finalResDate systemic sepPrivResolution sepPrivResDate appealNotice appealDate) *retain unmatched observations from master brick *identify which cases were resolved by party settlement gen settledCase=2 replace settledCase=1 if finalResType==1 | finalResType==2 count if system==1 & settledCase==1 *count number of eventType 4 (i.e. scheduling conf) occurring in each case *if want to count only for settled cases, then use *"duplicates tag caseCode eventType if finalResType==1|finalResType==2, gen(eventType4Ct)" duplicates tag caseCode eventType, gen(eventType4Ct) replace eventType4Ct=. if eventType!=4 generate eventType4Count=eventType4Ct replace eventType4Count=eventType4Count+1 replace eventType4Count=0 if eventType4Count==. gsort caseCode -eventType4Count replace eventType4Count=eventType4Count[_n-1] if caseCode==caseCode[_n-1] & eventType4Count0 & systemic==1 /******************************************************************************* Setting up the motions counts. *******************************************************************************/ use "2013_01_motions.dta", clear merge m:1 caseCode using "master_injunctive.dta", keepusing(finalResType finalResDate systemic ) *Leaving in cases from Master Brick that are not otherwise in the Motions Brick *identify settled cases gen settledCase=2 replace settledCase=1 if finalResType==1 | finalResType==2 *no missing values for motionType gen dispMotFiled=2 replace dispMotFiled=1 if motionType==1|motionType==10|motionType==14|motionType==16 *18 cases in which motionOutcome is missing, OK to treat these as not resolved gen dispMotResolved=2 replace dispMotResolved=1 if motionOutcome==1 & dispMotFiled==1 replace dispMotResolved=1 if motionOutcome==2 & dispMotFiled==1 replace dispMotResolved=1 if motionOutcome==3 & dispMotFiled==1 *no missing values for motionType gen discovMotFiled=2 replace discovMotFiled=1 if motionType==13 *18 cases in which motionOutcome is missing, OK to treat these as not resolved gen discovMotResolved=2 replace discovMotResolved=1 if discovMotFiled==1 & motionOutcome==1 replace discovMotResolved=1 if discovMotFiled==1 & motionOutcome==2 replace discovMotResolved=1 if discovMotFiled==1 & motionOutcome==3 duplicates tag caseCode dispMotFiled if dispMotFiled==1, gen(dispMotFiledCt) generate dispMotFiledCount=dispMotFiledCt replace dispMotFiledCount=dispMotFiledCount+1 replace dispMotFiledCount=0 if dispMotFiledCount==. gsort caseCode -dispMotFiledCount replace dispMotFiledCount=dispMotFiledCount[_n-1] if caseCode==caseCode[_n-1] & dispMotFiledCount 9; tab fyFiling; ** That generates the numbers below. Note: would be better to automate this; use "master_injunctive.dta" if systemic == 1, clear; /** totals **/ use "master_injunctive.dta" if systemic == 1, clear; collapse (sum)systemic (mean) sysPP sysDI sysBenPersEEOC sysNumCompl sysNumRecMon sysDamages sysGoals; save "temp_1.dta", replace; use "master_injunctive.dta" if systemic == 1, clear; collapse (sum) systemic (mean) sysPP sysDI sysBenPersEEOC sysNumCompl sysNumRecMon sysDamages sysGoals, by(fyFiling); append using "temp_1.dta"; *** These numbers are from the EEOC administrative data, above; gen EEOC = .; replace EEOC = 298 if fyFiling == 1997; replace EEOC = 373 if fyFiling == 1998; replace EEOC = 437 if fyFiling == 1999; replace EEOC = 288 if fyFiling == 2000; replace EEOC = 382 if fyFiling == 2001; replace EEOC = 327 if fyFiling == 2002; replace EEOC = 363 if fyFiling == 2003; replace EEOC = 375 if fyFiling == 2004; replace EEOC = 380 if fyFiling == 2005; replace EEOC = 373 if fyFiling == 2006; replace EEOC = 3596 if fyFiling == .; order fyFiling EEOC; label var fyFiling "Filing Year"; label var EEOC "EEOC cases filed"; label var systemic "Sample"; label var sysPP "Pattern or Practice"; label var sysDI "Disparate Impact"; label var sysBenPersEEOC "EEOC benefitted parties"; label var sysNumCompl "Complainants listed in court docs"; label var sysNumRecMon "Claimants awarded damages"; label var sysDamages "EEOC $1 million damages"; label var sysGoals "Affirmative Action"; tostring fyFiling, replace; replace fyFiling = "Total" if fyFiling == "."; export excel using tables.xlsx, firstrow(varlabels) sheet("t1") sheetmodify; rm temp_1.dta; /******************************************************************************* Table 2 *******************************************************************************/ use "master_injunctive.dta" if systemic == 1 & numRecMon ~=0 & numRecMon ~=., clear; local N = _N; preserve; collapse (mean) mean=numRecMon (median) med=numRecMon (p90) p90=numRecMon (max) max=numRecMon (sum) sum=numRecMon, by(fyFiling); save "temp_1.dta", replace; restore; preserve; collapse (mean) mean=numRecMon (median) med=numRecMon (p90) p90=numRecMon (max) max=numRecMon (sum) sum=numRecMon; save "temp_2.dta", replace; restore; contract fyFiling; save "temp_3.dta", replace; use "temp_1.dta", clear; append using "temp_2.dta"; *** mmerge is a special install: type "ssc install mmerge"; mmerge fyFiling using "temp_3.dta"; /* Changing layout/ look of table: */ replace _freq = `N' if _freq == .; tostring fyFiling, replace; replace fyFiling = "All Years" if fyFiling =="."; label var fyFiling "Filling Year"; label var _freq "N"; label var mean "Mean"; label var med "Median"; label var p90 "90 %ile"; label var max "Max"; label var sum "Total"; order fyFiling _freq; *rounding off decimal places; foreach var of varlist _freq-sum {; replace `var' = round(`var',1); }; drop _merge; export excel using tables.xlsx, firstrow(varlabels) sheet("t2") sheetmodify; rm temp_1.dta; rm temp_2.dta; rm temp_3.dta; /******************************************************************************* Table 3 1) if systemic == 1 & damages_adj ~= . 2) if systemic == 1 & damages_adj ~= . & source_damages_sum ~= "amtReliefEEOC" *******************************************************************************/ /******************************************************************************* Version 1: if systemic == 1 & damages_adj ~= . *******************************************************************************/ use "master_injunctive.dta" if systemic == 1 & damages_adj ~= . , clear; *if we wanted nominal dollars instead, replace damages_adj with amtDefPays; preserve; collapse (mean) mean=damages_adj (median) med=damages_adj (p90) p90=damages_adj (max) max=damages_adj (sum) sum=damages_adj, by(fyFiling); save "temp_1.dta", replace; restore; preserve; collapse (mean) mean=damages_adj (median) med=damages_adj (p90) p90=damages_adj (max) max=damages_adj (sum) sum=damages_adj; save "temp_2.dta", replace; restore; use "master_injunctive.dta" if systemic == 1 & damages_adj ~= ., clear; local N = _N; contract fyFiling; save "temp_3.dta", replace; use "temp_1.dta", clear; append using "temp_2.dta"; mmerge fyFiling using "temp_3.dta"; /* Changing layout/ look of table: */ replace _freq = `N' if _freq == .; tostring fyFiling, replace; replace fyFiling = "All Years" if fyFiling =="."; label var fyFiling "Filling Year"; label var _freq "N"; label var mean "Mean"; label var med "Median"; label var p90 "90 %ile"; label var max "Max"; label var sum "Total"; order fyFiling _freq; *converting to $(thousands); foreach var of varlist mean-sum {; replace `var' = `var'/1000; }; *round off decimal places; foreach var of varlist _freq-sum {; replace `var' = round(`var',1); }; drop _merge; export excel using tables.xlsx, firstrow(varlabels) sheet("t3v1") sheetmodify; rm temp_1.dta; rm temp_2.dta; rm temp_3.dta; /******************************************************************************* Version 2: Without EEOC DATA if systemic == 1 & & damages_adj ~= . & source_damages_sum ~= "amtReliefEEOC" ******************************************************************************* use "master_injunctive.dta" if systemic == 1 & benefitPersonsEEOC ~=0 & damages_adj ~= . & source_damages_sum ~= "amtReliefEEOC", clear; *for nominal dollars, replace damages_adj with amtDefPays; preserve; collapse (mean) mean=damages_adj (median) med=damages_adj (p90) p90=damages_adj (max) max=damages_adj (sum) sum=damages_adj, by(fyFiling); save "temp_1.dta", replace; restore; preserve; collapse (mean) mean=damages_adj (median) med=damages_adj (p90) p90=damages_adj (max) max=damages_adj (sum) sum=damages_adj; save "temp_2.dta", replace; restore; use "master_injunctive.dta" if systemic == 1 & benefitPersonsEEOC ~=0 & damages_adj ~= . & source_damages_sum ~= "amtReliefEEOC", clear; local N = _N; contract fyFiling; save "temp_3.dta", replace; use "temp_1.dta", clear; append using "temp_2.dta"; mmerge fyFiling using "temp_3.dta"; /* Changing layout/ look of table: */ replace _freq = `N' if _freq == .; tostring fyFiling, replace; replace fyFiling = "All Years" if fyFiling =="."; label var fyFiling "Filling Year"; label var _freq "N"; label var mean "Mean"; label var med "Median"; label var p90 "90 %ile"; label var max "Max"; label var sum "Total"; order fyFiling _freq; *converting to $(thousands); foreach var of varlist mean-sum {; replace `var' = `var'/1000; }; *round off decimal places; foreach var of varlist _freq-sum {; replace `var' = round(`var',1); }; drop _merge; export excel using tables.xlsx, firstrow(varlabels) sheet("t3v2") sheetmodify; rm temp_1.dta; rm temp_2.dta; rm temp_3.dta; */ /******************************************************************************* Table 4 *******************************************************************************/ use "master_injunctive.dta" if systemic == 1 & finalResType ~=18, clear; contract resSum, freq(N) percent(percent); *dropping cases litigated for Def; *drop if resSum == 3; *drop if resSum ==4; replace percent = percent/100; label var percent "%"; export excel using tables.xlsx, firstrow(varlabels) sheet("t4") sheetmodify; /******************************************************************************* Table 5 *******************************************************************************/ /* rows 1-4 */ use "motionsCountsUnique.dta" if systemic == 1 & settledCase ==1, clear; /*original syntax can be found in motions_setup.do*/ foreach var in discovMotFiledCount discovMotResolvedCount dispMotFiledCount dispMotResolvedCount {; preserve; collapse (mean) mean=`var' (median) med=`var' (p75) p75=`var' (p90) p90=`var' (max)max=`var'; save temp_`var'.dta, replace; restore; }; /* row 5 */ use "eventTypesUnique.dta" if systemic == 1 & settledCase ==1, clear; collapse (mean) mean=eventType4Count (median) med=eventType4Count (p75) p75=eventType4Count (p90) p90=eventType4Count (max)max=eventType4Count; save temp_eventType4Count.dta, replace; use "temp_discovMotFiledCount.dta", clear; foreach var in discovMotResolvedCount dispMotFiledCount dispMotResolvedCount eventType4Count {; append using "temp_`var'.dta"; }; label var mean "Mean"; label var med "Median"; label var p75 "75 %ile"; label var p90 "90 %ile"; label var max "Max"; gen c1 = ""; replace c1 = "1. Discovery Motions Filed" if _n == 1; replace c1 = "2. Discovery Motions Resolved" if _n == 2; replace c1 = "3. Substantive Motions Filed" if _n == 3; replace c1 = "4. Substantive Motions Resolved" if _n == 4; replace c1 = "5. Scheduling/Status Conference Held" if _n == 5; order c1; format mean %3.2f; export excel using tables.xlsx, firstrow(varlabels) sheet("t5") sheetmodify; foreach var in discovMotFiledCount discovMotResolvedCount dispMotFiledCount dispMotResolvedCount eventType4Count {; rm temp_`var'.dta; }; /******************************************************************************* Table 6 *******************************************************************************/ /* Table 6 row 1 is systemic cases with a known resolution */ use "master_injunctive.dta" if systemic == 1 & finalResType ~= 18, clear; local N = _N; collapse (mean) mean=days (p25) p25=days (median) med=days (p75) p75=days (p90) p90=days (max) max=days; gen N = `N'; gen c1 = "1. Days to first resolution"; save "temp_1.dta", replace; /* Table 6 row 2 is systemic cases with non-default injunctive decrees for which we have documentation */ use "master_injunctive.dta" if systemic ==1 & fullDocInjAvailable ==1 & injunctiveRelief ==1 & (resSum ==1 | resSum ==5), clear; local N = _N; collapse (mean) mean=numberPages (p25) p25=numberPages (median) med=numberPages (p75) p75=numberPages (p90) p90=numberPages (max) max=numberPages; gen N = `N'; gen c1 = "2. Decree pages"; save "temp_2.dta", replace; /* Table 6 row 3 is systemic cases is the same as row 2, where the decrees have a set term in months */ use "master_injunctive.dta" if systemic ==1 & fullDocInjAvailable ==1 & injunctiveRelief ==1 & (resSum ==1 | resSum ==5) & termTimeMonths ~=., clear; local N = _N; collapse (mean) mean=termTimeMonths (p25) p25=termTimeMonths (median) med=termTimeMonths (p75) p75=termTimeMonths (p90) p90=termTimeMonths (max) max=termTimeMonths; gen N = `N'; gen c1 = "3. Length of decree (months)"; save "temp_3.dta", replace; use temp_1.dta, clear; append using temp_2.dta; append using temp_3.dta; order c1 N; label var mean "Mean"; label var p25 "25 %ile"; label var med "Median"; label var p75 "75 %ile"; label var p90 "90 %ile"; label var max "Max"; foreach var in mean med {; replace `var' = round(`var',1); }; export excel using tables.xlsx, firstrow(varlabels) sheet("t6") sheetmodify; rm temp_1.dta; rm temp_2.dta; rm temp_3.dta; /******************************************************************************* Table 7 *******************************************************************************/ use "master_injunctive.dta" if systemic ==1 & fullDocInjAvailable ==1 & injunctiveRelief ==1 & (resSum ==1 | resSum ==5), clear; foreach var in defProhDiscrim defProhRetaliate defProhOther defReqEEOTrain defReqPostRights defReqDistRights defReqDiscPolicy defReqDRP defReqRecruiting defReqHiringCrit defReqProtocol goals defReqJobDesc {; preserve; contract `var', freq(N) percent(percent); drop if `var' != 1; gen c1 = "`var'"; drop `var'; save "temp_`var'.dta", replace; restore; }; use "temp_defProhDiscrim.dta", clear; foreach var in defProhRetaliate defProhOther defReqEEOTrain defReqPostRights defReqDistRights defReqDiscPolicy defReqDRP defReqRecruiting defReqHiringCrit defReqProtocol goals defReqJobDesc {; append using "temp_`var'.dta"; }; order c1 N percent; replace percent = round(percent,1); replace percent = percent/100; label var c1 "Type of remedy"; label var percent "%"; export excel using tables.xlsx, firstrow(varlabels) sheet("t7") sheetmodify; foreach var in defProhDiscrim defProhRetaliate defProhOther defReqEEOTrain defReqPostRights defReqDistRights defReqDiscPolicy defReqDRP defReqRecruiting defReqHiringCrit defReqProtocol goals defReqJobDesc {; rm temp_`var'.dta; }; /******************************************************************************* Table 8 *******************************************************************************/ use "master_injunctive.dta" if systemic ==1 & fullDocInjAvailable ==1 & injunctiveRelief ==1 & (resSum ==1 | resSum ==5), clear; foreach var in complianceRepReq recordRequired reportsRequired auditingRequired goals quantOutcomeRep quantOutcomeGoals {; preserve; contract `var', freq(N) percent(percent); drop if `var' != 1; gen c1 = "`var'"; drop `var'; save "temp_`var'.dta", replace; restore; }; use "temp_complianceRepReq.dta", clear; foreach var in recordRequired reportsRequired auditingRequired goals quantOutcomeRep quantOutcomeGoals {; append using "temp_`var'.dta"; }; order c1 N percent; replace percent = round(percent,1); replace percent = percent/100; label var c1 "Type of remedy"; label var percent "%"; export excel using tables.xlsx, firstrow(varlabels) sheet("t8") sheetmodify; foreach var in complianceRepReq recordRequired reportsRequired auditingRequired goals quantOutcomeRep quantOutcomeGoals {; rm temp_`var'.dta; }; /******************************************************************************* Table 9 *******************************************************************************/ use "master_injunctive.dta" if systemic ==1 & fullDocInjAvailable ==1 & injunctiveRelief ==1 & (resSum ==1 | resSum ==5), clear; foreach var in termSub goals measures {; preserve; contract `var', freq(N) percent(percent); drop if `var' != 1; gen c1 = "`var'"; drop `var'; save "temp_`var'.dta", replace; restore; }; use "temp_termSub.dta", clear; foreach var in goals measures {; append using "temp_`var'.dta"; }; order c1 N percent; replace percent = round(percent,1); replace percent = percent/100; label var c1 "Type of remedy"; label var percent "%"; export excel using tables.xlsx, firstrow(varlabels) sheet("t9") sheetmodify; foreach var in termSub goals measures {; rm temp_`var'.dta; }; /******************************************************************************* Table 10 *******************************************************************************/ use "master_injunctive.dta" if systemic ==1 & fullDocInjAvailable ==1 & injunctiveRelief ==1 & (resSum ==1 | resSum ==5), clear; preserve; /* creating row 1 */ local columns complRept complianceRept accessReq otherRole; foreach pre of local columns {; gen `pre' = 1 if `pre'Mgr == 1 | `pre'Peer == 1 | `pre'Union == 1 |`pre'Advoc == 1 | `pre'Consul == 1 | `pre'Monitor == 1 | `pre'EEOC == 1 | `pre'Prvt == 1; }; count if complRept == 1 | complianceRept == 1 | accessReq == 1 | otherRole == 1; local any_r1 = `r(N)'; collapse (sum) c2=complRept c3=complianceRept c4=accessReq c5=otherRole; gen c1 = `any_r1'; gen c6 = "Any Stakeholder"; save "row1.dta", replace; restore; local types Mgr Peer Union EEOC Prvt Consul Monitor Advoc; foreach type of local types {; preserve; count if complRept`type' ==1 | complianceRept`type' == 1 | accessReq`type' == 1| otherRole`type' == 1; local any = `r(N)'; collapse (sum) c2=complRept`type' c3=complianceRept`type' c4=accessReq`type' c5=otherRole`type'; gen c1 = `any'; gen c6 = "`type'"; save "temp_`type'.dta", replace; restore; }; use "row1.dta", clear; local types Mgr Peer Union EEOC Prvt Consul Monitor Advoc; foreach type of local types {; append using "temp_`type'.dta", force; }; labe var c6 ""; labe var c1 "Any Role"; labe var c2 "Complaint / Incident Reports"; labe var c3 "Compliance Reports"; labe var c4 "Monitoring"; labe var c5 "Other"; order c6 c1 c2 c3 c4 c5; export excel using tables.xlsx, firstrow(varlabels) sheet("t10") sheetmodify; rm "row1.dta"; local types Mgr Peer Union EEOC Prvt Consul Monitor Advoc; foreach type of local types {; rm "temp_`type'.dta"; }; /******************************************************************************* FIGURES A & B USING ALL SYSTEMIC CASES *******************************************************************************/ /** FIGURE A: Basis of Suit **/ use "master_injunctive.dta" if systemic == 1, clear; collapse (mean) basis_sex basis_race basis_retal basis_age basis_disability basis_religion; xpose, v clear; rename v1 mean; rename _varname basis; replace basis = "Sex/Pregnancy" if basis == "basis_sex"; replace basis = "Race*" if basis == "basis_race"; replace basis = "Retaliation" if basis == "basis_retal"; replace basis = "Age" if basis == "basis_age"; replace basis = "Disability" if basis == "basis_disability"; replace basis = "Religion" if basis == "basis_religion"; /* Remove Title title({bf:Figure A: Basis of Suit: EEOC Systemic Cases Filed 1997-2006}, margin(medium) color(black)) */ graph bar mean, over(basis, sort(mean) des) bargap(50) bar(1, color(black)) ylabel(#10, angle(horizontal) glcolor(gs12)) ytitle("") graphregion(color(white) lwidth(large)) name(figA, replace); graph export "Figure A.png", replace; /* Remove Title title({bf:Figure A: Basis of Suit: EEOC Systemic Cases Filed 1997-2006}, margin(medium) color(black)) */ graph bar mean, over(basis, sort(mean) des) bargap(50) bar(1, color(black)) ylabel(#10, angle(horizontal) glcolor(gs12)) ytitle("") graphregion(color(white) lwidth(large)) blabel(bar, format(%3.2f)) name(figA_labels, replace); graph export "Figure A with labels.png", replace; /** FIGURE B: Issues **/ use "master_injunctive.dta" if systemic ==1, clear; collapse (mean) dischargeIssue harassmentIssue hiringIssue empCondIssue payIssue promotionIssue disciplineIssue otherIssue demotionIssue medExamIssue trainingIssue leaveAccomIssue testingIssue; xpose, v clear; rename v1 mean; rename _varname issue; replace issue = "Discharge" if issue == "dischargeIssue"; replace issue = "Harassment" if issue == "harassmentIssue"; replace issue = "Hiring" if issue == "hiringIssue"; replace issue = "Conditions" if issue == "empCondIssue"; replace issue = "Pay" if issue == "payIssue"; replace issue = "Promotion" if issue == "promotionIssue"; replace issue = "Discipline" if issue == "disciplineIssue"; replace issue = "Other" if issue == "otherIssue"; replace issue = "Demotion" if issue == "demotionIssue"; replace issue = "Medical Exam" if issue == "medExamIssue"; replace issue = "Training" if issue == "trainingIssue"; replace issue = "Leave/Accomodation" if issue == "leaveAccomIssue"; replace issue = "Testing" if issue == "testingIssue"; /* Remove Title title("{bf:Figure B: Issues: EEOC Systemic Cases Filed 1997-2006}", margin(medium) color(black)) */ graph bar mean, over(issue, sort(mean) des label(angle(45))) bargap(50) bar(1, color(black)) ylabel(0(.1).5, angle(horizontal) glcolor(gs12)) ytitle("") graphregion(color(white) lwidth(large)) name(figB, replace); graph export "Figure B.png", replace; /* Remove Title title("{bf:Figure B: Issues: EEOC Systemic Cases Filed 1997-2006}", margin(medium) color(black)) */ graph bar mean, over(issue, sort(mean) des label(angle(45))) bargap(50) bar(1, color(black)) ylabel(0(.1).5, angle(horizontal) glcolor(gs12)) ytitle("") graphregion(color(white) lwidth(large)) blabel(bar, format(%3.2f)) name(figB_labels, replace); graph export "Figure B with labels.png", replace; *window manage close graph _all;