topic Re: Proc SQL - TeraData - own sql in SAS Enterprise Guide

Proc SQL - TeraData - own sql

RafaelZ — Mon, 29 Jun 2015 08:54:54 GMT

Hi.

When i copy paste SQL from TeraData SQL Editor to a SAS Proc SQL program

i see that the sql is rejected with errors.

it seems that sas is not sending the sql as is but is trying to translate it from it's own format to the DB.

for example the substring function in teradata is spelled "substring" and is sas is "substr".

Is there a way in a sas program to simply send the sql in the database own native format?

Will SAS/Access ODBC solve this problem?

Thanks

Rafael

Re: Proc SQL - TeraData - own sql

LinusH — Mon, 29 Jun 2015 10:49:09 GMT

It's called explicit SQL pass through / thru. See on line do for details.

Re: Proc SQL - TeraData - own sql

RafaelZ — Wed, 01 Jul 2015 12:39:18 GMT

Hi Linus

I looked it up and used the "connect to " as instructed in the following link

SAS/ACCESS(R) 9.2 for Relational Databases: Reference, Fourth Edition

Unfortunately my SQL is rejected by "Teradata Prepare" (log attached below)

The same SQL runs smoothly using the teradata sql editor

Am i doing something wrong?

There seems to be ! mark on line 28 in the log which is not in the original sql

Thanks

1 The SAS System 10:38 Tuesday, June 30, 2015

1 ;*';*";*/;quit;run;

2 OPTIONS PAGENO=MIN;

3 %LET _CLIENTTASKLABEL='Program41';

4 %LET _CLIENTPROJECTPATH='I:\xxx\SAS\Rafael\Main\Main22.egp';

5 %LET _CLIENTPROJECTNAME='Main22.egp';

6 %LET _SASPROGRAMFILE=;

8 ODS _ALL_ CLOSE;

9 OPTIONS DEV=ACTIVEX;

10 GOPTIONS XPIXELS=0 YPIXELS=0;

11 FILENAME EGSR TEMP;

12 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue

12 ! STYLESHEET=(URL="file:///C:/SASHome/SASEnterpriseGuide/5.1/Styles/HtmlBlue.css") NOGTITLE NOGFOOTNOTE

12 ! GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

14 GOPTIONS ACCESSIBLE;

15 proc sql;

16 connect to teradata as teracon (server=DWPROD schema=DWP_VALL );

19 select * from connection to teracon

21 (

23 SELECT

24 VBM376_INTERNAL_ORGANIZATION.Division_Nbr,

25 VBM376_INTERNAL_ORGANIZATION.Branch_Nbr,

26 count(distinct VBM374_USED_BRANCH_CUSTOMER.Branch_Cust_IP)

27 FROM

28 VBM376_INTERNAL_ORGANIZATION INNER JOIN VBM374_USED_BRANCH_CUSTOMER ON

28 ! (VBM376_INTERNAL_ORGANIZATION.Team_IP=VBM374_USED_BRANCH_CUSTOMER.Team_IP)

30 WHERE

32 VBM376_INTERNAL_ORGANIZATION.Division_Nbr = 700

33 AND ( substring(VBM374_USED_BRANCH_CUSTOMER.Status_Code,6,2) = '25' )

35 GROUP BY

36 1,

37 2

41 );

ERROR: Teradata prepare: Syntax error: expected something between the word 'Status_Code' and ','. SQL statement was: SELECT

VBM376_INTERNAL_ORGANIZATION.Division_Nbr, VBM376_INTERNAL_ORGANIZATION.Branch_Nbr, count(distinct

VBM374_USED_BRANCH_CUSTOMER.Branch_Cust_IP) FROM VBM376_INTERNAL_ORGANIZATION INNER JOIN VBM374_USED_BRANCH_CUSTOMER ON

(VBM376_INTERNAL_ORGANIZATION.Team_IP=VBM374_USED_BRANCH_CUSTOMER.Team_IP) WHERE VBM376_INTERNAL_ORGANIZATION.Division_Nbr =

700 AND ( substring(VBM374_USED_BRANCH_CUSTOMER.Status_Code,6,2) = '25' ) GROUP BY 1, 2.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

43 disconnect from teracon;

NOTE: Statement not executed due to NOEXEC option.

45 quit;

2 The SAS System 10:38Tuesday, June 30, 2015

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.74 seconds

cpu time 0.01 seconds

49 GOPTIONS NOACCESSIBLE;

50 %LET _CLIENTTASKLABEL=;

51 %LET _CLIENTPROJECTPATH=;

52 %LET _CLIENTPROJECTNAME=;

53 %LET _SASPROGRAMFILE=;

55 ;*';*";*/;quit;run;

56 ODS _ALL_ CLOSE;

59 QUIT; RUN;

Re: Proc SQL - TeraData - own sql

TomKari — Wed, 01 Jul 2015 13:35:54 GMT

It looks like something is wrong in your use of the substring function. Is this the correct syntax for Teradata?

Re: Proc SQL - TeraData - own sql

LinusH — Wed, 01 Jul 2015 14:30:26 GMT

It seems that Teradata has another "default" syntax for subtring.

This is not a SAS "problem". Test your Teradata SQl in SQL Assistant first, then insert into SAS SQL.

If you are not very comfortable with Teradata SQL, you are perhaps better off learning/using SAS SQL and use implicit SQL instead (SAS translates SAS SQL to the Teradata equivalent automatically for you).

Re: Proc SQL - TeraData - own sql

RafaelZ — Thu, 02 Jul 2015 10:23:10 GMT

Dear Tom and Linus

I Copied&Pasted the exact sql to TD assistant and it runs there with no problems as you can see from the TD screenshot.

However you are both right.

