BookmarkSubscribeRSS Feed
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
12 REPLIES 12
Reeza
Super User
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?
Reeza
Super User
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.
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
Reeza
Super User
you have a format in there, yymmddn8. Is that a valid sas format or a custom format that's been defined?
yymmddn8. is a valid sas format.

Thanks,
lm
Reeza
Super User
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
garybald
Calcite | Level 5
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"
FriedEgg
SAS Employee
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.
Peter_C
Rhodochrosite | Level 12
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
Thank you for reply Peter ! But i could not found the document, Could you please post me the link once again.

Thanks,
Lm

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 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
  • 12 replies
  • 6005 views
  • 0 likes
  • 5 in conversation