BookmarkSubscribeRSS Feed
newboy1218
Quartz | Level 8

Hi.. In my proc sql query, I want to have a where condition 

 

where my_dt = '31DEC9999'

However, in the dataset, "my_dt" is a numeric value, and I got the error message

 

image.png

 

ERROR: CLI prepare error: [Teradata][ODBC Teradata Driver][Teradata Database](-3535)A character
       string failed conversion to a numeric value.

Do you know how I can use date9. format condition?

5 REPLIES 5
novinosrin
Tourmaline | Level 20
where my_dt = '01JAN2019'd
newboy1218
Quartz | Level 8

I tried that too, but I still got an error message

 

ERROR: CLI prepare error: [Teradata][ODBC Teradata Driver][Teradata Database](-3706)Syntax error:
expected something between a string or a Unicode character literal and the word 'd'.

Tom
Super User Tom
Super User

@newboy1218 wrote:

I tried that too, but I still got an error message

 

ERROR: CLI prepare error: [Teradata][ODBC Teradata Driver][Teradata Database](-3706)Syntax error:
expected something between a string or a Unicode character literal and the word 'd'.


Show the full code. You cannot use SAS syntax for date literals in code that you are explicitly passing into Teradata. You need to use Teradata syntax in that part of the code.

libname mylib teradata .... ;

data test1;
  set mylib.mydata ;
  where mydate = '01AUG2019'd ;
run;

proc sql ;
connect using mylib ;
create table test2 as select * from mylib
 (select * from mydata where mydate = DATE'2019-08-01')
;
quit;

 

tsap
Pyrite | Level 9
Put the letter 'd' after the value you are supplying.

For example:
where my_dt = '01JAN2019'd

ballardw
Super User

@newboy1218 wrote:

Hi.. In my proc sql query, I want to have a where condition 

 

where my_dt = '01JAN2019'

However, in the dataset, "my_dt" is a numeric value, and I got the error message

 

ERROR: CLI prepare error: [Teradata][ODBC Teradata Driver][Teradata Database](-3535)A character
       string failed conversion to a numeric value.

Do you know how I can use date9. format condition?


The entire Proc SQL code would help. As it is we have to ask is this pass through to Teradata or not.

 

If the date is supposed to be treated as a SAS date value the code likely should be

where my_dt = '01JAN2019'd

to indicate a SAS date value. But you may need to pass a Teradata date string, which I am not familiar with.

 

So, please post the entire Proc Sql including the connection details. XXX out passwords, user names or sensitive values, but the whole code may be needed to determine what you want.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 7867 views
  • 0 likes
  • 5 in conversation