相邻年份相同名字归于同一列

854

收藏 2022-08-07

示例数据如下，我想要的是签字会计师名字相同的尽量在一列。今天上午请教了两列数据如何处理，但是最终发现发现个别公司是三个审计师，难度瞬间翻好几倍，我写了循环语句无法处理。于是又来求助了。
clear
input str12 stkcd double year str54 auditor1 str18 auditor2 str9 auditor3
"000417" 2000 "李静" "章正芳" ""
"000417" 2001 "谭汝静" "李静" ""
"000417" 2002 "李静" "谭汝建" ""
"000417" 2003 "马静" "吕勇军" ""
"000417" 2004 "李东昕" "吕勇军" ""
"000417" 2005 "李东昕" "李静" ""
"000417" 2006 "李静" "沈素莹" ""
"000417" 2007 "李静" "沈素莹" ""
"000417" 2008 "李静" "沈素莹" ""
"000417" 2009 "李静" "沈素莹" ""
"000417" 2010 "吕勇军" "沈素莹" ""
"000417" 2011 "吕勇军" "李静" ""
"000417" 2012 "李静" "刘力争" ""
"000417" 2013 "李静" "沈素莹" ""
"000417" 2014 "李静" "沈素莹" ""
"000417" 2015 "吕勇军" "沈素莹" ""
"000417" 2016 "李静" "沈素莹" ""
"000417" 2017 "李静" "沈素莹" ""
"000417" 2018 "吕勇军" "李静" ""
"000417" 2019 "张居忠" "文冬梅" ""
"000417" 2020 "张居忠" "文冬梅" "王申申"
"000417" 2021 "文冬梅" "王申申" ""
end

想要的结果如下
clear
input str12 stkcd double year str54 auditor1 str18 auditor2 str9 auditor3
"000417" 2000 "李静" "章正芳" ""
"000417" 2001 "李静" "谭汝静"  ""
"000417" 2002 "李静" "谭汝建" ""
"000417" 2003 "马静" "吕勇军" ""
"000417" 2004 "李东昕" "吕勇军" ""
"000417" 2005 "李东昕" "李静" ""
"000417" 2006 "沈素莹" "李静"  ""
"000417" 2007  "沈素莹"  "李静"  ""
"000417" 2008 "沈素莹"  "李静" ""
"000417" 2009 "沈素莹"  "李静" ""
"000417" 2010 "沈素莹" "吕勇军" ""
"000417" 2011  "李静"  "吕勇军"  ""
"000417" 2012 "李静" "刘力争" ""
"000417" 2013 "李静" "沈素莹" ""
"000417" 2014 "李静" "沈素莹" ""
"000417" 2015 "吕勇军" "沈素莹" ""
"000417" 2016 "李静" "沈素莹" ""
"000417" 2017 "李静" "沈素莹" ""
"000417" 2018  "李静" "吕勇军" ""
"000417" 2019 "张居忠" "文冬梅" ""
"000417" 2020 "张居忠" "文冬梅" "王申申"
"000417" 2021  "" "文冬梅" "王申申"
end

若自有两列的解决方法见帖子：https://bbs.pinggu.org/thread-11172305-1-1.html

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

全部回复

C2821183331

2022-8-7 23:54:47

原始数据截图：

处理效果：

两列数据处理方法见帖子：
https://bbs.pinggu.org/thread-11172305-1-1.html
需要将命令修改为：

qui levelsof year, local(years)
foreach i of local years{
g holding= auditor1
bys stkcd (year): g tag= auditor1==auditor2[_n-1]| auditor2==auditor1[_n-1]
qui replace auditor1= auditor2 if tag
qui replace auditor2= holding if tag
drop tag holding
}

附件列表

1.png

原图尺寸 16.9 KB