如何在SAS中用数组简化含宏变量的月度比率与求和计算？

问题背景 #

需对宽格式数据集按月份列计算ROLL列总和与ACCOUNTS列总和的百分比比率，同时保留ROLL列的求和结果，希望简化重复冗余的PROC SQL代码。

原始PROC SQL实现 #

PROC SQL;
CREATE TABLE WANT AS 
SELECT 
 (SUM(ROLL_&mmmyy2) /SUM(ACCOUNTS_&mmmyy2))*100 AS &mmmyy2
,(SUM(ROLL_&mmmyy3) /SUM(ACCOUNTS_&mmmyy3))*100 AS &mmmyy3
,(SUM(ROLL_&mmmyy4) /SUM(ACCOUNTS_&mmmyy4))*100 AS &mmmyy4
,(SUM(ROLL_&mmmyy5) /SUM(ACCOUNTS_&mmmyy5))*100 AS &mmmyy5
,(SUM(ROLL_&mmmyy6) /SUM(ACCOUNTS_&mmmyy6))*100 AS &mmmyy6
,(SUM(ROLL_&mmmyy7) /SUM(ACCOUNTS_&mmmyy7))*100 AS &mmmyy7
,(SUM(ROLL_&mmmyy8) /SUM(ACCOUNTS_&mmmyy8))*100 AS &mmmyy8
,(SUM(ROLL_&mmmyy9) /SUM(ACCOUNTS_&mmmyy9))*100 AS &mmmyy9
,(SUM(ROLL_&mmmyy10) /SUM(ACCOUNTS_&mmmyy10))*100 AS &mmmyy10
,(SUM(ROLL_&mmmyy11) /SUM(ACCOUNTS_&mmmyy11))*100 AS &mmmyy11
,(SUM(ROLL_&mmmyy12) /SUM(ACCOUNTS_&mmmyy12))*100 AS &mmmyy12
,(SUM(ROLL_&mmmyy13) /SUM(ACCOUNTS_&mmmyy13))*100 AS &mmmyy13
,(SUM(ROLL_&mmmyy14) /SUM(ACCOUNTS_&mmmyy14))*100 AS &mmmyy14

,SUM(ROLL_&mmmyy2) AS SUM_&mmmyy2
,SUM(ROLL_&mmmyy3) AS SUM_&mmmyy3
,SUM(ROLL_&mmmyy4) AS SUM_&mmmyy4
,SUM(ROLL_&mmmyy5) AS SUM_&mmmyy5
,SUM(ROLL_&mmmyy6) AS SUM_&mmmyy6
,SUM(ROLL_&mmmyy7) AS SUM_&mmmyy7
,SUM(ROLL_&mmmyy8) AS SUM_&mmmyy8
,SUM(ROLL_&mmmyy9) AS SUM_&mmmyy9
,SUM(ROLL_&mmmyy10) AS SUM_&mmmyy10
,SUM(ROLL_&mmmyy11) AS SUM_&mmmyy11
,SUM(ROLL_&mmmyy12) AS SUM_&mmmyy12
,SUM(ROLL_&mmmyy13) AS SUM_&mmmyy13
,SUM(ROLL_&mmmyy14) AS SUM_&mmmyy14

FROM HAVE;
QUIT;

其中&mmmyy(number)为日期类宏变量，用于动态指定月份列名。

尝试的DATA步数组实现（存在问题） #

DATA WANT;
SET HAVE;
ARRAY ROLL_&&MMMYY[12] ROLL_&&MMMYY2-ROLL_&&MMMYY14; 
ARRAY ACCOUNTS_&&MMMYY[12] ACCOUNT&&MMMYY2-ACCOUNT&&MMMYY14;
DO I = 2 TO 14;
&&MMMYY[I]= (SUM(ROLL_&&MMMYY[I])/SUM(ACCOUNTS_&&MMMYY[I]))*100;
END;
RUN;

数据示例 #

