Generating values for columns between a range

Question

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?

Answer 1

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.

Answer 2

The following works for me:

clear

input strL A strL B begin_yr end_yr
asset  brown 2007 2010
basics caramel 2015 2015
cows dork 2004 2006
end

generate id = _n
expand 2

clonevar year = begin_yr
bysort id: replace year = end_yr[2] if _n == _N

drop if _n == 3

tsset id year
tsfill

foreach var in  A B begin_yr end_yr {
    bysort id: replace `var' = `var'[1]
}

list

     +--------------------------------------------------+
     |      A         B   begin_yr   end_yr   id   year |
     |--------------------------------------------------|
  1. |  asset     brown       2007     2010    1   2007 |
  2. |  asset     brown       2007     2010    1   2008 |
  3. |  asset     brown       2007     2010    1   2009 |
  4. |  asset     brown       2007     2010    1   2010 |
  5. | basics   caramel       2015     2015    2   2015 |
     |--------------------------------------------------|
  6. |   cows      dork       2004     2006    3   2004 |
  7. |   cows      dork       2004     2006    3   2005 |
  8. |   cows      dork       2004     2006    3   2006 |
     +--------------------------------------------------+