topic date macro variable in explicit pass through sql in SAS Data Management

date macro variable in explicit pass through sql

csetzkorn — Fri, 13 Jan 2017 14:56:34 GMT

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!

Re: date macro variable in explicit pass through sql

RW9 — Fri, 13 Jan 2017 15:01:21 GMT

This line:

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

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

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

Re: date macro variable in explicit pass through sql

SASKiwi — Fri, 13 Jan 2017 21:53:16 GMT

If Teradata doesn't like double quotes try this:

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

Re: date macro variable in explicit pass through sql

csetzkorn — Sat, 14 Jan 2017 11:12:42 GMT

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?

Re: date macro variable in explicit pass through sql

csetzkorn — Sat, 14 Jan 2017 11:15:25 GMT

Sorry this does not work as terradata does not like the issued sql.

Re: date macro variable in explicit pass through sql

SASKiwi — Sun, 15 Jan 2017 06:01:22 GMT

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?

Re: date macro variable in explicit pass through sql

csetzkorn — Sun, 15 Jan 2017 13:53:18 GMT

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.

Re: date macro variable in explicit pass through sql

csetzkorn — Sun, 15 Jan 2017 14:13:36 GMT

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 ...

Re: date macro variable in explicit pass through sql

Patrick — Sun, 15 Jan 2017 22:32:03 GMT

@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.