Count the number of observations per company during a specific event window

[Helen Chang]

Hi!
I have a panel dataset A which includes user_id, leave_date (e.g., YYYY-MM-DD), and company id.
In another panel dataset B, it includes event date (e.g., 30 Jun 10) and company id. I would like to capture the total number or count of user_id that left the company (based on the leave_date) per company (using cusip) within each event window which is one year after the event date (using date in dataset B). Is there anyway I can achieve this? I have attached the sample data below. Thanks in advance!


Dataset A

* Example generated by -dataex-. For more info, type help dataex
clear
input long user_id str10 leave_date str9 cusip float fyear
336907342 "2008-01-01" "68243Q106" 2008
337485700 "2008-01-01" "68243Q106" 2008
 10459228 "2010-07-01" "68243Q106" 2010
255190430 "2010-03-01" "68243Q106" 2010
330228506 "2010-02-01" "68243Q106" 2010
550117998 "2010-09-01" "68243Q106" 2010
571387759 "2010-07-01" "68243Q106" 2010
 31878540 "2011-04-01" "68243Q106" 2011
 57745696 "2011-08-01" "68243Q106" 2011
 72547125 "2011-02-01" "68243Q106" 2011
310357766 "2011-01-01" "68243Q106" 2011
569437147 "2011-08-01" "68243Q106" 2011
789639337 "2011-01-01" "68243Q106" 2011
314391842 "2012-02-01" "68243Q106" 2012
408672720 "2012-12-01" "68243Q106" 2012
430024400 "2012-01-01" "68243Q106" 2012
462773347 "2012-03-01" "68243Q106" 2012
670117117 "2012-11-01" "68243Q106" 2012
104276030 "2013-07-01" "68243Q106" 2013
370770494 "2013-07-01" "68243Q106" 2013
386339208 "2013-09-01" "68243Q106" 2013
558710444 "2013-06-01" "68243Q106" 2013
538768267 "2014-08-01" "68243Q106" 2014
 51657130 "2015-05-01" "68243Q106" 2015
186770535 "2015-08-01" "68243Q106" 2015
355231848 "2015-01-01" "68243Q106" 2015
381088878 "2015-02-01" "68243Q106" 2015
386878134 "2015-10-01" "68243Q106" 2015
421403597 "2015-11-01" "68243Q106" 2015
669015336 "2015-08-01" "68243Q106" 2015
 59670332 "2016-03-01" "68243Q106" 2016
181207927 "2016-11-01" "68243Q106" 2016
334406486 "2016-05-01" "68243Q106" 2016
352949279 "2016-08-01" "68243Q106" 2016
463661679 "2016-01-01" "68243Q106" 2016
131196369 "2017-08-01" "68243Q106" 2017
151176087 "2017-11-01" "68243Q106" 2017
202366412 "2017-04-01" "68243Q106" 2017
203362062 "2017-04-01" "68243Q106" 2017
224242545 "2017-08-01" "68243Q106" 2017
323739480 "2017-04-01" "68243Q106" 2017
363595530 "2017-10-01" "68243Q106" 2017
507422660 "2017-01-01" "68243Q106" 2017
796256912 "2017-04-01" "68243Q106" 2017
187253354 "2018-03-01" "68243Q106" 2018
399171105 "2018-08-01" "68243Q106" 2018
435147592 "2018-07-01" "68243Q106" 2018
467045306 "2018-08-01" "68243Q106" 2018
468994091 "2018-08-01" "68243Q106" 2018
511665782 "2018-04-01" "68243Q106" 2018
618242460 "2018-12-01" "68243Q106" 2018
628518962 "2018-05-01" "68243Q106" 2018
652976786 "2018-01-01" "68243Q106" 2018
 34139550 "2019-01-01" "68243Q106" 2019
101325201 "2019-01-01" "68243Q106" 2019
214404348 "2019-08-01" "68243Q106" 2019
338020760 "2019-08-01" "68243Q106" 2019
388619049 "2019-04-01" "68243Q106" 2019
454706466 "2019-08-01" "68243Q106" 2019
565276982 "2019-02-01" "68243Q106" 2019
683797892 "2019-09-01" "68243Q106" 2019
 31147920 "2020-08-01" "68243Q106" 2020
 60029641 "2020-08-01" "68243Q106" 2020
