如何优化SAS缺失值检查宏？减少数据库请求次数

问题核心 #

原宏%database_null_check针对库中每一列单独发起一次数据库查询，当库中列数超过1000时，会产生上千次数据库请求；同时面对亿级行的大表，重复扫描全表导致效率极低，且无法通过缓存全表的方式优化。

优化思路 #

核心是按表批量处理：每个表仅发起一次数据库请求，在同一次查询中完成总行数统计（COUNT(*)）和所有列的非缺失值统计（COUNT(列名)），再通过转置将结果转换成原宏的纵向输出格式，彻底减少数据库交互次数。

优化后宏代码 #

%macro database_null_check(database= /*数据库LIBREF，示例：DMdata*/);
    %put ---------------------------------------------------------------;
    %put --- %upcase(&sysmacroname) 宏开始执行;
    %put ---;
    %put --- 宏参数值;
    %put --- database       =       &database;
    %put ---------------------------------------------------------------;

    /* 获取库下表和列的元数据，按表分组 */
    PROC SQL NOPRINT;
        CREATE TABLE table_cols AS
            SELECT 
                memname AS Tabelnaam,
                name AS Kolomnaam,
                /* 拼接每个列的COUNT语句，用于后续生成SQL */
                cats('COUNT(', name, ') AS cnt_', name) AS col_count_sql
            FROM dictionary.columns
            WHERE libname = %UPCASE("&database")
            ORDER BY memname, name;

        /* 按表分组，拼接该表所有列的COUNT语句 */
        SELECT 
            memname,
            cats('SELECT "', memname, '" AS Tabelnaam, COUNT(*) AS Aantal_regels, ', 
                catx(', ', col_count_sql), ' FROM ', libname, '.', memname) AS table_query
        INTO :table_list SEPARATED BY ';',
             :query_list SEPARATED BY ';'
        FROM (
            SELECT libname, memname, catx(', ', col_count_sql) AS col_count_sql
            FROM table_cols
            GROUP BY libname, memname
        );
    RUN;

    /* 执行所有表的查询，合并结果 */
    PROC SQL;
        CREATE TABLE all_table_stats AS
            &query_list.;
    RUN;

    /* 将宽表转置为纵向格式，匹配原宏输出结构 */
    PROC TRANSPOSE DATA=all_table_stats OUT=long_stats(RENAME=(COL1=Aantal_gevulde_regels _NAME_=Kolomnaam)) ;
        BY Tabelnaam Aantal_regels;
        VAR cnt_:;
    RUN;

    /* 清理转置后的列名前缀 */
    DATA long_stats_clean;
        SET long_stats;
        Kolomnaam = substr(Kolomnaam, 5); /* 去掉"cnt_"前缀 */
    RUN;

    /* 生成最终输出表，与原宏格式一致 */
    PROC SQL;
        CREATE TABLE output_null_controle AS
            SELECT 
                Tabelnaam,
                Kolomnaam,
                Aantal_regels FORMAT COMMA10.0,
                Aantal_gevulde_regels FORMAT COMMA10.0,
                (Aantal_regels - Aantal_gevulde_regels) AS Missende_regels FORMAT COMMA10.0,
                (Aantal_gevulde_regels / Aantal_regels) AS Percentage_gevuld FORMAT PERCENT10.2
            FROM long_stats_clean
            WHERE Tabelnaam IS NOT NULL;
    RUN;

    /* 清理临时表 */
    %dsdelete(ds=table_cols all_table_stats long_stats long_stats_clean);

    %put ---------------------------------------------------------------;
    %put --- %upcase(&sysmacroname) 宏执行结束;
    %put ---------------------------------------------------------------;
%mend;

关键优化点说明 #

1. 元数据分组处理：通过dictionary.columns按表分组，为每个表生成包含所有列COUNT语句的SQL，每个表仅需一次数据库请求。
2. 批量执行查询：将所有表的查询语句拼接后一次性执行，减少数据库连接开销。
3. 宽表转纵向：用PROC TRANSPOSE将每个表的宽统计结果转成原宏的纵向行结构，保证输出格式完全一致。
4. 减少重复扫描：每个表仅被数据库扫描一次，同时完成总行数和所有列的非缺失值统计，亿级大表的处理效率提升尤为明显。

输出示例（与原宏一致） #

内容的提问来源于stack exchange，提问作者Jesper van Beemdelust