BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jade_SAS
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
JBailey
Barite | Level 11

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

28 REPLIES 28
Reeza
Super User

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.

JBailey
Barite | Level 11

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 

Jade_SAS
Pyrite | Level 9

Thank you, Reeza and JBailey! 

MRDM
Obsidian | Level 7

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.

SASKiwi
PROC Star

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

JBailey
Barite | Level 11

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

MRDM
Obsidian | Level 7

@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

JBailey
Barite | Level 11

Hi @MRDM

 

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

 

Best wishes,

Jeff

Tom
Super User Tom
Super User

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

JohnJPS
Quartz | Level 8

It was communicated as the Global Forum that this will finally be fixed in version 9.5.

MRDM
Obsidian | Level 7

@JohnJPSwrote:

It was communicated as the Global Forum that this will finally be fixed in version 9.5.


Do you know which session this was covered in so I can watch it.

Thanks

LinusH
Tourmaline | Level 20
@MRDM :
Comparing SAS® Viya® and SAS® 9.4 Capabilities: A Tale of Two SAS® Platform Engines, including PDFs, photos, and more.

Presented by Mark Schneider.

https://event.crowdcompass.com/sasgf2018/activity/CvjwHlcluj
Data never sleeps
Tom
Super User Tom
Super User

@LinusH wrote:
@MRDM :
Comparing SAS® Viya® and SAS® 9.4 Capabilities: A Tale of Two SAS® Platform Engines, including PDFs, photos, and more.

Presented by Mark Schneider.

https://event.crowdcompass.com/sasgf2018/activity/CvjwHlcluj

Is there a better link?

Please enter the event password to see this event.

 

LinusH
Tourmaline | Level 20
@Tom sorry there isn't really.
Mark doesn't seem to have published anything to the proceedings, and no video that covered the event.
So this is the best I can so for now:
https://twitter.com/LinusHjorth/status/983397393050746880?s=19
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 28 replies
  • 37838 views
  • 14 likes
  • 12 in conversation