Accessing Teradata table

[hkassamNS]

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.

[hkassamNS]

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

[Tom]

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

[hkassamNS]

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.

[SASKiwi · Accepted solution]

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.

[hkassamNS]

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!

[Tom]

---

@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.