I looked in the teradata documentation and found that there are two formats for the substring function: ANSI and Teradata non of which is identical to mine.

Teradata Syntax

ANSI Syntax

I still don't understand how the problematic syntax runs on TD assistant. maybe the ODBC knows how to translate it..

The reason that i insist on this to work is that i copy SQLs from "business objects" application to SAS programs and i try to avoid modifications as much as possible.

However this indeed is not a SAS problem

Thank you

Re: Proc SQL - TeraData - own sql

LinusH — Thu, 02 Jul 2015 11:07:54 GMT

And then it's a ODBC issue...?

Try to past it in another ODBC client (not SAS) and see what happens.

Re: Proc SQL - TeraData - own sql

RafaelZ — Sun, 05 Jul 2015 10:20:09 GMT

Hi.

Using TD ODBC not through SAS - it works

But not through SAS.

I would try using SAS/ACCESS ODBC

but we didn't purchase the license.

Meanwhile I think i can get along with the situation as is..

Thanks

Rafael

Re: Proc SQL - TeraData - own sql

Tom — Sun, 05 Jul 2015 11:28:36 GMT

If you are going to use ANSI syntax instead of Teradata syntax then you probably should tell Teradata that first by setting the MODE= option in the CONNECT statement.

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a003349368.htm

Re: Proc SQL - TeraData - own sql

jakarman — Sun, 05 Jul 2015 14:45:18 GMT

the default mode for TD SQL assistant is MODE=Teradata , the default with SAS/ACCESS is MODE=Ansi.
Switching the mode is not only a matter of syntax but also of behavior. Every SQL statement with TD should be followed by a commit in Ansi mode to make the updates persistent. That is not needed with mode=Teradata. You could see that at the examples using the execute syntax using explicit pass through.

Getting all the data to SAS with a select * is a bad idea as TD is parallel processing all data where SAS doesn't.

Using volatile tables in TD will require a global SAS/TD session. The combination TD/SAS is nice but is requiring some skills for the dedicated behavior.

Re: Proc SQL - TeraData - own sql

RafaelZ — Tue, 07 Jul 2015 07:43:34 GMT

Thank you all for your replies.

If i used SAS/ACCESS ODBC to access Teradata

Will it be exactly the same as sending the same sql through TD assistant using the same ODBC ?

I am considering suggesting purchasing it but i need to be sure that each SQL that i send through it will be executed

as if sent using the TD assistant using the same odbc

Thanks

Rafael

Re: Proc SQL - TeraData - own sql

jakarman — Tue, 07 Jul 2015 13:00:19 GMT

RafaelZ, please forget SAS/ACCESS ODBC as in improvement in contrary as you miss all teradata options you have availabel now you can expect more issues. The mode teradata / ansi (TD environment) is a basic option you should understand as the same with the SAS/ACCESS tD specifics. You cannot circumvent that need for knowledge switching tools.

http://support.sas.com/documentation/cdl/en/acreldb/67589/HTML/default/viewer.htm#n15q85z7nwkl3pn189ekttbnnzbe.htm

Re: Proc SQL - TeraData - own sql

JoeMadden — Wed, 22 Jul 2015 12:08:11 GMT

If you want to use pure Teradata SQL in SAS you'll need to load the data into another Teradata table and then Select from table table using pass through, rough example below;

Proc Sql;

Connect to Teradata as TD (<<connection info>>);

Execute ( Insert into <<TeradataTableStage>> Select Col1,Col2,Col3 From TeradatatableSourceData ) by TD;

Execute (commit work) by TD; /* <<< This commit does not happen automatically via SAS */

Create Table test as
Select * From connection to TD

(Select * From <<TeradataTableStage>>);

Disconnect from TD;

Quit;

Hope this helps.

Re: Proc SQL - TeraData - own sql

jakarman — Wed, 22 Jul 2015 17:13:27 GMT

Joe, that commit will work automatically wit SAS when the connection is setup in mode=Teradata. When sharing Ansi modes tha do not share those session settings

Re: Proc SQL - TeraData - own sql

JoeMadden — Thu, 23 Jul 2015 07:27:08 GMT

Thanks Jaap.

Re: Proc SQL - TeraData - own sql

Vi_ — Mon, 11 Jun 2018 17:55:28 GMT

Hi guys,

I knew this is an old post but I am running the same issue using EG 7.1.

However, I am able to run the program and get the same result from Teradata by changing Substring to substr in SAS. I am not sure why it work and would like to know is this appropriate?

Here's my example:

proc sql;
connect to Teradata as tera(
authdomain=TeraAuth
tdpid=xxxx
mode=teradata
connection=global
);

execute (
CREATE VOLATILE TABLE Test AS (
	SELECT ID, Substr(ID,3,5) AS NewID 
	FROM YYYtable 
	GROUP BY 1,2
) WITH DATA ON COMMIT PRESERVE ROWS
) by tera;

If I change to Substring (ID, 3,5) to Substr(ID,3,5) in sas, it works and gives me the same result like Tera's......If someone could support my solution or explain a bit would be great.

Thanks in advance,

Re: Proc SQL - TeraData - own sql

SASKiwi — Tue, 12 Jun 2018 07:48:19 GMT

So what confirmation do you want beyond the fact that the sub-string function is named differently in two different flavours of SQ? I suggest you look up the documentation for both for more details.

Re: Proc SQL - TeraData - own sql

Vi_ — Wed, 13 Jun 2018 20:26:30 GMT

Hi,

So I looked up Teradata's documentary. Substring and Substr are slightly different although they are all doing substring work.

SAS Substr is equal to Substring in Teradata.

My confusion is that when I code in sas, I ask sas to connect to Tera and perform query in tera, and then, create table in sas. So how did Teradata understand SAS substr and perform the query?

Thanks,