/* calculates average human capital characteristics 
by firm by combining worker level data (worker education, 
tenure, age, work experience) and breakdown of firm's total 
employment by occupational category from Section 5 of main
questionnaire */

/* also generates separate file of owner/ manager human
capital and characteristics */

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

/* Tanzania Wave 1 data */

data temp1; set tz.tz1new;
options nolabel;

firm=key;
wave=1;

empft=v2401f;
emppt=v2401p;
peak=v2402;

emp92=empft+emppt;

/*males*/
if v2501031=. then v2501031=0;
if v2502031=. then v2502031=0;
if v2503031=. then v2503031=0;
if v2504031=. then v2504031=0;
if v2505031=. then v2505031=0;
if v2506031=. then v2506031=0;
if v2507031=. then v2507031=0;
if v2508031=. then v2508031=0;
if v2509031=. then v2509031=0;
if v2510031=. then v2510031=0;
if v2511031=. then v2511031=0;
if v2512031=. then v2512031=0;
if v2513031=. then v2513031=0;

/*females*/
if v2501032=. then v2501032=0;
if v2502032=. then v2502032=0;
if v2503032=. then v2503032=0;
if v2504032=. then v2504032=0;
if v2505032=. then v2505032=0;
if v2506032=. then v2506032=0;
if v2507032=. then v2507032=0;
if v2508032=. then v2508032=0;
if v2509032=. then v2509032=0;
if v2510032=. then v2510032=0;
if v2511032=. then v2511032=0;
if v2512032=. then v2512032=0;

/* male employees */
manm92=v2501031;
adminm92=v2502031;
salesm92=v2503031;
superm92=v2508031;
techm92=v2504031+v2505031;
prodm92=v2506031+v2507031+v2511031;
servm92=v2509031;
trainm92=v2510031+v2512031;

/* female employees */
manw92=v2501032;
adminw92=v2502032;
salesw92=v2503032;
superw92=v2508032;
techw92=v2504032+v2505032;
prodw92=v2506032+v2507032+v2511032;
servw92=v2509032;
trainw92=v2510032+v2512032;

/* all employees */
man92=manm92+manw92;
admin92=adminm92+adminw92;
sales92=salesm92+salesw92;
super92=superm92+superw92;
tech92=techm92+techw92;
prod92=prodm92+prodw92;
serv92=servm92+servw92;
train92=trainm92+trainw92;

/* share of female employees by category */
manfsh92=manw92*100/man92;
admfsh92=adminw92*100/admin92;
salfsh92=salesw92*100/sales92;
supfsh92=superw92*100/super92;
tecfsh92=techw92*100/tech92;
profsh92=prodw92*100/prod92;
serfsh92=servw92*100/serv92;
trafsh92=trainw92*100/train92;

/* check on table total */
twork92=man92+admin92+sales92+super92+tech92+prod92
+serv92+train92;
empck1=emp92-twork92;

/* assume that missing workers from breakdown table
are mainly casual production workers */

if emp92>twork92 then prod92=prod92+empck1;

emp=emp92;
man=man92;
admin=admin92;
sales=sales92;
super=super92;
tech=tech92;
prod=prod92;
serv=serv92;
train=train92;

keep firm wave emp92 manm92 adminm92 salesm92 superm92
techm92 prodm92 servm92 trainm92
manw92 adminw92 salesw92 superw92
techw92 prodw92 servw92 trainw92
man92 admin92 sales92 super92
tech92 prod92 serv92 train92
manfsh92 admfsh92 salfsh92 supfsh92
tecfsh92 profsh92 serfsh92 trafsh92
twork92 empck1 empck2 emp man admin sales super
tech prod serv train;

proc sort; by firm wave;
proc means; run;



/* WAVE 2 DATA */

data temp2; set tz.tz2new;
options nolabel;

firm=key;
wave=2;

emp93=v2101;
if emp93=. then emp93=0;

