/* This code replicates the tables from "Characterising Green Employment: The impacts of greening on workforce composition" */
* Note: The table numbers are not in order;
* To replicate a given table, search for the table number and/or table title, then run all the code that comes before it.

 * The following raw data files are taken from the .txt files provided by O*NET and the BLS, downloaded July 2014:
 * Career Changers Matrix.txt - saved as changers.dta
 * Career Starters Matrix.txt - saved as starters.dta
 * BLS data (oe.data.1.AllData.txt) - saved as Rbls.dta
 	* To ensure the code runs properly, first save the following .txt files under the following names:
 * Green Occupations.txt - greencategory.dta
 * Education, Training, and Experience.txt - standardskills.dta
 * Green Task Statements.txt - greentaskstatements.dta
 * Job Zones.txt - jobzone.dta
 * Occupation Data.txt - onetraw.dta
 * Occupation Reference.txt - title.dta
 * Task Ratings.txt - taskratings.dta
 * Task Statements.txt - taskstatements.dta
 * Work Activities.txt - saved as activities.dta 
 * Work Context.txt - saved as context.dta 
 * Abilities.txt - saved as abilities.dta 
 **** All constructed data files start with the letter "R"

* Before running the code, 1. Make sure mmerge is installed 
ssc install mmerge

* 2. Change the file directory here
cd "/Users/tipoegeorge/Desktop/Green Economy/retest"

**************************************************************************
** Import the data from .txt format and save as .dta files
**************************************************************************
clear
infix str onetsoccode 1-10 str careerchangers 11-28 index 29-35  using "Career Changers Matrix.txt"
	drop if _n == 1
save changers.dta, replace

clear
infix str onetsoccode 1-10 str careerstarters 11-28 index 29-35  using "Career Starters Matrix.txt"
	drop if _n == 1
save starters.dta, replace

use onetraw.dta, clear
	cap drop description
   mmerge onetsoccode using starters.dta, type(1:n) ukeep(careerstarters)
    drop if _merge == 1 /* drop blank cells - everything was matched */ 
    drop _merge
    rename onetsoccode var1
	rename careerstarters onetsoccode 
   mmerge onetsoccode using title.dta, type(n:1) ukeep(title)
   	rename title titlestarters
   	keep if _merge == 3
   	drop _merge
   	rename onetsoccode careerstarters
   	rename var1 onetsoccode
    rename onetsoctitle title
   	bysort onetsoccode: gen index = _n
save Rstarters.dta, replace

use onetraw.dta, clear
	mmerge onetsoccode using changers.dta, type(1:n) ukeep(careerchangers)
    drop _merge
    rename onetsoccode var1
	rename careerchangers onetsoccode 
   mmerge onetsoccode using title.dta, type(n:1) ukeep(title)
   	rename title titlechangers
   	keep if _merge == 3
   	drop _merge
   	rename onetsoccode careerchangers
   	rename var1 onetsoccode
   	rename onetsoctitle title
   	bysort onetsoccode: gen index = _n
   	bysort onetsoccode: gen maxcount = _N
save Rchangers.dta, replace

use Rstarters.dta, clear
   mmerge onetsoccode index using Rchangers.dta, type(1:1)
   	drop _merge
	/* sort onetsoccode careerstarters
	qui by onetsoccode careerstarters: gen dup = cond(_N == 1, 0, _n)
	drop if dup > 1
	drop dup */
save Ronetmain.dta, replace

/* Green occupation variable - 204 green occupations */ 
use Ronetmain.dta, clear
   mmerge onetsoccode using greencategory.dta, type(n:1)
	drop if _merge == 2
	drop _merge
	rename greenoccupationalcategory categorymain
	rename onetsoccode var1
	rename careerstarters onetsoccode
   mmerge onetsoccode using greencategory.dta, type(n:1)
	drop if _merge == 2
	drop _merge
	rename greenoccupationalcategory categorystarters
	rename onetsoccode careerstarters
	rename careerchangers onetsoccode
  mmerge onetsoccode using greencategory.dta, type(n:1)
	drop if _merge == 2
	drop _merge
	rename greenoccupationalcategory categorychangers
	rename onetsoccode careerchangers
	rename var1 onetsoccode
save Ronetmain.dta, replace

* creating greenjob variables - dummy (= 1 if job is any type of green job)
use Ronetmain.dta, clear
	gen greenjob = (categorymain != "")
global var "starters changers"
 foreach N of global var {
	gen greenjob`N' = (category`N' != "")
 }

* Number of each job type, for both career changers and career starters (rows sum to 10)
 forvalues i = 0/1 {
	egen counts`i' = total(greenjobstarters == `i'), by(onetsoccode)
	egen countc`i' = total(greenjobchangers == `i' & careerchangers != ""), by(onetsoccode)
 }

	count if counts0 == 10 /* 252 */
	count if countc0 == 10 /* 287 */
	count if counts0 == 10 & countc0 == 10 /* 172 */ 
	count if greenjob == 0 & counts0 == 10 & countc0 == 10 /* 171 */
	count if greenjob == 0 & counts0 == 10 & countc0 == maxcount /* 171 */

	* Note: i) greenjob is coded so that Brown = 0, Green = 1, Other = 2 
	* ii) 10 occupations are green & have all non-green alternatives;
	* iii) 171 occupations are non-green & have all non-green alternatives ("Other")

