identify years of violations.

[River Huang]

Dear All, I find this question here (https://bbs.pinggu.org/forum.php?mod...=1#pid56831974). The dataset is

* Example generated by -dataex-. To install: ssc install dataex
clear
input long code str10 pubyear str79 vioyear
 4 "2013-06-06" "2010,2011,2012,2013"                    
 4 "2010-01-30" "2008,2009,2010"                         
 5 "2010-06-25" "2007,2008,2009"                         
 7 "2013-09-23" "2012,2013"                              
 7 "2014-08-28" "2011,2012,2013,2014"                    
 7 "2014-09-30" "2011,2012,2013,2014"                    
 7 "2015-05-26" "2014,2015"                              
 7 "2015-12-23" "2014"                                   
 7 "2015-03-24" "2014"                                   
 7 "2014-06-18" "2012,2013"                              
 7 "2016-02-26" "2014,2015"                              
 7 "2016-05-11" "2014,2015"                              
 9 "2011-05-09" "2010,2011"                              
10 "2011-11-30" "2003,2005,2007,2009,2010,2011"          
10 "2009-12-25" "2003,2004,2005,2006,2007,2008,2009"     
10 "2015-12-25" "2015"                                   
10 "2010-06-29" "2003,2004,2005,2006,2007,2008,2009,2010"
10 "2017-07-07" "2016"                                   
10 "2017-06-09" "2015,2016"                              
end

The purpose is, for each `code' (firm), to create a dummy of violation whenever the years appear in `pubyear' (announce different types of violations). For instance, code=4, the dummy variable should be equal to 1 if the years are 2008, 2009, 2010, 2011, 2012, 2013, 0 otherwise. when code=7, the dummy variable should be equal to 1 if the years 2011, 2012, 2013, 2014, 2015, 0 otherwise. Any suggestions?

[Clyde Schechter]

* Example generated by -dataex-. To install: ssc install dataex
clear
input long code str10 pubyear str79 vioyear
 4 "2013-06-06" "2010,2011,2012,2013"                    
 4 "2010-01-30" "2008,2009,2010"                         
 5 "2010-06-25" "2007,2008,2009"                         
 7 "2013-09-23" "2012,2013"                              
 7 "2014-08-28" "2011,2012,2013,2014"                    
 7 "2014-09-30" "2011,2012,2013,2014"                    
 7 "2015-05-26" "2014,2015"                              
 7 "2015-12-23" "2014"                                   
 7 "2015-03-24" "2014"                                   
 7 "2014-06-18" "2012,2013"                              
 7 "2016-02-26" "2014,2015"                              
 7 "2016-05-11" "2014,2015"                              
 9 "2011-05-09" "2010,2011"                              
10 "2011-11-30" "2003,2005,2007,2009,2010,2011"          
10 "2009-12-25" "2003,2004,2005,2006,2007,2008,2009"     
10 "2015-12-25" "2015"                                   
10 "2010-06-29" "2003,2004,2005,2006,2007,2008,2009,2010"
10 "2017-07-07" "2016"                                   
10 "2017-06-09" "2015,2016"                              
end

preserve
keep code vioyear
split vioyear, parse(",") gen(vyear) destring
gen long obs_no = _n
reshape long vyear, i(obs_no) j(_j)
keep code vyear
duplicates drop
drop if missing(vyear)
tempfile vyears
save `vyears'

restore
gen pubdate = daily(pubyear, "YMD")
assert missing(pubdate) == missing(pubyear)
format pubdate %td
gen vyear = yofd(pubdate)
merge m:1 code vyear using `vyears', keep(master match)
gen byte wanted = (_merge == 3)
drop _merge

[River Huang]

Dear Clyde, Thank you for the reply. I think the answer to the question is the first part of your code.

// Clyde Schechter
keep code vioyear
split vioyear, parse(",") gen(vyear) destring
gen long obs_no = _n
reshape long vyear, i(obs_no) j(_j)
keep code vyear
duplicates drop
drop if missing(vyear)
sort code vyear

. list, sepby(code)

     +------------------+
     | code   vyear   d |
     |------------------|
  1. |    4    2008   1 |
  2. |    4    2009   1 |
  3. |    4    2010   1 |
  4. |    4    2011   1 |
  5. |    4    2012   1 |
  6. |    4    2013   1 |
     |------------------|
  7. |    5    2007   1 |
  8. |    5    2008   1 |
  9. |    5    2009   1 |
     |------------------|
 10. |    7    2011   1 |
 11. |    7    2012   1 |
 12. |    7    2013   1 |
 13. |    7    2014   1 |
 14. |    7    2015   1 |
     |------------------|
 15. |    9    2010   1 |
 16. |    9    2011   1 |
     |------------------|
 17. |   10    2003   1 |
 18. |   10    2004   1 |
 19. |   10    2005   1 |
 20. |   10    2006   1 |
 21. |   10    2007   1 |
 22. |   10    2008   1 |
 23. |   10    2009   1 |
 24. |   10    2010   1 |
 25. |   10    2011   1 |
 26. |   10    2015   1 |
 27. |   10    2016   1 |
     +------------------+