256424495 "2020-03-01" "68243Q106" 2020
199038913 "2021-08-01" "68243Q106" 2021
321507574 "2021-04-01" "68243Q106" 2021
587211431 "2021-10-01" "68243Q106" 2021
608034110 "2021-04-01" "68243Q106" 2021
199038913 ""           "68243Q106"    .
328052610 ""           "68243Q106"    .
625328227 ""           "68243Q106"    .
271968341 "2018-11-01" "88025U109" 2018
457674770 "2019-06-01" "88025U109" 2019
773914788 "2019-08-01" "88025U109" 2019
453302582 "2020-01-01" "88025U109" 2020
 32830025 "2021-05-01" "88025U109" 2021
276303144 "2021-02-01" "88025U109" 2021
433787457 "2021-08-01" "88025U109" 2021
422623504 ""           "88025U109"    .
  5095909 "2008-01-01" ""          2008
 15740761 "2008-07-01" ""          2008
 76444998 "2008-03-01" ""          2008
105573875 "2008-07-01" ""          2008
240928909 "2008-03-01" ""          2008
356623772 "2008-12-01" ""          2008
528112920 "2008-01-01" ""          2008
664305358 "2008-06-01" ""          2008
454706189 "2017-08-01" ""          2017
567626175 "2017-08-01" ""          2017
207552837 "2009-03-01" ""          2009
707957156 "2014-03-01" ""          2014
404972740 "2016-08-01" ""          2016
347522934 "2018-05-01" ""          2018
 35139747 "2012-08-01" ""          2012
427861269 "2012-07-01" ""          2012
147470303 "2014-11-01" ""          2014
284402667 "2014-12-01" ""          2014
331175229 "2014-12-01" ""          2014
516482980 "2015-12-01" ""          2015
599770980 "2015-12-01" ""          2015
end

Dataset B

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 gvkey double fyear str10 cusip long date
"017947" 2008 "38059T106" 17713
"165239" 2008 "92204A405" 17622
"165233" 2008 "92204A876" 17622
"175417" 2008 "922908538" 17622
"174101" 2008 "921908844" 17622
"158537" 2008 "92204A108" 17622
"158553" 2008 "922908595" 17622
"158555" 2008 "922908629" 17622
"165234" 2008 "92204A306" 17622
"165235" 2008 "92204A603" 17622
"175414" 2008 "922908512" 17622
"165242" 2008 "92204A801" 17622
"158557" 2008 "922908736" 17622
"165236" 2008 "92204A884" 17622
"158559" 2008 "922908751" 17622
"146651" 2008 "922908652" 17622
"143851" 2008 "922908769" 17622
"158558" 2008 "922908744" 17622
"176060" 2008 "921946406" 17622
"158556" 2008 "922908637" 17622
"165240" 2008 "92204A504" 17622
"164640" 2008 "922908553" 17622
"165238" 2008 "92204A207" 17622
"165241" 2008 "92204A702" 17622
"158554" 2008 "922908611" 17622
"009818" 2008 "835699307" 17805
"013312" 2008 "088606108" 17652
"209382" 2008 "91912E105" 17744
"206457" 2008 "69343P105" 17683
"221612" 2008 "87944W105" 17713
"014590" 2008 "35671D857" 17652
"004990" 2008 "364760108" 17591
"105089" 2008 "151290889" 17744
"060894" 2010 "13342B105" 18382
"016478" 2010 "26875P101" 18443
"135990" 2011 "629377508" 18717
"200856" 2011 "204448104" 18992
"140033" 2011 "37244E107" 18992
"004990" 2011 "364760108" 18658
"006307" 2013 "812350106" 19389
"010124" 2013 "864159108" 19417
"180635" 2013 "18911Q102" 19539
"006788" 2014 "75281A109" 19996
"006461" 2014 "497266106" 19813
"023697" 2017 "835451105" 21092
"102704" 2017 "91311E102" 21123
"019402" 2017 "36237H101" 21000
"034443" 2018 "260557103" 21488
"029108" 2018 "703481101" 21274
"034067" 2019 "62914V106" 21730
end
format %tdD_m_Y date