***** Stats Workshop 1: Managing Data *****
* March 31, 2008

* Commands covered: insheet, append, reshape, merge


*** Importing a Spreadsheet

insheet using unions1.csv, comma names 	/*comma-separated-values files can be generated by Excel*/

rename year country
label var country ""

forvalues a = 2/44 {      				/*Loop from 2 to 44, using i as the counter*/
	local year = 1958 + `a'				/*Generate the right year for each loop*/
	rename v`a' uden`year'				/*Rename each variable with the right year*/
}

save unions1.dta



insheet using unions2.csv, comma names 	/*Repeat for second spreadsheet*/

rename year country
label var country ""

forvalues a = 2/44 {      		
	local year = 1958 + `a'		
	rename v`a' uden`year'		
}

save unions1.dta


*** Appending Additional Observations 
append using unions2.dta				/*Append the observations from the file unions2.dta*/

save unions.dta, replace


*** Reshaping the Data
reshape long uden, i(country)	j(year)	/*Change uden from wide to long format*/


sort country year
save unions.dta, replace


*** Merging the New Variables
use xwvsevs_1981_2000_v20060423.dta, clear

tab s003								/*We need a country variable*/
tab s003, nolabel

decode s003, generate(country)
tab country
replace country=proper(country)

gen year=s020

sort country year  
save xwvs.dta, replace

merge country year using unions.dta, keep(uden)

tab _merge

tab country if _merge==3, sum(uden)		/*Korea? Mexico? Slovak Republic? UK?*/
tab country

clear
use xwvs.dta

replace country = "Korea" if country=="Republic Of Korea"
replace country = "Slovak Republic" if country=="Slovakia"
replace country = "United Kingdom" if country=="Great Britain"
replace country = "United Kingdom" if country=="Northern Ireland"

sort country year
merge country year using unions.dta, keep(uden)
tab _merge
tab country if _merge==3, sum(uden)

save xwvs.dta, replace