SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

date macro variable in explicit pass through sql

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 110
Accepted Solution

date macro variable in explicit pass through sql

This Sql work fine:

 

SELECT 
TOP 10 *
FROM SomeCalendarData
WHERE SomeDate = '2016/01/01';

 

So using a 'string macro variable' like so should work as well:

 

 

%let StartDate = 2016/01/01;

proc sql;
   connect to teradata 
    (BULKLOAD=YES MODE=TERADATA user=&user. Password=&passwd.);

   CREATE TABLE WorkTest.test AS
   select * from connection to teradata
   ( 
		SELECT 
			TOP 10 *      
		FROM SomeCalendarData
		WHERE SomeDate = %bquote('&StartDate.');
   ); 
quit;

Any idea why this is not the case? Thanks!

 


Accepted Solutions
Solution
‎08-30-2017 10:50 AM
Frequent Contributor
Posts: 110

Re: date macro variable in explicit pass through sql

Posted in reply to csetzkorn

Thanks for all your help, This:

 

%let StartDate = 2016/01/01;

proc sql;
   connect to teradata 
    (BULKLOAD=YES MODE=TERADATA user=&user. Password=&passwd.);

   CREATE TABLE WorkTest.test AS
   select * from connection to teradata
   ( 
		SELECT 
			TOP 10 *      
		FROM SomeCalendarData
		WHERE SomeDate = CAST(%bquote('&StartDate.') AS DATE FORMAT 'YYYY-MM-DD');
   ); 
quit;

Does the trick. Although I am still unsure why an explicit CAST like this should be necessary?! Bizare ... 

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

Re: date macro variable in explicit pass through sql

Posted in reply to csetzkorn

This line:

		WHERE SomeDate = %bquote('&StartDate.');

Your macro variable will not resolve unless its in doubel quotes:

		WHERE SomeDate = %bquote("&StartDate.");

 

Frequent Contributor
Posts: 110

Re: date macro variable in explicit pass through sql

Sorry this does not work as terradata does not like the issued sql.
Super User
Posts: 3,250

Re: date macro variable in explicit pass through sql

[ Edited ]
Posted in reply to csetzkorn

If Teradata doesn't like double quotes try this:

 

WHERE SomeDate = %str(%')&StartDate.%str(%');
Frequent Contributor
Posts: 110

Re: date macro variable in explicit pass through sql

After this I am getting: ERROR: Teradata prepare: A character string failed conversion to a numeric value. SQL statement was: SELECT TOP 10 * FROM
SomeCalendarData WHERE SomeDate = '2016/01/01';. This is a bit confusing as SELECT TOP 10 * FROM
SomeCalendarData WHERE SomeDate = '2016/01/01'; works fine in sql assistent when run against teradata. Any ideas?
Super User
Posts: 3,250

Re: date macro variable in explicit pass through sql

Posted in reply to csetzkorn

I've never used Teradata but a quick Google search for Teradata date formats suggests '2016-01-01' is more commonly used. Have you tried that?

Frequent Contributor
Posts: 110

Re: date macro variable in explicit pass through sql

As i said: SELECT TOP 10 * FROM
SomeCalendarData WHERE SomeDate = '2016/01/01'; returns the expected results when I issue it from another teradata client (sql assistent) so I am a bit lost what's going on. Maybe SAS's explicit tries to be clever whilst causing issues.
Solution
‎08-30-2017 10:50 AM
Frequent Contributor
Posts: 110

Re: date macro variable in explicit pass through sql

Posted in reply to csetzkorn

Thanks for all your help, This:

 

%let StartDate = 2016/01/01;

proc sql;
   connect to teradata 
    (BULKLOAD=YES MODE=TERADATA user=&user. Password=&passwd.);

   CREATE TABLE WorkTest.test AS
   select * from connection to teradata
   ( 
		SELECT 
			TOP 10 *      
		FROM SomeCalendarData
		WHERE SomeDate = CAST(%bquote('&StartDate.') AS DATE FORMAT 'YYYY-MM-DD');
   ); 
quit;

Does the trick. Although I am still unsure why an explicit CAST like this should be necessary?! Bizare ... 

Respected Advisor
Posts: 4,173

Re: date macro variable in explicit pass through sql

Posted in reply to csetzkorn

@csetzkorn

Although I am still unsure why an explicit CAST like this should be necessary?

 

According to docu that's the syntax:

CAST ( expression AS data_type [ ( length ) ] )

 https://msdn.microsoft.com/en-AU/library/ms187928.aspx#BKMK_examples

 

If you just use the date string without any cast, then are you sure that this string gets converted to a SQL Server representation of DATE and not of DATETIME2 due to Data Type Precedence?

https://msdn.microsoft.com/en-us/library/ms190309.aspx

 

I'd assume that if it becomes DATETIME2 then your code executes without any error but the where clause doesn't behave as you'd expected it.

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 2080 views
  • 3 likes
  • 4 in conversation