如何在SAS中按个体起始年份计算对应10年PM2.5变量均值？

针对按个体动态计算PM2.5均值的需求，以下是两种实用的SAS实现方法：

方法一：数组（ARRAY）方法（推荐） #

数组是处理这类按个体动态选择变量场景的最优方案，能直接针对每个观测的reqyear值筛选对应年份的PM2.5变量：

data want;
    set have;
    /* 定义数组，下标直接对应年份，关联所有pm25_total变量 */
    array pm25[2000:2018] pm25_total2000-pm25_total2018;
    
    start_year = reqyear - 9;
    pm25_avg = .;
    sum_pm25 = 0;
    count = 0;
    
    /* 遍历目标年份区间内的变量，累加非缺失值 */
    do year = start_year to reqyear;
        if not missing(pm25[year]) then do;
            sum_pm25 + pm25[year];
            count + 1;
        end;
    end;
    
    /* 计算均值，避免因全缺失导致的除以0错误 */
    if count > 0 then pm25_avg = sum_pm25 / count;
    
    drop start_year sum_pm25 count;
run;

说明： #

• 数组下标直接使用年份值，和变量名后缀的年份一一对应，无需额外映射；
• 循环逻辑会根据每个个体的reqyear动态计算起始年份，遍历对应区间的变量；
• 加入了缺失值处理，只统计非缺失的PM2.5数值，避免结果失真。

方法二：PROC SQL 条件求和法 #

如果更习惯用SQL语法，可以通过CASE语句筛选每个个体的目标变量，再计算均值：

proc sql;
    create table want as
    select *,
        (sum(
            case when 2000 between (reqyear-9) and reqyear then pm25_total2000 else . end,
            case when 2001 between (reqyear-9) and reqyear then pm25_total2001 else . end,
            case when 2002 between (reqyear-9) and reqyear then pm25_total2002 else . end,
            case when 2003 between (reqyear-9) and reqyear then pm25_total2003 else . end,
            case when 2004 between (reqyear-9) and reqyear then pm25_total2004 else . end,
            case when 2005 between (reqyear-9) and reqyear then pm25_total2005 else . end,
            case when 2006 between (reqyear-9) and reqyear then pm25_total2006 else . end,
            case when 2007 between (reqyear-9) and reqyear then pm25_total2007 else . end,
            case when 2008 between (reqyear-9) and reqyear then pm25_total2008 else . end,
            case when 2009 between (reqyear-9) and reqyear then pm25_total2009 else . end,
            case when 2010 between (reqyear-9) and reqyear then pm25_total2010 else . end,
            case when 2011 between (reqyear-9) and reqyear then pm25_total2011 else . end,
            case when 2012 between (reqyear-9) and reqyear then pm25_total2012 else . end,
            case when 2013 between (reqyear-9) and reqyear then pm25_total2013 else . end,
            case when 2014 between (reqyear-9) and reqyear then pm25_total2014 else . end,
            case when 2015 between (reqyear-9) and reqyear then pm25_total2015 else . end,
            case when 2016 between (reqyear-9) and reqyear then pm25_total2016 else . end,
            case when 2017 between (reqyear-9) and reqyear then pm25_total2017 else . end,
            case when 2018 between (reqyear-9) and reqyear then pm25_total2018 else . end
        ) / count(
            case when 2000 between (reqyear-9) and reqyear then pm25_total2000 else . end,
            case when 2001 between (reqyear-9) and reqyear then pm25_total2001 else . end,
            case when 2002 between (reqyear-9) and reqyear then pm25_total2002 else . end,
            case when 2003 between (reqyear-9) and reqyear then pm25_total2003 else . end,
            case when 2004 between (reqyear-9) and reqyear then pm25_total2004 else . end,
            case when 2005 between (reqyear-9) and reqyear then pm25_total2005 else . end,
            case when 2006 between (reqyear-9) and reqyear then pm25_total2006 else . end,
            case when 2007 between (reqyear-9) and reqyear then pm25_total2007 else . end,
            case when 2008 between (reqyear-9) and reqyear then pm25_total2008 else . end,
            case when 2009 between (reqyear-9) and reqyear then pm25_total2009 else . end,
            case when 2010 between (reqyear-9) and reqyear then pm25_total2010 else . end,
            case when 2011 between (reqyear-9) and reqyear then pm25_total2011 else . end,
            case when 2012 between (reqyear-9) and reqyear then pm25_total2012 else . end,
            case when 2013 between (reqyear-9) and reqyear then pm25_total2013 else . end,
            case when 2014 between (reqyear-9) and reqyear then pm25_total2014 else . end,
            case when 2015 between (reqyear-9) and reqyear then pm25_total2015 else . end,
            case when 2016 between (reqyear-9) and reqyear then pm25_total2016 else . end,
            case when 2017 between (reqyear-9) and reqyear then pm25_total2017 else . end,
            case when 2018 between (reqyear-9) and reqyear then pm25_total2018 else . end
        )) as pm25_avg
    from have;
quit;

说明： #

• 每个CASE语句判断对应年份是否在目标区间内，符合条件则取变量值，否则返回缺失值；
• 通过SUM和COUNT分别计算有效数值的总和与数量，最终得到均值；
• 缺点是需要手动列出所有年份的CASE语句，变量较多时操作繁琐，适合变量数量少的场景。

为什么宏变量方法不可行？ #

宏变量是SAS编译阶段解析的全局常量，无法针对每个观测（个体）动态调整取值，所以%eval(&reqy.-9)只能得到一个固定值，无法实现按个体计算的需求。

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