* jobtype variables - 0 if brown, 1-3 for different types of green jobs, 4 if other
 gen jobtype = .
	replace jobtype = 1 if categorymain == "Green Increased Demand"
	replace jobtype = 2 if categorymain == "Green Enhanced Skills"
	replace jobtype = 3 if categorymain == "Green New & Emerging"
	replace jobtype = 4 if greenjob == 0 & counts0 == 10 & countc0 == maxcount

 foreach N of global var {
	gen jobtype`N' = .
	replace jobtype`N' = 1 if category`N' == "Green Increased Demand"
	replace jobtype`N' = 2 if category`N' == "Green Enhanced Skills"
	replace jobtype`N' = 3 if category`N' == "Green New & Emerging"
 }


global other "19-3031.01	19-3031.02	19-3031.03	19-3039.01	19-3041.00	19-3091.01	19-3094.00	21-1011.00	21-1014.00	21-1022.00	23-2091.00	25-1022.00	25-1042.00	25-1054.00	25-1061.00	25-1062.00	25-1063.00	25-1064.00	25-1066.00	25-1067.00	25-1071.00	25-1072.00	25-1081.00	25-1082.00	25-1111.00	25-1112.00	25-1113.00	25-1123.00	25-1124.00	25-1125.00	25-1126.00	25-1192.00	25-1193.00	25-1194.00	25-2011.00	25-9041.00	27-1012.00	27-1023.00	27-2011.00	27-2021.00	27-2022.00	27-2031.00	27-2032.00	27-2042.01	27-2042.02	27-3012.00	27-4021.00	29-1011.00	29-1021.00	29-1022.00	29-1023.00	29-1024.00	29-1041.00	29-1061.00	29-1062.00	29-1063.00	29-1064.00	29-1065.00	29-1066.00	29-1067.00	29-1069.03	29-1069.09	29-1071.00	29-1081.00	29-1122.00	29-1122.01	29-1123.00	29-1124.00	29-1125.00	29-1127.00	29-1131.00	29-1141.00	29-1141.01	29-1141.02	29-1141.03	29-1141.04	29-1161.00	29-1171.00	29-1199.01	29-1199.04	29-2021.00	29-2031.00	29-2032.00	29-2033.00	29-2052.00	29-2053.00	29-2054.00	29-2061.00	29-2091.00	29-9091.00	29-9092.00	31-1011.00	31-1013.00	31-2011.00	31-2012.00	31-2021.00	31-2022.00	31-9011.00	31-9091.00	31-9092.00	31-9094.00	31-9095.00	31-9096.00	31-9099.01	33-3011.00	33-3012.00	33-3051.01	33-9091.00	33-9092.00	35-2011.00	35-2012.00	35-2013.00	35-2014.00	35-2015.00	35-3011.00	35-3021.00	35-3022.00	35-3031.00	35-3041.00	35-9011.00	35-9031.00	37-2012.00	39-1012.00	39-2021.00	39-3011.00	39-3012.00	39-3031.00	39-3091.00	39-3092.00	39-3093.00	39-4021.00	39-5011.00	39-5012.00	39-5091.00	39-5092.00	39-5093.00	39-7011.00	39-9011.00	39-9011.01	39-9021.00	39-9031.00	39-9032.00	39-9041.00	41-2011.00	41-2021.00	41-9012.00	43-3021.01	43-3021.02	43-3031.00	43-3041.00	43-3051.00	43-4031.02	43-4051.03	43-4121.00	43-4161.00	43-5081.01	43-5081.03	43-6011.00	43-6012.00	43-6014.00	43-9022.00	43-9041.01	43-9061.00	45-2041.00	49-9063.00	49-9064.00	51-6021.00	51-6052.00	51-6092.00	51-9195.05	53-2031.00"
 foreach N of global other {
	replace jobtype = 4 if onetsoccode == "`N'"
	replace jobtypestarters = 4 if careerstarters == "`N'"
	replace jobtypechangers = 4 if careerchangers == "`N'"
	replace greenjob = 2 if onetsoccode == "`N'"
	replace greenjobstarters = 2 if careerstarters == "`N'"
	replace greenjobchangers = 2 if careerchangers == "`N'"
 }
	replace jobtype = 0 if jobtype == .
	replace jobtypechangers = 0 if jobtypechangers == .
	replace jobtypestarters = 0 if jobtypestarters == .


* Total (by occupation) of similar jobs, by category */
 forvalues i = 0/2 {
	egen sumsr`i' = total(greenjobstarters == `i'), by(onetsoccode)
	egen sumcr`i' = total(greenjobchangers == `i'), by(onetsoccode)
 }
save Ronetmain.dta, replace

use Ronetmain.dta, clear
	sort onetsoccode
	qui by onetsoccode: gen dup = cond(_N == 1, 0, _n)
	drop if dup > 1
	drop dup

keep onetsoccode title jobtype
save Ronetlist.dta, replace

****************
/* Constructing tasks dataset - 974 occupations */
* Task Ratings.txt - saved as taskratings.dta (939 occupations, task ratings add up to 100)
* Task Statements.txt - saved as taskstatements.dta 
* Green Task Statements.txt - saved as greentaskstatements.dta
* Job Zones.txt - saved as jobzone.dta

use jobzone.dta, clear
	gen skill = .
	replace skill = 1 if jobzone == 1 
	replace skill = 1 if jobzone == 2
	replace skill = 2 if jobzone == 3
	replace skill = 3 if jobzone == 4 
	replace skill = 3 if jobzone == 5 
save Rjobzone.dta, replace	

