Dummy variable for consecutive years with a specific amount

[Jan Knippelmeyer]

Hello there,

I have a panel dataset in long format with observations for companies over 6 years (2012-2017) and try to create a dummy variable (mandCSR) which turns to 1 if a company has more than 500 employees (variable WC07011) in the consecutive years 2015 - 2016 or 2016 – 2017. Each observation and company has its own identifier. The dummy should turn to 1 for all observations of the company, so even for the years 2012-2014.

Below there is an short example of my data.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str18 iden str13 ISIN long WC07011 float(year i)
"AT0000652250 2012" "AT0000652250"  521 2012  1
"AT0000652250 2013" "AT0000652250"  505 2013  1
"AT0000652250 2014" "AT0000652250"  530 2014  1
"AT0000652250 2015" "AT0000652250"  555 2015  1
"AT0000652250 2016" "AT0000652250"  575 2016  1
"AT0000652250 2017" "AT0000652250"  577 2017  1
"AT0000809058 2012" "AT0000809058" 1073 2012  2
"AT0000809058 2013" "AT0000809058" 1132 2013  2
"AT0000809058 2014" "AT0000809058"  791 2014  2
"AT0000809058 2015" "AT0000809058"  853 2015  2
"AT0000809058 2016" "AT0000809058"  545 2016  2
"AT0000809058 2017" "AT0000809058"  406 2017  2
"AT0000815402 2012" "AT0000815402"  456 2012  3
"AT0000815402 2013" "AT0000815402"  509 2013  3
"AT0000815402 2014" "AT0000815402"  555 2014  3
"AT0000815402 2015" "AT0000815402"  685 2015  3
"AT0000815402 2016" "AT0000815402"  716 2016  3
"AT0000815402 2017" "AT0000815402"  748 2017  3
"BE0003818359 2012" "BE0003818359"  796 2012  4
"BE0003818359 2013" "BE0003818359"  810 2013  4
"BE0003818359 2014" "BE0003818359"  417 2014  4
"BE0003818359 2015" "BE0003818359"  435 2015  4
"BE0003818359 2016" "BE0003818359"  508 2016  4
"BE0003818359 2017" "BE0003818359"  600 2017  4
"BE0003820371 2012" "BE0003820371"  463 2012  5
"BE0003820371 2013" "BE0003820371"  486 2013  5
"BE0003820371 2014" "BE0003820371"  512 2014  5
"BE0003820371 2015" "BE0003820371"  485 2015  5
"BE0003820371 2016" "BE0003820371"  481 2016  5
"BE0003820371 2017" "BE0003820371"  493 2017  5
"CY0100470919 2012" "CY0100470919"  547 2012  6
"CY0100470919 2013" "CY0100470919"  595 2013  6
"CY0100470919 2014" "CY0100470919"  796 2014  6
"CY0100470919 2015" "CY0100470919"  851 2015  6
"CY0100470919 2016" "CY0100470919"  662 2016  6
"CY0100470919 2017" "CY0100470919"  430 2017  6
"CY0101162119 2012" "CY0101162119"  462 2012  7
"CY0101162119 2013" "CY0101162119"  569 2013  7
"CY0101162119 2014" "CY0101162119"  543 2014  7
"CY0101162119 2015" "CY0101162119"  398 2015  7
"CY0101162119 2016" "CY0101162119"  260 2016  7
"CY0101162119 2017" "CY0101162119"  254 2017  7
"DE0005008007 2012" "DE0005008007"   10 2012  8
"DE0005008007 2013" "DE0005008007"   16 2013  8
"DE0005008007 2014" "DE0005008007"  102 2014  8
"DE0005008007 2015" "DE0005008007"  268 2015  8
"DE0005008007 2016" "DE0005008007"  354 2016  8
"DE0005008007 2017" "DE0005008007"  555 2017  8
"DE0005102008 2012" "DE0005102008"  327 2012  9
"DE0005102008 2013" "DE0005102008"  325 2013  9
"DE0005102008 2014" "DE0005102008"  374 2014  9
"DE0005102008 2015" "DE0005102008"  438 2015  9
"DE0005102008 2016" "DE0005102008"  457 2016  9
"DE0005102008 2017" "DE0005102008"  504 2017  9
"DE0005104806 2012" "DE0005104806"  302 2012 10
"DE0005104806 2013" "DE0005104806"  326 2013 10
"DE0005104806 2014" "DE0005104806"  453 2014 10
"DE0005104806 2015" "DE0005104806"  481 2015 10
"DE0005104806 2016" "DE0005104806"  578 2016 10
"DE0005104806 2017" "DE0005104806"  621 2017 10
"DE0005111702 2012" "DE0005111702"  217 2012 11
"DE0005111702 2013" "DE0005111702"  253 2013 11
"DE0005111702 2014" "DE0005111702"  267 2014 11
"DE0005111702 2015" "DE0005111702"  313 2015 11
"DE0005111702 2016" "DE0005111702"  386 2016 11
"DE0005111702 2017" "DE0005111702"  512 2017 11
"DE0005141907 2012" "DE0005141907"  420 2012 12
"DE0005141907 2013" "DE0005141907"  451 2013 12
"DE0005141907 2014" "DE0005141907"  444 2014 12
"DE0005141907 2015" "DE0005141907"  506 2015 12
"DE0005141907 2016" "DE0005141907"  505 2016 12
"DE0005141907 2017" "DE0005141907"  529 2017 12
"DE0005157101 2012" "DE0005157101"  473 2012 13
"DE0005157101 2013" "DE0005157101"  509 2013 13
"DE0005157101 2014" "DE0005157101"  513 2014 13
"DE0005157101 2015" "DE0005157101"  545 2015 13
"DE0005157101 2016" "DE0005157101"  542 2016 13
"DE0005157101 2017" "DE0005157101"  564 2017 13
"DE0005199905 2012" "DE0005199905"  471 2012 14
"DE0005199905 2013" "DE0005199905"  463 2013 14
"DE0005199905 2014" "DE0005199905"  478 2014 14
"DE0005199905 2015" "DE0005199905"  816 2015 14
"DE0005199905 2016" "DE0005199905"  892 2016 14
"DE0005199905 2017" "DE0005199905"  874 2017 14
"DE0005220909 2012" "DE0005220909"  490 2012 15
"DE0005220909 2013" "DE0005220909"  644 2013 15
"DE0005220909 2014" "DE0005220909"  690 2014 15
"DE0005220909 2015" "DE0005220909"  824 2015 15
"DE0005220909 2016" "DE0005220909"  924 2016 15
"DE0005220909 2017" "DE0005220909"  975 2017 15
"DE0005493092 2012" "DE0005493092"  461 2012 16
"DE0005493092 2013" "DE0005493092"  535 2013 16
"DE0005493092 2014" "DE0005493092"  569 2014 16
"DE0005493092 2015" "DE0005493092"  605 2015 16
"DE0005493092 2016" "DE0005493092"  703 2016 16
"DE0005493092 2017" "DE0005493092"  756 2017 16
"DE0005495329 2012" "DE0005495329"  675 2012 17
"DE0005495329 2013" "DE0005495329"  725 2013 17
"DE0005495329 2014" "DE0005495329"  698 2014 17
"DE0005495329 2015" "DE0005495329"  387 2015 17
end

