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.
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));
drop camp_status start_dt;
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.
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_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
start_dt as max_start_dtime
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