program define makepanel, rclass
*!	program makepanel    version 1.0    3May2018  by Mead Over
	syntax newvarlist using , saving(string) [clear replace yrsprior(real 10) file_w(string)  sy(string)]

	*	Create a panel data set from the following file
	use `using', `clear'

	if "`sy'"~="" {
		rename `sy' sy
	}

	capture confirm variable file_w
	if _rc~=0 {
		if "`file_w'"~="" {
			gen str file_w = "`file_w'"
		}
		else {
			local ntkns = 0
			tokenize `"`using'"'
			while "`i'" ~= "" {
				local ntkns = `ntkns' + 1
			}
			gen str file_w = "`ntkns'"
			di as txt "Since the variable file_w is not in the -using gfile-"  ///
				_n "user did not specify the file identifier in the option -file_w(string)-,"  ///
				_n "the file identifiere becomes the name of the -using-file,"  ///
				_n "which is: " as res "`ntkns'"
		}
	}

	encode file_w, gen(fcode)
		label var fcode "Encoded file identifier"
	encode country, gen(ccode)
		label var ccode "Encoded country"

	sort ccode sy
	qui sum `varlist' if ccode==ccode[_n-1] & sy==sy[_n-1] |  ccode==ccode[_n+1] & sy==sy[_n+1]
*	count if ccode==ccode[_n-1] & sy==sy[_n-1] |  ccode==ccode[_n+1] & sy==sy[_n+1]

	*	For four countries there are two different annual observations
	*	So we must collapse the original data to have one observation
	*	per country-year
*	list file_w ccode sy `varlist' if ccode==ccode[_n-1] & sy==sy[_n-1] |  ccode==ccode[_n+1] & sy==sy[_n+1]

	tempvar dupsy
	qui clonevar `dupsy' = sy

	//	By running -retainlbl-, we don't need to retype all the variable labels
	retainlbl fcode `varlist', store

	collapse (count) nobs=`dupsy' (mean) fcode `varlist'  ///
		(first) firstfile=fcode (last) lastfile=fcode, by(ccode sy)

	retainlbl fcode `varlist', restore

	recast long fcode, force

	lab var nobs "Number of surveys in single calendar year"
	tab nobs, mi

	qui compress

	label values fcode fcode
	label values firstfile fcode
	label values lastfile fcode

*	list fcode firstfile lastfile ccode sy if firstfile~=lastfile

	tempvar f1 f2
	decode fcode, gen(file_w)
	decode firstfile, gen(`f1')
	decode lastfile, gen(`f2')
	qui replace file_w = `f1' + " & " + `f2' if firstfile~=lastfile

*	list file_w firstfile lastfile ccode sy if firstfile~=lastfile

	drop firstfile lastfile fcode
	order file_w ccode sy

	tsset ccode sy
	tempvar yrnonmsng
	qui gen `yrnonmsng' = 0

	levelsof ccode
	local lvls `r(levels)'

	tempfile vbyv
	local firstsave yes
	foreach v of varlist `varlist' {
		di _n as txt "************************************"  ///
			_n "For the variable " as res "`v'"

		foreach c of numlist `lvls' {
			local cname : label ccode `c'
			di _n as txt "For the country " as res "`cname'"

			qui replace `yrnonmsng' =  (ccode==`c' & `v' < . )
			sum sy if `yrnonmsng', meanonly
				if `r(N)'>0 {
					local mostrecent = `r(max)'
					di as txt "  the most recent year observed is: " as res "`mostrecent'"
					sum sy if `yrnonmsng' & sy < `mostrecent'-`yrsprior'-1, meanonly
					if `r(N)'>0 {
						local mostrecentprior = `r(max)'
						di as txt "  and the most recent year at least " as res "`yrsprior'"  ///
							as txt " years prior is: " as res "`mostrecentprior'"

						preserve
							keep file_w ccode sy nobs `v'
							keep if ccode==`c' & (sy == `mostrecent' | sy==`mostrecentprior')

							gen byte earlylate = 1 if sy==`mostrecentprior'
							replace  earlylate = 2 if sy==`mostrecent'

							gen long `v'sy = `mostrecentprior' if sy==`mostrecentprior'
							replace  `v'sy = `mostrecent'     if sy==`mostrecent'
								lab var `v'sy "Survey year for variable: `v'"

							compress
							if "`firstsave'"=="yes" {
								sort ccode earlylate
								save `vbyv', replace
								local firstsave no
							}
							else {
								sort ccode earlylate
								merge 1:1 ccode earlylate using `vbyv'
									tab _merge
									drop _merge
								save `vbyv', replace
							}
							local newvarlist `newvarlist' `v' `v'sy
*							local sylist `sylist' `v'sy
							des, f
							sum
						restore
					}
					else {
						di as txt "  and there is no survey at least " as res "`yrsprior'" as txt " years prior
					}
				}
				else {
					di as txt "there are no observations in any survey year."
				}

			qui replace `yrnonmsng' =  0
		}
		qui replace `yrnonmsng' =  0
*		local newvarlist `newvarlist' `v' `v'sy
		local sylist `sylist' `v'sy
	}

	clear
	use `vbyv', clear

	set trace off
	di _n as txt "List of survey year variables:"
	des `sylist', f
	egen symean = rowmean(`sylist')
		lab var symean "Mean survey year"
	local nvars = wordcount("`sylist'")
	if `nvars'>1 {
		egen sysd = rowsd(`sylist')
	}
	else {
		gen sysd = .
	}
		lab var sysd "SD of survey year"
	rename sy mrsy
		label var mrsy "Most recent earlier/later survey year among variables in this data file"

	compress

	label define earlylate 1 "Earlier" 2 "Later"
	label values earlylate earlylate
	label var earlylate "Earlier or later survey year, by country"

	label data "Two years of survey data on variables `varlist'"
	order `newvarlist', alphabetic
	order file_w ccode earlylate mrsy symean sysd nobs , first
	sort ccode earlylate
	tsset ccode earlylate
	save `saving', `replace'

	return local savefile `saving'
end /* end of program makepanel  */
*	Version 1.1	23May2018	Adds options for file_w, and sy(string)
*		Allows only a single variable to be extracted from the processed data file.