Hi Patrick Thank you very much for your interest. The reason i ask is that i can't use proc import and export to excahnge files with SAS Server because our SAS server can not access our local network yet. I thought that if i had access to my own WORK folder or any other dedicated private folder, i could move files to that folder and use that in my SAS Programs. Also The final output of many of our reports is in the form of excel files with multiple sheets and i need the proc export for that Thanks Rafael ... View more

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 ... View more

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 ... View more

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 ... View more

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     ... View more

Thank you very much - i didn't know that. Rafael ... View more