如何将日期参数传递至SAS临时（Work）表查询？语法错误排查

问题场景 #

通过SAS Passthrough从Oracle数据库生成work.inc_mails临时表后，使用start_sas_month和end_sas_month宏变量筛选上月数据时，出现Error 22-322语法错误，无法生成正确的筛选数据集。原代码如下：

%let n_months = 1;

%let date = %sysfunc(date());

%let start_month = %sysfunc(intnx(month, &date., -&n_months.));
%let start_sas_month = %str(%')%sysfunc(putn(&start_month., date9.))%str(%'); %put &start_sas_month.;

%let end_month =  %sysfunc(intnx(day, %sysfunc(intnx(month, &date., 1-&n_months., b)), -1, s));
%let end_sas_month = %str(%')%sysfunc(putn(&end_month., date9.))%str(%'); %put &end_sas_month.;


proc sql;
connect to oracle as xyz (authdomain="XYZ_READ" path=XYZ_P);
create table work.inc_mails as 
select * from connection to xyz

(   
    SELECT 
    ap.Id,
    ap.Completed_Date

    FROM xy.Appointments ap
); 

disconnect from xyz;
Quit;


proc sql;
CREATE TABLE final_tbl AS
SELECT
i.Id,
i.Completed_Date

FROM work.inc_mails i

WHERE 
i.Completed_Date >= &start_sas_month.
AND i.Completed_Date <= &end_sas_month.
;
quit;

错误原因 #

核心问题是数据类型不匹配：

• work.inc_mails中的Completed_Date是从Oracle读取的SAS日期数值（本质是自1960年1月1日起的天数，属于数值型）
• 而生成的start_sas_month和end_sas_month是带单引号的字符型日期字符串（比如'01JAN2024'）
  直接用数值型日期和字符型字符串做比较，SAS无法识别这种语法，从而触发错误。

解决方案 #

方案1：使用数值型日期宏变量（推荐） #

直接生成SAS日期数值型的宏变量，避免类型转换问题，代码更简洁高效：

%let n_months = 1;
%let date = %sysfunc(date());

/* 生成上月第一天的SAS日期数值（intnx函数加b参数取月初） */
%let start_month = %sysfunc(intnx(month, &date., -&n_months., b));
/* 生成上月最后一天的SAS日期数值（intnx函数加e参数取月末） */
%let end_month = %sysfunc(intnx(month, &date., -&n_months., e));

proc sql;
connect to oracle as xyz (authdomain="XYZ_READ" path=XYZ_P);
create table work.inc_mails as 
select * from connection to xyz
(   
    SELECT 
    ap.Id,
    ap.Completed_Date
    FROM xy.Appointments ap
); 
disconnect from xyz;
Quit;

proc sql;
CREATE TABLE final_tbl AS
SELECT
i.Id,
i.Completed_Date
FROM work.inc_mails i
WHERE 
i.Completed_Date >= &start_month.
AND i.Completed_Date <= &end_month.;
quit;

方案2：将SAS日期转为字符型后比较 #

如果要保留字符型宏变量的写法，需要把Completed_Date转为对应的date9.字符格式再进行比较：

%let n_months = 1;
%let date = %sysfunc(date());

%let start_month = %sysfunc(intnx(month, &date., -&n_months., b));
%let start_sas_month = %str(%')%sysfunc(putn(&start_month., date9.))%str(%'); 

%let end_month = %sysfunc(intnx(month, &date., -&n_months., e));
%let end_sas_month = %str(%')%sysfunc(putn(&end_month., date9.))%str(%'); 

proc sql;
connect to oracle as xyz (authdomain="XYZ_READ" path=XYZ_P);
create table work.inc_mails as 
select * from connection to xyz
(   
    SELECT 
    ap.Id,
    ap.Completed_Date
    FROM xy.Appointments ap
); 
disconnect from xyz;
Quit;

proc sql;
CREATE TABLE final_tbl AS
SELECT
i.Id,
i.Completed_Date
FROM work.inc_mails i
WHERE 
put(i.Completed_Date, date9.) >= &start_sas_month.
AND put(i.Completed_Date, date9.) <= &end_sas_month.;
quit;

方案3：直接在Oracle端筛选（性能优化） #

如果数据量较大，推荐直接在Passthrough查询中完成日期筛选，减少SAS端的数据传输量，提升效率：

%let n_months = 1;
%let date = %sysfunc(date());

/* 生成Oracle兼容的YYYY-MM-DD格式日期字符串 */
%let start_oracle_month = %sysfunc(putn(%sysfunc(intnx(month, &date., -&n_months., b)), yymmdd10.));
%let end_oracle_month = %sysfunc(putn(%sysfunc(intnx(month, &date., -&n_months., e)), yymmdd10.));

proc sql;
connect to oracle as xyz (authdomain="XYZ_READ" path=XYZ_P);
/* 直接在Oracle端筛选后返回结果，无需先生成中间表 */
create table final_tbl as 
select * from connection to xyz
(   
    SELECT 
    ap.Id,
    ap.Completed_Date
    FROM xy.Appointments ap
    WHERE ap.Completed_Date BETWEEN to_date('&start_oracle_month.', 'YYYY-MM-DD') 
                               AND to_date('&end_oracle_month.', 'YYYY-MM-DD')
); 
disconnect from xyz;
Quit;

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