如何用SAS Studio批量读取SQL文本文件并导出Snowflake执行结果至Excel

问题描述 #

我在SAS Studio的文件夹中存储了多个包含Snowflake数据库SQL代码的.txt文件，需要实现批量读取这些文件，通过SAS Studio在Snowflake上执行SQL，并将每个SQL的执行结果分别导出为Excel文件。我熟悉SQL但刚接触SAS，目前已实现单文件读取执行并导出为CSV的功能，现寻求批量处理的详细步骤与解决方案。

现有单文件处理代码：

data _null_;             *reading the SQL script into a variable, hopefully under 32767?;
infile "/dslanalytics-shared/dgupt12/SQLs/Query.txt" recfm=f lrecl=32767 pad;
input @1 sqlcode $32767.;
call symputx('sqlcode',sqlcode);  *putting it into a macro variable;
run;

proc sql;
connect to odbc as mycon (complete="DRIVER={SnowflakeDSIIDriver};
SERVER=;
UID=&usr.;
PWD=&pwd.;
WAREHOUSE=;
DATABASE=;
SCHEMA=;
dbcommit=10000 autocommit=no
readbuff=200 insertbuff=200;");

create table final_export as
select * from connection to mycon(&sqlcode.);
disconnect from mycon;
quit;

proc export data = work.final_export
outfile = "/dslanalytics-shared/dgupt12/Report/final_report.csv"
DBMS = csv REPLACE;
run;

批量处理实现步骤 #

1. 批量获取SQL文件列表 #

通过SAS的filename结合系统命令，抓取指定文件夹下所有.txt格式的SQL脚本路径，并生成包含文件路径和文件名的数据集，用于后续循环处理。

2. 封装单文件处理宏 #

将单文件读取、Snowflake执行、结果导出的逻辑封装成SAS宏，让每个文件的处理逻辑可复用。

3. 循环遍历执行宏 #

利用宏循环遍历文件列表数据集，逐个调用宏处理每个SQL文件，确保每个执行结果对应导出为同名Excel文件。

4. 适配Excel导出格式 #

将原CSV导出改为Excel格式（DBMS=xlsx），并关联SQL文件名生成对应的输出文件路径。

完整批量处理代码 #

/* 1. 获取指定文件夹下的所有.txt SQL文件列表（Unix/Linux环境） */
filename sqlfiles pipe 'ls /dslanalytics-shared/dgupt12/SQLs/*.txt';
/* Windows环境请替换为：filename sqlfiles pipe 'dir /b "你的SQL文件夹路径\*.txt"' */

data sql_file_list;
  length file_path $256 file_name $100;
  infile sqlfiles truncover;
  input file_path $256.;
  /* 提取纯文件名（不含路径和后缀），用于生成对应Excel文件名 */
  file_name = scan(scan(file_path, -1, '/'), 1, '.'); 
run;

/* 2. 封装处理单个SQL文件的宏 */
%macro process_sql(file_path=, file_name=);
  /* 读取SQL脚本到宏变量 */
  data _null_;
    infile "&file_path." recfm=f lrecl=32767 pad;
    input @1 sqlcode $32767.;
    call symputx('sqlcode', sqlcode);
  run;

  /* 连接Snowflake并执行SQL */
  proc sql;
    connect to odbc as mycon (complete="DRIVER={SnowflakeDSIIDriver};
    SERVER=; /* 填写你的Snowflake服务器地址 */
    UID=&usr.; /* 确保已提前定义&usr.宏变量 */
    PWD=&pwd.; /* 确保已提前定义&pwd.宏变量 */
    WAREHOUSE=; /* 填写你的仓库名 */
    DATABASE=; /* 填写你的数据库名 */
    SCHEMA=; /* 填写你的模式名 */
    dbcommit=10000 autocommit=no
    readbuff=200 insertbuff=200;");

    create table work.temp_result as
    select * from connection to mycon(&sqlcode.);
    disconnect from mycon;
  quit;

  /* 导出为Excel文件，文件名与原SQL文件对应 */
  proc export data = work.temp_result
  outfile = "/dslanalytics-shared/dgupt12/Report/&file_name..xlsx"
  DBMS = xlsx REPLACE;
  run;

  /* 删除临时表释放资源 */
  proc datasets library=work nolist;
    delete temp_result;
  quit;
%mend process_sql;

/* 3. 循环遍历文件列表，批量执行宏 */
proc sql noprint;
  select count(*) into :file_count from sql_file_list;
  /* 将文件路径和文件名转成宏变量列表 */
  select quote(trim(file_path)), quote(trim(file_name)) 
    into :file_paths separated by ' ', :file_names separated by ' ' 
    from sql_file_list;
quit;

/* 循环处理每个文件 */
%do i=1 %to &file_count.;
  %let current_path = %scan(&file_paths., &i., ' ');
  %let current_name = %scan(&file_names., &i., ' ');
  %process_sql(file_path=&current_path., file_name=&current_name.);
%end;

关键注意事项 #

• 环境适配：Windows和Unix/Linux环境的文件列表命令不同，需根据SAS运行环境调整filename语句。
• SQL长度限制：如果SQL脚本超过32767字符，需修改读取逻辑，比如定义更长的字符变量（SAS 9.4及以上支持）并调整infile参数：

  data _null_;
    length sqlcode $100000;
    infile "&file_path." recfm=f lrecl=100000 pad flowover;
    input sqlcode $char100000.;
    call symputx('sqlcode', sqlcode, 'l'); /* 避免宏变量截断 */
  run;
  

• 连接参数：确保Snowflake的服务器、仓库、数据库等参数已正确填写，&usr.和&pwd.需提前通过%let定义。
• 权限验证：确认SAS Studio有读取SQL文件夹和写入Report文件夹的权限。

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