06-29-2015 04:54 AM
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?
07-01-2015 08:39 AM
I looked it up and used the "connect to " as instructed in the following link
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
1 The SAS System 10:38 Tuesday, June 30, 2015
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
26 count(distinct VBM374_USED_BRANCH_CUSTOMER.Branch_Cust_IP)
28 VBM376_INTERNAL_ORGANIZATION INNER JOIN VBM374_USED_BRANCH_CUSTOMER ON
28 ! (VBM376_INTERNAL_ORGANIZATION.Team_IP=VBM374_USED_BRANCH_CUSTOMER.Team_IP)
32 VBM376_INTERNAL_ORGANIZATION.Division_Nbr = 700
33 AND ( substring(VBM374_USED_BRANCH_CUSTOMER.Status_Code,6,2) = '25' )
35 GROUP BY
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.
2 The SAS System Tuesday, 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=;
56 ODS _ALL_ CLOSE;
59 QUIT; RUN;
07-01-2015 10:30 AM
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).
07-02-2015 06:23 AM
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.
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
07-05-2015 06:20 AM
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..
07-05-2015 07:28 AM
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.
07-05-2015 10:45 AM
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.
07-07-2015 03:43 AM
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
07-07-2015 09:00 AM
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.
07-22-2015 08:08 AM
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;
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;
Hope this helps.
07-22-2015 01:13 PM
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