topic Re: Accessing Teradata table in SAS Programming

Accessing Teradata table

hkassamNS — Fri, 16 Aug 2024 16:58:29 GMT

I am trying to access a table member in a teradata library. Although the library "MyTera2" is successfully assigned I am getting an error message when running proc sql. Something to do with the period not recognized. The Carload table is a member within the Revenue_pview library. Please see below.

proc sql;
Select *
FROM MyTera2.REVENUE_PVIEW.CARLOAD CL

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, (, ',', ANSIMISS, AS, CROSS, EXCEPT, FULL, GROUP, HAVING,
INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE.

ERROR 200-322: The symbol is not recognized and will be ignored.

Can someone please help. Thanks.

Re: Accessing Teradata table

hkassamNS — Fri, 16 Aug 2024 17:25:25 GMT

To clarify, the error message indicates that the period between pview and carload is not a recognized symbol. Thanks.

Re: Accessing Teradata table

Tom — Fri, 16 Aug 2024 17:29:31 GMT

SAS dataset names use only two levels. The LIBREF (which is optional and will default to WORK, or in rare cases USER or some other libref you set with the USER system option) and the MEMNAME.

A MEMNAME cannot contain a period.

So assuming the libref MYTERA2 was defined to point to the schema/database named REVENUE_PVIEW on your Teradata server then your code should be:

FROM MyTera2.CARLOAD CL

If for some reason you are trying to access a dataset from a different schema/database than the one you pointed the libref towards then you can use the SCHEMA= dataset option to set it.

FROM MyTera2.CARLOAD(schema="REVENUE_PVIEW") CL

Re: Accessing Teradata table

hkassamNS — Fri, 16 Aug 2024 18:47:03 GMT

Hi Tom

Thanks for the response.

Below is my libname statement:

libname MyTera2 teradata user='xxxx@LDAP' password=XXXXXXXX
server="tdprod.nscorp.com" connection=global;
NOTE: Libref MYTERA2 was successfully assigned as follows:
Engine: TERADATA
Physical Name: tdprod.nscorp.com

Under MYTERA2 there are a number of databases - one of them is REVENUE_PVIEW. CARLOAD is a table within REVENUE_PVIEW. The servers tree looks like this:

MyTERA2 >> REVENUE_PVIEW >> CARLOAD

I tried both methods:

Method 1:

proc sql;
select *
from MyTera2.CARLOAD;
WHERE WB_SN_URRWIN = '1037498014';
QUIT;

ERROR: File MYTERA2.CARLOAD.DATA does not exist.

Method 2:

proc sql;
select *
from MyTera2.CARLOAD(schema=REVENUE_PVIEW);
WHERE WB_SN_URRWIN = '1037498014';
QUIT;

This doesn't work either - the system becomes unresponsive and I have to shut down SAS in Task Manager.

Maybe there is an option somewhere which will allow pointing to the 3rd level member in the server? Any further suggestions will be most welcome. Thanks.

Re: Accessing Teradata table

SASKiwi — Sat, 17 Aug 2024 00:29:16 GMT

Typically SAS LIBNAME statements used to access relational databases, need to point to schemas if you are using SAS SQL. Try modifying your LIBNAME like so:

libname MyTera2 teradata user='xxxx@LDAP' password=XXXXXXXX
   server="tdprod.nscorp.com" connection=global database = 'REVENUE_PVIEW';

You may also need SCHEMA = but you will need to figure the value for yourself.

Re: Accessing Teradata table

Tom — Sat, 17 Aug 2024 03:57:38 GMT

@hkassamNS wrote:

Hi Tom

Thanks for the response.

Below is my libname statement:

libname MyTera2 teradata user='xxxx@LDAP' password=XXXXXXXX
server="tdprod.nscorp.com" connection=global;
NOTE: Libref MYTERA2 was successfully assigned as follows:
Engine: TERADATA
Physical Name: tdprod.nscorp.com

Under MYTERA2 there are a number of databases - one of them is REVENUE_PVIEW. CARLOAD is a table within REVENUE_PVIEW. The servers tree looks like this:

MyTERA2 >> REVENUE_PVIEW >> CARLOAD

I tried both methods:

Method 1:

proc sql;
select *
from MyTera2.CARLOAD;
WHERE WB_SN_URRWIN = '1037498014';
QUIT;

ERROR: File MYTERA2.CARLOAD.DATA does not exist.

Method 2:

proc sql;
select *
from MyTera2.CARLOAD(schema=REVENUE_PVIEW);
WHERE WB_SN_URRWIN = '1037498014';
QUIT;

This doesn't work either - the system becomes unresponsive and I have to shut down SAS in Task Manager.

Maybe there is an option somewhere which will allow pointing to the 3rd level member in the server? Any further suggestions will be most welcome. Thanks.

My guess is that the second option did work, but since most people who pay for Teradata do it because they have HUGE data to store you did not wait long enough for you attempt to copy ALL of the data from the Teradata server to the SAS server.

Try running something that will not return so much data.

Re: Accessing Teradata table

hkassamNS — Mon, 19 Aug 2024 20:19:18 GMT

Hi SASKiwi,

Your suggestion worked! Here is my full code:

libname MyTera4 teradata user='xxxx@LDAP' password=xxxxx
server="tdprod.nscorp.com" connection=global database=REVENUE_PVIEW SCHEMA=REVENUE_PVIEW;
proc sql;
select *
from MyTera4.CARLOAD
WHERE WB_SN_URRWIN = '1037498014';
QUIT;

Note - I am naming the schema same as database.

Thank you very much!