Stata学习：如何构建企业新设子公司变量？

嵩人 · 发表于 2022-9-22 17:05:18

Stata看点

分组保留首次出现的观测
地理信息匹配

文献来源

刘秉镰等（2022）以被参控股公司的注册年份为时间，计算每个上市公司每年度在各省份新设子公司的数量。此外，根据母公司是否在本地和异地设立子公司，生成相应的虚拟变量。

删除参股比例小于50%以及持股不详的样本，只保留控股样本
删除子公司注册地址位于境外和北京、天津、上海、重庆4个直辖市的样本

刘秉镰,张伟静,刘玉海.地方经济政策不确定性与企业扩张选址——基于上市公司设立子公司的证据[J].经济地理,2022,42(05):23-35.

数据来源

国泰安。

下载表名上市公司子公司联营合营情况表
数据区间 1999-12-31 至 2021-12-31
选择代码全部代码
输出类型 Excel2007格式（*.xlsx）
选择字段证券代码[Symbol] 年度区间[EndDate] 关联公司名称[RalatedParty] 注册地[RegisterAddress] 区域标识[Sgnrgn] 是否退出[ISExit]
筛选条件 [DirectHoldingRatio] >= &#39;50&#39; [AreaName] = &#39;中国&#39;

清洗数据

clear
import excel &#34;D:\Download\上市公司子公司联营合营情况表184219666\STK_NotesSubJoint.xlsx&#34;, sheet(&#34;sheet1&#34;) firstrow
foreach var of varlist * {
label variable `var&#39; &#34;`=`var&#39;[1]&#39;&#34;
replace `var&#39; = &#34;&#34; if _n == 1
destring `var&#39; , replace
}
drop in 1/2
g year = substr(E,1,4)
drop if IS == &#34;1&#34;
drop IS E
foreach i in 北京天津上海重庆{
drop if regexm(Re, &#34;`i&#39;&#34;) == 1
}
drop if Re == &#34;&#34;
rename Sy Stkcd
order St y
destring S* y, force replace
drop Sg

* 保留公司首次出现的子公司
sort S y
g n = _n
bys S Ra: egen m = min(n)
order n m
sort n
keep if n == m
drop n m
order S y Re参考以下做法，放入do文件运行：

Mr Figurant：Stata学习：如何构建地区制度期望落差变量？

