Help with reshape

[Gianfranco Siciliano]

Hello everyone,

I have got the following matrix.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(isin year) str3(last_name_0 last_name_1 last_name_2 last_name_3 last_name_4 last_name_5 last_name_6 last_name_7 last_name_8 last_name_9)
16 2006 "" "LMR" "LMR" "LMR" "" "" "" "" "" ""
16 2007 "" "LMR" "LMR" "LMR" "" "" "" "" "" ""
16 2008 "" "LMR" "LMR" "LMR" "" "" "" "" "" ""
16 2009 "" "LMR" "LMR" "LMR" "" "" "" "" "" ""
16 2010 "" "LMR" "LMR" "LMR" "" "" "" "" "" ""
16 2011 "" "LMR" "LMR" "LMR" "" "" "" "" "" ""
16 2012 "" "LMR" "LMR" "" " " " " "" "" "" ""
16 2013 "" "LMR" "LMR" "" " " " " "" "" "" ""
end

I would like to reshape observations per isin and year such as the output after the reshape looks as follows.

isin	year	last_name
16	2006	LMR
16	2006	LMR
16	2006	LMR
16	2007	LMR
16	2007	LMR
16	2007	LMR
16	2008	LMR
16	2008	LMR
16	2008	LMR
16	2009	LMR
16	2009	LMR
16	2009	LMR
16	2010	LMR
16	2010	LMR
16	2010	LMR
16	2011	LMR
16	2011	LMR
16	2011	LMR
16	2012	LMR
16	2012	LMR
16	2013	LMR
16	2013	LMR

Can you please help me on this matter?
Thank you very much,
Gianfranco

[Nick Cox]

Thanks for the data example.

clear
input int(isin year) str3(last_name_0 last_name_1 last_name_2 last_name_3 last_name_4 last_name_5 last_name_6 last_name_7 last_name_8 last_name_9)
16 2006 "" "LMR" "LMR" "LMR" "" "" "" "" "" ""
16 2007 "" "LMR" "LMR" "LMR" "" "" "" "" "" ""
16 2008 "" "LMR" "LMR" "LMR" "" "" "" "" "" ""
16 2009 "" "LMR" "LMR" "LMR" "" "" "" "" "" ""
16 2010 "" "LMR" "LMR" "LMR" "" "" "" "" "" ""
16 2011 "" "LMR" "LMR" "LMR" "" "" "" "" "" ""
16 2012 "" "LMR" "LMR" "" " " " " "" "" "" ""
16 2013 "" "LMR" "LMR" "" " " " " "" "" "" ""
end

gen long obs = _n 

reshape long last_name_ , i(obs) j(which) 

drop if missing(trim(last_name_))

list, sepby(isin year)

    +--------------------------------------+
     | obs   which   isin   year   last_n~_ |
     |--------------------------------------|
  1. |   1       1     16   2006        LMR |
  2. |   1       2     16   2006        LMR |
  3. |   1       3     16   2006        LMR |
     |--------------------------------------|
  4. |   2       1     16   2007        LMR |
  5. |   2       2     16   2007        LMR |
  6. |   2       3     16   2007        LMR |
     |--------------------------------------|
  7. |   3       1     16   2008        LMR |
  8. |   3       2     16   2008        LMR |
  9. |   3       3     16   2008        LMR |
     |--------------------------------------|
 10. |   4       1     16   2009        LMR |
 11. |   4       2     16   2009        LMR |
 12. |   4       3     16   2009        LMR |
     |--------------------------------------|
 13. |   5       1     16   2010        LMR |
 14. |   5       2     16   2010        LMR |
 15. |   5       3     16   2010        LMR |
     |--------------------------------------|
 16. |   6       1     16   2011        LMR |
 17. |   6       2     16   2011        LMR |
 18. |   6       3     16   2011        LMR |
     |--------------------------------------|
 19. |   7       1     16   2012        LMR |
 20. |   7       2     16   2012        LMR |
     |--------------------------------------|
 21. |   8       1     16   2013        LMR |
 22. |   8       2     16   2013        LMR |
     +--------------------------------------+

See https://www.stata.com/support/faqs/d...-with-reshape/ for a reminder that you may need to create an identifier if one does not exist.

[Gianfranco Siciliano]

Dear Nick,
thank you so much for your help. It works perfectly!
A pleasure to receive your help.
Thanks,
Gianfranco