Pass Through in SAS

UDA
Fan

Pass Through in SAS

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

Re: Pass Through in SAS

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 autoexec.sas 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;
Fan

Re: Pass Through in SAS

Wow! it worked. Thanks Bobduell..
Enthusiast

Re: Pass Through in SAS

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);
quit;

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