SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

about table name longer than 32 character and data types in SAS

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 111
Accepted Solution

about table name longer than 32 character and data types in SAS

Hi All,

 

    I have several question regard to the table name (or column name) longer than 32 character and data types in SAS:

    1) If Teradata table name is longer than 32 character, how can SAS handle it?

       I know for SQL table we can do a workaround and using the SQL PASS-through to access the table, can we do this too with Teradata? Any there any other options?

 

    2) Can SAS handle column names that longer than 32 now? I know there is 32 character limitation, but after going through several post on the forum, it seems SAS is working on the columns names that longer than 32. Anyone knows the most recent update on this?

 

    3) Is ther any data types that in Teradata or SQL that will not work in SAS? Could you please let know the details of it.

 

    4) If 'Yes" for 3), how can we handle it?

 

Thank you very much!

 

Thanks,

Jade


Accepted Solutions
Solution
‎04-13-2017 02:15 PM
SAS Employee
Posts: 199

Re: about table name longer than 32 character and data types in SAS

[ Edited ]

Hi @Jade_SAS

 

Question #1 - SAS cannot handle table names longer than 32 characters. This limit can be worked-around by creating a database view on the table and making the view name 32 characters long, or less.

 

Question #2 - SAS variable names cannot be longer than 32 characters. There are plans to address this but the work hasn't been scheduled and is not on a roadmap.

 

Question #3 - Teradata BIGINT and NUMBER data types are problematic because they exceed the precision of SAS 9.4. This SAS Note may help: http://support.sas.com/kb/39/831.html

 

This communities topic has information on BIGINT: https://communities.sas.com/t5/SAS-Data-Management/How-do-to-access-a-bigint-in-Teradata-from-Enterp...

 

Here is a code example using NUMBER:

 

15   libname mytera teradata server=vat user=sasxjb password=XXXXXX;
NOTE: Libref MYTERA was successfully assigned as follows:
      Engine:        TERADATA
      Physical Name: vat

16   proc sql;
17      connect using mytera;
18      execute (create table jeff_num_test (x int, problem number)) by mytera;
19      execute (commit) by mytera;
20      execute (insert into jeff_num_test values (1,1)) by mytera;
21   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.18 seconds
      cpu time            0.04 seconds


22   proc sql;
23      select * from mytera.jeff_num_test;
NOTE: Writing HTML Body file: sashtml.htm
NOTE: No rows were selected.
NOTE: The following 1 column(s) in MYTERA.jeff_num_test  have been automatically dropped because they
      have a datatype that is not supported by this engine:
problem <--- Jeff Note: this is the name of the actual column... 

24   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.56 seconds
      cpu time            0.39 seconds


If I didn't include a readable column in the table I would have gotten this error:

35   proc sql;
36      connect using mytera;
37      execute (create table jeff_num_test2 (problem number)) by mytera;
38      execute (commit) by mytera;
39      execute (insert into jeff_num_test2 values (1)) by mytera;
40   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.50 seconds
      cpu time            0.04 seconds


41
42   proc sql;
43      select * from mytera.jeff_num_test2;
ERROR: Table MYTERA.jeff_num_test2 doesn't have any columns. PROC SQL requires each of its tables to
       have at least 1 column.
ERROR: Error trying to read from a DBMS table. At least one column must be selected.
NOTE: The following 1 column(s) in MYTERA.jeff_num_test2  have been automatically dropped because
      they have a datatype that is not supported by this engine:
problem <--- Jeff Note: this is the name of the actual column...


44 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.16 seconds cpu time 0.04 seconds

 

Here is the source code:

libname mytera teradata server=tserv user=sasxjb password=password1;


proc sql;
   connect using mytera;
   execute (create table jeff_num_test (x int, problem number)) by mytera;
   execute (commit) by mytera;
   execute (insert into jeff_num_test values (1,1)) by mytera;
quit;

proc sql;
   select * from mytera.jeff_num_test;
quit;

proc sql;
   connect using mytera;
   execute (create table jeff_num_test2 (problem number)) by mytera;
   execute (commit) by mytera;
   execute (insert into jeff_num_test2 values (1)) by mytera;
quit;

proc sql;
   select * from mytera.jeff_num_test2;
quit;
   

Hope this helps,

Jeff 

View solution in original post


All Replies
Super User
Posts: 17,734

Re: about table name longer than 32 character and data types in SAS

1. If Pass Through is set up for Teradata then it can be handled in the same way as SQL. SAS cannot read tables longer than 32 characters, you'll need to create a view with a shorter name. 

2. No, there's no recent update to this, AFAIK.

3. Not that 'won't work' but sometimes even datetimes can be handled differently in different DB systems so you're users will experience different behaviour.

Solution
‎04-13-2017 02:15 PM
SAS Employee
Posts: 199

Re: about table name longer than 32 character and data types in SAS

[ Edited ]

Hi @Jade_SAS

 

Question #1 - SAS cannot handle table names longer than 32 characters. This limit can be worked-around by creating a database view on the table and making the view name 32 characters long, or less.

 

Question #2 - SAS variable names cannot be longer than 32 characters. There are plans to address this but the work hasn't been scheduled and is not on a roadmap.

 

Question #3 - Teradata BIGINT and NUMBER data types are problematic because they exceed the precision of SAS 9.4. This SAS Note may help: http://support.sas.com/kb/39/831.html

 

