我有以下查询,该查询使用 proc sql 在 SAS 中运行,其中我有一个包含月份结束日期的自动变量,但它会导致以下错误
ERROR: Prepare error: ICommandPrepare::Prepare failed. : ERROR: Attribute '2017-02-28' not found
查询:
proc sql;
connect to oledb (datasource='10.1.0.105' provider=nzoledb
user=&user_id password=&pwd properties=('initial catalog'=ODS));
create table &user..Pers_test as select * from connection to oledb
(SELECT a.ID from DBO.Table1
where a.SOURCE_SYSTEM_CREATED_DTM <= "&monthend."
Group by a.SWID order by a.SWID
);
%let _sql_xrc=&sqlxrc;
disconnect from oledb;
quit;
但是,查询会在硬编码时间戳时运行。
proc sql;
connect to oledb (datasource='10.1.0.105' provider=nzoledb
user=&user_id password=&pwd properties=('initial catalog'=ODS));
create table &user..Pers_test as select * from connection to oledb
(SELECT a.ID from DBO.Table1
where a.SOURCE_SYSTEM_CREATED_DTM <= '2017-02-28 00:00:00'
Group by a.SWID order by a.SWID
);
%let _sql_xrc=&sqlxrc;
disconnect from oledb;
quit;
我尝试过转换,子字符串,但都会导致相同的错误。感谢任何帮助来解决自动化变量。
变量未在单引号下解析,因此使用双引号。但是作为双引号,该列无法识别值,并且抛出了错误。因此,该变量必须在单引号下解析。
在单引号下解析变量的代码如下
cast(%unquote(%str(%')&monthend.%str(%')) as datetime)
我修改了Karan Pappala的答案,使其对我有用:
%unquote(%str(%')&execution_method.%str(%'))