intck on teradata tables

[AJ_Brien]

Hello,

I'm trying to execute intck statement on a teradata table:

case when intck('day',close_date,today() ) le 80 then 'Y' else 'N' end as Closed

but it gives me the error: ERROR: Teradata prepare: Syntax error: expected something between '(' and the string 'day'.

I then found that intck is not supported on teradata.

Today() gives a num type, and close_date variable is num type with date9. format.

so when I try to run this: case when ('today()' - 'close_date')le 80 then 'Y' else 'N' end as Closed, it obviously gives me this error:

ERROR: Teradata prepare: A character string failed conversion to a numeric value.

which is understandable and I know I need to convert close_date into num too. But I'm not sure syntax wise how to do that conversion on a teradata table and then use it to assign the value to the 'Closed' flag.

Appreciate the help!

This is the code:

proc sql;  
		connect to teradata (user="%sysget(USER)" password="&dbpass." tdpid=rchtera mode=teradata);
		drop table   uwork.pop;
	create table uwork.pop (FASTLOAD=YES) as select * from connection to teradata
(	select top 10 
a.accno,  
b.close_date
/*,case when intck('day',b.close_date,today() ) le 80 then 'Y' else 'N' end as Closed*/
,case when ('today()' - 'b.close_date')le 80 then 'Y' else 'N' end as Closed

from table1 a left join table2 b on a.accno=b.accno
);
DISCONNECT FROM TERADATA;
quit;

[Panagiotis]

I haven't used Teradata before, or have access to it. But there are two ways to work with data in a database. See the following post: https://communities.sas.com/t5/SAS-Programming/Implicit-vs-Explicit-SQL-Pass-through-SQL-Query-in-SA...

You are using what's called Explicit-SQL-Pass-Through. That is, you are using Teradata SQL syntax in SAS and passing it directly to the database. That works well if you are familiar with native Teradata SQL and know those functions.

Since you seem to be using SAS functions, not all are known to Teradata since Teradata doesn't have those functions (or a different name for them). Since you are using SAS functions, you might want to try Implicit-SQL-Pass-Through. Here you are using SAS PROC SQL (and SAS functions) in your query, and SAS is translating your SAS SQL query to native Teradata SQL. The engine does the translating work for you. All you need to do is connect to the database using the LIBNAME statement and the options for your database.

Few specifics to SAS PROC SQL in your example:

- The TODAY() function finds today's date in SAS. From the looks of it that won't work in Teradata since Teradata uses current_date. See the Teradata doc for current_date.

- From a quick search Teradata does not have the INTCK function. You would have to use Teradata's method for calculating days between two dates. See the Teradata doc for Calculating the Difference in Days Between DATE Values.

You could rewrite your query using some Teradata functions (linked above). But if you are more comfortable with PROC SQL I'd probably try and use implicit pass through with the LIBNAME statement and let SAS do the work. See some documentation:

• LIBNAME Statement for the Teradata Engine
• Sample of LIBNAME and PROC SQL Pass-Through code to connect to Teradata database using SAS/ACCESS® I...
• SAS/ACCESS Interface to Teradata paper

- Peter

[hashman]

@AJ_Brien:

1. Looks as though you're using the explicit pass-thru access to TD, so you're limited to the TD=specific SQL syntax which, obviously doesn't support SAS functions like INTCK.

2. If you accessed TD via a LIBNAME engine, INTCK would work, as the function would be invoked on the SAS' side after having the TD date translated into the SAS date value. In this case, though, you'd lose the TOP functionality, which is specific to TD and not supported by SAS. 

3. Which is why you're likely better off staying with the pass-thru and using TD-specific functions to find the difference in days between the current date and close date. IIRC, the syntax is as simple as (you'll need to research/try to verify):

CURRENT_DATE - B.CLOSE_DATE

4.  Since you're creating a SAS table as a result of the query, I don't think that the FASTLOAD option is relevant.    

Kind regards

Paul D.

[AJ_Brien]

Thank you for your response, I added the following libname:
libname uwork teradata user="%sysget(USER)" password="&dbpass." tdpid=rchtera mode=ansi database=user_work;

I can let go of the top functionality if that would help make intk function work.

What change would you recommend to this code:
libname uwork teradata user="%sysget(USER)" password="&dbpass." tdpid=rchtera mode=ansi database=user_work;

proc sql;
connect to teradata (user="%sysget(USER)" password="&dbpass." tdpid=rchtera mode=teradata);
drop table uwork.pop;
create table uwork.pop (FASTLOAD=YES) as select * from connection to teradata
( select
a.accno,
b.close_date
,case when intck('day',b.close_date,today() ) le 80 then 'Y' else 'N' end as Closed
/*,case when ('today()' - 'b.close_date')le 80 then 'Y' else 'N' end as Closed*/

from table1 a left join table2 b on a.accno=b.accno
);
DISCONNECT FROM TERADATA;
quit;

when I run this at the moment, I get this error:
ERROR: Teradata prepare: Syntax error: expected something between '(' and the string 'day'.

I even tried doing: case when intck('day',close_date,current_date), I still get the same error.

Appreciate your help!

[Panagiotis]

