cd H:\Giovanni\MyPapers\Peri_Ottaviano_aggregate\REDUX_2008\empirics\CPS_regressions
capture log close
clear


*** produce average wage, hours worked and employment by sex-education-experience-year cells

set memory 2g
set matsize 400
use "H:\ipums_data\CPS\cps62_07"

* eliminate people in group quarters 
drop if gq ==0 | gq==3 |gq==4  
*select people in working age
drop if age<18
*select people who worked a positive amount of weeks and hours
drop if wkswork2==0 &year<=1975 
drop if wkswork1==0 &year>1975

drop if hrswork==0 & year<=1975
drop if uhrswork==0 & year>=1976

drop if perwt<=0
*** in this sample we do not eliminate self-employed 

*define edu and exp codes
generate byte edu=1*(educrec<=6) + 2*(educrec==7) + 3*(educrec==8) + 4*(educrec==9)

generate experience=age-17 if edu==1
replace experience=age-19 if edu==2
replace experience=age-21 if edu==3
replace experience=age-23 if edu==4
drop if experience<1 | experience>40

generate exp=1*(experience>=1 & experience<=5) + 2*(experience>=6 & experience<=10) + 3*(experience>=11 & experience<=15) + 4*(experience>=16 & experience<=20)+5*(experience>=21 & experience<=25) + 6*(experience>=26 & experience<=30) + 7*(experience>=31 & experience<=35) + 8*(experience>=36 & experience<=40)

** define hours and weeks worked ***
generate hours=hrswork
	replace hours=uhrswork if year>=1976

generate weeks=6.5*(wkswork2==1) + 20*(wkswork2==2) + 33*(wkswork2==3) + 43.5*(wkswork2==4) + 48.5*(wkswork2==5) + 51*(wkswork2==6) 
	replace weeks=wkswork1 if year>=1976

*** define wages
gen yearly=incwage
gen weekly=yearly/weeks
gen hourly=weekly/hours
gen howo=perwt*hours*weeks

keep  edu exp sex year howo perwt yearly weekly hourly
sort year edu exp sex

*** total hours and employment and average wages

collapse (mean) weekly (rawsum) hours=howo employment=perwt (count) size=howo [aw=howo], by(year edu exp sex)
keep edu exp sex year weekly hours employment size 

** use average hours worked by the group 1962-2007 as constant weight to calculate the wages
egen wage_ave=mean(weekly), by(edu exp sex)


** sum across experience-sex groups using average relative wages as weights for the labor supply

collapse (sum) hours employment (rawsum) hours_nonw=hours employment_nonw=employment obs=size [aw=wage_ave], by(year edu)
keep edu year hours employment hours_nonw employment_nonw obs

save hours_63_07, replace