Long (SQL-Server via ODBC) table names & column names

[AndrewHowell]

Background:

• SAS 9.4 M3 (Windows).
• SAS/Access to ODBC (connecting to SQL-Server database)
• I have SAS Platform Admin and ODBC Admin permissions on the Windows server.
• I can define the ODBC settings to connect the server to the SQL Server databases
• I can define the Server & Connections in the Server Manager plug-in of Management Console
• I can define the libraries in the Data Library Manager plug-in of Management Console

Problems:

• Tables with tablename lengths exceeding 32 characters do not appear when registering tables.
• Other tables (which DO define in metadata) but have multiple (similarly names) long column names won't open in Enterprise Guide.

If a table has one column with a name length exceeding 32 characters, it can open ok in EG. For example:

• ThisIsMyReallyReallyLongColumnName

However, if a table has multiple columns whose first 32 characters are identical, the table won't open in EG. For example

• ThisIsMyReallyReallyLongColumnNameOne
• ThisIsMyReallyReallyLongColumnNameOne
• "ThisIsMyReallyReallyLongColumnNa" is the first 32 characters in both names

If I were to open this table in EG, I get an error:

• Variable ThisIsMyReallyReallyLongColumnN0 is not on file LibName.TableName

I've played with so many options (VALIDVARNAME, etc) when defining the libraries, and when registering the tables in metadata, when opening EG, that I'm now starting to go in circles.

So, any assistance appreciated..

[Kurt_Bremser]

Create views in SQL Server where table(view) name and column names adhere to the 32-character limit. SAS simply does not have more than 32 characters reserved for such names, so longer names will never work until that is expanded in a major release.

[SASKiwi · Accepted solution]

There are just two workarounds for dealing with this currently

• Create a view in SQL Server which shortens the offending long table names and columns whose first 32 characters are the same.
• Access the tables using SQL Passthru which allows you to reference table names and columns of any length and then define shorter SAS-compliant names - you could make these permanent SAS views so they are visible and usable by other SAS users.

I understand that SAS is looking at enhancements to deal with the 32 character limitations but as yet there is no confirmation of when or if that might happen. There was confirmation of this in another post from a SAS employee:

https://communities.sas.com/t5/SAS-Data-Management/about-table-name-longer-than-32-character-and-dat...

There is a SASware Ballot suggestion for this: https://communities.sas.com/t5/SASware-Ballot-Ideas/Metadata-amp-colon-More-space-please/idi-p/22031...

Search the community for many other posts on the 32 character limitation.

[AndrewHowell]

I was hoping to just find the right combination of options where I could just reference the table/columns as a "Name Constant"$.

Oh well.. Thanks for the responses..

[SASKiwi]

Unfortunately there are no SAS options that will allow the "Name Constant"n to go beyond 32 characters. If it were allowed SAS would still have to trim the column names when storing them as SAS tables.

I can understand why it is taking such a long time to decide what to do with this issue. If it is done globally across the whole SAS system it would be a major rewrite of massive proportions. I'm old enough to remember when SAS went from an 8 character limit to 32 - we all thought that was revolutionary!

If enhancements were made to just deal with the external database 32 character limitation you would still have the problem of having to trim them back to 32 when stored as SAS tables. I don't think there are any easy answers here.