**************
*HOUSEKEEPING*
**************
clear all
clear matrix
set more off
set mem 1000m

capture log close

log using rais2003.log, replace

*********************************************
*% OF PLANTS IN EACH SIZE BIN FOR 2003*
*********************************************

local yy=2003

use rais_plants_size`yy'.dta, clear
append using aux_cnae.dta

gen ind3d=substr(grupcnae95, 7, 8)
drop if ind3d=="DO" | ind3d=="FORM"
gen ind2d=substr(ind3d, 1, 2)
destring ind2d, replace force
destring ind3d, replace force
replace year=`yy' if year==.

*AGGREGATING ACROSS TYPES OF INDUSTRIES

foreach x in zero ate4 de5a9 de10a19 de20a49 de50a99 de100a249 de250a499 de500a999 oumais ignorado total {  

egen `x'_ag=sum(`x') if ind2<=5 
egen `x'_ag2=max(`x'_ag)
replace `x'= `x'_ag2 if grupcnae95=="agric_fish"
drop `x'_ag `x'_ag2

egen `x'_ext=sum(`x') if (ind2>=10 & ind2<=14)  
egen `x'_ext2=max(`x'_ext)
replace `x'= `x'_ext2 if grupcnae95=="extractive"
drop `x'_ext `x'_ext2

egen `x'_man=sum(`x') if (ind2>=15 & ind2<=37) 
egen `x'_man2=max(`x'_man)
replace `x'= `x'_man2 if grupcnae95=="manufacturing"
drop `x'_man `x'_man2

egen `x'_serv=sum(`x') if ind2>37 
egen `x'_serv2=max(`x'_serv)
replace `x'= `x'_serv2 if grupcnae95=="services_all"
drop `x'_serv `x'_serv2

egen `x'_servpa=sum(`x') if (ind2>37 & ind2<=74) | (ind2>80) 
egen `x'_servpa2=max(`x'_servpa)
replace `x'= `x'_servpa2 if grupcnae95=="services_exceptpadmin"
drop `x'_servpa `x'_servpa2

}

gen share_plants_0=zero/total
gen share_plants_upto4=ate4/total
gen share_plants_5to9=de5a9/total
gen share_plants_10to19=de10a19/total
gen share_plants_20to49=de20a49/total
gen share_plants_50to99=de50a99/total
gen share_plants_100to249=de100a249/total
gen share_plants_250to499=de250a499/total
gen share_plants_500to999=de500a999/total
gen share_plants_over999=oumais/total


*NOTE1: The line "Total" under the variable "grupcnae95" has now the share of plants in each size bin 
*NOTE2: The other lines contain the share of plants in each size bin for each 3-digit industry

so grupcnae95
save perc_plants_sizebin.dta, replace

********************************
*AVERAGE FIRM SIZE PER SIZE BIN*
********************************

use rais_empl_size`yy'.dta, clear

rename zero zeroempl
rename ate4 ate4empl 
rename de5a9 de5a9empl
rename de10a19 de10a19empl
rename de20a49 de20a49empl
rename de50a99 de50a99empl
rename de100a249 de100a249empl 
rename de250a499 de250a499empl
rename de500a999 de500a999empl 
rename oumais oumaisempl
rename ignorado ignoradoempl
rename total totalempl

so grupcnae95

merge grupcnae95 using perc_plants_sizebin.dta
drop if grupcnae95=="IGNORADO" | grupcnae95=="NAO INFORM"
tab _merge
drop _merge


foreach x in zeroempl ate4empl de5a9empl de10a19empl de20a49empl de50a99empl de100a249empl de250a499empl de500a999empl oumaisempl ignoradoempl totalempl {  

egen `x'_ag=sum(`x') if ind2<=5 
egen `x'_ag2=max(`x'_ag)
replace `x'= `x'_ag2 if grupcnae95=="agric_fish"
drop `x'_ag `x'_ag2

egen `x'_ext=sum(`x') if (ind2>=10 & ind2<=14)  
egen `x'_ext2=max(`x'_ext)
replace `x'= `x'_ext2 if grupcnae95=="extractive"
drop `x'_ext `x'_ext2

egen `x'_man=sum(`x') if (ind2>=15 & ind2<=37) 
egen `x'_man2=max(`x'_man)
replace `x'= `x'_man2 if grupcnae95=="manufacturing"
drop `x'_man `x'_man2

egen `x'_serv=sum(`x') if ind2>37 
egen `x'_serv2=max(`x'_serv)
replace `x'= `x'_serv2 if grupcnae95=="services_all"
drop `x'_serv `x'_serv2

egen `x'_servpa=sum(`x') if (ind2>37 & ind2<=74) | (ind2>80) 
egen `x'_servpa2=max(`x'_servpa)
replace `x'= `x'_servpa2 if grupcnae95=="services_exceptpadmin"
drop `x'_servpa `x'_servpa2

}

gen avsize_0 = zeroempl/zero
gen avsize_upto4 = ate4empl/ate4 
gen avsize_5to9 = de5a9empl/de5a9
gen avsize_10to19 = de10a19empl/de10a19
gen avsize_20to49 = de20a49empl/de20a49
gen avsize_50to99=de50a99empl/de50a99 
gen avsize_100to249=de100a249empl/de100a249  
gen avsize_250to499=de250a499empl/de250a499 
gen avsize_500to999=de500a999empl/de500a999  
gen avsize_over999=oumaisempl/oumais 
gen avsize=totalempl/total 

save size`yy'.dta, replace

list share* if grupcnae95=="Total"
list avsize* if grupcnae95=="Total"

log close