BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
csetzkorn
Lapis Lazuli | Level 10

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
csetzkorn
Lapis Lazuli | Level 10

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

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

This line:

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

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

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

 

csetzkorn
Lapis Lazuli | Level 10
Sorry this does not work as terradata does not like the issued sql.
SASKiwi
PROC Star

If Teradata doesn't like double quotes try this:

 

WHERE SomeDate = %str(%')&StartDate.%str(%');
csetzkorn
Lapis Lazuli | Level 10
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?
SASKiwi
PROC Star

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?

csetzkorn
Lapis Lazuli | Level 10
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.
csetzkorn
Lapis Lazuli | Level 10

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

Patrick
Opal | Level 21

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 21878 views
  • 4 likes
  • 4 in conversation