/* program to merge wave 1 and 2 data and create missing observations
for workers interviewed in both waves; similar for wave 3 */


libname tz 'd:\tanzrped\sasdata';
libname cd 'd:\tanzrped\createdata';


/* WAVE 2 WORKER DATA FILE
************************** */

/* creates worker id number which was omitted from wk2 file */
data temp1; set tz.wk2new;
proc sort; by key;

data temp2; set temp1; by key;
if first.key then wid=0;
wid=wid+1;
retain wid;
proc means; run;


data work2; set temp2;
options linesize=80;
options nolabel;

firm=key;
wave=2;
year=2;

ethnic=ori_2;
name=name_2;

/* excludes europeans and others from analysis */
if ethnic=2 then delete;
if ethnic=4 then delete;

intw1=int_2;

sex=sex_2;
age=age_2;

yrendsc=yeasch_2;

if edu_2=1 then edlevela=1;
if edu_2=2 then edlevela=2;
if edu_2=3 then edlevela=3;
if edu_2=4 then edlevela=4;
if edu_2=5 then edlevela=5;
if edu_2=6 then edlevelb=7;
if edu_2=7 then edlevelb=9;
if edu_2=8 then edlevelb=8;
if edu_2=9 then edlevela=10;
if edu_2=10 then edlevela=10;
if edu_2=11 then edlevela=10;
if edu_2=12 then edlevela=10;

edtype=efor_2;
edform=efnb_2;

if edlevela=1 then educ=0;
if edlevela=2 then educ=7;
if yrendsc lt 1964 and edlevela=2 then educ=4;
if edlevela=3 then educ=8;
if edlevela=4 then educ=11;
if edlevela=5 then educ=13;

if edtype=1 then years=edform;
if edtype=2 then years=edform+7;
if edtype=2 and edlevela=3 then years=edform+8;

if educ=. then educ=years;
if years lt educ and years ne . then educ=years;

if edlevelb=7 then educ=9;
if edlevelb=8 then educ=14;
if edlevelb=9 then educ=13;
if edlevelb=9 and edlevela=2 then educ=9;
if edlevelb=9 and edtype=1 then educ=9;

if edlevela=10 then educ=16;

/* imputed education levels */

yrborn=1994-age;
impeduc=yrendsc-yrborn-6;
if impeduc<educ then impeduc=educ;
if impeduc>30 then impeduc=educ;

/* tenure variable */
years=nby_2;
months=nbm_2;
if months=. then months=0;
if years=. then years=0;
pcyear=months/12;
tenure=years+pcyear;
if tenure=0 then tenure=.;

work=1993-yrendsc;

pexp=wexp_2;


/* occupational categories */

occupat=jobn_2;

if firm=421 and wid=1 then occupat=13;
if occupat=17 or occupat=18 or occupat=20 then occupat=19;

if occupat=1 or occupat=2 then ocdum='amgmt';
if occupat=3 or occupat=4 then ocdum='badmin';
if occupat=5 or occupat=6 then ocdum='badmin';
if occupat=7 then ocdum='cleric';
if occupat=9 or occupat=10 then ocdum='csales';
if occupat=12 then ocdum='dsuper';
if occupat=13 or occupat=14 then ocdum='etech';
if occupat=8 or occupat=11 or occupat=15 then ocdum='fprod';
if occupat=16 or occupat=17 or occupat=19 then ocdum='fprod';
if occupat=18 then ocdum='gappren';

if occupat=1 or occupat=2 then mgmt=1; else mgmt=0;
if occupat=3 or occupat=4 or occupat=5 or occupat=6 then admin=1;
else admin=0;
if occupat=7 then cleric=1; else cleric=0;
if occupat=9 or occupat=10 then sales=1; else sales=0;
if occupat=12 then super=1; else super=0;
if occupat=13 or occupat=14 then tech=1; else tech=0;
if occupat=8 or occupat=11 or occupat=15
or occupat=16 or occupat=17 or occupat=19 then prod=1; else prod=0;
if occupat=18 then appren=1; else appren=0;

paid=payn_2;


/* current wages - no information on whether wage is net or
gross of tax (assume gross salary) and include allowances */

tunit=salnu_2;
wagebt=saln_2;
food=alaf_2;
house=alah_2;
cloth=alac_2;
trans=alat_2;