empftp=v2102r1;
empftc=v2102r2;
emppt=v2102r3;

emp93ck=empftp+empftc+emppt;

/*males*/
if v2207m1=. then v2207m1=0;
if v2207m2=. then v2207m2=0;
if v2207m3=. then v2207m3=0;
if v2207m4=. then v2207m4=0;
if v2207m5=. then v2207m5=0;
if v2207m6=. then v2207m6=0;
if v2207m7=. then v2207m7=0;
if v2207m8=. then v2207m8=0;
if v2207m9=. then v2207m9=0;
if v2207m10=. then v2207m10=0;
if v2207m11=. then v2207m11=0;
if v2207m12=. then v2207m12=0;
if v2207m13=. then v2207m13=0;
if v2207m14=. then v2207m14=0;
if v2207m15=. then v2207m15=0;
if v2207m16=. then v2207m16=0;
if v2207m17=. then v2207m17=0;
if v2207m18=. then v2207m18=0;
if v2207m19=. then v2207m19=0;
if v2207m20=. then v2207m20=0;
if v2207mt=. then v2207mt=0;

/*females*/
if v2207w1=. then v2207w1=0;
if v2207w2=. then v2207w2=0;
if v2207w3=. then v2207w3=0;
if v2207w4=. then v2207w4=0;
if v2207w5=. then v2207w5=0;
if v2207w6=. then v2207w6=0;
if v2207w7=. then v2207w7=0;
if v2207w8=. then v2207w8=0;
if v2207w9=. then v2207w9=0;
if v2207w10=. then v2207w10=0;
if v2207w11=. then v2207w11=0;
if v2207w12=. then v2207w12=0;
if v2207w13=. then v2207w13=0;
if v2207w14=. then v2207w14=0;
if v2207w15=. then v2207w15=0;
if v2207w16=. then v2207w16=0;
if v2207w17=. then v2207w17=0;
if v2207w18=. then v2207w18=0;
if v2207w19=. then v2207w19=0;
if v2207w20=. then v2207w20=0;
if v2207wt=. then v2207wt=0;

/* male employees */
manm93=v2207m1+v2207m2;
adminm93=v2207m3+v2207m4+v2207m5+v2207m6;
clerm93=v2207m7+v2207m8;
salesm93=v2207m9+v2207m10;
superm93=v2207m12;
techm93=v2207m13+v2207m14;
prodm93=v2207m15+v2207m16+v2207m17+v2207m18+v2207m19+v2207m20;
servm93=v2207m11;
totalm93=v2207mt;

/* female employees */
manw93=v2207w1+v2207w2;
adminw93=v2207w3+v2207w4+v2207w5+v2207w6;
clerw93=v2207w7+v2207w8;
salesw93=v2207w9+v2207w10;
superw93=v2207w12;
techw93=v2207w13+v2207w14;
prodw93=v2207w15+v2207w16+v2207w17+v2207w18+v2207w19+v2207w20;
servw93=v2207w11;
totalw93=v2207wt;

/* all employees */
man93=manm93+manw93;
admin93=adminm93+adminw93;
sales93=salesm93+salesw93;
cler93=clerm93+clerw93;
super93=superm93+superw93;
tech93=techm93+techw93;
prod93=prodm93+prodw93;
serv93=servm93+servw93;
total93=totalm93+totalw93;

/* share of female employees by category */
manfsh93=manw93*100/man93;
admfsh93=adminw93*100/admin93;
salfsh93=salesw93*100/sales93;
clefsh93=clerw93*100/cler93;
supfsh93=superw93*100/super93;
tecfsh93=techw93*100/tech93;
profsh93=prodw93*100/prod93;
serfsh93=servw93*100/serv93;
totfsh93=totalw93*100/total93;

/* check on table total */
twork93=man93+admin93+sales93+cler93+super93+tech93+prod93
+serv93;
empck1=total93-twork93;
empck2=emp93-twork93;

