PROC SQL子查询问题：筛选排除指定列的SAS表字段

问题描述 #

需求是从SASHELP.VCOLUMN中提取指定库表的所有字段，排除HAVE表中attribute_exclusion列指定的字段。当前使用PROC SQL子查询的代码未生效，返回了所有字段，但直接硬编码排除值时功能正常。

HAVE表结构及数据如下：

当前使用的PROC SQL代码：

proc sql;
create table want as
    select upcase(compress(t1.libname)) as libname
        , upcase(compress(t1.memname)) as memname
        , upcase(compress(t1.name)) as name
    from SASHELP.VCOLUMN t1, have t2
    where upcase(compress(t2.library)) = upcase(compress(t1.libname))
    and upcase(compress(t2.table)) = upcase(compress(t1.memname))
    and upcase(compress(t1.name)) not in 
        (SELECT t3.attribute_exclusion
        from have t3
        where upcase(compress(t3.library)) = upcase(compress(t1.libname))
        and upcase(compress(t3.table)) = upcase(compress(t1.memname))
        )
    ;
quit; 

问题原因 #

核心问题是HAVE表的attribute_exclusion列存储的是字符串形式的字段列表（比如('column_A','column_H')），而非SQL可直接识别的字段集合。

子查询返回的是单个完整字符串值，而非拆分后的独立字段名。当执行NOT IN判断时，是拿VCOLUMN中的字段名和这个完整字符串做对比，显然不会匹配，因此无法排除任何字段。

解决方法 #

方法1：拆分排除字段为独立行后关联 #

先将HAVE表中每个记录的排除字段拆分为单独行，再通过左关联过滤掉需要排除的字段：

/* 拆分HAVE表的排除字段为独立行 */
data have_split;
    set have;
    /* 移除括号、引号，提取纯字段名列表 */
    exclude_str = compress(attribute_exclusion, '()''');
    /* 按逗号拆分列表，逐个输出排除字段 */
    do i = 1 to countw(exclude_str, ',');
        exclude_col = upcase(compress(scan(exclude_str, i, ',')));
        output;
    end;
    drop exclude_str i attribute_exclusion;
run;

/* 关联拆分表，过滤排除字段 */
proc sql;
create table want as
    select upcase(compress(t1.libname)) as libname
         , upcase(compress(t1.memname)) as memname
         , upcase(compress(t1.name)) as name
    from SASHELP.VCOLUMN t1
    left join have_split t2
        on upcase(compress(t1.libname)) = upcase(compress(t2.library))
        and upcase(compress(t1.memname)) = upcase(compress(t2.table))
        and upcase(compress(t1.name)) = t2.exclude_col
    where t2.exclude_col is null; /* 保留未匹配到排除字段的记录 */
quit;

方法2：动态生成SQL语句 #

利用PROC SQL NOPRINT生成对应每个库表的过滤SQL，直接使用attribute_exclusion中的字符串作为NOT IN的条件，更贴合硬编码时的逻辑：

/* 生成动态SQL语句 */
proc sql noprint;
    select 
        catx(' ', 
             "select upcase(compress(libname)) as libname, upcase(compress(memname)) as memname, upcase(compress(name)) as name",
             "from SASHELP.VCOLUMN",
             "where upcase(compress(libname)) = '", upcase(compress(library)), "'",
             "and upcase(compress(memname)) = '", upcase(compress(table)), "'",
             "and upcase(compress(name)) not in ", attribute_exclusion
        ) into :sql_stmt separated by ';'
    from have;
quit;

/* 执行动态SQL并合并结果 */
proc sql;
    &sql_stmt;
    create table want as select * from union all corr;
quit;

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