Thanks for the reply. Below a code and at the end i commented one (two) issue.
Code:
clear
*input dataset
input int year quarter str34 origin long(totalpersonspendingstartyear appliedduringyear rejected)
2003 1 "China" 0 4 0
2003 2 "China" 4 0 0
2003 3 "China" 4 2 0
2003 4 "China" 2 0 0
2003 1 "Japan" 1 1 1
2003 2 "Japan" 1 1 1
2003 3 "Japan" 1 1 1
2003 4 "Japan" 1 1 1
2004 1 "Japan" 3 1 1
2004 2 "Japan" 1 6 1
2003 3 "Korea" 1 1 1
2003 4 "Korea" 1 1 1
2004 1 "Korea" 1 1 1
2004 2 "Korea" 1 1 1
2005 3 "Taiwan" 1 1 1
2005 4 "Taiwan" 1 1 1
2006 1 "Taiwan" 1 1 1
2006 2 "Taiwan" 1 1 1
2004 2 "Laos" 1 1 1
2004 3 "Laos" 1 1 1
2004 4 "Laos" 1 1 1
2005 1 "Laos" 1 1 1
end
*cleansing
capture noisily drop date_yq
gen date_yq = yq(year, quarter)
format %tq date_yq
*rename variable
rename origin country
*move date_yq
move date_yq country
//your code starts from here
expand 3, gen(expandob) //adding two more versions of every observation currently in your dataset
sort country expandob date_yq //sorting by origin, then whether it's an observation we've just created, then by year
ds date_yq country expandob, not
local toempty `r(varlist)' //storing all the variables that you want to be empty in a local
foreach var of local toempty{
replace `var' = . if expandob == 1 //making these variables empty
}
foreach var of varlist date_yq country{
bysort country: replace date_yq = date_yq[_n-1]+1 if expandob == 1 //replacing the years so they're sequential from the most recent year for each country in your original data
}
drop expandob //dropping the expandob variable
sort country date_yq
*problems:
* - each country should have as end date 2019q3, not different. +
* It is fine if the starting date is different
* - I should fill in also year quarter (easy to do)
Comment