topic Re: reference a table of a lib inside a "teradata connection" in SAS Programming

reference a table of a lib inside a "teradata connection"

arcastro — Thu, 09 Dec 2021 17:36:05 GMT

how to reference a lib table while inside a teradata connection?

Re: reference a table of a lib inside a "teradata connection"

Reeza — Thu, 09 Dec 2021 17:55:39 GMT

Are you using Explicit pass through?
In that case you're pushing SQL directly to your server and your server cannot see your SAS data sets but there are workarounds if you explain more what you're trying to do.

Re: reference a table of a lib inside a "teradata connection"

arcastro — Mon, 13 Dec 2021 13:02:54 GMT

Thanks for your reply!

I am using this method to connect Teradata:

"PROC SQL;
CONNECT TO TERADATA(SERVER='xxxx' SCHEMA='VWDW' USER="xxxx" PASSWORD="xxxx" MODE=TERADATA);"

I want to see some table in my work/lib in SAS and make filters using my own table inside the SAS.

Is it possible?

Re: reference a table of a lib inside a "teradata connection"

Tom — Mon, 13 Dec 2021 13:23:02 GMT

@arcastro wrote:

Thanks for your reply!

I am using this method to connect Teradata:

"PROC SQL;
CONNECT TO TERADATA(SERVER='xxxx' SCHEMA='VWDW' USER="xxxx" PASSWORD="xxxx" MODE=TERADATA);"

I want to see some table in my work/lib in SAS and make filters using my own table inside the SAS.

Is it possible?

It is still hard to tell what you want to do. You can clarify by describing a simple example. Show what tables you have, clearly indicating which are in Teradata and which are not, and then describe the query you need to execute using those tables.

Note that you should be able to create volatile tables inside your Teradata database that will live for the duration of your Teradata session. Similar to WORK datasets in SAS.

Re: reference a table of a lib inside a "teradata connection"

Reeza — Mon, 13 Dec 2021 16:09:05 GMT

@arcastro wrote:

Thanks for your reply!

I am using this method to connect Teradata:

"PROC SQL;
CONNECT TO TERADATA(SERVER='xxxx' SCHEMA='VWDW' USER="xxxx" PASSWORD="xxxx" MODE=TERADATA);"

I want to see some table in my work/lib in SAS and make filters using my own table inside the SAS.

Is it possible?

Most likely yes, but exactly how depends on the query.

Re: reference a table of a lib inside a "teradata connection"

arcastro — Mon, 13 Dec 2021 16:52:48 GMT

Thanks again!

You can see a simple example below.

I am connecting in a Teradata Server, and I want to make a join with a table that is outside of this connection, like a Lib or Work. I just wanna make a filter using auxiliar table in my teradata query. This auxlliar tabela could be temporarily, no problem.

LIBNAME R_PNL '/sasusers/workgroup/PNL';

PROC SQL;
CONNECT TO TERADATA(SERVER='xxx' SCHEMA='VWDW' USER="xxxxx" PASSWORD="xxxx" MODE=TERADATA);
CREATE TABLE Test AS
SELECT * FROM CONNECTION TO TERADATA
(Select * FROM VWDW.VDW_TERA AS X
INNER JOIN R_PNL.COSTUMERS AS Y ON Y.ID = X.ID
);
DISCONNECT FROM TERADATA;
QUIT;

Re: reference a table of a lib inside a "teradata connection"

Reeza — Mon, 13 Dec 2021 17:29:33 GMT

LIBNAME R_PNL '/sasusers/workgroup/PNL';

proc sql noprint;
select distinct iD into :id_list separated by ", "
from r_pnl.costumers;
quit;


PROC SQL;
CONNECT TO TERADATA(SERVER='xxx' SCHEMA='VWDW' USER="xxxxx" PASSWORD="xxxx" MODE=TERADATA);
CREATE TABLE Test AS
SELECT * FROM CONNECTION TO TERADATA
(Select * FROM VWDW.VDW_TERA AS X
INNER JOIN R_PNL.COSTUMERS AS Y ON Y.ID  in (&id_list)
);
DISCONNECT FROM TERADATA;
QUIT;

Here's one way if the list of ID's is manageable (macro variables have a limit of 64k characters)

If it gets too big you can create a dynamic macro to generate the list instead.

Re: reference a table of a lib inside a "teradata connection"

arcastro — Mon, 13 Dec 2021 18:57:31 GMT

The auxiliar table has more than 65534 characters.

ERROR: The length of the value of the macro variable ID_LIST (65540) exceeds the maximum length (65534). The value has been truncated to 65534 characters.

How would it be "dynamic macro"?