topic Re: Table names longer than 32 character using ODBC in SAS Data Management

Table names longer than 32 character using ODBC

JackyK — Fri, 19 Apr 2019 16:56:42 GMT

Hello everyone,

First time posting, I don't know if this is the right place to ask. But I'm using ODBC to access a database. I'm trying to read a table with a name longer than 32 characters. Are there anyway to work around this problem?

Asking the database management to change the table name is not an option...

An example of my code:

data t;
   set mylib.xxx;
run;

where 'xxx' is actually 51 characters long.

Currently I have two solutions in mind:

1) Uninstalling current SAS and download the 64 bit version

2) Export the data by hand

But I would like to know if there are other simpler approaches before I try these two options.

Thanks

Re: Table names longer than 32 character using ODBC

LinusH — Fri, 19 Apr 2019 17:03:01 GMT

This question has been asked several times. Beside my short notes, do a seach to get more flesh on th bone.
1. Create views in the database that complies with SAS naming conventions.
2. Use SQL Explicit Pass Through to make the mapping to SAS compliant names in your query.

Your option 1 won't help.

Re: Table names longer than 32 character using ODBC

VDD — Fri, 19 Apr 2019 17:25:14 GMT

as @LinusH says you may want to have the DB admins create view that are consistent with SAS.

Views will not require them to dedicate permanent data storage.

Re: Table names longer than 32 character using ODBC

Tom — Fri, 19 Apr 2019 21:20:39 GMT

Personally I like to yell at people that think it is helpful to make longer and longer table and variable names.

But it normally is the same as yelling at a brick wall.

Try just using pass thru SQL.

proc sql;
connect using mylib;
create table t as select * from connection to mylib
(select * from my_table_with_a_name_so_long_that_it_is_longer_than_any_single_line_of_a_program_should_be
);
quit;

Re: Table names longer than 32 character using ODBC

SASKiwi — Fri, 19 Apr 2019 22:34:05 GMT

I understand SAS is addressing this limitation - increase to 128 characters is planned - for all SAS and non-SAS tables and column names in SAS 9.5. However I'm guessing this release is still some way off as we are still in the 9.4 cycle.

Re: Table names longer than 32 character using ODBC

Kurt_Bremser — Sat, 20 Apr 2019 05:40:12 GMT

@Tom wrote:

Personally I like to yell at people that think it is helpful to make longer and longer table and variable names.

But it normally is the same as yelling at a brick wall.

Let's me think of the phrase "Thick as a brick".

Re: Table names longer than 32 character using ODBC

SASKiwi — Sat, 20 Apr 2019 06:36:55 GMT

@Kurt_Bremser - you're not a fan of Jethro Tull are you by any chance?! Happen to be listening to the album of that name right now LOL.

Re: Table names longer than 32 character using ODBC

VDD — Sat, 20 Apr 2019 12:17:19 GMT

I am a fan of Jethro Tull. Thanks @SASKiwi for taking me back 40 years.

Re: Table names longer than 32 character using ODBC

Kurt_Bremser — Sat, 20 Apr 2019 12:35:08 GMT

@SASKiwi wrote:

@Kurt_Bremser - you're not a fan of Jethro Tull are you by any chance?! Happen to be listening to the album of that name right now LOL.

Well, I'm a little more mainstream there. My favorite is Aqualung, as it contains one of the most iconic rock pieces ever.

But I like the phrase. "IQ of a burned piece of mud" is pretty damning 😉

Re: Table names longer than 32 character using ODBC

Angel_Boy — Thu, 16 Jan 2020 04:44:57 GMT

Hello SAS community,

I am looking to see if there has been any update to making SAS work with greater than 32 characters.

This seems to have been an issue for several years now and it doesn't seem like SAS is doing anything in regards to this issue.

When/how soon can we expect SAS to make this a reality? It would be nice to hear back from any of the SAS reps on this.

Re: Table names longer than 32 character using ODBC

SASKiwi — Thu, 16 Jan 2020 19:16:47 GMT

@Angel_Boy - I haven't heard anything further. The last significant SAS update was last November for SAS 9.4M6.