sort stkcd year 证券公司ID
gen t = _n
gen number=1 if 证券公司ID!=证券公司ID[_n-1]
replace number=0 if number==.
bys stkcd year:egen 分析师追踪人数=sum(number)
但存在券商发布每个公司的报告 公布日和终止日之间的时间跨度不一致的问题。比如一个报告的公布日为2007-10-30,终止日2009-12-31,那么应视为该证券公司在2007年、2008年、2009年三年均追踪了某一上市公司。以上代码的计算方式只考虑了报告发布当年有多少分析师追踪,向大家请教:正确的代码应是怎样的?
数据如下:
- * Example generated by -dataex-. To install: ssc install dataex
- clear
- input str6 证券代码 str10(报告公布日 预测终止日) long 证券公司ID str48 证券公司名称 int year long stkcd double(t number 分析师追踪人数) int year_end double year_gap
- "000001" "2002-06-28" "2002-12-31" 103962 "大鹏证券有限责任公司" 2002 1 1 1 2 2002 0
- "000001" "2002-07-18" "2002-12-31" 106064 "国泰君安证券股份有限公司" 2002 1 2 1 2 2002 0
- "000001" "2003-04-04" "2003-12-31" 103983 "南方证券股份有限公司" 2003 1 3 1 2 2003 0
- "000001" "2003-06-04" "2003-12-31" 104776 "中信证券股份有限公司" 2003 1 4 1 2 2003 0
- "000001" "2004-03-29" "2004-12-31" 101506 "广发证券股份有限公司" 2004 1 5 1 4 2004 0
- "000001" "2004-08-18" "2005-12-31" 102306 "宏源证券股份有限公司" 2004 1 6 1 4 2005 1
- "000001" "2004-08-18" "2004-12-31" 102306 "宏源证券股份有限公司" 2004 1 7 0 4 2004 0
- "000001" "2004-08-18" "2006-12-31" 102306 "宏源证券股份有限公司" 2004 1 8 0 4 2006 2
- "000001" "2004-04-15" "2005-12-31" 104100 "申银万国证券股份有限公司" 2004 1 9 1 4 2005 1
- "000001" "2004-04-15" "2004-12-31" 104100 "申银万国证券股份有限公司" 2004 1 10 0 4 2004 0
- "000001" "2004-04-15" "2005-12-31" 106064 "国泰君安证券股份有限公司" 2004 1 11 1 4 2005 1
- "000001" "2004-04-15" "2004-12-31" 106064 "国泰君安证券股份有限公司" 2004 1 12 0 4 2004 0
- "000001" "2004-06-29" "2005-12-31" 106064 "国泰君安证券股份有限公司" 2004 1 13 0 4 2005 1
- "000001" "2004-06-29" "2004-12-31" 106064 "国泰君安证券股份有限公司" 2004 1 14 0 4 2004 0
- "000001" "2005-11-01" "2007-12-31" 101506 "广发证券股份有限公司" 2005 1 15 1 5 2007 2
- "000001" "2005-11-01" "2005-12-31" 101506 "广发证券股份有限公司" 2005 1 16 0 5 2005 0
- "000001" "2005-07-31" "2005-12-31" 101506 "广发证券股份有限公司" 2005 1 17 0 5 2005 0
- "000001" "2005-07-31" "2006-12-31" 101506 "广发证券股份有限公司" 2005 1 18 0 5 2006 1
- "000001" "2005-07-31" "2007-12-31" 101506 "广发证券股份有限公司" 2005 1 19 0 5 2007 2
- "000001" "2005-11-01" "2006-12-31" 101506 "广发证券股份有限公司" 2005 1 20 0 5 2006 1
- "000001" "2005-09-29" "2005-12-31" 104165 "华夏证券股份有限公司" 2005 1 21 1 5 2005 0
- "000001" "2005-09-29" "2006-12-31" 104165 "华夏证券股份有限公司" 2005 1 22 0 5 2006 1
- "000001" "2005-08-19" "2007-12-31" 105523 "华泰证券股份有限公司" 2005 1 23 1 5 2007 2
- "000001" "2005-08-19" "2005-12-31" 105523 "华泰证券股份有限公司" 2005 1 24 0 5 2005 0
- "000001" "2005-08-19" "2006-12-31" 105523 "华泰证券股份有限公司" 2005 1 25 0 5 2006 1
- "000001" "2005-05-09" "2005-12-31" 106031 "国信证券有限责任公司" 2005 1 26 1 5 2005 0
- "000001" "2005-05-09" "2007-12-31" 106031 "国信证券有限责任公司" 2005 1 27 0 5 2007 2
- "000001" "2005-05-09" "2006-12-31" 106031 "国信证券有限责任公司" 2005 1 28 0 5 2006 1
- "000001" "2005-11-06" "2007-12-31" 107973 "中信建投证券有限责任公司" 2005 1 29 1 5 2007 2
- "000001" "2005-11-06" "2008-12-31" 107973 "中信建投证券有限责任公司" 2005 1 30 0 5 2008 3
- "000001" "2005-11-06" "2006-12-31" 107973 "中信建投证券有限责任公司" 2005 1 31 0 5 2006 1
- "000001" "2006-02-22" "2006-12-31" 10974 "招商证券股份有限公司" 2006 1 32 1 9 2006 0
- "000001" "2006-02-22" "2005-12-31" 10974 "招商证券股份有限公司" 2006 1 33 0 9 2005 -1
- "000001" "2006-08-17" "2007-12-31" 101434 "长江证券有限责任公司" 2006 1 34 1 9 2007 1
- "000001" "2006-08-17" "2006-12-31" 101434 "长江证券有限责任公司" 2006 1 35 0 9 2006 0
- "000001" "2006-04-27" "2008-12-31" 101506 "广发证券股份有限公司" 2006 1 36 1 9 2008 2
- "000001" "2006-08-23" "2007-12-31" 101506 "广发证券股份有限公司" 2006 1 37 0 9 2007 1
- "000001" "2006-04-04" "2007-12-31" 101506 "广发证券股份有限公司" 2006 1 38 0 9 2007 1
- "000001" "2006-10-27" "2007-12-31" 101506 "广发证券股份有限公司" 2006 1 39 0 9 2007 1
- "000001" "2006-04-04" "2008-12-31" 101506 "广发证券股份有限公司" 2006 1 40 0 9 2008 2
- "000001" "2006-04-27" "2007-12-31" 101506 "广发证券股份有限公司" 2006 1 41 0 9 2007 1
- "000001" "2006-10-27" "2008-12-31" 101506 "广发证券股份有限公司" 2006 1 42 0 9 2008 2
- "000001" "2006-06-12" "2008-12-31" 101506 "广发证券股份有限公司" 2006 1 43 0 9 2008 2
- "000001" "2006-08-23" "2008-12-31" 101506 "广发证券股份有限公司" 2006 1 44 0 9 2008 2
- "000001" "2006-04-27" "2006-12-31" 101506 "广发证券股份有限公司" 2006 1 45 0 9 2006 0
- "000001" "2006-06-12" "2006-12-31" 101506 "广发证券股份有限公司" 2006 1 46 0 9 2006 0
- "000001" "2006-04-04" "2006-12-31" 101506 "广发证券股份有限公司" 2006 1 47 0 9 2006 0
- "000001" "2006-10-27" "2006-12-31" 101506 "广发证券股份有限公司" 2006 1 48 0 9 2006 0
- "000001" "2006-08-23" "2006-12-31" 101506 "广发证券股份有限公司" 2006 1 49 0 9 2006 0
- "000001" "2006-06-12" "2007-12-31" 101506 "广发证券股份有限公司" 2006 1 50 0 9 2007 1
- "000001" "2006-04-10" "2007-12-31" 102333 "东北证券股份有限公司" 2006 1 51 1 9 2007 1
- "000001" "2006-04-10" "2006-12-31" 102333 "东北证券股份有限公司" 2006 1 52 0 9 2006 0
- "000001" "2006-05-30" "2006-12-31" 102333 "东北证券股份有限公司" 2006 1 53 0 9 2006 0
- "000001" "2006-05-30" "2007-12-31" 102333 "东北证券股份有限公司" 2006 1 54 0 9 2007 1
- "000001" "2006-10-26" "2006-12-31" 104393 "中国国际金融有限公司" 2006 1 55 1 9 2006 0
- "000001" "2006-10-26" "2007-12-31" 104393 "中国国际金融有限公司" 2006 1 56 0 9 2007 1
- "000001" "2006-06-15" "2006-12-31" 104393 "中国国际金融有限公司" 2006 1 57 0 9 2006 0
- "000001" "2006-06-15" "2007-12-31" 104393 "中国国际金融有限公司" 2006 1 58 0 9 2007 1
- "000001" "2006-01-11" "2006-12-31" 104534 "世纪证券有限责任公司" 2006 1 59 1 9 2006 0
- "000001" "2006-01-11" "2005-12-31" 104534 "世纪证券有限责任公司" 2006 1 60 0 9 2005 -1
- "000001" "2006-04-19" "2006-12-31" 104534 "世纪证券有限责任公司" 2006 1 61 0 9 2006 0
- "000001" "2006-04-19" "2007-12-31" 104534 "世纪证券有限责任公司" 2006 1 62 0 9 2007 1
- "000001" "2006-03-08" "2006-12-31" 104776 "中信证券股份有限公司" 2006 1 63 1 9 2006 0
- "000001" "2006-03-08" "2005-12-31" 104776 "中信证券股份有限公司" 2006 1 64 0 9 2005 -1
- "000001" "2006-10-13" "2007-12-31" 106064 "国泰君安证券股份有限公司" 2006 1 65 1 9 2007 1
- "000001" "2006-05-28" "2006-12-31" 106064 "国泰君安证券股份有限公司" 2006 1 66 0 9 2006 0
- "000001" "2006-05-28" "2007-12-31" 106064 "国泰君安证券股份有限公司" 2006 1 67 0 9 2007 1
- "000001" "2006-07-04" "2007-12-31" 106064 "国泰君安证券股份有限公司" 2006 1 68 0 9 2007 1
- "000001" "2006-07-04" "2006-12-31" 106064 "国泰君安证券股份有限公司" 2006 1 69 0 9 2006 0
- "000001" "2006-10-13" "2006-12-31" 106064 "国泰君安证券股份有限公司" 2006 1 70 0 9 2006 0
- "000001" "2006-02-14" "2005-12-31" 107973 "中信建投证券有限责任公司" 2006 1 71 1 9 2005 -1
- "000001" "2006-11-06" "2008-12-31" 107973 "中信建投证券有限责任公司" 2006 1 72 0 9 2008 2
- "000001" "2006-11-06" "2007-12-31" 107973 "中信建投证券有限责任公司" 2006 1 73 0 9 2007 1
- "000001" "2006-11-06" "2006-12-31" 107973 "中信建投证券有限责任公司" 2006 1 74 0 9 2006 0
- "000001" "2007-12-03" "2008-12-31" 10823 "天相投资顾问有限公司" 2007 1 75 1 16 2008 1
- "000001" "2007-10-23" "2007-12-31" 10823 "天相投资顾问有限公司" 2007 1 76 0 16 2007 0
- "000001" "2007-12-03" "2007-12-31" 10823 "天相投资顾问有限公司" 2007 1 77 0 16 2007 0
- "000001" "2007-10-23" "2009-12-31" 10823 "天相投资顾问有限公司" 2007 1 78 0 16 2009 2
- "000001" "2007-10-23" "2008-12-31" 10823 "天相投资顾问有限公司" 2007 1 79 0 16 2008 1
- "000001" "2007-12-03" "2008-12-31" 10974 "招商证券股份有限公司" 2007 1 80 1 16 2008 1
- "000001" "2007-10-23" "2008-12-31" 10974 "招商证券股份有限公司" 2007 1 81 0 16 2008 1
- "000001" "2007-10-23" "2007-12-31" 10974 "招商证券股份有限公司" 2007 1 82 0 16 2007 0
- "000001" "2007-12-03" "2007-12-31" 10974 "招商证券股份有限公司" 2007 1 83 0 16 2007 0
- "000001" "2007-12-03" "2009-12-31" 10974 "招商证券股份有限公司" 2007 1 84 0 16 2009 2
- "000001" "2007-12-03" "2007-12-31" 101434 "长江证券股份有限公司" 2007 1 85 1 16 2007 0
- "000001" "2007-12-03" "2008-12-31" 101434 "长江证券股份有限公司" 2007 1 86 0 16 2008 1
- "000001" "2007-03-23" "2009-12-31" 101506 "广发证券股份有限公司" 2007 1 87 1 16 2009 2
- "000001" "2007-10-23" "2008-12-31" 101506 "广发证券股份有限公司" 2007 1 88 0 16 2008 1
- "000001" "2007-03-23" "2008-12-31" 101506 "广发证券股份有限公司" 2007 1 89 0 16 2008 1
- "000001" "2007-10-23" "2009-12-31" 101506 "广发证券股份有限公司" 2007 1 90 0 16 2009 2
- "000001" "2007-05-16" "2007-12-31" 101506 "广发证券股份有限公司" 2007 1 91 0 16 2007 0
- "000001" "2007-10-23" "2007-12-31" 101506 "广发证券股份有限公司" 2007 1 92 0 16 2007 0
- "000001" "2007-03-23" "2007-12-31" 101506 "广发证券股份有限公司" 2007 1 93 0 16 2007 0
- "000001" "2007-12-18" "2007-12-31" 102306 "宏源证券股份有限公司" 2007 1 94 1 16 2007 0
- "000001" "2007-12-18" "2009-12-31" 102306 "宏源证券股份有限公司" 2007 1 95 0 16 2009 2
- "000001" "2007-12-18" "2008-12-31" 102306 "宏源证券股份有限公司" 2007 1 96 0 16 2008 1
- "000001" "2007-03-22" "2007-12-31" 104100 "申银万国证券股份有限公司" 2007 1 97 1 16 2007 0
- "000001" "2007-10-23" "2007-12-31" 104100 "申银万国证券股份有限公司" 2007 1 98 0 16 2007 0
- "000001" "2007-10-23" "2009-12-31" 104100 "申银万国证券股份有限公司" 2007 1 99 0 16 2009 2
- "000001" "2007-03-22" "2008-12-31" 104100 "申银万国证券股份有限公司" 2007 1 100 0 16 2008 1
- end