如何从DICTIONARY表获取首个DATE和NUM格式的列名？

你之前用max()函数得到的是列名字母顺序最大的结果，而非变量在表中的实际顺序，要解决这个问题，必须利用DICTIONARY.COLUMNS里的VARNUM属性——它记录了变量在表中的位置序号，按这个排序就能拿到真实的变量顺序。

下面提供两种可行的实现方式：

方法1：PROC SQL窗口函数实现 #

通过窗口函数ROW_NUMBER()按库名、表名分组，对变量按VARNUM升序排序，筛选出每个表中符合条件的第一条记录：

proc sql;
create table want as
select 
    UPCASE(libname) as mylib,
    UPCASE(memname) as mytable,
    max(case when rn_num = min_num_rn then first_att_num end) as first_att_num,
    max(case when rn_num = min_date_rn then first_att_date end) as first_att_date
from (
    select 
        libname,
        memname,
        case when type='num' and xtype in ('','decimal') then upcase(name) end as first_att_num,
        case when substr(format,1,4)='DATE' and xtype in ('date','num') then upcase(name) end as first_att_date,
        row_number() over(partition by libname, memname order by varnum) as rn_num,
        /* 记录每个表中首个NUM列的行号 */
        min(case when type='num' and xtype in ('','decimal') then row_number() over(partition by libname, memname order by varnum) end) 
            over(partition by libname, memname) as min_num_rn,
        /* 记录每个表中首个DATE列的行号 */
        min(case when substr(format,1,4)='DATE' and xtype in ('date','num') then row_number() over(partition by libname, memname order by varnum) end) 
            over(partition by libname, memname) as min_date_rn
    from DICTIONARY.COLUMNS
    where (upcase(libname), upcase(memname)) in (select upcase(libname), upcase(memname) from have)
)
group by libname, memname;
quit;

方法2：DATA步实现（逻辑更直观） #

先把字典表的变量按库、表、VARNUM排序，再按表分组，逐个检查变量，记录第一个符合条件的列名：

/* 先整理需要查询的表列表 */
proc sort data=have out=have_sorted;
    by libname memname;
run;

/* 提取目标表的变量信息，按实际顺序排序 */
proc sql;
create table cols_sorted as
select 
    UPCASE(t1.libname) as mylib,
    UPCASE(t1.memname) as mytable,
    upcase(t1.name) as colname,
    t1.type,
    t1.xtype,
    t1.format
from DICTIONARY.COLUMNS t1
inner join have_sorted t2
    on upcase(t2.libname) = upcase(t1.libname)
    and upcase(t2.memname) = upcase(t1.memname)
order by mylib, mytable, t1.varnum;
quit;

/* 遍历每个表，提取首个符合条件的列 */
data want;
    set cols_sorted;
    by mylib mytable;
    /* 每个表开始时清空临时变量 */
    if first.mytable then do;
        first_att_num = '';
        first_att_date = '';
    end;
    /* 找到第一个NUM类型列就记录 */
    if first_att_num = '' and type='num' and xtype in ('','decimal') then
        first_att_num = colname;
    /* 找到第一个DATE格式列就记录 */
    if first_att_date = '' and substr(format,1,4)='DATE' and xtype in ('date','num') then
        first_att_date = colname;
    /* 每个表最后一行输出结果 */
    if last.mytable then output;
    keep mylib mytable first_att_num first_att_date;
run;

核心说明 #

• VARNUM是关键：它决定了变量在原表中的顺序，这是你之前代码缺失的逻辑。
• 原代码用max()是按列名的字母顺序取值，和变量实际位置无关，所以结果不符合预期。
• DATA步的写法逻辑更清晰，适合调试；PROC SQL窗口函数则更简洁，处理大数据量时效率更高。

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