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

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

Jade_SAS — Wed, 12 Apr 2017 17:24:49 GMT

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

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

Reeza — Wed, 12 Apr 2017 17:44:36 GMT

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.

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

JBailey — Wed, 12 Apr 2017 17:58:19 GMT

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-Enterprise-Guide/td-p/217275

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

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

Jade_SAS — Thu, 13 Apr 2017 18:16:34 GMT

Thank you, Reeza and JBailey!

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

MRDM — Tue, 18 Apr 2017 09:45:40 GMT

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.

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

SASKiwi — Tue, 18 Apr 2017 20:47:22 GMT

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

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

JBailey — Wed, 19 Apr 2017 20:15:28 GMT

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

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

MRDM — Thu, 27 Jul 2017 09:24:17 GMT

@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

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

JBailey — Thu, 27 Jul 2017 11:47:54 GMT

Hi @MRDM

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

Best wishes,

Jeff

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

Tom — Thu, 27 Jul 2017 13:27:09 GMT

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

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

JohnJPS — Mon, 07 May 2018 18:56:26 GMT

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

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

MRDM — Tue, 08 May 2018 08:55:28 GMT

@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

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

LinusH — Wed, 09 May 2018 15:34:43 GMT

@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

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

Tom — Wed, 09 May 2018 15:38:22 GMT

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

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

LinusH — Wed, 09 May 2018 16:26:33 GMT

@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

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

Mark_sas — Wed, 09 May 2018 23:42:20 GMT

As mentioned in my SGF talk, the 9.5 release of SAS is currently slated to contain long name support (i.e., up to 255 characters) for member names of type DATA, VIEW, and ITEMSTOR, as well as variable names for DATA, VIEW, and PROGRAM.

Those interested in a copy of the paper associated with my talk can contact their SAS account representative, asking them to request the latest version from me.

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

SASKiwi — Thu, 10 May 2018 21:36:06 GMT

Am I correct in assuming that SAS client tools like EG will support long names if connected to a SAS 9.5 server, including the displaying of long external database table names and columns?

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

ChrisHemedinger — Thu, 10 May 2018 21:51:11 GMT

Hi @SASKiwi - reasonable expectation, yes. Though it may take a little while for all clients to be in sync with that.

BTW, we also updated the SASWare Ballot items about the 32-char limit and plans for lifting it. No more detail than @Mark_sas shared here, but it is something to look forward to.

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

MRDM — Fri, 11 May 2018 07:46:28 GMT

Is there a rough ETA for 9.5? I know M releases normally come around September but am unsure about 'major' releases? I'm attending the UK forum in a month so there maybe more info by then.

Thanks

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

Mark_sas — Fri, 11 May 2018 14:23:25 GMT

The current target for 9.5 is the end of this year.