如何统计多个Excel文件的观测数并填充到SAS新数据表

原有代码问题说明 #

• dbms=xlxs拼写错误，正确值为dbms=xlsx
• 路径拼接逻辑错误，&i&&dt会把循环序号拼到日期前缀，不符合你给出的路径规则
• 变量解析错误，woorkbook1_&i&&date固定取workbook1，不会随循环切换为workbook2/workbook3
• 每次循环输出的hcw_&dt会被覆盖，无法保留多文件统计结果

最优解决方案 #

使用SAS内置的XLSX引擎通过LIBNAME关联Excel文件，直接读取元数据获取观测数，全程不会导入全量单元格内容，耗时仅为全量导入的1%不到，适配你给出的路径规则和目标表结构的完整代码如下：

%let path=C:\files;
/* 按【2位年日期|4位年日期】格式填入所有需要遍历的日期，多个日期用空格分隔 */
%let date_list=SEPT21|SEPT2021 OCT21|OCT2021 NOV21|NOV2021;

%macro get_excel_obs;
/* 初始化空目标表 */
proc sql;
    create table work.excel_obs_result (
        Date char(6),
        Workbook1 num,
        Workbook2 num,
        Workbook3 num
    );
quit;

/* 遍历所有日期 */
%do dt_idx=1 %to %sysfunc(countw(&date_list, %str( )));
    %let current_pair=%scan(&date_list, &dt_idx, %str( ));
    %let dt_short=%scan(&current_pair, 1, |); /* 提取2位年日期，如SEPT21 */
    %let dt_long=%scan(&current_pair, 2, |); /* 提取4位年日期，如SEPT2021 */
    %let wb1_obs=0;
    %let wb2_obs=0;
    %let wb3_obs=0;

    /* 遍历3个工作簿 */
    %do wb_idx=1 %to 3;
        %let xlsx_fullpath=&path\&dt_short\workbook&wb_idx._&dt_long..xlsx;
        /* 跳过不存在的文件 */
        %if %sysfunc(fileexist(&xlsx_fullpath)) %then %do;
            /* 只读方式关联Excel文件，仅读取元数据 */
            libname tmpwb xlsx "&xlsx_fullpath" access=readonly;
            /* 从系统字典表读取指定工作表的观测数 */
            proc sql noprint;
                select nobs into :wb&wb_idx._obs 
                from dictionary.tables 
                where libname='TMPWB' and memname=upcase(tranwrd('VCMR Data', ' ', '_'))
                ;
            quit;
            libname tmpwb clear;
        %end;
    %end;

    /* 将当前日期的统计结果写入目标表 */
    proc sql;
        insert into work.excel_obs_result 
        values ("&dt_short", &wb1_obs, &wb2_obs, &wb3_obs);
    quit;
%end;
%mend get_excel_obs;

/* 执行宏，最终结果保存在excel_obs_result表中 */
%get_excel_obs;

说明 #

• 执行完成后work.excel_obs_result就是你需要的目标表结构，不存在的文件对应字段值为0，可根据需求调整默认值
• 如果工作表名有其他特殊字符，调整memname的筛选规则即可，支持模糊匹配
• 本地SAS 9.4及以上版本默认支持XLSX引擎，无需额外安装组件

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