Geek Answers Handbook

Use Macro SAS variable in Proc SQL Teradata passthrough

I have an SQL statement that I want 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 want to pass this through a SQL pass-through and pass the date to the SQL program, for example ...

 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 gives the same result as the previous SQL statement. However, I really want 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 tried various combinations of quotes and different date formats ... what am I missing here? Thanks.

+4
source share
3 answers

You can use the %BQUOTE() macro to resolve macro variables in 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
+5
source

Try the following:

 %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=%str(%'&input_date%') ); disconnect from tera; quit;
+1
source

as with 201501 date format to make working macro-variable

 %let input_date = 201506; %let input_date2=input(put(intnx('month',%sysfunc(inputn(&input_date,yymmn6.)),0,'b'),10.),yymmddd10.) 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_DATE2') ); disconnect from tera; quit;
0
source

More articles:


All Articles