Stata中实现类似Excel COUNTIFS的简单计数需求

需求说明 #

现有四个变量：

• AllCEOs：0/1虚拟变量，标记有效条目
• Year：年份变量
• TwoSic1、TwoSic2：存储行业ID的字符串变量

需要实现：按年份统计，对应年份中某行业ID（可出现在TwoSic1或TwoSic2任意一列）的有效AllCEOs数量。核心难点在于需同时遍历两列行业ID进行计数，常规egen total(...)命令无法处理行业ID跨列出现的场景。

补充说明：需生成变量_CountTwoSic1，其中_CountTwoSic1[row1]表示对应年份内，TwoSic1[row1]的值在TwoSic1和TwoSic2两列中出现的有效条目（AllCEOs=1）次数。后续对TwoSic2的重复操作无需额外处理。

示例数据 #

解决方案 #

通过重塑数据+分组统计+合并回原数据的思路实现，以下是具体Stata代码：

方法1：基于reshape的分步实现 #

* 1. 生成唯一观测ID，用于后续合并
gen obs_id = _n

* 2. 临时保存原数据，将两列行业ID转为长格式
preserve
keep obs_id Year AllCEOs TwoSic1 TwoSic2
reshape long TwoSic, i(obs_id Year) j(sic_col)
rename TwoSic sic_code

* 3. 按年份+行业ID统计有效条目数
bysort Year sic_code: gen total_count = total(AllCEOs)

* 4. 去重后转回宽格式，合并回原数据
drop sic_col
duplicates drop obs_id Year, force
rename total_count _CountTwoSic1_temp
restore
merge 1:1 obs_id Year using `tempfile', nogen

* 5. 匹配当前观测TwoSic1对应的计数结果
gen _CountTwoSic1 = .
forvalues i = 1/`=_N' {
    local sic = TwoSic1[`i']
    local yr = Year[`i']
    replace _CountTwoSic1 = _CountTwoSic1_temp if Year == `yr' & sic_code == "`sic'"
}

* 清理临时变量
drop obs_id _CountTwoSic1_temp sic_code

方法2：基于stack的高效实现 #

* 1. 生成唯一观测ID
gen obs_id = _n

* 2. 生成行业ID-年份的计数映射表
preserve
keep Year TwoSic1 TwoSic2 AllCEOs
stack TwoSic1 TwoSic2, into(sic_code) clear
bysort Year sic_code: gen total_count = total(AllCEOs)
duplicates drop Year sic_code, force
save "sic_year_count.dta", replace
restore

* 3. 合并映射表到原数据，匹配TwoSic1对应的计数
merge m:1 Year sic_code = TwoSic1 using "sic_year_count.dta", nogen
rename total_count _CountTwoSic1

* 清理临时文件与变量
erase "sic_year_count.dta"
drop obs_id

内容的提问来源于stack exchange，提问作者Lennart Osses