Hope you can help me and thanks in advance!

[Nick Cox]

Here is one way to do it. I first create indicators for being above 500 in 2015, 2016, 2017. Using inrange() protects against false positives if there are missing values. The machinery here is discussed in https://www.stata-journal.com/sjpdf....iclenum=dm0055 Then it's all downhill.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str18 iden str13 ISIN long WC07011 float(year i)
"AT0000652250 2012" "AT0000652250"  521 2012  1
"AT0000652250 2013" "AT0000652250"  505 2013  1
"AT0000652250 2014" "AT0000652250"  530 2014  1
"AT0000652250 2015" "AT0000652250"  555 2015  1
"AT0000652250 2016" "AT0000652250"  575 2016  1
"AT0000652250 2017" "AT0000652250"  577 2017  1
"AT0000809058 2012" "AT0000809058" 1073 2012  2
"AT0000809058 2013" "AT0000809058" 1132 2013  2
"AT0000809058 2014" "AT0000809058"  791 2014  2
"AT0000809058 2015" "AT0000809058"  853 2015  2
"AT0000809058 2016" "AT0000809058"  545 2016  2
"AT0000809058 2017" "AT0000809058"  406 2017  2
"AT0000815402 2012" "AT0000815402"  456 2012  3
"AT0000815402 2013" "AT0000815402"  509 2013  3
"AT0000815402 2014" "AT0000815402"  555 2014  3
"AT0000815402 2015" "AT0000815402"  685 2015  3
"AT0000815402 2016" "AT0000815402"  716 2016  3
"AT0000815402 2017" "AT0000815402"  748 2017  3
"BE0003818359 2012" "BE0003818359"  796 2012  4
"BE0003818359 2013" "BE0003818359"  810 2013  4
"BE0003818359 2014" "BE0003818359"  417 2014  4
"BE0003818359 2015" "BE0003818359"  435 2015  4
"BE0003818359 2016" "BE0003818359"  508 2016  4
"BE0003818359 2017" "BE0003818359"  600 2017  4
"BE0003820371 2012" "BE0003820371"  463 2012  5
"BE0003820371 2013" "BE0003820371"  486 2013  5
"BE0003820371 2014" "BE0003820371"  512 2014  5
"BE0003820371 2015" "BE0003820371"  485 2015  5
"BE0003820371 2016" "BE0003820371"  481 2016  5
"BE0003820371 2017" "BE0003820371"  493 2017  5
"CY0100470919 2012" "CY0100470919"  547 2012  6
"CY0100470919 2013" "CY0100470919"  595 2013  6
"CY0100470919 2014" "CY0100470919"  796 2014  6
"CY0100470919 2015" "CY0100470919"  851 2015  6
"CY0100470919 2016" "CY0100470919"  662 2016  6
"CY0100470919 2017" "CY0100470919"  430 2017  6
"CY0101162119 2012" "CY0101162119"  462 2012  7
"CY0101162119 2013" "CY0101162119"  569 2013  7
"CY0101162119 2014" "CY0101162119"  543 2014  7
"CY0101162119 2015" "CY0101162119"  398 2015  7
"CY0101162119 2016" "CY0101162119"  260 2016  7
"CY0101162119 2017" "CY0101162119"  254 2017  7
"DE0005008007 2012" "DE0005008007"   10 2012  8
"DE0005008007 2013" "DE0005008007"   16 2013  8
"DE0005008007 2014" "DE0005008007"  102 2014  8
"DE0005008007 2015" "DE0005008007"  268 2015  8
"DE0005008007 2016" "DE0005008007"  354 2016  8
"DE0005008007 2017" "DE0005008007"  555 2017  8
"DE0005102008 2012" "DE0005102008"  327 2012  9
"DE0005102008 2013" "DE0005102008"  325 2013  9
"DE0005102008 2014" "DE0005102008"  374 2014  9
"DE0005102008 2015" "DE0005102008"  438 2015  9
"DE0005102008 2016" "DE0005102008"  457 2016  9
"DE0005102008 2017" "DE0005102008"  504 2017  9
"DE0005104806 2012" "DE0005104806"  302 2012 10
"DE0005104806 2013" "DE0005104806"  326 2013 10
"DE0005104806 2014" "DE0005104806"  453 2014 10
"DE0005104806 2015" "DE0005104806"  481 2015 10
"DE0005104806 2016" "DE0005104806"  578 2016 10
"DE0005104806 2017" "DE0005104806"  621 2017 10
"DE0005111702 2012" "DE0005111702"  217 2012 11
"DE0005111702 2013" "DE0005111702"  253 2013 11
"DE0005111702 2014" "DE0005111702"  267 2014 11
"DE0005111702 2015" "DE0005111702"  313 2015 11
"DE0005111702 2016" "DE0005111702"  386 2016 11
"DE0005111702 2017" "DE0005111702"  512 2017 11
"DE0005141907 2012" "DE0005141907"  420 2012 12
"DE0005141907 2013" "DE0005141907"  451 2013 12
"DE0005141907 2014" "DE0005141907"  444 2014 12
"DE0005141907 2015" "DE0005141907"  506 2015 12
"DE0005141907 2016" "DE0005141907"  505 2016 12
"DE0005141907 2017" "DE0005141907"  529 2017 12
"DE0005157101 2012" "DE0005157101"  473 2012 13
"DE0005157101 2013" "DE0005157101"  509 2013 13
"DE0005157101 2014" "DE0005157101"  513 2014 13
"DE0005157101 2015" "DE0005157101"  545 2015 13
"DE0005157101 2016" "DE0005157101"  542 2016 13
"DE0005157101 2017" "DE0005157101"  564 2017 13
"DE0005199905 2012" "DE0005199905"  471 2012 14
"DE0005199905 2013" "DE0005199905"  463 2013 14
"DE0005199905 2014" "DE0005199905"  478 2014 14
"DE0005199905 2015" "DE0005199905"  816 2015 14
"DE0005199905 2016" "DE0005199905"  892 2016 14
"DE0005199905 2017" "DE0005199905"  874 2017 14
"DE0005220909 2012" "DE0005220909"  490 2012 15
"DE0005220909 2013" "DE0005220909"  644 2013 15
"DE0005220909 2014" "DE0005220909"  690 2014 15
"DE0005220909 2015" "DE0005220909"  824 2015 15
"DE0005220909 2016" "DE0005220909"  924 2016 15
"DE0005220909 2017" "DE0005220909"  975 2017 15
"DE0005493092 2012" "DE0005493092"  461 2012 16
"DE0005493092 2013" "DE0005493092"  535 2013 16
"DE0005493092 2014" "DE0005493092"  569 2014 16
"DE0005493092 2015" "DE0005493092"  605 2015 16
"DE0005493092 2016" "DE0005493092"  703 2016 16
"DE0005493092 2017" "DE0005493092"  756 2017 16
"DE0005495329 2012" "DE0005495329"  675 2012 17
"DE0005495329 2013" "DE0005495329"  725 2013 17
"DE0005495329 2014" "DE0005495329"  698 2014 17
"DE0005495329 2015" "DE0005495329"  387 2015 17
end

