Use SAS Macro Variable within Proc SQL Teradata passthrough

Question

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.

Strange - that code you provide looks fine to me (with the single quotes being assigned inside the macro variable). I tested it against a mySQL database and it worked fine. Can you try it one more time just to humor me because I find it difficult to believe that it isn't working. Maybe do it in a new SAS session. — Robert Penridge
– Robert Penridge, Commented Apr 4, 2016 at 16:04

Chris J · Accepted Answer · 2015-07-30 08:20:59Z

7

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;

answered Jul 30, 2015 at 8:20

Chris J

7,7942 gold badges27 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pyll Over a year ago

Thanks, Chris! This solution is perfect, and I learned a new macro function.

cs0815 Over a year ago

I am getting "Teradata prepare: A character string failed conversion to a numeric value". Any ideas?

Chris J Over a year ago

@csetzkorn Does the string represent a valid date?

Stig Eide · Accepted Answer · 2015-07-30 12:51:54Z

1

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

edited Jul 30, 2015 at 12:51

answered Jul 30, 2015 at 6:30

Stig Eide

1,0628 silver badges14 bronze badges

2 Comments

pyll Over a year ago

Thanks, Stig. This works perfectly, though I prefer Chris J's solution. It looks a little cleaner.

Stig Eide Over a year ago

Hi, yeah the BQUOTE looked better, Im going to use that in the future too ;)

mkluwe · Accepted Answer · 2016-04-04 14:56:47Z

0

as with such format of date 201501 to make a working macrovariable

%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;

edited Apr 4, 2016 at 14:56

mkluwe

4,0893 gold badges32 silver badges51 bronze badges

answered Apr 4, 2016 at 13:44

freza

1

2 Comments

mkluwe Over a year ago

Can you explain? What is the supposed effect of %BQUOTE('&INPUT_DATE2')?

freza Over a year ago

just as input_date=2015-06-01

Collectives™ on Stack Overflow

Use SAS Macro Variable within Proc SQL Teradata passthrough

3 Answers 3

3 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related