Account Number  ROLL_Jun23  Roll_May23  Roll_Apr23  Roll_Mar23 Accounts_Jun23   Accounts_May23  Accounts_Apr23  Accounts_Mar23
1                  1            0           0           0           1                   1           1                 1
2                  0            1           1           0           1                   1           0                 0
3                  0            0           0           0           1                   0           0                 0
4                  1            1           1           1           1                   1           1                 0
5                  1            1           1           1           1                   1           1                 0
6                  1            1           0           0           1                   1           0                 0

预期输出 #

Jun23        May23        Apr23        Mar23        
66.67        80.00        100.00       200.00

正确的简化实现方案 #

方案1：宏循环生成PROC SQL代码 #

利用SAS宏循环替代重复的列定义，代码简洁易维护：

%MACRO CALC_RATIO;
PROC SQL;
CREATE TABLE WANT AS 
SELECT 
    %DO I=2 %TO 14;
        (SUM(ROLL_&&mmmyy&I) / SUM(ACCOUNTS_&&mmmyy&I))*100 AS &&mmmyy&I
        %IF &I NE 14 %THEN ,;
    %END;
    ,
    %DO I=2 %TO 14;
        SUM(ROLL_&&mmmyy&I) AS SUM_&&mmmyy&I
        %IF &I NE 14 %THEN ,;
    %END;
FROM HAVE;
QUIT;
%MEND CALC_RATIO;

%CALC_RATIO;

方案2：DATA步+数组（扩展性更强） #

通过通配符匹配列，无需枚举所有列名，适合列数动态变化的场景：

/* 第一步：计算各ROLL和ACCOUNTS列的总和 */
DATA SUM_TEMP;
    SET HAVE END=LAST;
    ARRAY ROLL_COLUMNS[*] ROLL_:; /* 匹配所有ROLL开头的列 */
    ARRAY ACCOUNTS_COLUMNS[*] ACCOUNTS_:; /* 匹配所有ACCOUNTS开头的列 */
    ARRAY SUM_ROLL[*] SUM_ROLL1-SUM_ROLL%SYSFUNC(COUNTW(%QUOTE(ROLL_&&mmmyy2-ROLL_&&mmmyy14),-));
    ARRAY SUM_ACCOUNTS[*] SUM_ACCOUNTS1-SUM_ACCOUNTS%SYSFUNC(COUNTW(%QUOTE(ROLL_&&mmmyy2-ROLL_&&mmmyy14),-));
    
    DO I=1 TO DIM(ROLL_COLUMNS);
        SUM_ROLL[I] + ROLL_COLUMNS[I];
        SUM_ACCOUNTS[I] + ACCOUNTS_COLUMNS[I];
    END;
    
    IF LAST THEN OUTPUT;
    KEEP SUM_ROLL: SUM_ACCOUNTS:;
RUN;

/* 第二步：计算比率并生成目标数据集 */
DATA WANT;
    SET SUM_TEMP;
    ARRAY SUM_ROLL[*] SUM_ROLL:;
    ARRAY SUM_ACCOUNTS[*] SUM_ACCOUNTS:;
    ARRAY RATIO_COLUMNS[*] &&mmmyy2-&&mmmyy14; /* 对应月份列名 */
    ARRAY SUM_COLUMNS[*] SUM_&&mmmyy2-SUM_&&mmmyy14; /* 对应总和列名 */
    
    DO I=1 TO DIM(SUM_ROLL);
        RATIO_COLUMNS[I] = (SUM_ROLL[I]/SUM_ACCOUNTS[I])*100;
        SUM_COLUMNS[I] = SUM_ROLL[I];
    END;
    
    KEEP &&mmmyy2-&&mmmyy14 SUM_&&mmmyy2-SUM_&&mmmyy14;
    FORMAT &&mmmyy2-&&mmmyy14 8.2; /* 设置百分比列格式 */
RUN;

方案说明 #

• 宏循环方案保留了PROC SQL的简洁性，仅通过循环生成重复计算语句，适合需保持宽格式输出且宏变量已定义的场景。
• DATA步数组方案通过通配符匹配列，扩展性更强，无需手动枚举所有列名，适配列数动态变化的情况。

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