forval y = 15/17 { 
    egen above`y' = total(inrange(WC07011, 500, .) & (year == 20`y')), by(i) 
} 

gen OK = (above15 & above16) | (above16 & above17) 

list i year W above* OK if  inrange(i, 4, 7), sepby(i)  


     +-------------------------------------------------------+
     | i   year   WC07011   above15   above16   above17   OK |
     |-------------------------------------------------------|
 19. | 4   2012       796         0         1         1    1 |
 20. | 4   2013       810         0         1         1    1 |
 21. | 4   2014       417         0         1         1    1 |
 22. | 4   2015       435         0         1         1    1 |
 23. | 4   2016       508         0         1         1    1 |
 24. | 4   2017       600         0         1         1    1 |
     |-------------------------------------------------------|
 25. | 5   2012       463         0         0         0    0 |
 26. | 5   2013       486         0         0         0    0 |
 27. | 5   2014       512         0         0         0    0 |
 28. | 5   2015       485         0         0         0    0 |
 29. | 5   2016       481         0         0         0    0 |
 30. | 5   2017       493         0         0         0    0 |
     |-------------------------------------------------------|
 31. | 6   2012       547         1         1         0    1 |
 32. | 6   2013       595         1         1         0    1 |
 33. | 6   2014       796         1         1         0    1 |
 34. | 6   2015       851         1         1         0    1 |
 35. | 6   2016       662         1         1         0    1 |
 36. | 6   2017       430         1         1         0    1 |
     |-------------------------------------------------------|
 37. | 7   2012       462         0         0         0    0 |
 38. | 7   2013       569         0         0         0    0 |
 39. | 7   2014       543         0         0         0    0 |
 40. | 7   2015       398         0         0         0    0 |
 41. | 7   2016       260         0         0         0    0 |
 42. | 7   2017       254         0         0         0    0 |
     +-------------------------------------------------------+

[Jan Knippelmeyer]

Thanks a lot thats exactly what I needed.