SAS---TERA

[Ronein]

Hello

Here are 3 codes that connect to  tera and create data sets.

Why Way2 is not working (error)?

Why Way3 is much quicker than Way1?

What is the name of WAY1 and WAY3?

Is WAY3 called "TERA connector" (AS I underdstand here I must use tera language and cant use sas language (cant use data steps)

IS WAY1 called tera engine? As I understand here I must use sas language and cannpt use tera lanugage

Please note that WAY1 is workking without libaname (I run include statement and porbably here the admin defined it )

/**WAY1- Work 100% but slow**/
proc sql;
create table Way1_VBM374 as
SELECT *
from teradata.VBM374_USED_BRANCH_CUSTOMER as a
where monotonic() <=1000
;
quit;

/**WAY2- error*/
proc sql;
create table Way2_VBM374 as
SELECT top 1000 *
from teradata.VBM374_USED_BRANCH_CUSTOMER  
;
quit;
/*30         SELECT top 1000 **/
/*                      ____*/
/*                      22*/
/*                      76*/
/*ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, */
/*              CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.  */
/**/
/*ERROR 76-322: Syntax error, statement will be ignored.*/


/**WAY3- Work 100% and quick**/
proc sql;  
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);
create table Way3_VBM374  as 
select * from connection to teradata
(
SELECT top 1000 a.*
from VBM374_USED_BRANCH_CUSTOMER  as a 
);
disconnect from teradata;
quit ;


 

[Tom · Accepted solution]

Just look carefully at what your code is saying.

In WAY1 you are asking SAS to read every observation from the remote dataset and call the SAS function MONOTONIC() and based on the value of the result deside whether to save that observation into the SAS dataset.  So if the dataset in Teradata has a lot more than 1000 observations you are moving a lot more data between the Teradata server and the SAS server.

You might try using dataset option instead and see what SAS does. 

proc sql;
create table Way1_VBM374 as
SELECT *
from teradata.VBM374_USED_BRANCH_CUSTOMER(obs=1000) as a
;
quit;

Or more naturally:

data Way1_VBM374;
  set teradata.VBM374_USED_BRANCH_CUSTOMER(obs=1000);
run;

Remember there are options you can set that will cause SAS to report in the SAS log what TERADATA code it is generating when you do this type of IMPLICIT reference to teradata.

WAY2 is just a coding error.  SAS does not support the extensions to the SQL language that TERADATA has a added, like LIMIT.

WAY3 you asked teradata to select 1000 observations and then copied only those observations over to SAS.  So much less data is moved from Teradata server to the SAS server.

NOTE: There is no need to retype your access credentials just to use pass thru SQL code.  Just reuse the connection you made when you created the TERADATA libref by using the CONNECT USING syntax instead of the CONNECT TO syntax.

[Ronein]

Great and thank you.

Only 2 questions please-

1-How do you know that monotonic() is done after data transfer from tera table into sas daat set (and not before)?

By the slow time it take to run it I know that you are right 100% but I wonder how can knew it that this action is done only after the all dat mobe from tera into sas?

2- select top as I understand is sql language and cannot use it in proc sql. Am I right?

I summary the useful information you gave me-

/**WAY1A-Very quick way**/
 /**Only 1000 observations are moving from tea table into sas data set***/
proc sql;
create table  VBM374 as
SELECT *
from teradata.VBM374_USED_BRANCH_CUSTOMER(obs=1000) as a
;
quit;

/**WAY1B-Same like WAY1A--Very quick way**/
/**Only 1000 observations are moving from tea table into sas data set***/
Data VBM374;
SET teradata.VBM374_USED_BRANCH_CUSTOMER(obs=1000);
Run;


/**WAY3-Slow**/
/***All observations in tera table are moving into sas data set and only then show 1000 observations***/
proc sql;
create table Way1_VBM374 as
SELECT *
from teradata.VBM374_USED_BRANCH_CUSTOMER as a
where monotonic() <=1000
;
quit;


/**WAY4-error because SLEECt TOP is not proc sql language***/
proc sql;
create table Way2_VBM374 as
SELECT top 1000 *
from teradata.VBM374_USED_BRANCH_CUSTOMER  
;
quit;