use taskratings.dta, clear
	global vars "standarderror lowercibound uppercibound recommendsuppress category"
	foreach N of global vars {
	replace `N' = "." if `N' == "n/a"
	}
	destring standarderror lowercibound uppercibound, gen(standard_error lowerCIbound upperCIbound) force
	destring category, replace force  
    drop standarderror lowercibound uppercibound
	rename standard_error standarderror
	rename lowerCIbound lowercibound
	rename upperCIbound uppercibound

  * Frequency - how often task is used on the job
	egen maxfreq = max(datavalue) if category != ., by(onetsoccode taskid)
	replace maxfreq = datavalue if category == .
    drop if maxfreq != datavalue
    drop if onetsoccode == ""
	rename category frequency
	bysort onetsoccode taskid: gen count = _N
	egen meanfreq = mean(frequency) if count > 3, by(onetsoccode taskid)
		* some tasks have more than 1 mode - take the average of modes
	replace maxfreq = meanfreq if count > 3 & scaleid == "FT"
	replace maxfreq = frequency if count == 3
	bysort onetsoccode taskid scaleid: gen index = _n
	drop if index > 1
	drop count meanfreq index
save Rtaskfrequency.dta, replace


use Rtaskfrequency.dta, clear
	mmerge onetsoccode taskid using greentaskstatements.dta, type(n:1) ukeep(task greentasktype)
	drop _merge
save Rtaskfrequency.dta, replace

use Rtaskfrequency.dta, clear
	mmerge onetsoccode taskid using taskstatements.dta, type(n:1) ukeep(tasktype)
	* missings are all "n/a"
	drop _merge
	mmerge onetsoccode using greencategory.dta, type(n:1) 
	drop _merge
	mmerge onetsoccode using Rjobzone.dta, type(n:1) ukeep(skill)
	drop _merge
	mmerge onetsoccode using Ronetlist.dta, type(n:1) 
	cap drop root occupationcode 
	drop _merge 
	replace jobtype = 1 if greenoccupationalcategory == "Green Increased Demand"
	replace jobtype = 2 if greenoccupationalcategory == "Green Enhanced Skills"
	replace jobtype = 3 if greenoccupationalcategory == "Green New & Emerging"
 gen greentask = (greentask != "")
save Rtaskfrequency.dta, replace


**************************************************************************
** Table 10, Table 3, Table 4, Table 5, Table 16
**************************************************************************

* Table 10: Mean number of linked jobs listed in each data file, by job type (SD in brackets)
use Ronetmain.dta, clear

tab jobtype

forvalues j = 0/2 {
forvalues i = 0/2 {
sum sumsr`i' if greenjob == `j'
sum sumcr`i' if greenjob == `j'
}
}

* Table 3: Share of green tasks (green jobs only)
use Rtaskfrequency.dta, clear
	egen totaltasks = count(taskid) if scaleid != "IM" & scaleid != "RT", by(onetsoccode)
	egen totalgreen = total(greentask == 1) if scaleid != "IM" & scaleid != "RT", by(onetsoccode)
	gen pctgreen = totalgreen / totaltasks

preserve
	keep if pctgreen != .
	sort onetsoccode
	qui by onetsoccode: gen dup = cond(_N == 1, 0, _n)
	drop if dup > 1
	drop dup

	tab jobtype
 forvalues i = 1/3 {
	sum pctgreen if jobtype == `i' 
 }
 forvalues i = 1/3 {
	sum totalgreen if jobtype == `i' 
 }
restore

* Table 4: Share of green tasks, by skill level 
	* Note: code for Table 3 needs to be run before this code 
	* ii) skill level not available for some jobs
	
preserve
	keep if pctgreen != .
	sort onetsoccode
	qui by onetsoccode: gen dup = cond(_N == 1, 0, _n)
	drop if dup > 1
	drop dup

 	tab jobtype
 forvalues i = 2/3 {
 forvalues j = 1/3 {
 	sum pctgreen if jobtype == `i' & skill == `j'
 }
 }
restore

* Table 5: Comparison of task content across green subcategories 
	* Note: all values are rescaled to be on a 1-5 scale 
 gen rescaled = maxfreq if scaleid == "IM"
	replace rescaled = datavalue if scaleid == "IM" & maxfreq == .
	replace rescaled = maxfreq/20 if scaleid == "RT"
	replace rescaled = datavalue/20 if scaleid == "RT" & maxfreq == .
	replace rescaled = maxfreq/(1.4) if scaleid == "FT"

 forvalues i = 1/3 {
 forvalues j = 0/1 {
	sum rescaled if scaleid == "FT" & jobtype == `i' & greentask == `j'
	sum rescaled if scaleid == "IM" & jobtype == `i' & greentask == `j'
	sum rescaled if scaleid == "RT" & jobtype == `i' & greentask == `j'
 }
 }

* Table 6: Mean skill level, by job type
preserve
	keep if skill != . & jobtype != .
	sort onetsoccode
	qui by onetsoccode: gen dup = cond(_N == 1, 0, _n)
	drop if dup > 1
	drop dup

 forvalues i = 0/4 {
	sum skill if jobtype == `i'
 }
restore

* Table 16: Frequency of core and supplemental tasks (green jobs only)
	* Note: Non-core = supplemental or n/a
 gen core = (tasktype == "Core")
 gen roundfreq = round(maxfreq) if scaleid == "FT"
	* Note: Some frequencies are not integer because there was more than one mode
 forvalues i = 2/3 {
 forvalues j = 0/1 {
	tab roundfreq if core == 0 & jobtype == `i' & greentask == `j'
	tab roundfreq if core == 1 & jobtype == `i' & greentask == `j'
 }
 }
save Rtaskfrequency.dta, replace 


/****************/
/* Constructing standard skills dataset */
* Education, Training, and Experience.txt - saved as standardskills.dta
* Standard skill measures used: Years of education (2.D.1), Years of experience (3.A.1), Years of training (3.A.3)

