All Forums UDA
Oblak 2 posts Joined 08/07
23 Oct 2007
Pass Through in SAS

Anybody who knows how to do a pass through statement from SAS to Teradata?

bobduell 8 posts Joined 10/07
24 Oct 2007

It's very simple; use PROC SQL. For convenience sake, I create SAS macro variables "terauser" and "terapass" that contain my Teradata login ID and password. I put the macro definitions in my program (which is read-only to my ID). That way, my code doesn't need to change whenever my password changes.Here's a trivial example to create a SAS dataset containing the Teradata system date:proc sql; connect to teradata (user="&terauser" password="&terapass" mode=TERADATA); create table TERADATA_DATE as select * from connection to teradata ( /* Teradata SQL goes here */ select current_date );quit;The "from connection to teradata" structure is part of the FROM component of the SELECT statement and means to submit everything between the parentheses to Teradata.To run a Teradata SQL statement that does not return a result (like CREATE TABLE or COLLECT STATISTICS), use the SAS "execute" statement:proc sql; connect to teradata (user="&terauser" password="&terapass" mode=TERADATA); execute ( collect statistics on mydb.mytable index (myindex) ) by teradata;quit;

Oblak 2 posts Joined 08/07
09 Mar 2008

Wow! it worked. Thanks Bobduell..

gunn 8 posts Joined 11/14
02 Mar 2015

Please answer if there is a way to keep connection alive between multiple proc sql's?
For example:

proc sql;
connect to teradata as tera (user=testuser pass=testpass);
and then just use this connection in other proc sql's:

proc sql;
execute (drop table salary) by tera;
execute (commit) by tera;
execute (create table salary (current_salary float, name char(10)))
by tera;
disconnect from tera;

You must sign in to leave a comment.