This communities topic has information on BIGINT: https://communities.sas.com/t5/SAS-Data-Management/How-do-to-access-a-bigint-in-Teradata-from-Enterp...

 

Here is a code example using NUMBER:

 

15   libname mytera teradata server=vat user=sasxjb password=XXXXXX;
NOTE: Libref MYTERA was successfully assigned as follows:
      Engine:        TERADATA
      Physical Name: vat

16   proc sql;
17      connect using mytera;
18      execute (create table jeff_num_test (x int, problem number)) by mytera;
19      execute (commit) by mytera;
20      execute (insert into jeff_num_test values (1,1)) by mytera;
21   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.18 seconds
      cpu time            0.04 seconds


22   proc sql;
23      select * from mytera.jeff_num_test;
NOTE: Writing HTML Body file: sashtml.htm
NOTE: No rows were selected.
NOTE: The following 1 column(s) in MYTERA.jeff_num_test  have been automatically dropped because they
      have a datatype that is not supported by this engine:
problem <--- Jeff Note: this is the name of the actual column... 

24   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.56 seconds
      cpu time            0.39 seconds


If I didn't include a readable column in the table I would have gotten this error:

35   proc sql;
36      connect using mytera;
37      execute (create table jeff_num_test2 (problem number)) by mytera;
38      execute (commit) by mytera;
39      execute (insert into jeff_num_test2 values (1)) by mytera;
40   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.50 seconds
      cpu time            0.04 seconds


41
42   proc sql;
43      select * from mytera.jeff_num_test2;
ERROR: Table MYTERA.jeff_num_test2 doesn't have any columns. PROC SQL requires each of its tables to
       have at least 1 column.
ERROR: Error trying to read from a DBMS table. At least one column must be selected.
NOTE: The following 1 column(s) in MYTERA.jeff_num_test2  have been automatically dropped because
      they have a datatype that is not supported by this engine:
problem <--- Jeff Note: this is the name of the actual column...


44 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.16 seconds cpu time 0.04 seconds

 

Here is the source code:

libname mytera teradata server=tserv user=sasxjb password=password1;


proc sql;
   connect using mytera;
   execute (create table jeff_num_test (x int, problem number)) by mytera;
   execute (commit) by mytera;
   execute (insert into jeff_num_test values (1,1)) by mytera;
quit;

proc sql;
   select * from mytera.jeff_num_test;
quit;

proc sql;
   connect using mytera;
   execute (create table jeff_num_test2 (problem number)) by mytera;
   execute (commit) by mytera;
   execute (insert into jeff_num_test2 values (1)) by mytera;
quit;

proc sql;
   select * from mytera.jeff_num_test2;
quit;
   

Hope this helps,

Jeff 

Frequent Contributor
Posts: 111

Re: about table name longer than 32 character and data types in SAS

Thank you, Reeza and JBailey! 

Occasional Contributor
Posts: 12

Re: about table name longer than 32 character and data types in SAS

Are there any plans to address the table name issue. It's a massive pain if you have a lot of tables and register them to randomly have some missing. Creating views is all well and good, except for a lot of our stuff we only have read access so can't do it. Or if we can it requires a DBA and about two weeks notice as well as documents outlining risks etc. Would be nice if SAS just supported the same lengths SQL does.

Super User
Posts: 3,100

Re: about table name longer than 32 character and data types in SAS

[ Edited ]

@JBailey's response states there are plans for supporting > 32 character variable names but doesn't confirm the same for table names. It would make sense from an architectural point of view to do both at the same time otherwise it would just create massive confusion with users - IMHO.

 

@MRDM - don't forget you can use SQL passthru referencing DBMS table names > 32 characters long as an alternative to creating SQL views. The only problem is having to use another SQL tool to verify the correct name so you can copy that over to SAS.

SAS Employee
Posts: 199

Re: about table name longer than 32 character and data types in SAS

[ Edited ]

Long names for tables is being discussed, but there has been no firm decision made.

Occasional Contributor
Posts: 12

Re: about table name longer than 32 character and data types in SAS

@JBailey

 

Is there any progress on this? I could really do with long variable names in the very near future and table names would be a nice to have at some stage as well.

 

I'm doing an export that relies on using paramaterised values (of variable names) that are exported using a data step with a keep= statement, I'd much rather have an exact match than have it try and pick the most likely (as the ends are dropped), that would also be less efficient and more complex as well.

 

To be honest the whole issue is causing us problems with processes and having to mess about with workarounds, but also we're trying to encourage users to transition from SSIS to SAS and telling them they can't readily access some tables/variables is not helping

SAS Employee
Posts: 199

Re: about table name longer than 32 character and data types in SAS

Hi @MRDM

 

The situation is still the same. It is being discussed but there are no firm plans at this time.

 

Best wishes,

Jeff

Super User
Super User
Posts: 6,495

Re: about table name longer than 32 character and data types in SAS


JBailey wrote:

Hi @MRDM

 

The situation is still the same. It is being discussed but there are no firm plans at this time.

 

Best wishes,

Jeff


@JBailey Could SAS put together a paper on how to query metadata from various key database vendors platforms and use that to automate the creation of SAS defined views that map the variable/table names to valid SAS names?  Then at least users that have this issue will have a good starting point for a way to deal with it.  I would start with a read-only method and then if possible explore how to handle needs to update database tables.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 996 views
  • 3 likes
  • 6 in conversation