/* assume that missing workers from breakdown table
are mainly casual production workers */

if emp93>twork93 then prod93=prod93+empck2;

/* adjustment for Tanzania Breweries no breakdown
of 1992 workers available */

if firm=100 then prod93=1992*0.75;
if firm=100 then man93=1992*0.01;
if firm=100 then admin93=1992*0.12;
if firm=100 then sales93=1992*0.12;

emp=emp93;
man=man93;
admin=admin93;
sales=sales93;
cler=cler93;
super=super93;
tech=tech93;
prod=prod93;
serv=serv93;
total=total93;

keep firm wave emp93 manm93 adminm93 salesm93 superm93
clerm93 techm93 prodm93 servm93 totalm93
manw93 adminw93 salesw93 superw93 clerw93
techw93 prodw93 servw93 totalw93
man93 admin93 sales93 super93
tech93 prod93 serv93 cler93 total93
manfsh93 admfsh93 salfsh93 supfsh93
tecfsh93 profsh93 serfsh93 clefsh93
twork93 empck1 empck2 emp man admin cler sales super
tech prod serv total;

/* if empck2 ne 0;
proc print; var firm emp93 total93 twork93; run;
*/

proc sort; by firm wave;
proc means; run;


/* WAVE 3 DATA */

data temp3; set tz.tz3new;
options nolabel;

firm=key;
wave=3;

emppt=v2601pt;
empft=v2601ft;
emp95=emppt+empft;

if firm=530 then emp95=10;

/*males*/
if v2715m01=. then v2715m01=0;
if v2715m02=. then v2715m02=0;
if v2715m03=. then v2715m03=0;
if v2715m04=. then v2715m04=0;
if v2715m05=. then v2715m05=0;
if v2715m06=. then v2715m06=0;
if v2715m07=. then v2715m07=0;
if v2715m08=. then v2715m08=0;
if v2715m09=. then v2715m09=0;
if v2715m10=. then v2715m10=0;
if v2715m11=. then v2715m11=0;
if v2715m12=. then v2715m12=0;
if v2715m13=. then v2715m13=0;
if v2715m14=. then v2715m14=0;
if v2715m15=. then v2715m15=0;
if v2715m16=. then v2715m16=0;
if v2715m17=. then v2715m17=0;
if v2715m18=. then v2715m18=0;
if v2715m19=. then v2715m19=0;
if v2715mt=. then v2715mt=0;
if v2715t=. then v2715t=0;

/*females*/
if v2715w01=. then v2715w01=0;
if v2715w02=. then v2715w02=0;
if v2715w03=. then v2715w03=0;
if v2715w04=. then v2715w04=0;
if v2715w05=. then v2715w05=0;
if v2715w06=. then v2715w06=0;
if v2715w07=. then v2715w07=0;
if v2715w08=. then v2715w08=0;
if v2715w09=. then v2715w09=0;
if v2715w10=. then v2715w10=0;
if v2715w11=. then v2715w11=0;
if v2715w12=. then v2715w12=0;
if v2715w13=. then v2715w13=0;
if v2715w14=. then v2715w14=0;
if v2715w15=. then v2715w15=0;
if v2715w16=. then v2715w16=0;
if v2715w17=. then v2715w17=0;
if v2715w18=. then v2715w18=0;
if v2715w19=. then v2715w19=0;
if v2715wt=. then v2715wt=0;
if v2715t=. then v2715t=0;

/* male employees */
manm95=v2715m01;
adminm95=v2715m02+v2715m03+v2715m04+v2715m05;
clerm95=v2715m06+v2715m07;
salesm95=v2715m08+v2715m09;
superm95=v2715m11;
techm95=v2715m12+v2715m13;
prodm95=v2715m14+v2715m15+v2715m16+v2715m17+v2715m18+v2715m19;
servm95=v2715m10;
totalm95=v2715mt;

