topic Re: Getting Error when a Datepart function is applied on a Teradata variable in SAS Programming

Getting Error when a Datepart function is applied on a Teradata variable

DatepartfunctiononaTeradatavariable — Thu, 03 Mar 2011 16:39:30 GMT

Hi,

Using SQL Pass Through Facility, I connected to Teradata and was planning to create a SAS dataset. In the WHERE clause of PROC SQL, i used datepart function to pull the date from Teradata datetime value and to compare with a macro variable.

I am getting an error. Please help.

Thanks

Re: Getting Error when a Datepart function is applied on a Teradata variable

Reeza — Thu, 03 Mar 2011 17:17:02 GMT

What's the error?

Here's a few things you can look into:
1) Does SQL pass through the function your using?
See
http://support.sas.com/kb/23/194.html

2) Is the macro variable resolving properly?

Re: Getting Error when a Datepart function is applied on a Teradata variable

DatepartfunctiononaTeradatavariable — Thu, 03 Mar 2011 17:29:57 GMT

I am getting Teradata Prepare error.

Re: Getting Error when a Datepart function is applied on a Teradata variable

Reeza — Thu, 03 Mar 2011 18:12:25 GMT

I'm assuming the query works without the where clause or when the date you're comparing to is a full date (ie not using datepart)?

It would help if you posted the log with the code and error message, if possible, XXXX'ing out any confidential info.

Re: Getting Error when a Datepart function is applied on a Teradata variable

DatepartfunctiononaTeradatavariable — Thu, 03 Mar 2011 19:00:15 GMT

Here is the code and error log below,

91 rsubmit;
NOTE: Remote submit to DV commencing.
292 %let START_YYYYMMDD=20100801;
293 proc sql noprint;
294 connect to teradata as tera (tdpid=PTIA1EDW user="XXXXX" password=XXXXX);
295 create table abc as
296 select * from connection to tera
297 ( select row_id,
298 c_num,
299 c_status,
300 c_type,
301 input(put( datepart(start_dt), yymmddn8.), 8.) as max_start_dt
302 from w_source_d
303 where
304 upper(c_type)='IND' and
305 camp_status in ('L','C') and
306 datepart(start_dt) >= input( "&START_YYYYMMDD", yymmdd8.)
307 );
ERROR: Teradata prepare: Syntax error: expected something between ',' and the 'input' keyword. SQL statement was: select row_id, c_num, c_status, c_type,
input(put( datepart(start_dt), yymmddn8.), 8.) as max_start_dt from w_source_d
where upper(c_type)='IND' and camp_status in ('L','C') and
datepart(start_dt) >=input( "&START_YYYYMMDD", yymmdd8.).

Thanks

Re: Getting Error when a Datepart function is applied on a Teradata variable

Reeza — Thu, 03 Mar 2011 19:07:18 GMT

you have a format in there, yymmddn8. Is that a valid sas format or a custom format that's been defined?

Re: Getting Error when a Datepart function is applied on a Teradata variable

DatepartfunctiononaTeradatavariable — Thu, 03 Mar 2011 19:30:34 GMT

yymmddn8. is a valid sas format.

Thanks,
lm

Re: Getting Error when a Datepart function is applied on a Teradata variable

Reeza — Thu, 03 Mar 2011 19:58:38 GMT

I'm still not sure that Teradata knows how to interpret the datepart function is pass-through code.
See valid functions here:
http://support.sas.com/resources/papers/teradata.pdf

However, I am unfamiliar with that specific db so possibly post your question on SAS-L for more responses.

Cheers,
Reeza

Re: Getting Error when a Datepart function is applied on a Teradata variable

garybald — Thu, 03 Mar 2011 20:56:22 GMT

try changing line 301 to drop the input and put and change where the format is assinged

datepart(start_dt) as max_start_dt format=yymmdd8.

then you should be able to change line 306 to

max_start_dt >= "START_YYYYMMDD"

Re: Getting Error when a Datepart function is applied on a Teradata variable

FriedEgg — Thu, 03 Mar 2011 21:50:24 GMT

Instead of using explicit SQL you could instead do it implicitly. Below is an example quickly rewriting your proc sql.

libname trlib teradata user=fakeuser password=fakepass;

%let START_YYYYMMDD=20100801;

data abc;
set trlib.w_source_d(
keep=row_id c_num c_status c_type start_dt camp_status
where=(upper(c_type)='IND' and camp_status in ('L','C') and datepart(start_dt) >= "&START_YYYYMMDD"d));
max_start_dt=datepart(start_dt);
drop camp_status start_dt;
run;

In the above SAS should process the where clause in-database if the datepart function is compatible, otherwise it will process it downstream. This method is usually slower. There is plenty of documentation around on that if you look for it.

Re: Getting Error when a Datepart function is applied on a Teradata variable

Peter_C — Sat, 05 Mar 2011 10:26:32 GMT

solve your problem with an alternative approach.
Instead of making Teradata convert its date time value into a date so that you can compare with your date macro variable, just convert the use of your date macro variable to look like a datetime - much simpler for all concerned!

using implicit pass-thru allows you to keep it streamlined, and use sas datetime constants ...

%let START_YYYYMMDD=20100801;
%let start_date = 01AUG2010 ; * simpler for SAS syntax in date constant form;
proc sql noprint;
libname tera teradata tpdid=PTIA1EDW user="XXXXX" password=XXXXX ;
* not entirely sure of this syntax ;
create table abc as
select row_id,
c_num,
c_status,
c_type,
start_dt as max_start_dtime
from tera.w_source_d
where
upcase(c_type)='IND' and
camp_status in ('L','C') and
start_dt GE "&START_date:0:0:0"dt
;
variable MAX_START_dTime will be extracted as a datetime value, but that can be converted in your next step, there should be no need to trouble Teradata with a SAS function like DATEPART()

for better examples, see Coders' Corner paper 105-2011 "Explicit SQL Pass-Through: Is It Still Useful?" by Frank Capobianco of the Teradata Corporation.

better still, next month, come, listen to Frank and ask questions
SAS Global Forum 2011, Las Vegas
Cesears Palace, 6-April-2011 14:00 room Roman II

peterC

Re: Getting Error when a Datepart function is applied on a Teradata variable

DatepartfunctiononaTeradatavariable — Mon, 07 Mar 2011 20:08:36 GMT

Thank you for reply Peter ! But i could not found the document, Could you please post me the link once again.

Thanks,
Lm

Re: Getting Error when a Datepart function is applied on a Teradata variable

Peter_C — Mon, 07 Mar 2011 22:23:39 GMT

start here
http://support.sas.com/events/sasglobalforum/2011/