if tunit=1 then delete;
if tunit=2 then wagebt=wagebt*22;
if tunit=3 then wagebt=wagebt*4;
if tunit=4 then wagebt=wagebt;
if tunit=5 then wagebt=wagebt/12;

/* give workers with missing wages the mean for their
occupational category */
if wagebt=. and ocdum='fprod' then wagebt=14000;
if wagebt=. and ocdum='amgmt' then wagebt=40000;
if wagebt=. and ocdum='etech' then wagebt=12500;

if food=. then food=0;
if house=. then house=0;
if cloth=. then cloth=0;
if trans=. then trans=0;

if firm=102 and wid=6 then trans=135000;
allowa=food+house+cloth+trans;

if tunit=2 then allowa=allowa*22;
if tunit=3 then allowa=allowa*4;
if tunit=4 then allowa=allowa;
if tunit=5 then allowa=allowa/12;

earn1=wagebt+allowa;

probon=alap_2/12;
xmas=alax_2/12;

if probon=. then probon=0;
if xmas=. then xmas=0;

earn=earn1+probon+xmas;
allow=earn-wagebt;
pcallow=allow/earn;

/* wage last year calculations - excluding allowances */
tunitb=sallu_2;
wagebtly=sall_2;

if tunitb=1 then delete;
if tunitb=2 then wagebtly=wagebtly*22;
if tunitb=3 then wagebtly=wagebtly*4;
if tunitb=4 then wagebtly=wagebtly;
if tunitb=5 then wagebtly=wagebtly/12;

/* starting wages - excluding allowances */
tunitc=salsu_2;
wagebtst=sals_2;

if tunitc=1 then delete;
if tunitc=2 then wagebtst=wagebtst*22;
if tunitc=3 then wagebtst=wagebtst*4;
if tunitc=4 then wagebtst=wagebtst;
if tunitc=5 then wagebtst=wagebtst/12;

hours=hours_2;

trainn=trnow_2;
trainp=trpas_2;

keep firm wave year wid wid1 ethnic name sex age impeduc
 yrendsc edlevela edlevelb edtype edform tenure pexp
occupat ocdum paid tunit earn wagebt wagebtly wagebtst hours trainn
trainp educ intw1 allow pcallow food house cloth trans probon xmas
mgmt admin sales cleric super tech prod appren;

proc means; run;
proc print; var firm wid wid1 name age sex; run;

/* data steps to merge data from wave 1 for workers in
wave 2 that have been matched */

data temp4; set cd.work1;
wid1=wid;
wage93=wagebt;
earn93=earn;

keep firm wid1 wage93 earn93 age sex ethnic yrendsc
edlevela edlevelb edform educ tenure;

proc means; run;
proc sort; by firm wid1;

data temp5; set work2;

if wid1=. then delete;
proc means; run;
proc sort; by firm wid1;


data merge1; merge temp5 (in=a) temp4; by firm wid1; if a;

age=age+1;
tenure=tenure+1;
wid2=wid;

proc means; run;
proc print; var firm wid wid1 age sex; run;
proc sort; by firm wid;


data cd.merge12; merge work2 merge1; by firm wid;
proc means; run;

proc sort; by ocdum;
proc means; var firm wid occupat educ earn wagebt pcallow wagebtly; by ocdum; run;


/* checking wages and allowances across waves 1 and 2 */

data cd.allow1; set merge1;

wage94=wagebt;
allow94=allow;
earn94=earn;

change=(wage94-wage93)/wage93*100;

diff94=earn94/wage94;

earn93c=wage93*diff94;

proc print; var firm wid wage93 earn93 wage94 earn94 change; run;
proc means; run;


/* WAVE 2 EARNINGS FUNCTIONS */

data earnfc2a; set cd.merge12;

agesq=age*age;
expsq=tenure*tenure;

lwage=log(wagebt);

proc reg; model lwage=age agesq educ tenure mgmt admin
sales cleric super tech; run;


data earnfc2b; set cd.merge12;

agesq=age*age;
expsq=tenure*tenure;

learn=log(earn);

proc reg; model learn=age agesq educ tenure mgmt admin
sales cleric super tech; run;


/* ****************** */
/* WAVE 3 WORKER DATA
********************* */

/* creates worker id number which was omitted from wk3 file */

data temp13; set tz.wk3new;
proc sort; by key;

data temp14; set temp13; by key;
if first.key then wid=0;
wid=wid+1;
retain wid;
proc means; run;


data work3; set temp14;
options linesize=80;
options nolabel;

