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
** eliminate people who do not have or report invalid wage income
drop if incwage==0 | incwage==999999| incwage==999998

** eliminate self-employed
keep if classwkr>=20 & classwkr<=28
***accept IPUMS topcode

*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 yearly weekly hourly
sort year edu exp sex

*** average wages (hours weighted) and total hours

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

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

** average across experience-sex groups using fixed hours weights
collapse (mean) weekly hourly (rawsum) tot_hours=hours obs=size [aw=hour_ave], by(year edu)
keep edu year weekly hourly 

save wages_63_07, replace