/* female employees */
manw95=v2715w01;
adminw95=v2715w02+v2715w03+v2715w04+v2715w05;
clerw95=v2715w06+v2715w07;
salesw95=v2715w08+v2715w09;
superw95=v2715w11;
techw95=v2715w12+v2715w13;
prodw95=v2715w14+v2715w15+v2715w16+v2715w17+v2715w18+v2715w19;
servw95=v2715w10;
totalw95=v2715wt;

/* all employees */
man95=manm95+manw95;
admin95=adminm95+adminw95;
sales95=salesm95+salesw95;
cler95=clerm95+clerw95;
super95=superm95+superw95;
tech95=techm95+techw95;
prod95=prodm95+prodw95;
serv95=servm95+servw95;
total95=totalm95+totalw95;

/* share of female employees by category */
manfsh95=manw95*100/man95;
admfsh95=adminw95*100/admin95;
salfsh95=salesw95*100/sales95;
clefsh95=clerw95*100/cler95;
supfsh95=superw95*100/super95;
tecfsh95=techw95*100/tech95;
profsh95=prodw95*100/prod95;
serfsh95=servw95*100/serv95;
totfsh95=totalw95*100/total95;

/* check on table total */
twork95=man95+admin95+sales95+cler95+super95+tech95+prod95
+serv95;
empck1=total95-twork95;
empck2=emp95-twork95;

/* assume that missing workers from breakdown table
are mainly casual production workers */

if emp95>twork95 then prod95=prod95+empck2;

/* adjustment for Tanzania Breweries no breakdown
of 1920 workers available */

if firm=100 then prod95=1920*0.75;
if firm=100 then man95=1920*0.01;
if firm=100 then admin95=1920*0.12;
if firm=100 then sales95=1920*0.12;

emp=emp95;
man=man95;
admin=admin95;
sales=sales95;
cler=cler95;
super=super95;
tech=tech95;
prod=prod95;
serv=serv95;
total=total95;

keep firm wave emp95 manm95 adminm95 salesm95 superm95
clerm95 techm95 prodm95 servm95 totalm95
manw95 adminw95 salesw95 superw95 clerw95
techw95 prodw95 servw95 totalw95
man95 admin95 sales95 super95
tech95 prod95 serv95 cler95 total95
manfsh95 admfsh95 salfsh95 supfsh95
tecfsh95 profsh95 serfsh95 clefsh95
twork95 empck1 empck2 emp man admin cler sales super
tech prod serv total;

/* if empck2 ne 0;
proc print; var firm emp95 total95 twork95; run;
*/

proc sort; by firm wave;
proc means; run;

/* occupational breakdown for 3 waves */

data workocc; merge temp1 temp2 temp3; by firm wave;
drop total;
proc means; run;


/* CREATING AVERAGE HUMAN CAPITAL LEVELS BY FIRM */

/* worker level data set up human capital variables */
data temp4; set cd.work123;

if mgmt=1 then manyrs=impeduc;
if mgmt=1 then manedu=educ;
if mgmt=1 then manage=age;
if mgmt=1 then manten=tenure;
if mgmt=1 then manpex=pexp;

if admin=1 then adminyrs=impeduc;
if admin=1 then adminedu=educ;
if admin=1 then adminage=age;
if admin=1 then adminten=tenure;
if admin=1 then adminpex=pexp;

if cleric=1 then cleryrs=impeduc;
if cleric=1 then cleredu=educ;
if cleric=1 then clerage=age;
if cleric=1 then clerten=tenure;
if cleric=1 then clerpex=pexp;

if sales=1 then salyrs=impeduc;
if sales=1 then saledu=educ;
if sales=1 then salage=age;
if sales=1 then salten=tenure;
if sales=1 then salpex=pexp;

if super=1 then supyrs=impeduc;
if super=1 then supedu=educ;
if super=1 then supage=age;
if super=1 then supten=tenure;
if super=1 then suppex=pexp;

