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
It's called explicit SQL pass through / thru. See on line do for details.
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
It looks like something is wrong in your use of the substring function. Is this the correct syntax for Teradata?
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).
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
And then it's a ODBC issue...?
Try to past it in another ODBC client (not SAS) and see what happens.
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
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
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.
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
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.
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.
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
Thanks Jaap.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.