firm=key;
wave=3;
year=3;

ethnic=ori_3;
/* excludes europeans and others from analysis */
if ethnic=2 then delete;
if ethnic=4 then delete;
if firm=427 and wid=10 then delete;

name=name_3;
sex=sex_3;
age=age_3;

yrendsc=yeasch_3;
if yrendsc=0 then yrendsc=.;
yrendsc=yrendsc+1900;

if edu_3=1 then edlevela=1;
if edu_3=2 then edlevela=2;
if edu_3=3 then edlevela=3;
if edu_3=4 then edlevela=4;
if edu_3=5 then edlevela=5;
if edu_3=6 then edlevelb=7;
if edu_3=7 then edlevelb=9;
if edu_3=8 then edlevelb=8;
if edu_3=9 then edlevela=10;
if edu_3=10 then edlevela=10;
if edu_3=11 then edlevela=10;
if edu_3=12 then edlevela=10;

edtype=efor_3;
edform=efnb_3;

if edlevela=1 then educ=0;
if edlevela=2 then educ=7;
if yrendsc lt 1964 and edlevela=2 then educ=4;
if edlevela=3 then educ=8;
if edlevela=4 then educ=11;
if edlevela=5 then educ=13;

if edtype=1 then years=edform;
if edtype=2 then years=edform+7;
if edtype=2 and edlevela=3 then years=edform+8;

if educ=. then educ=years;
if years lt educ and years ne . then educ=years;

if edlevelb=7 then educ=9;
if edlevelb=8 then educ=14;
if edlevelb=9 then educ=13;
if edlevelb=9 and edlevela=2 then educ=9;
if edlevelb=9 and edtype=1 then educ=9;

if edlevela=10 then educ=16;

/* imputed education levels */
yrborn=1995-age;
impeduc=yrendsc-yrborn-6;
if impeduc<educ then impeduc=educ;
if impeduc>30 then impeduc=educ;


/* tenure variable */
years=nby_3;
months=nbm_3;
if months=. then months=0;
if years=. then years=0;
pcyear=months/12;
tenure=years+pcyear;
if tenure=0 then tenure=.;

pexp=wexp_3;

/* occupation coding changed in wave 3 so needs to be made
consistent with other waves */

occupat=jobn_3;

if occupat gt 1 then occupat=occupat+1;
if occupat=17 or occupat=18 or occupat=20 then occupat=19;

if occupat=1 or occupat=2 then ocdum='amgmt';
if occupat=3 or occupat=4 then ocdum='badmin';
if occupat=5 or occupat=6 then ocdum='badmin';
if occupat=7 then ocdum='cleric';
if occupat=9 or occupat=10 then ocdum='csales';
if occupat=12 then ocdum='dsuper';
if occupat=13 or occupat=14 then ocdum='etech';
if occupat=8 or occupat=11 or occupat=15 then ocdum='fprod';
if occupat=16 or occupat=17 or occupat=19 then ocdum='fprod';
if occupat=18 then ocdum='happren';


/* dummy variables for occupation categories */
if occupat=1 or occupat=2 then mgmt=1; else mgmt=0;
if occupat=3 or occupat=4 or occupat=5 or occupat=6 then admin=1;
else admin=0;
if occupat=7 then cleric=1; else cleric=0;
if occupat=9 or occupat=10 then sales=1; else sales=0;
if occupat=12 then super=1; else super=0;
if occupat=13 or occupat=14 then tech=1; else tech=0;
if occupat=8 or occupat=11 or occupat=15
or occupat=16 or occupat=17 or occupat=19 then prod=1; else prod=0;
if occupat=18 then appren=1; else appren=0;

paid=payn_3;


/* current wages - no information on whether wage is net or
gross of tax (assume gross salary) and include allowances */

tunit=salnu_3;
wagebt=saln_3;
food=alaf_3;
house=alah_3;
cloth=alac_3;
trans=alat_3;

if tunit=1 then delete;
if tunit=2 then wagebt=wagebt*22;
if tunit=3 then wagebt=wagebt*4;
if tunit=4 then wagebt=wagebt;
if tunit=5 then wagebt=wagebt/12;


/* give workers with missing wages the mean for their
occupational category */
if wagebt=. and ocdum='fprod' then wagebt=19000;
if wagebt=. and ocdum='amgmt' then wagebt=50000;


