Generate additional rows and merging of data

[Sebastian Finke]

Hello!

I need to merge two panel datasets into one. For this, I need to conduct two steps:

1. Create for each ISIN year variables from 1997-2021 except those that are already present in the dataset.

So eventually, I want to transform this:

ISIN	year
isin1	1998
isin1	2012
isin1	2013
isin2	2007
isin2	2008
isin2	2015

...into this:

ISIN	year
isin1	1997
isin1	1998
...	...
isin1	2021
isin2	1997
isin2	1998
...	...
isin2	2021

2. I need to merge the following (patchy) dataset onto the above dataset based on the ISINs and years:

ISIN	year	P/E ratio
isin1	1998	5
isin1	2015	2
isin2	1997	4.5
isin2	2017	3

Eventually, this is supposed to be the outcome:

ISIN	year	P/E ratio
isin1	1997	NA
isin1	1998	5
...	...
isin1	2020	NA
isin2	1997	4.5
isin2	1998	NA
...	...
isin2	2021	NA

Does anybody know how I could code this in STATA?

Thank you very much in advance!

[Andrew Musau]

#2

help merge

#1

* Example generated by -dataex-. For more info, type help dataex
clear
input str5 ISIN int year
"isin1" 1998
"isin1" 2012
"isin1" 2013
"isin2" 2007
"isin2" 2008
"isin2" 2015
end

contract ISIN, freq(Year)
expand `=2021-1997+1'
bys ISIN: replace Year= 1996+_n

Res.:

. l, sepby(ISIN)

     +--------------+
     |  ISIN   Year |
     |--------------|
  1. | isin1   1997 |
  2. | isin1   1998 |
  3. | isin1   1999 |
  4. | isin1   2000 |
  5. | isin1   2001 |
  6. | isin1   2002 |
  7. | isin1   2003 |
  8. | isin1   2004 |
  9. | isin1   2005 |
 10. | isin1   2006 |
 11. | isin1   2007 |
 12. | isin1   2008 |
 13. | isin1   2009 |
 14. | isin1   2010 |
 15. | isin1   2011 |
 16. | isin1   2012 |
 17. | isin1   2013 |
 18. | isin1   2014 |
 19. | isin1   2015 |
 20. | isin1   2016 |
 21. | isin1   2017 |
 22. | isin1   2018 |
 23. | isin1   2019 |
 24. | isin1   2020 |
 25. | isin1   2021 |
     |--------------|
 26. | isin2   1997 |
 27. | isin2   1998 |
 28. | isin2   1999 |
 29. | isin2   2000 |
 30. | isin2   2001 |
 31. | isin2   2002 |
 32. | isin2   2003 |
 33. | isin2   2004 |
 34. | isin2   2005 |
 35. | isin2   2006 |
 36. | isin2   2007 |
 37. | isin2   2008 |
 38. | isin2   2009 |
 39. | isin2   2010 |
 40. | isin2   2011 |
 41. | isin2   2012 |
 42. | isin2   2013 |
 43. | isin2   2014 |
 44. | isin2   2015 |
 45. | isin2   2016 |
 46. | isin2   2017 |
 47. | isin2   2018 |
 48. | isin2   2019 |
 49. | isin2   2020 |
 50. | isin2   2021 |
     +--------------+

.

See also https://www.statalist.org/forums/help#spelling