if tech=1 then techyrs=impeduc;
if tech=1 then techedu=educ;
if tech=1 then techage=age;
if tech=1 then techten=tenure;
if tech=1 then techpex=pexp;

if prod=1 then prodyrs=impeduc;
if prod=1 then prodedu=educ;
if prod=1 then prodage=age;
if prod=1 then prodten=tenure;
if prod=1 then prodpex=pexp;

keep firm wave wid impeduc educ age tenure pexp
manyrs manedu manage manten manpex
adminyrs adminedu adminage adminten adminpex
cleryrs cleredu clerage clerten clerpex
salyrs saledu salage salten salpex
supyrs supedu supage supten suppex
techyrs techedu techage techten techpex
prodyrs prodedu prodage prodten prodpex;

proc means; run;
proc sort; by firm wave wid;


/* breakdown of total workforce by occupation */
data temp5; set workocc;

if cler=. then cler=0;
if serv=. then serv=0;
if train=. then train=0;

skill=man+admin+cler+sales+super;
unskill=tech+prod+serv+train;

total=skill+unskill;

prod=prod+serv+train;

keep firm wave man admin cler sales
super tech prod skill unskill total;

proc means; run;


proc means mean n sum; run;
proc sort; by firm wave;



/* creates mean HC endowments by occupational category for
each firm using the worker level data */
data temp6; set temp4;
proc sort; by firm wave;

proc means noprint; var
impeduc educ age tenure pexp
manyrs manedu manage manten manpex
adminyrs adminedu adminage adminten adminpex
cleryrs cleredu clerage clerten clerpex
salyrs saledu salage salten salpex
supyrs supedu supage supten suppex
techyrs techedu techage techten techpex
prodyrs prodedu prodage prodten prodpex;
by firm wave;

output out=mhumcap
mean=impeduc educ age tenure pexp
manyrs manedu manage manten manpex
adminyrs adminedu adminage adminten adminpex
cleryrs cleredu clerage clerten clerpex
salyrs saledu salage salten salpex
supyrs supedu supage supten suppex
techyrs techedu techage techten techpex
prodyrs prodedu prodage prodten prodpex;
run;

/* creates mean HC endowments by occupational category across
all firms using the worker level data */
data temp7; set temp4;
proc sort; by wave;

proc means noprint; var
manyrs manedu manage manten manpex
adminyrs adminedu adminage adminten adminpex
cleryrs cleredu clerage clerten clerpex
salyrs saledu salage salten salpex
supyrs supedu supage supten suppex
techyrs techedu techage techten techpex
prodyrs prodedu prodage prodten prodpex;
by wave;

output out=mocc
mean=mmanyrs mmanedu mmanage mmanten mmanpex
madminyr madmined madminag madminte madminpe
mcleryrs mcleredu mclerage mclerten mclerpex
msalyrs msaledu msalage msalten msalpex
msupyrs msupedu msupage msupten msuppex
mtechyrs mtechedu mtechage mtechten mtechpex
mprodyrs mprodedu mprodage mprodten mprodpex;
run;

/* replaces missing values for HC endowments from worker data
with mean for each occupational category */

data humcap1; merge mhumcap temp5; by firm wave;
proc sort; by wave;

data humcap2; merge humcap1 mocc; by wave;

if manyrs=. then manyrs=mmanyrs;
if manedu=. then manedu=mmanedu;
if manage=. then manage=mmanage;
if manten=. then manten=mmanten;
if manpex=. then manpex=mmanpex;

if adminyrs=. then adminyrs=madminyr;
if adminedu=. then adminedu=madmined;
if adminage=. then adminage=madminag;
if adminten=. then adminten=madminte;
if adminpex=. then adminpex=madminpe;