if food=. then food=0;
if house=. then house=0;
if cloth=. then cloth=0;
if trans=. then trans=0;

allowa=food+house+cloth+trans;

if tunit=2 then allowa=allowa*22;
if tunit=3 then allowa=allowa*4;
if tunit=4 then allowa=allowa;
if tunit=5 then allowa=allowa/12;

earn1=wagebt+allowa;

probon=alap_3/12;
xmas=alax_3/12;

if probon=. then probon=0;
if xmas=. then xmas=0;

earn=earn1+probon+xmas;
allow=earn-wagebt;
pcallow=allow/earn;


/* wage last year calculations - excluding allowances */
tunitb=sallu_3;
wagebtly=sall_3;

if tunitb=1 then delete;
if tunitb=2 then wagebtly=wagebtly*22;
if tunitb=3 then wagebtly=wagebtly*4;
if tunitb=4 then wagebtly=wagebtly;
if tunitb=5 then wagebtly=wagebtly/12;

/* starting wages - excluding allowances */
tunitc=salsu_3;
wagebtst=sals_3;

if tunitc=1 then delete;
if tunitc=2 then wagebtst=wagebtst*22;
if tunitc=3 then wagebtst=wagebtst*4;
if tunitc=4 then wagebtst=wagebtst;
if tunitc=5 then wagebtst=wagebtst/12;

hours=hours_3;

intw2=int_3;

keep firm wave year intw2 wid wid2 ethnic name sex age impeduc
 yrendsc edlevela edlevelb edtype edform educ tenure pexp
occupat ocdum paid tunit wagebt allow pcallow
food house cloth trans probon xmas
earn wagebtly wagebtst hours
mgmt admin cleric sales super tech prod appren;

proc means; run;
proc print; var firm wid wagebt earn ocdum; run;
proc sort; by firm wid;


/* data steps to merge data from new wave 2 file for workers in
wave 3 that have been matched */

data temp24; set cd.merge12;
wid2=wid;

keep firm wid2 age sex ethnic yrendsc
edlevela edlevelb edform edtype educ tenure;

proc means; run;
proc sort; by firm wid2;


data temp25; set work3;

if wid2=. then delete;
proc means; run;
proc sort; by firm wid2;


data merge21; merge temp25 (in=a) temp24; by firm wid2; if a;

age=age+1;
tenure=tenure+1;
wid3=wid;

proc means; run;
proc print; var firm wid wid2 age sex; run;
proc sort; by firm wid;


data cd.merge23; merge work3 merge21; by firm wid;

if firm=100 then delete;

proc means; run;
/* proc print; var firm wid wid2 age sex tenure ocdum wagebt; run;*/

proc sort; by ocdum;
proc means; var firm wid age educ wagebt earn pcallow; by ocdum; run;

proc sort; by firm wid;


/* datasteps to derive file of workers matched across all 3 waves */


data comb1; set merge1;
proc means; run;
proc sort; by firm wid2;

data comb2; set merge21;
proc means; run;
proc sort; by firm wid2;

data comb12; merge comb1 (in=a) comb2 (in=b);
by firm wid2; if a; if b;

proc means; run;
proc print; var firm wid1 wid2 wid3 age sex; run;




/* WAVE 3 EARNINGS FUNCTIONS */

data earnfc3; set cd.merge23;

agesq=age*age;
lwage=log(wagebt);

proc reg; model lwage=age agesq educ tenure mgmt admin cleric
sales super tech;
run;



data earnfc31; set cd.merge23;

agesq=age*age;
learn=log(earn);

proc reg; model learn=age agesq educ tenure mgmt admin cleric
sales super tech;
run;




/* CHECKS on occupational classes and crosstabs */

/* 
data check1; set cd.work1;

proc sort; by ocdum;

proc means; var firm wid wagebt age educ tenure; by ocdum; run;
proc print; var firm wid wagebt age educ tenure; by ocdum; run;

data check12; set check1;
if ocdum =' ';
proc print; var firm wid occupat age educ tenure; run;



data check2; set cd.merge12;

proc sort; by ocdum;

proc means; var firm wid wagebt age educ tenure; by ocdum; run;
proc print; var firm wid wagebt age educ tenure; by ocdum; run;


data check3; set cd.merge23;

proc sort; by ocdum;

proc means; var firm wid wagebt age educ tenure; by ocdum; run;
proc print; var firm wid wagebt age educ tenure; by ocdum; run;

*/