use standardskills.dta, clear
egen maxfreq = max(datavalue), by(onetsoccode elementid)
    drop if maxfreq != datavalue
	bysort onetsoccode elementid: gen count = _N
	egen meanfreq = mean(category) if count > 1, by(onetsoccode elementid)
		* some tasks have more than 1 mode - take the average of modes
	replace maxfreq = meanfreq if count > 1
	replace maxfreq = category if count == 1
	bysort onetsoccode elementid: gen index = _n
	drop if index > 1
	drop count meanfreq index
	drop if elementid == "3.A.2" /* On-site of in-plant training (similar to on-the-job training) */
save Rstandardskills.dta, replace

**************************************************************************
** Table 7
**************************************************************************
* Table 7: Summary statistics of 3 standard skill measures
use Rstandardskills.dta, clear
	mmerge onetsoccode using greencategory.dta, type(n:1) 
	drop _merge 
	mmerge onetsoccode using Rjobzone.dta, type(n:1) ukeep(skill)
	drop _merge
	mmerge onetsoccode using Ronetlist.dta, type(n:1) 
	drop _merge
	cap drop root occupationcode 
	replace jobtype = 1 if greenoccupationalcategory == "Green Increased Demand"
	replace jobtype = 2 if greenoccupationalcategory == "Green Enhanced Skills"
	replace jobtype = 3 if greenoccupationalcategory == "Green New & Emerging"