Looks like you are combining the two. I'll mock some code up that you can test. I don't have access to teradata so test my code.

Explicit Pass Through (Using native Teradata SQL). See @hashman response about functionality. 

Everything here in the parentheses is native to Teradata. That is i'm sending this Teradata SQL directly to the database. I cannot use SAS functionality in the parentheses. So i'm not using INTCK or TODAY(). Instead i'm using Teradata's CURRENT_DATE and just subtracting the dates to get the total number of days. You can see the Teradata documentation I posted above.

proc sql;
connect to teradata (user="%sysget(USER)" password="&dbpass." tdpid=rchtera mode=teradata);
drop table uwork.pop;
create table uwork.pop (FASTLOAD=YES) as 
select * from connection to teradata
   (select a.accno,
           b.close_date,
           case 
              when CURRENT_DATE - B.CLOSE_DATE le 80 then 'Y'
              else 'N' 
           end as Closed
       from table1 a left join table2 b 
       on a.accno=b.accno);
DISCONNECT FROM TERADATA;
quit;

Implicit Pass Through (Using native Teradata SQL).

Here I want SAS to convert PROC SQL to native Teradata SQL. This allows me to use SAS functions.

Since the LIBNAME statement is pointing to the Teradata database, I don't need the CONNECT TO statement in PROC SQL anymore because i'm already connecting to the database using the LIBNAME statement. Thus, I can use the uwork library like a typical SAS library, but it's referencing tables in the database. 

ex) uwork.table1, uwork.table2, etc. These will be your Teradata tables as long as the options you set are correct.

For a quick check after you run the LIBNAME statement take a look at your libraries on the navigation pane and make sure you see UWORK. Expand and you should see tables.

Now use PROC SQL and the SAS functions you want. Just reference the tables using uwork. Don't need the CONNECT TO statement.

libname uwork teradata user="%sysget(USER)" password="&dbpass." tdpid=rchtera mode=ansi database=user_work;

proc sql;
create table uwork.pop
select a.accno,
       b.close_date,
       case 
         when intck('days',b.close_date,today()) le 80 then 'Y'
           else 'N' 
         end as Closed
       from uwork.table1 a left join uwork.table2 b 
       on a.accno=b.accno;
quit;


libname uwork clear;

- Peter

[hashman]

@AJ_Brien:

1. First, make sure that you see the table TABLE1 and TABLE2 in UWORK library. If you do, good; if not, your sysadmin may help.

2. Are you creating a TD table or SAS table as a result of the query? If it's SAS in the WORK library, then it should be WORK.POP, not UWORK.POP (UWORK points to TD) and you shouldn't use FASTLOAD. Otherwise, keep the references intact.  

3. Assuming that you're querying TD and deliver the result set to the SAS WORK library as data set POP, the query would look like:

proc sql ;                                                           
  drop table work.pop ;                                              
  create table work.pop as                                           
  select a.accno                                                     
       , b.close_date                                                
       , case when intck ('day', b.close_date, date()) le 80 then 'Y'
              else 'N'                                               
         end as Closed                                               
  from  uwork.table1 a left join                                     
        uwork.table2 b                                               
  on    a.accno = b.accno                                            
  ;                                                                  
quit ;                                                               

Since you're using the libname access now, this is what connects you with TD, so you don't need the connect/disconnect instructions.

Kind regards

Paul D.      

[AJ_Brien]

I have to pull the result into uwork instead of work since the resulting dataset is huge, so using teradata tables with fastload helps.

The table1 and table 2 referred have their teradata database names called cards, I've associated those with librefs, so now everything is happening through TD.

this is my latest code:

libname uwork teradata user="%sysget(USER)" password="&dbpass." tdpid=rchtera mode=ansi database=user_work;
libname wrktera teradata user="%sysget(USER)" password="&dbpass." tdpid=rchtera mode=ansi database=cards;
proc sql;
connect to teradata (user="%sysget(USER)" password="&dbpass." tdpid=rchtera mode=teradata);
drop table uwork.pop;
create table uwork.pop (FASTLOAD=YES) as select * from connection to teradata
( select top 10
a.accno,
b.close_date
, case when ('current_date' - 'b.close_dt') le 80 then 'Y' else 'N' end as Closed

from cards.table1 a left join cards.table2 b on a.accno=b.accno
);
DISCONNECT FROM TERADATA;
quit;

This gives me the error:
ERROR: Teradata prepare: A character string failed conversion to a numeric value.
My close_dt is num with date9. Format.

[hashman · Accepted solution]

@AJ_Brien:

Methinks your quoted CURRENT_DATE may be interpreted by TD as a character string rather than a signal to extract the current date and use the value. I'd try to unquote it. The quoted b.close_dt is also a suspect. Have you tried your query in TD SQL Assistant first? That's where I'd normally debug it, and then when it runs error-free, transplant it into proc SQL. 

[Panagiotis]

Agree with @hashman, try unquoting the values. Take a look at the Teradata documentation I posted above. It contains some example.

[AJ_Brien]

Thank you so much for your help!

[AJ_Brien]

this worked, thank you!

[Panagiotis]

Glad it worked out for you!

- Peter