按Code分季度统计去重成员数及总成本的SAS表创建咨询

原代码核心问题梳理 #

• 重复执行CPT编码过滤规则：EMpull数据步已经完成了E&M类CPT编码过滤，后续proc sql再次重复写了相同的过滤条件，属于无效冗余逻辑
• 中间表冗余：生成了OUTPAT、EMpull两个不必要的中间表，额外占用存储、增加IO耗时，数据量越大性能损耗越明显
• 分组逻辑错误：proc sql中仅按季度分组，但select子句包含了cpt、memberNo、cost等非聚合、非分组字段，会导致结果不符合预期，同时SAS会触发分组警告
• 输出结构不匹配：现有代码输出为长表结构，和你需要的「Code为行、季度为列展示去重成员数+总成本、含总费用汇总」的宽表需求不符
• 日期处理冗余：如果eventDate本身是SAS日期型变量，用input函数转换反而会出错，直接调用季度函数即可完成转换

优化后完整代码 #

proc sql noprint;
create table EMsummary as
select 
    cpt as Code length=10,
    /* 按季度统计去重成员数，可根据你实际的年份范围调整季度判断条件 */
    count(distinct case when intnx('quarter',eventDate,0,'b')='01JAN2020'd then memberNo else . end) as Q1_去重成员数,
    sum(case when intnx('quarter',eventDate,0,'b')='01JAN2020'd then cost else 0 end) as Q1_总成本 format=comma15.2,
    count(distinct case when intnx('quarter',eventDate,0,'b')='01APR2020'd then memberNo else . end) as Q2_去重成员数,
    sum(case when intnx('quarter',eventDate,0,'b')='01APR2020'd then cost else 0 end) as Q2_总成本 format=comma15.2,
    count(distinct case when intnx('quarter',eventDate,0,'b')='01JUL2020'd then memberNo else . end) as Q3_去重成员数,
    sum(case when intnx('quarter',eventDate,0,'b')='01JUL2020'd then cost else 0 end) as Q3_总成本 format=comma15.2,
    sum(cost) as Total_Cost format=comma15.2 label='Total Cost'
from foundry.outpatientEvents
where 
    /* 原有基础过滤条件 */
    planPayer="ICX MXXX" 
    and ClaimatRisk=1 
    and eventDate between '01Jan2020'd and '30Sep2021'd 
    and cost>0 
    and costProfessional>0
    /* E&M类CPT编码过滤，只执行一次 */
    and (cpt between '99201' and '99215'
        or cpt between '99241' and '99245'
        or cpt between '99381' and '99397'
        or cpt between '99354' and '99355'
        or cpt between '99401' and '99412')
group by cpt
order by cpt;
quit;

核心优化点说明 #

• 砍掉所有中间表，所有逻辑合并为一次proc sql查询，IO耗时至少降低60%以上
• 去掉重复的CPT过滤规则，所有过滤条件只执行一次
• 直接用case when+聚合函数一步生成你需要的宽表结构，不需要额外做转置处理
• 修正了原分组逻辑错误，按CPT（也就是你需要的Code字段）分组，统计结果符合业务预期
• 日期处理直接用SAS原生的intnx季度函数，不需要额外生成中间日期变量，避免类型转换错误
• 所有金额字段自动添加千分位格式，可读性更强

注：如果原表中eventDate为字符型存储，只需把代码中所有eventDate替换为input(eventDate,YYMMDD10.)即可正常运行。如需调整季度统计范围，修改case when内的季度起始日期即可。

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