if cleryrs=. then cleryrs=mcleryrs;
if cleredu=. then cleredu=mcleredu;
if clerage=. then clerage=mclerage;
if clerten=. then clerten=mclerten;
if clerpex=. then clerpex=mclerpex;

if salyrs=. then salyrs=msalyrs;
if saledu=. then saledu=msaledu;
if salage=. then salage=msalage;
if salten=. then salten=msalten;
if salpex=. then salpex=msalpex;

if supyrs=. then supyrs=msupyrs;
if supedu=. then supedu=msupedu;
if supage=. then supage=msupage;
if supten=. then supten=msupten;
if suppex=. then suppex=msuppex;

if techyrs=. then techyrs=mtechyrs;
if techedu=. then techedu=mtechedu;
if techage=. then techage=mtechage;
if techten=. then techten=mtechten;
if techpex=. then techpex=mtechpex;

if prodyrs=. then prodyrs=mprodyrs;
if prodedu=. then prodedu=mprodedu;
if prodage=. then prodage=mprodage;
if prodten=. then prodten=mprodten;
if prodpex=. then prodpex=mprodpex;

proc sort; by firm wave;
proc means; run;


/* proc print; var firm adminyrs adminedu adminage
adminten adminpex; run; */

data cd.humcap; set humcap2;

yrswgt=(manyrs*man+adminyrs*admin+cleryrs*cler+salyrs*sales+supyrs*super
+techyrs*tech+prodyrs*prod)/total;

eduwgt=(manedu*man+adminedu*admin+cleredu*cler+saledu*sales+supedu*super
+techedu*tech+prodedu*prod)/total;

agewgt=(manage*man+adminage*admin+clerage*cler+salage*sales+supage*super
+techage*tech+prodage*prod)/total;

tenwgt=(manten*man+adminten*admin+clerten*cler+salten*sales+supten*super
+techten*tech+prodten*prod)/total;

pexwgt=(manpex*man+adminpex*admin+clerpex*cler+salpex*sales+suppex*super
+techpex*tech+prodpex*prod)/total;

if wave=1 then round=1;
if wave=2 then round=2;
if wave=3 then round=3;

keep firm wave round man admin cler sales
super tech prod skill unskill total
yrswgt eduwgt agewgt tenwgt pexwgt;

/*
proc print; var firm wave yrswgt eduwgt agewgt tenwgt pexwgt; run;
*/
proc means; run;
proc sort; by firm round;



/* CREATING FILE OF TYPES OF MANAGERS */

data temp11; set tz.tz1new;
options nolabel;
firm=key;
wave=1;
manm=V2501031;
manw=V2501032;
if manm=. then manm=0;
if manw=. then manw=0;
man=manm+manw;
keep firm wave man;
proc means; run;
proc sort; by firm wave;

data temp12; set tz.tz2new;
options nolabel;
firm=key;
wave=2;
empmanm=V2207m1;
empmanw=v2207w1;
ownmanm=v2207m2;
ownmanw=v2207w2;
if empmanm=. then empmanm=0;
if empmanw=. then empmanw=0;
if ownmanm=. then ownmanm=0;
if ownmanw=. then ownmanw=0;
empman=empmanm+empmanw;
ownman=ownmanm+ownmanw;
keep firm wave empman ownman;
proc means; run;
proc sort; by firm wave;

data temp13; set tz.tz3new;
options nolabel;
firm=key;
wave=3;
manm=V2715m01;
manw=v2715w01;
if manm=. then manm=0;
if manw=. then manw=0;
man=manm+manw;
keep firm wave man;
proc means; run;
proc sort; by firm wave;

data firm1; set temp12;
if empman=0 then noman=1; else noman=0;
proc sort; by firm;

data firm2; merge temp11 temp13; by firm;
proc sort; by firm;

data cd.firm3; merge firm1 firm2; by firm;
if noman=. and man=0 then noman=1;
if noman=. and man>0 then noman=0;
proc means; var firm man empman ownman noman; run;
