如何将含数组函数与临时列的SAS逻辑转换为SQL Server语句？

先把原SAS代码的核心逻辑理清楚，方便转换：

• 第一步：将SEL_MFCFINAN表按exer_sin, num_sin, num_vic升序，dat_oper, num_mvt降序排序，得到排序后的SEL表。这一步是为了让每个num_vic组内的记录按操作日期从新到旧排列。
• 第二步：对每个num_vic组（以exer_sin, num_sin, num_vic为分组键）初始化两个数组code和codep（长度为2023-2013+1=11，对应2013到2023每一年），初始值都是'0'。
• 第三步：按组内记录顺序（从新到旧），对每一年（从2023倒推到2013）的年底日期（12月31日）进行判断：如果当前记录的dat_oper<=该年底，且对应年份的code值还是'0'（说明该组还没输出过对应年份的记录），就将code对应位置设为'1'，标记sit='F'并输出这条记录。
• 最终效果：每个num_vic组，针对2013到2023的每一年，最多输出一条记录——即该组中操作日期最晚且不晚于当年年底的那条记录。

静态版本（YEAR=2023） #

先写针对2023年的静态SQL，逻辑清晰：

WITH SortedData AS (
    -- 模拟SAS的proc sort，给每个分组内的记录按dat_oper降序、num_mvt降序排号
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY exer_sin, num_sin, num_vic ORDER BY dat_oper DESC, num_mvt DESC) AS rn
    FROM SEL_MFCFINAN
),
YearlyDates AS (
    -- 生成2013到2023的年底日期
    SELECT CAST('2013-12-31' AS DATE) AS year_end
    UNION ALL SELECT '2014-12-31'
    UNION ALL SELECT '2015-12-31'
    UNION ALL SELECT '2016-12-31'
    UNION ALL SELECT '2017-12-31'
    UNION ALL SELECT '2018-12-31'
    UNION ALL SELECT '2019-12-31'
    UNION ALL SELECT '2020-12-31'
    UNION ALL SELECT '2021-12-31'
    UNION ALL SELECT '2022-12-31'
    UNION ALL SELECT '2023-12-31'
),
GroupedYearMatches AS (
    -- 找到每个分组、每个年份下，最早符合条件的记录（rn最小，即最新的那条符合dat_oper<=year_end的记录）
    SELECT 
        s.exer_sin,
        s.num_sin,
        s.num_vic,
        s.dat_oper,
        s.num_mvt,
        y.year_end,
        -- 取分组内第一条符合条件的记录
        FIRST_VALUE(s.rn) OVER (PARTITION BY s.exer_sin, s.num_sin, s.num_vic, y.year_end ORDER BY s.rn) AS match_rn
    FROM SortedData s
    CROSS JOIN YearlyDates y
    WHERE s.dat_oper <= y.year_end
)
-- 筛选出每个分组、每个年份的唯一匹配记录，标记sit='F'
SELECT 
    exer_sin,
    num_sin,
    num_vic,
    dat_oper,
    num_mvt,
    -- 保留原表其他字段的话，需要在这里加上，比如：s.* 但要排除rn
    'F' AS sit
FROM GroupedYearMatches
WHERE rn = match_rn
ORDER BY exer_sin, num_sin, num_vic, year_end DESC;

动态版本（支持任意YEAR值） #

如果需要像SAS宏一样支持动态年份，可以用动态SQL实现：

DECLARE @YEAR INT = 2023;
DECLARE @StartYear INT = 2013;
DECLARE @YearDates NVARCHAR(MAX) = '';

-- 生成动态的年份日期UNION语句
WHILE @StartYear <= @YEAR
BEGIN
    SET @YearDates += CASE WHEN @YearDates = '' THEN '' ELSE ' UNION ALL ' END
        + 'SELECT ''' + CAST(@StartYear AS VARCHAR) + '-12-31'' AS year_end';
    SET @StartYear += 1;
END

-- 拼接完整SQL并执行
DECLARE @FullSQL NVARCHAR(MAX) = '
WITH SortedData AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY exer_sin, num_sin, num_vic ORDER BY dat_oper DESC, num_mvt DESC) AS rn
    FROM SEL_MFCFINAN
),
YearlyDates AS (
    ' + @YearDates + '
),
GroupedYearMatches AS (
    SELECT 
        s.exer_sin,
        s.num_sin,
        s.num_vic,
        s.dat_oper,
        s.num_mvt,
        y.year_end,
        FIRST_VALUE(s.rn) OVER (PARTITION BY s.exer_sin, s.num_sin, s.num_vic, y.year_end ORDER BY s.rn) AS match_rn
    FROM SortedData s
    CROSS JOIN YearlyDates y
    WHERE s.dat_oper <= y.year_end
)
SELECT 
    exer_sin,
    num_sin,
    num_vic,
    dat_oper,
    num_mvt,
    -- 按需添加原表其他字段
    ''F'' AS sit
FROM GroupedYearMatches
WHERE rn = match_rn
ORDER BY exer_sin, num_sin, num_vic, year_end DESC;
';

EXEC sp_executesql @FullSQL;

• SAS的proc sort → SQL的ROW_NUMBER()窗口函数实现分组内排序，PARTITION BY对应SAS的by分组，ORDER BY对应排序规则。
• SAS的first.num_vic + retain数组 → SQL中通过FIRST_VALUE()找到分组内第一条符合条件的记录，确保每个年份每个分组只输出一条，对应SAS中code数组标记已处理的逻辑。
• SAS的年份循环%do j=&YEAR %to 2013 %by -1 → SQL中生成年份日期列表，通过CROSS JOIN关联每个记录和每个年份，再筛选符合条件的记录。

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