retrieve datetime values from teradata using sas

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

retrieve datetime values from teradata using sas

Hi all,

Merry Christmas ...

i am trying to retrieve data from teradata (the date is in format 30/11/2014 16:38:00 ) using SAS.

the below code works fine in teradate:

select *

from E_AN.nr

where Type_= 39 and cast(Tra_Dt as date) =  cast( '30/11/2014' as date format 'dd/mm/yyyy')

where as the below SAS code is not giving me any errors nor values,it says the query is running and its been almost several hours..

data _null_;

    date9 = intnx('day',today()-21,0);

    date11 = put(date9, yymmdd10.);

  call symput('mydate', compbl("'" || date11 || "'" ));

  run;

  data _null_;

  put "&mydate";

  run;

proc sql;

connect to teradata as myconn( user = "XXX@LDAP" password = XXXX

server = new

database= E_AN);

create table payment as

select

Type_,

Ba_o,

Tra_Dt

from connection to myconn

( select

nr.Type_,

nr.Ba_o,

nr.Tra_Dt

from

E_AN.nr

where  Type_= 39 and Tra_Dt = &mydate;

quit;

any help will be great...


Accepted Solutions
Solution
‎12-20-2014 11:49 PM
Respected Advisor
Posts: 3,834

Re: retrieve datetime values from teradata using sas

Macro variable &mydate resolves today to '2014-11-30'. This is a string an not a date value so you need to cast() it. I believe you also forgot to set the closing bracket at the end of your explicit pass-through SQL code block.

Try:

from connection to myconn

(....

where  Type_= 39 and Tra_Dt=cast( &mydate as date format 'yyyy-mm-dd')

);

quit;

View solution in original post


All Replies
Solution
‎12-20-2014 11:49 PM
Respected Advisor
Posts: 3,834

Re: retrieve datetime values from teradata using sas

Macro variable &mydate resolves today to '2014-11-30'. This is a string an not a date value so you need to cast() it. I believe you also forgot to set the closing bracket at the end of your explicit pass-through SQL code block.

Try:

from connection to myconn

(....

where  Type_= 39 and Tra_Dt=cast( &mydate as date format 'yyyy-mm-dd')

);

quit;

Contributor
Posts: 44

Re: retrieve datetime values from teradata using sas

Thanks Patrick much appreciated ..

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 177 views
  • 0 likes
  • 2 in conversation