Desktop productivity for business analysts and programmers

Proc SQL - TeraData - own sql

Reply
Occasional Contributor
Posts: 14

Proc SQL - TeraData - own sql

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

Super User
Posts: 5,386

Re: Proc SQL - TeraData - own sql

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

Data never sleeps
Occasional Contributor
Posts: 14

Re: Proc SQL - TeraData - own sql

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=;

7        

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

13       

14         GOPTIONS ACCESSIBLE;

15         proc sql;

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

17       

18       

19         select * from connection to teracon

20       

21         (

22       

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)

29       

30         WHERE

31       

32           VBM376_INTERNAL_ORGANIZATION.Division_Nbr  =  700

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

34       

35         GROUP BY

36           1,

37           2

38       

39       

40       

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.

42       

43           disconnect from teracon;

NOTE: Statement not executed due to NOEXEC option.

44       

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

    

46       

47       

48       

49         GOPTIONS NOACCESSIBLE;

50         %LET _CLIENTTASKLABEL=;

51         %LET _CLIENTPROJECTPATH=;

52         %LET _CLIENTPROJECTNAME=;

53         %LET _SASPROGRAMFILE=;

54       

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

56         ODS _ALL_ CLOSE;

57       

58       

59         QUIT; RUN;

60    

PROC Star
Posts: 1,146

Re: Proc SQL - TeraData - own sql

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

Super User
Posts: 5,386

Re: Proc SQL - TeraData - own sql

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).

Data never sleeps
Occasional Contributor
Posts: 14

Re: Proc SQL - TeraData - own sql

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 Smiley Happy

Thank you

Super User
Posts: 5,386

Re: Proc SQL - TeraData - own sql

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

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

Data never sleeps
Occasional Contributor
Posts: 14

Re: Proc SQL - TeraData - own sql

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

Super User
Super User
Posts: 6,845

Re: Proc SQL - TeraData - own sql

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

Valued Guide
Posts: 3,208

Re: Proc SQL - TeraData - own sql

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.

---->-- ja karman --<-----
Occasional Contributor
Posts: 14

Re: Proc SQL - TeraData - own sql

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

Valued Guide
Posts: 3,208

Re: Proc SQL - TeraData - own sql

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#n15q85z7nwkl3pn189...

---->-- ja karman --<-----
Occasional Contributor
Posts: 8

Re: Proc SQL - TeraData - own sql

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.

Valued Guide
Posts: 3,208

Re: Proc SQL - TeraData - own sql

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

---->-- ja karman --<-----
Occasional Contributor
Posts: 8

Re: Proc SQL - TeraData - own sql

Thanks Jaap.

Ask a Question
Discussion stats
  • 14 replies
  • 1615 views
  • 1 like
  • 6 in conversation