global vars "2.D.1 3.A.1 3.A.3"
 foreach N of global vars {
 forvalues i = 0/4 {
	sum maxfreq if elementid == "`N'" & jobtype == `i'
 }
 }
save Rstandardskills.dta, replace


/********************/
/* Measure of skill distance */
* Work Activities.txt - saved as activities.dta (contains activities with prefix "4.A.")
* Work Context.txt - saved as context.dta (contains activities with prefix "4.C.")
* Abilities.txt - saved as abilities.dta (contains activities with prefix "1.A.")

* Non-routine analytical (3): 4.A.2.a.4, 4.A.2.b.2, 4.A.4.a.1
* Non-routine interactive (3): 4.A.4.a.4, 4.A.4.b.4, 4.A.4.b.5
* Routine cognitive (3): 4.C.3.b.4, 4.C.3.b.7, 4.C.3.b.8 (CX, reverse)
* Routine manual (3): 4.A.3.a.3, 4.C.2.d.1.i, 4.C.3.d.3
* Non-routine manual (4): 4.A.3.a.4, 4.C.2.d.1.g, 1.A.2.a.2, 1.A.1.f.1
* Routine index (RTI): log(1 + 4.5*RC + 4.5*RM) - log(1 + 4.5*NRA + 4.5*NRI)
* NOTE: Only need task importance ("IM") data, or context ("CX") data

use activities.dta, clear
	keep if scaleid == "IM"
save Ractivities.dta, replace

use context.dta, clear
	keep if category == "n/a"
	drop category
save Rcontext.dta, replace

use abilities.dta, clear
	keep if scaleid == "IM"
save Rabilities.dta, replace

use Ractivities.dta, clear
append using Rcontext.dta
append using Rabilities.dta

	replace datavalue = 5-datavalue if elementid == "4.C.3.b.8"

 gen keep = .
global items "4.A.2.a.4 4.A.2.b.2 4.A.4.a.1 4.A.4.a.4 4.A.4.b.4 4.A.4.b.5  4.C.3.b.4 4.C.3.b.7 4.C.3.b.8  4.A.3.a.3 4.C.2.d.1.i 4.C.3.d.3 4.A.3.a.4 4.C.2.d.1.g 1.A.2.a.2 1.A.1.f.1"
 foreach N of global items {
	replace keep = 1 if elementid == "`N'"
 }

	drop if keep != 1
	cap drop keep
save Rskills.dta, replace

use Rskills.dta, clear
 global NRA "4.A.2.a.4 4.A.2.b.2 4.A.4.a.1"
 global NRI "4.A.4.a.4 4.A.4.b.4 4.A.4.b.5"
 global RC "4.C.3.b.4 4.C.3.b.7 4.C.3.b.8"
 global RM "4.A.3.a.3 4.C.2.d.1.i 4.C.3.d.3"
 global NRM "4.A.3.a.4 4.C.2.d.1.g 1.A.2.a.2 1.A.1.f.1"

 gen skilltype = ""
 foreach N of global NRA {
	replace skilltype = "NRA" if elementid == "`N'"
 }
 foreach N of global NRI {
	replace skilltype = "NRI" if elementid == "`N'"
 }
 foreach N of global RC {
	replace skilltype = "RC" if elementid == "`N'"
 }
 foreach N of global RM {
	replace skilltype = "RM" if elementid == "`N'"
 }
 foreach N of global NRM {
	replace skilltype = "NRM" if elementid == "`N'"
 }

	egen meanimportance = mean(datavalue), by(onetsoccode skilltype)
	sort onetsoccode skilltype
	qui by onetsoccode skilltype: gen dup = cond(_N == 1, 0, _n)
	drop if dup > 1
	drop dup
	keep onetsoccode skilltype meanimportance
save Rskills.dta, replace

use Ronetmain.dta, clear
	mmerge onetsoccode using Rskills.dta, type(n:n)
	drop if _merge == 2
	drop _merge
	rename meanimportance meanimp_main

	rename onetsoccode var1
	rename careerstarters onetsoccode 
   mmerge onetsoccode skilltype using Rskills.dta, type(n:1)
   	drop if _merge == 2
   	drop _merge
   	rename meanimportance meanimp_starters
   	rename onetsoccode careerstarters
   	rename careerchangers onetsoccode 
   mmerge onetsoccode skilltype using Rskills.dta, type(n:1)
   	drop if _merge == 2
   	drop _merge
   	rename onetsoccode careerchangers
   	rename var1 onetsoccode
    rename meanimportance meanimp_changers
   mmerge onetsoccode using Rjobzone.dta, type(n:1) ukeep(skill)
   	drop if _merge == 2
	drop _merge
save Ronetmain_skills.dta, replace

use Ronetmain_skills.dta, clear
 global skilltypes "NRA NRI RC RM NRM"
 foreach N of global skilltypes {
 gen var1 = meanimp_main if skilltype == "`N'"
	sort onetsoccode
	by onetsoccode: carryforward var1, gen(`N')
	gsort -onetsoccode
	carryforward `N', back replace
	drop var1
 } 
 foreach N of global skilltypes {
 gen var1 = meanimp_changers if skilltype == "`N'"
	sort careerchangers
	by careerchangers: carryforward var1, gen(`N'1)
	gsort -careerchangers
	carryforward `N'1, back replace
	drop var1
 } 
 foreach N of global skilltypes {
 gen var1 = meanimp_starters if skilltype == "`N'"
	sort careerstarters
	by careerstarters: carryforward var1, gen(`N'2)
	gsort -careerstarters
	carryforward `N'2, back replace
	drop var1
 } 
save Ronetmain_skills.dta, replace

* Normalising to be between 0 & 1 
 foreach N of global skilltypes {
	replace `N' = `N'/5
	replace `N'1 = (`N'1)/5
	replace `N'2 = (`N'2)/5
 }
	
	* Routine Task Intensity index (1 = changers, 2 = starters)
	gen RTI = ln(1 + 4.5*RC + 4.5*RM) - ln(1 + 4.5*NRA + 4.5*NRI)
	gen RTI1 = ln(1 + 4.5*RC1 + 4.5*RM1) - ln(1 + 4.5*NRA1 + 4.5*NRI1)
	gen RTI2 = ln(1 + 4.5*RC1 + 4.5*RM1) - ln(1 + 4.5*NRA1 + 4.5*NRI1)
	
	* Skill distance measure
	gen skilldistancec = (abs(NRA-NRA1)+abs(NRI-NRI1)+abs(RC-RC1)+abs(RM-RM1)+abs(NRM-NRM1)+abs(RTI-RTI1))/6
	gen skilldistances = (abs(NRA-NRA2)+abs(NRI-NRI2)+abs(RC-RC2)+abs(RM-RM2)+abs(NRM-NRM2)+abs(RTI-RTI2))/6
	
	* Transition type (which jobtype to which jobtype)
	gen transitions = ""
	gen transitionc = ""
	forvalues i = 0/4 {
	forvalues j = 0/4 {
	replace transitions = "`i'`j'" if jobtype == `i' & jobtypestarters == `j'
	replace transitionc = "`i'`j'" if jobtype == `i' & jobtypechangers == `j'
	}
	}
save Ronetmain_skills.dta, replace

**************************************************************************
** Table 17, Table 18, Table 8, Table 19, Table 20, Table 21, Table 22
**************************************************************************
* Table 17: Autor, Levy, Murnane (2003) Skill content measures 
* Table 18: Autor and Dorne (2013) Routine Task Intensity Index, by job type
use Ronetmain_skills.dta, clear
preserve
	sort onetsoccode
	qui by onetsoccode: gen dup = cond(_N == 1, 0, _n)
	drop if dup > 1
	drop dup

 forvalues i = 0/4 {
	sum RTI if jobtype == `i'
 }


 foreach N of global skilltypes {
 forvalues i = 0/4 {
	sum `N' if jobtype == `i'
 }
 }
restore

* Table 8: Mean skill distance, according to type of job transition 
use Ronetmain_skills.dta, clear
preserve
	sort onetsoccode index
	qui by onetsoccode index: gen dup = cond(_N == 1, 0, _n)
	drop if dup > 1
	drop dup
	* Non-green to green transitions
 global vars "01 02 03"
 foreach N of global vars {
	sum skilldistancec if transitionc == "`N'"
	sum skilldistances if transitions == "`N'"
 }
	* Green to green transitions
 forvalues j = 1/3 {
	sum skilldistancec if greenjob == 1 & jobtype == `j'
	sum skilldistances if greenjob == 1 & jobtype == `j'
 }
restore

* Table 19: Mean skill distance between job types, by skill level (career changers)
* Table 20: Mean skill distance between job types, by skill level (career starters)
use Ronetmain_skills.dta, clear
preserve
	keep if careerchangers != ""
	sort onetsoccode index
	qui by onetsoccode index: gen dup = cond(_N == 1, 0, _n)
	drop if dup > 1
	drop dup

 global vars "00 01 02 03 04 10 11 12 13 14 20 21 22 23 24 30 31 32 33 34 40 44"
 forvalues i = 1/3 {
 foreach N of global vars {
	sum skilldistancec if skill == `i' & transitionc == "`N'"
 }
 }
 forvalues i = 1/3 {
 foreach N of global vars {
	sum skilldistances if skill == `i' & transitions == "`N'"
 }
 }
	tab transitionc skill if careerchangers != ""
	tab transitions skill
restore 

* Table 21: Mean skill distance, by skill level and sector (career changers)
* Table 22: Mean skill distance, by skill level and sector (career starters)
clear
infix str areatypecode 4 areacode 5-11 industrycode 12-17 occupationcode 18-23 datatypecode 24-25 year 32-36 value 50-60 footnotecode 61-70 using oe.data.1.AllData.txt
	* Datatype code 1 = employment (rounded to nearest 10)
	* footnotecode == 8 : Estimate not released; == 5: high wage occupation (greater than $187,199 per year/$90 per hour)
save Rbls.dta, replace

use Rbls.dta, replace
	keep if areatypecode == "N" /* National data */
	keep if datatypecode == 1

	drop if occupationcode == 0 /* all occupations */
	drop if industrycode == . 
	tostring occupationcode, gen(occupationcode2)
	gen root4 = substr(occupationcode2,3,4)
	gen root3 = substr(occupationcode2,4,3)
	gen root0 = substr(occupationcode2,6,1)
	drop if root4 == "0000"
	drop if root3 == "000"
	drop if root0 == "0"

	* Sectorcode: combines sector codes of BLS 
	nsplit industrycode, digits(2 4) generate(sectorcode other)

 gen sector = .
	replace sector = 0 if sectorcode == 0 
	replace sector = 1 if sectorcode == 11
	replace sector = 2 if sectorcode == 21 
	replace sector = 3 if sectorcode == 22
	replace sector = 4 if sectorcode == 23 
	replace sector = 5 if sectorcode == 31 | sectorcode == 32 | sectorcode == 33 
	replace sector = 6 if sectorcode == 42 
	replace sector = 7 if sectorcode == 44 | sectorcode == 45 
	replace sector = 8 if sectorcode == 48 | sectorcode == 49 
	replace sector = 9 if sectorcode == 51 
	replace sector = 10 if sectorcode == 52
	replace sector = 11 if sectorcode == 53 
	replace sector = 12 if sectorcode == 54 
	replace sector = 13 if sectorcode == 55
	replace sector = 14 if sectorcode == 56
	replace sector = 15 if sectorcode == 61 
	replace sector = 16 if sectorcode == 62
	replace sector = 17 if sectorcode == 71 
	replace sector = 18 if sectorcode == 72
	replace sector = 19 if sectorcode == 81 
	replace sector = 20 if sectorcode == 99 

save Rbls_sector.dta, replace 

use Ronetmain_skills.dta, clear
	sort onetsoccode index
	qui by onetsoccode index: gen dup = cond(_N == 1, 0, _n)
	drop if dup > 1
	drop dup

	split onetsoccode , parse("-" ".")
	gen root = "0000"
	egen occupationcode = concat(onetsoccode1 onetsoccode2) /* First 6 digits of 8-digit code */
	destring occupationcode, replace force
   drop onetsoccode1 onetsoccode2 onetsoccode3
   
	mmerge occupationcode using Rbls_sector.dta, type(n:n) ukeep(sector)
	drop if _merge == 2
	sort onetsoccode index sector
	qui by onetsoccode index sector: gen dup = cond(_N == 1, 0, _n)
	drop if dup > 1

 forvalues i = 0/20 {
 forvalues j = 1/3 {
	sum skilldistancec if sector == `i' & skill == `j'
 }
 }
	* Overall mean
 forvalues i = 0/20 {
	sum skilldistancec if sector == `i'
	sum skilldistances if sector == `i'
 }

 forvalues i = 0/20 {
 forvalues j = 1/3 {
	sum skilldistances if sector == `i' & skill == `j'
 }
 }
clear

**************************************************************************
** Table 15, Table 1, Table 2 
**************************************************************************
/* Estimating green employment */
* Table 15: State and national green employment shares, ranked by total green employment

use Rbls.dta, clear
	keep if areatypecode == "S"
	keep if datatypecode == 1	/* 37,717 observations */
save Rbls_state.dta, replace

use Ronetlist.dta, clear
	split onetsoccode , parse("-" ".")
	gen root = "0000"
	egen occupationcode = concat(onetsoccode1 onetsoccode2) /* First 6 digits of 8-digit code */
	destring occupationcode, replace force
   drop onetsoccode1 onetsoccode2 onetsoccode3
save Ronetlist.dta, replace 

* lower bound on employment (assume missing data are 'other' jobs)
use Rbls_state.dta, clear
	drop if occupationcode == 0 /* These are aggregate data for all occupations */
	tostring occupationcode, gen(occupationcode2)
	gen root = substr(occupationcode2,3,4)
	
	preserve
	keep if root == "0000"
	egen workforce = total(value), by(areacode)
		* Total employment will be weakly greater than the sum in each category because of missing data
		* at the disaggregated level, and rounding (employment only given to the nearest 10)
	sort areacode
	qui by areacode: gen dup = cond(_N == 1, 0, _n)
    drop if dup > 1
   	drop dup
   save Rtotalemp.dta, replace
   	restore
   	
	mmerge occupationcode using Ronetlist.dta, type(n:n) ukeep(jobtype)
	drop if _merge == 2
	drop _merge

	drop if root == "0000" /* these are aggregated data on all occupations in that category */
	
	bysort areacode occupationcode: gen num = _N
   	bysort areacode occupationcode jobtype: gen num1 = _N
   	
	sort areacode occupationcode jobtype 
	qui by areacode occupationcode jobtype: gen dup = cond(_N == 1, 0, _n)
    drop if dup > 1
   	drop dup
   	
   	gen employment = round((num1/num)*value)	/* employment by state, occupation, jobtype */
	egen temployment = total(employment), by(areacode jobtype)

	sort areacode jobtype 
	qui by areacode jobtype: gen dup = cond(_N == 1, 0, _n)
    drop if dup > 1
   	drop dup
   	
   	merge m:1 areacode using Rtotalemp.dta, keepusing(workforce)
	gen share = (temployment/workforce)*100
outsheet workforce temployment areacode jobtype share using Rbls_lowerbound.csv, comma replace

	 
* Table 1: Employment shares by sector, ranked by total green employment
use Rbls.dta, clear
	keep if areatypecode == "N" /* National data */
	keep if datatypecode == 1

	drop if occupationcode == 0 /* all occupations */
	drop if industrycode == . 
	tostring occupationcode, gen(occupationcode2)
	gen root4 = substr(occupationcode2,3,4)
	gen root3 = substr(occupationcode2,4,3)
	gen root0 = substr(occupationcode2,6,1)
	drop if root4 == "0000"
	drop if root3 == "000"
	drop if root0 == "0"

	* Sectorcode: combines sector codes of BLS 
	nsplit industrycode, digits(2 4) generate(sectorcode other)
	
 gen sector = .
	replace sector = 0 if sectorcode == 0 
	replace sector = 1 if sectorcode == 11
	replace sector = 2 if sectorcode == 21 
	replace sector = 3 if sectorcode == 22
	replace sector = 4 if sectorcode == 23 
	replace sector = 5 if sectorcode == 31 | sectorcode == 32 | sectorcode == 33 
	replace sector = 6 if sectorcode == 42 
	replace sector = 7 if sectorcode == 44 | sectorcode == 45 
	replace sector = 8 if sectorcode == 48 | sectorcode == 49 
	replace sector = 9 if sectorcode == 51 
	replace sector = 10 if sectorcode == 52
	replace sector = 11 if sectorcode == 53 
	replace sector = 12 if sectorcode == 54 
	replace sector = 13 if sectorcode == 55
	replace sector = 14 if sectorcode == 56
	replace sector = 15 if sectorcode == 61 
	replace sector = 16 if sectorcode == 62
	replace sector = 17 if sectorcode == 71 
	replace sector = 18 if sectorcode == 72
	replace sector = 19 if sectorcode == 81 
	replace sector = 20 if sectorcode == 99 
	* no missing values
 	
	nsplit industrycode, digits(3 3) generate(subsectorcode ending)
	keep if ending == 0

 egen totalemp = total(value), by(sector)
	drop if industrycode == 0 /* aggregated data for all industries (sector = 0) */

	mmerge occupationcode using Ronetlist.dta, type(n:n) ukeep(jobtype)
	drop if _merge == 2
	drop _merge
	
   	bysort industrycode occupationcode: gen num = _N
   	bysort industrycode occupationcode jobtype: gen num1 = _N
	sort industrycode occupationcode jobtype 
	qui by industrycode occupationcode jobtype: gen dup = cond(_N == 1, 0, _n)
    drop if dup > 1
   	drop dup
   	
 gen employment = round((num1/num)*value)	/* employment by industry, occupation, jobtype */
 egen temployment = total(employment), by(jobtype sector)
	sort sector jobtype 
	qui by sector jobtype: gen dup = cond(_N == 1, 0, _n)
    drop if dup > 1
   	drop dup
   	
 gen share = (temployment/totalemp)*100
	sort sector
outsheet sector totalemp temployment jobtype share using Rbls_sector.csv, comma replace 


/* Wages */
* Table 2: National annual wage quartiles (USD), by skill-level and job subcategory 
use Rjobzone.dta, clear
	split onetsoccode , parse("-" ".")
 egen occupationcode = concat(onetsoccode1 onetsoccode2) /* First 6 digits of 8-digit code */
	destring occupationcode, replace force
   cap drop onetsoccode1 onetsoccode2 onetsoccode3
save Rjobzone.dta, replace

use greencategory.dta, clear
	split onetsoccode , parse("-" ".")
 egen occupationcode = concat(onetsoccode1 onetsoccode2) /* First 6 digits of 8-digit code */
	destring occupationcode, replace force
   cap drop onetsoccode1 onetsoccode2 onetsoccode3
save Rgreencategory.dta, replace

use Rbls.dta, clear
 gen tokeep = (datatypecode == 12 | datatypecode == 13 | datatypecode == 14) /* Wage quartiles */
	drop if tokeep != 1
	keep if areacode == 0 /* National level */
	drop if industrycode == 0 /* All industries */
	drop if industrycode == 1
	drop if industrycode == .
	drop if occupationcode == 0 /* All occupations */

	tostring occupationcode, gen(occupationcode2)
 gen root4 = substr(occupationcode2,3,4)
 gen root3 = substr(occupationcode2,4,3)
 gen root0 = substr(occupationcode2,6,1)
	drop if root4 == "0000"
	drop if root3 == "000"
	drop if root0 == "0"

	sort occupationcode datatypecode
	mmerge occupationcode using Rgreencategory.dta, type(n:n)
	drop _merge
	mmerge occupationcode using Rjobzone.dta, type(n:n) ukeep(skill)
	drop _merge
	mmerge occupationcode using Ronetlist.dta, type(n:n) ukeep(jobtype) 
	drop _merge
	*cap drop root occupationcode 
	replace jobtype = 1 if greenoccupationalcategory == "Green Increased Demand"
	replace jobtype = 2 if greenoccupationalcategory == "Green Enhanced Skills"
	replace jobtype = 3 if greenoccupationalcategory == "Green New & Emerging"	
	
	sort industrycode occupationcode datatypecode jobtype skill
	qui by industrycode occupationcode datatypecode jobtype skill: gen dup = cond(_N == 1, 0, _n)
    drop if dup > 1
   	drop dup

	* Assume that missing data belongs to 'other' category (as with employment estimates)
 gen jobtype2 = jobtype
	replace jobtype2 = 4 if jobtype == .	

* Mean wage (unweighted - just a simple average)
 forvalues j = 0/4 {
 forvalues i = 1/3 {
	sum value if skill == `i' & jobtype == `j'& datatypecode == 12
	sum value if skill == `i' & jobtype == `j'  & datatypecode == 13
	sum value if skill == `i' & jobtype == `j'  & datatypecode  == 14
 }
 }

**************************************************************************
** Table 9, Figure 3, Table 11, Table 12
**************************************************************************
/* Career paths */

* Table 9: Number of times green jobs listed as similar to green rival jobs, by job subcategory
use Ronetmain.dta, clear 
	tab jobtypechangers if jobtype == 0
	tab jobtypestarters if jobtype == 0

	tab jobtypechangers if jobtype == 4
	tab jobtypestarters if jobtype == 4
	* Note: These are both zero 
	
* Network analysis 
use Ronetmain.dta, clear
	keep if careerchangers != ""
 nwset onetsoccode careerchangers, name(onet) edgelist directed keeporiginal 
 nwsummarize onet

 nwgeodesic onet /* lists the shortest path from all nodes to each other */
outsheet _nodeid _nodeoriginal _geo* using network.csv, comma replace

* Figure 3: Visual representation for career changers
 nwplot onet if _n >= 157 & _n <= 171

* Table 11: Average length of shortest path between any 2 given jobs, by job type
* Note: Run this before running the R code. The final table is made in R.
use Ronetmain.dta, clear
 nwset onetsoccode careerstarters, name(onetstarters) edgelist directed keeporiginal
 nwgeodesic onetstarters /* lists the shortest path from all nodes to each other */
outsheet _nodeid _nodeoriginal _geo* using networkstarters.csv, comma replace


* Table 12: Percentage frequency of path length to any green job
use Ronetmain.dta, clear

 gen shortestc = .
	replace shortestc = 1 if sumcr1 != 0 & sumcr1 != . & greenjob == 0

preserve
	keep onetsoccode sumsr0 sumsr1 sumsr2 sumcr0 sumcr1 sumcr2
	sort onetsoccode
	qui by onetsoccode: gen dup = cond(_N == 1, 0, _n)
    drop if dup > 1
   	drop dup
save numgreenjobs.dta, replace
restore

 global vars "sumsr0 sumsr1 sumsr2 sumcr0 sumcr1 sumcr2"
 foreach N of global vars {
	rename `N' `N'_1
 }
	rename onetsoccode var1
	rename careerchangers onetsoccode
 mmerge onetsoccode using numgreenjobs.dta, type(n:1)
	drop if _merge == 2
	drop _merge

	replace shortestc = 2 if sumcr1 != 0 & sumcr1 != . & jobtypechangers == 0 & greenjob == 0 & shortestc == .
	egen minshortestc = min(shortestc), by(var1)
	replace minshortestc = 3 if minshortestc == . & greenjob == 0

tab minshortestc
	* Note: Since there are 10 entries per job, divide the numbers by 10 to get actual frequencies. 
	* The percentages are the same.

use Ronetmain.dta, clear
 gen shortests = .
	replace shortests = 1 if sumsr1 != 0 & sumsr1 != . & greenjob == 0

 global vars "sumsr0 sumsr1 sumsr2 sumcr0 sumcr1 sumcr2"
 foreach N of global vars {
	rename `N' `N'_1
 }
	rename onetsoccode var1
	rename careerstarters onetsoccode
 mmerge onetsoccode using numgreenjobs.dta, type(n:1)
	drop if _merge == 2
	drop _merge

	replace shortests = 2 if sumsr1 != 0 & sumsr1 != . & jobtypestarters == 0 & greenjob == 0 & shortests == .
	egen minshortests = min(shortests), by(var1)
	replace minshortests = 3 if minshortests == . & greenjob == 0

tab minshortests

use Ronetmain.dta, clear
 gen shortestco = .
	replace shortestco = 1 if sumcr1 != 0 & sumcr1 != . & greenjob == 2

 global vars "sumsr0 sumsr1 sumsr2 sumcr0 sumcr1 sumcr2"
 foreach N of global vars {
	rename `N' `N'_1
 }
	rename onetsoccode var1
	rename careerchangers onetsoccode
 mmerge onetsoccode using numgreenjobs.dta, type(n:1)
	drop if _merge == 2
	drop _merge

	replace shortestco = 2 if sumcr1 != 0 & sumcr1 != . & jobtypechangers == 0 & greenjob == 2 & shortestco == .
	egen minshortestco = min(shortestco), by(var1)
	replace minshortestco = 3 if minshortestco == . & greenjob == 2

tab minshortestco

use Ronetmain.dta, clear
 gen shortestso = .
	replace shortestso = 1 if sumsr1 != 0 & sumsr1 != . & greenjob == 2

 global vars "sumsr0 sumsr1 sumsr2 sumcr0 sumcr1 sumcr2"
 foreach N of global vars {
	rename `N' `N'_1
 }
	rename onetsoccode var1
	rename careerstarters onetsoccode
 mmerge onetsoccode using numgreenjobs.dta, type(n:1)
	drop if _merge == 2
	drop _merge

	replace shortestso = 2 if sumsr1 != 0 & sumsr1 != . & jobtypestarters == 0 & greenjob == 2 & shortestso == .
	egen minshortestso = min(shortestso), by(var1)
	replace minshortestso = 3 if minshortestso == . & greenjob == 2

tab minshortestso