how to reshape?

[River Huang]

Dear All, How can I reshape the following data into a standard long format panel data?

// https://bbs.pinggu.org/thread-10907696-1-1.html
* Example generated by -dataex-. To install: ssc install dataex
clear
input float stkcd str89 violation_year
4 "2010,2011,2012,2013"              
9 "2016"                              
10 "2003,2004,2005,2006,2007,2008,2009"
10 "2003,2005,2007,2009,2010,2011"    
10 "2015,2016"                        
end

Note that, for some firms (say, `stkcd'=10), there are some duplicates in the variable `violation_year'. Thanks.

[Andrew Musau]

I presume that the following is what you want:

* Example generated by -dataex-. For more info, type help dataex
clear
input float stkcd str89 violation_year
 4 "2010,2011,2012,2013"              
 9 "2016"                              
10 "2003,2004,2005,2006,2007,2008,2009"
10 "2003,2005,2007,2009,2010,2011"    
10 "2015,2016"                        
end

split violation_year, p(,) gen(year) destring
reshape long year, i(stkcd violation_year) j(which)
contract stkcd year, nomiss

Res.:

. l, sepby(stkcd)

     +----------------------+
     | stkcd   year   _freq |
     |----------------------|
  1. |     4   2010       1 |
  2. |     4   2011       1 |
  3. |     4   2012       1 |
  4. |     4   2013       1 |
     |----------------------|
  5. |     9   2016       1 |
     |----------------------|
  6. |    10   2003       2 |
  7. |    10   2004       1 |
  8. |    10   2005       2 |
  9. |    10   2006       1 |
 10. |    10   2007       2 |
 11. |    10   2008       1 |
 12. |    10   2009       2 |
 13. |    10   2010       1 |
 14. |    10   2011       1 |
 15. |    10   2015       1 |
 16. |    10   2016       1 |
     +----------------------+

.

[River Huang]

Dear Andrew, Many thanks for your help.