I have the following dataset
A B begin_yr end_yr
asset brown 2007 2010
asset blue 2008 2008
basics caramel 2015 2015
cows dork 2004 2006
I want A and B to have rows for each year represented.
I expanded for each year:
gen x = end_yr - begin_yr
expand x +1
This gives me the following:
A B begin_yr end_yr x
asset brown 2007 2010 3
asset brown 2007 2010 3
asset brown 2007 2010 3
asset brown 2007 2010 3
asset blue 2008 2008 0
basics caramel 2015 2015 0
cows dork 2004 2006 2
Ultimately, I want the following dataset:
A B begin_yr end_yr x year
asset brown 2007 2010 3 2007
asset brown 2007 2010 3 2008
asset brown 2007 2010 3 2009
asset brown 2007 2010 3 2010
asset blue 2008 2008 0 2008
basics caramel 2015 2015 0 2015
cows dork 2004 2006 2 2004
cows dork 2004 2006 2 2005
cows dork 2004 2006 2 2006
This is what I have so far:
gen year = begin_yr if begin_yr!=end_yr
How do I populate the rest of the variable year?
Here's a twist building on @Pearly Spencer's code:
clear
input strL A strL B begin_yr end_yr
asset brown 2007 2010
basics caramel 2015 2015
cows dork 2004 2006
end
gen toexpand = end - begin + 1
expand toexpand
bysort A : gen year = begin + _n - 1
list, sepby(A)
+--------------------------------------------------------+
| A B begin_yr end_yr toexpand year |
|--------------------------------------------------------|
1. | asset brown 2007 2010 4 2007 |
2. | asset brown 2007 2010 4 2008 |
3. | asset brown 2007 2010 4 2009 |
4. | asset brown 2007 2010 4 2010 |
|--------------------------------------------------------|
5. | basics caramel 2015 2015 1 2015 |
|--------------------------------------------------------|
6. | cows dork 2004 2006 3 2004 |
7. | cows dork 2004 2006 3 2005 |
8. | cows dork 2004 2006 3 2006 |
+--------------------------------------------------------+
Nothing against tsset or tsfill but neither is needed for this.