* 省份识别
cap drop Province flag
g Province = &#34;&#34;
g flag = 0
foreach i in 北京天津上海重庆河北山西辽宁吉林黑龙江江苏浙江 ///
         安徽福建江西山东河南湖北湖南广东海南四川贵州 ///
         云南陕西甘肃青海内蒙古广西西藏宁夏新疆{
replace Province = &#34;`i&#39;&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}

foreach i in 北京西城丰台{
replace Province = &#34;北京&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 天津{
replace Province = &#34;天津&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 石家庄{
replace Province = &#34;河北&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 太原{
replace Province = &#34;山西&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 呼和浩特内蒙{
replace Province = &#34;内蒙古&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 沈阳{
replace Province = &#34;辽宁&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 长春{
replace Province = &#34;吉林&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 哈尔滨{
replace Province = &#34;黑龙江&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 上海浦东新区{
replace Province = &#34;上海&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 南京{
replace Province = &#34;江苏&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 杭州{
replace Province = &#34;浙江&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 合肥{
replace Province = &#34;安徽&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 福州{
replace Province = &#34;福建&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 南昌{
replace Province = &#34;江西&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 济南{
replace Province = &#34;山东&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 郑州{
replace Province = &#34;河南&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 武汉{
replace Province = &#34;湖北&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 长沙{
replace Province = &#34;湖南&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 广州{
replace Province = &#34;广东&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 南宁{
replace Province = &#34;广西&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 海口{
replace Province = &#34;海南&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 重庆九龙坡区{
replace Province = &#34;重庆&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 成都{
replace Province = &#34;四川&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 贵阳{
replace Province = &#34;贵州&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 昆明{
replace Province = &#34;云南&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 拉萨{
replace Province = &#34;西藏&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 西安{
replace Province = &#34;陕西&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 兰州{
replace Province = &#34;甘肃&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 西宁{
replace Province = &#34;青海&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 银川{
replace Province = &#34;宁夏&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 乌鲁木齐{
replace Province = &#34;新疆&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}

foreach i in 石家庄张家口承德唐山秦皇岛廊坊保定沧州衡水邢台邯郸{
replace Province = &#34;河北&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 太原大同朔州忻州阳泉晋中吕梁长治临汾晋城运城{
replace Province = &#34;山西&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 呼伦贝尔通辽赤峰巴彦淖尔乌兰察布包头鄂尔多斯乌海{
replace Province = &#34;内蒙古&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 大连铁岭阜新抚顺朝阳本溪辽阳鞍山盘锦锦州葫芦岛营口瓦房店丹东连市沙河口{
replace Province = &#34;辽宁&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 长春白城松原吉林四平辽源白山通化{
replace Province = &#34;吉林&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 黑河伊春齐齐哈尔鹤岗佳木斯双鸭山绥化大庆七台河鸡西牡丹江{
replace Province = &#34;黑龙江&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 苏州连云港徐州宿迁淮安盐城泰州扬州镇江南通常州无锡张家港吴江宜兴江阴靖江常熟昆山太仓武进区{
replace Province = &#34;江苏&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 湖州嘉兴绍兴舟山宁波金华衢州台州丽水温州富阳嘉善永康诸暨{
replace Province = &#34;浙江&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 淮北亳州宿州蚌埠阜阳淮南滁州六安马鞍山芜湖宣城巢湖{
replace Province = &#34;安徽&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 宁德南平三明莆田龙岩泉州漳州厦门晋江{
replace Province = &#34;福建&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 九江景德镇上饶鹰潭抚州新余宜春萍乡吉安赣州{
replace Province = &#34;江西&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 德州滨州东营烟台威海淄博潍坊聊城泰安莱芜青岛龙口邹平临沭禹城阳谷宁阳沂源{
replace Province = &#34;山东&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 安阳鹤壁濮阳新乡焦作三门峡开封洛阳商丘许昌平顶山长葛新郑{
replace Province = &#34;河南&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 十堰襄樊随州荆门孝感宜昌黄冈鄂州荆州黄石咸宁潜江武昌珞瑜路{
replace Province = &#34;湖北&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 岳阳张家界常德益阳湘潭株洲娄底怀化邵阳衡阳永州郴州浏阳宁乡{
replace Province = &#34;湖南&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 珠海深圳韶关梅州河源清远潮州揭阳汕头肇庆惠州佛山东莞中山怀集县江门台山湛江{
replace Province = &#34;广东&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 桂林南宁柳州梧州北海防城港钦州贵港玉林百色贺州河池来宾崇左{
replace Province = &#34;广西&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 三亚三沙儋州{
replace Province = &#34;海南&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 广元巴中绵阳德阳达州南充遂宁广安资阳眉山雅安{
replace Province = &#34;四川&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 遵义六盘水安顺铜仁毕节黔东南黔南黔西南 {
replace Province = &#34;贵州&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 昭通丽江曲靖保山玉溪临沧普洱{
replace Province = &#34;云南&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 日喀则昌都林芝山南那曲{
replace Province = &#34;西藏&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 榆林延安铜川渭南宝鸡咸阳商洛汉中安康韩城杨凌农业{
replace Province = &#34;陕西&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 嘉峪关酒泉张掖金昌武威白银庆阳平凉定西天水陇南{
replace Province = &#34;甘肃&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 海东互助县{
replace Province = &#34;青海&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 石嘴山吴忠中卫固原{
replace Province = &#34;宁夏&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}
foreach i in 克拉玛依吐鲁番哈密库尔勒{
replace Province = &#34;新疆&#34; if regexm(Reg, &#34;`i&#39;&#34;) == 1 & Province == &#34;&#34;
}

* 继续排除直辖市数据
foreach i in 北京天津上海重庆{
drop if regexm(P, &#34;`i&#39;&#34;) == 1
}
foreach i in 东城区西城区崇文区宣武区朝阳区丰台区石景山区 ///
         海淀区门头沟区房山区通州区顺义区昌平区大兴区怀柔区平谷区{
drop if regexm(Reg, &#34;`i&#39;&#34;) == 1 & f == 0
}
foreach i in 滨海新区和平区河东区河西区南开区河北区红桥区东丽区西青区 ///
         津南区北辰区武清区宝坻区静海区宁河区蓟州区{
drop if regexm(Reg, &#34;`i&#39;&#34;) == 1 & f == 0
}
foreach i in 黄浦区徐汇区长宁区静安区普陀区虹口区杨浦区浦东新区闵行区 ///
         宝山区嘉定区金山区松江区青浦区奉贤区崇明区浦东{
drop if regexm(Reg, &#34;`i&#39;&#34;) == 1 & f == 0
}
foreach i in 万州区黔江区涪陵区渝中区大渡口区江北区沙坪坝区九龙坡区南岸区 ///
         北碚区渝北区巴南区长寿区江津区合川区永川区南川区綦江区大足区 ///
      璧山区铜梁区潼南区荣昌区开州区梁平区武隆区{
drop if regexm(Reg, &#34;`i&#39;&#34;) == 1 & f == 0
}
foreach i in 香港澳门台湾 {
drop if regexm(Reg, &#34;`i&#39;&#34;) == 1 & f == 0
}

drop if Reg == &#34;中国&#34;
drop if Reg == &#34;中国大陆&#34;
drop if Reg == &#34;中国内地&#34;

order P Re
replace flag = 1 if Province != &#34;&#34;
tabu flag
* list Re if f == 0匹配了：
   flag |    Freq.    Percent       Cum.
------------+-----------------------------------
      0 |    9,361    10.40    10.40
      1 |    80,656    89.60    100.00
------------+-----------------------------------
   Total |    90,017    100.00这里还需要更多人工识别和补齐，但本文不再展开。
调用：

如何构建数字普惠金融变量：上市公司注册地城市信息（注册地.dta）

继续计算：
rename Reg Z
rename S Symbol
merge m:1 S using 注册地, nogen keep(1 3)
* 子公司省份前两个字
g M = substr(Province,1,6)
* 是否同省
g same = (M == Prov2)
g diff = (M != Prov2)计算每个上市公司每年度在各省份新设子公司的数量：
bys S y Z: egen 各省份新设子公司的数量 = count(y)是否在本地和异地设立子公司，生成相应的虚拟变量：
bys S y: egen 本地新设立子公司 = sum(same)
bys S y: egen 异地新设立子公司 = sum(diff)
replace 本 = 1 if 本 > 1
replace 异 = 1 if 异 > 1保存1：
keep S y 各本异
duplicates drop
tabstat 各, by(y) s(N mean sd min p25 p50 p75 max) c(s)
list in 1/5
save 各省份新设子公司的数量得到：
   +---------------------------------------------+
   | Symbol year 各省~量本地~司异地~司 |
   |---------------------------------------------|
  1. |    1 1999       1       1       1 |
  2. |    1 1999       2       1       1 |
  3. |    1 2020       1       1       0 |
  4. |    2 1999       1       1       1 |
  5. |    2 1999       9       1       1 |
   +---------------------------------------------+

Summary for variables: 各省份新设子公司的数量
Group variable: year

year |       N    Mean       SD    Min    p25    p50    p75    Max
---------+--------------------------------------------------------------------------------
1999 |    759 2.54809  3.199603       1       1       1       3       31
2000 |    568  1.670775  1.607651       1       1       1       2       16
2001 |    650  1.690769  1.621715       1       1       1       2       15
2002 |    538  1.581784  1.295244       1       1       1       2       11
2003 |    587 1.55707  1.904207       1       1       1       2       37
2004 |    819  1.544567  1.431877       1       1       1       2       17
2005 |    616  1.391234  1.128142       1       1       1       1       13
2006 |    665  1.428571  1.181469       1       1       1       1       13
2007 |    1094  1.702011  1.944061       1       1       1       2       28
2008 |    1097  1.678213  1.895396       1       1       1       2       24
2009 |    1170  1.833333  2.320087       1       1       1       2       36
2010 |    1319  1.666414 1.6893       1       1       1       2       21
2011 |    1788  1.658837  1.663979       1       1       1       2       29
2012 |    1744  1.635321 1.92219       1       1       1       2       38
2013 |    1767  1.517827  1.254931       1       1       1       2       16
2014 |    1588 1.45466  1.244455       1       1       1       1       28
2015 |    1866 1.57717  1.510888       1       1       1       2       31
2016 |    2242  1.599019  1.672482       1       1       1       2       36
2017 |    2747  1.645795 1.55716       1       1       1       2       31
2018 |    2419 1.50186  1.458091       1       1       1       2       31
2019 |    2481  1.482467  1.214411       1       1       1       2       25
2020 |    2913  1.593546  1.640905       1       1       1       2       47
2021 |    2747  1.582454  1.498248       1       1       1       2       38
---------+--------------------------------------------------------------------------------
Total |    34184  1.609642  1.649662       1       1       1       2       47
------------------------------------------------------------------------------------------保存2：
drop 各
duplicates drop
tabu 本异
save 本地异地新设立子公司得到结果

本地新 | 异地新设立子公
设立子 |       司
   公司 |       0       1 |    Total
-----------+----------------------+----------
      0 |       0    10,508 | 10,508
      1 | 10,218    6,802 | 17,020
-----------+----------------------+----------
   Total | 10,218    17,310 | 27,528 （完）

苏宇锋 · 发表于 5 天前

求沙发

		自动登录	找回密码
密码			立即注册

Stata学习：如何构建企业新设子公司变量？

浏览过的版块