在 Proc SQL Teradata passthrough 中使用 SAS 宏变量 [英] Use SAS Macro Variable within Proc SQL Teradata passthrough

问题描述

我有一个 SQL 语句，希望使用 SAS EG (9.4) 自动执行.以下语句已在 Teradata SQL Assistant 中测试并有效.

select * from TD.DATA where date='2015-06-01'

现在我希望通过 proc SQL pass through 来推送它，并将日期提供给 SQL 程序，就像这样......

proc sql;以 tera(user=&tera_user password="&tera_pwd" tdpid=terap) 的身份连接到 teradata；创建表 MYDATA 作为select * from connection to tera(select * from TD.DATA where date='2015-06-01');与 tera 断开连接；放弃;

上面的代码已经过测试并产生与前面的 SQL 语句完全相同的输出.但是，我真正想要的是做这样的事情:

%let input_date='2015-06-01';进程sql;以 tera(user=&tera_user password="&tera_pwd" tdpid=terap) 的身份连接到 teradata；创建表 MYDATA 作为select * from connection to tera(select * from TD.DATA where date=&input_date.);与 tera 断开连接；放弃;

我尝试了各种引用和不同日期格式的组合......我在这里遗漏了什么?谢谢.

解决方案

您可以使用 %BQUOTE() 宏函数来解析单引号内的宏变量.

<前>%let input_date = 2015-06-01;进程sql;以 tera(user=&tera_user password="&tera_pwd" tdpid=terap) 的身份连接到 teradata；创建表 MYDATA 作为select * from connection to tera(select * from TD.DATA where date = %BQUOTE('&INPUT_DATE'));与 tera 断开连接；放弃;

I have a SQL statement that I wish to automate using SAS EG (9.4). The following statement has been tested in Teradata SQL Assistant and works.

select * from TD.DATA where date='2015-06-01'

Now I wish to push this through a proc SQL pass through, and feed the date to the SQL program, like so....

proc sql;
connect to teradata as tera(user=&tera_user password="&tera_pwd" tdpid=terap);
create table MYDATA as 
select * from connection to tera
(
select * from TD.DATA where date='2015-06-01'
);
disconnect from tera;
quit;

The above code has been tested and produces the exact same output as the previous SQL statement. However, what I really want is to do something like this:

%let input_date='2015-06-01';
proc sql;
connect to teradata as tera(user=&tera_user password="&tera_pwd" tdpid=terap);
create table MYDATA as 
select * from connection to tera
(
select * from TD.DATA where date=&input_date.
);
disconnect from tera;
quit;

I have tried various combinations of quotations and different date formats....what am I missing here? Thanks.

解决方案

You can use the %BQUOTE() macro function to resolve macro variables within single quotes.

%let input_date = 2015-06-01;
proc sql;
  connect to teradata as tera(user=&tera_user password="&tera_pwd" tdpid=terap);
  create table MYDATA as 
  select * from connection to tera
  (
   select * from TD.DATA where date = %BQUOTE('&INPUT_DATE')
  );
  disconnect from tera;
quit;

这篇关于在 Proc SQL Teradata passthrough 中使用 SAS 宏变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！