Dealing with 32 character restrictions

[nrose]

Hi all,

SAS has long had a restriction in the ability to read table names and field names more than 32 characters in length. Why this is still the case in an era where third party DBMS's don't have this restriction is a mystery, and it wouldn't be so bad except that, setting up an ODBC connection to a database means that SAS cannot 'see' these tables.

So, have other users had the same issue, and how do they resolve it? Using explicit passthrough seems to negate the whole point in investing thousands of dollars in a Data Integration Suite, so, how do others workaround this very annoying issue?

Would really appreciate any advice.

Thanks.

PS. Telling an external organisation to create new tables and views to accomodate this deficiency is not an option for us.

[Keith]

I came across this problem fairly recently with Oracle tables (as well as column names >32 characters).  Having done lots of research into it, the only solution we could find that worked (apart from renaming the tables) was to create views with names <32 characters.  Fortunately I was dealing with internal data, so it wasn't too much trouble to get the new views created.

With long column names, SAS just truncates them so they are still visible, but it can't do this with table names.

Sorry I couldn't give you a better solution.

[Ksharp]

You can try these two  libname options :

preserve_col_names=yes

perserve_tab_names=yes

I am not sure whether it would be helpful . Maybe SAS will truncate them to be 32 length.

[cakencookies19]

There is nothing worse than someone making long code words for us to write code for! People in general are long-winded.

In fact, now when I'm working on any programming I first check the data to see if the variables are long names and immediately send an email letting them know about the 32 character restriction and the preference for smaller names.

[SASKiwi]

That might work sometimes, but I need to access external databases that I have no involvement in developing or maintaining. If those systems support > 32 character names then so should SAS. 

[Tom]

Personally I would complain to the external organization.  Table and variable NAMES longer than 32 characters would make if EXTREMELY difficult if not impossible to write programs.  You would fill up almost the entire visible part of a line of code with one entity name!  It would be like the old Bill Cosby comedy routine about the kindergarten kid trying to write the alphabet with a big fat pencil. "A......, B....., teacher can I have another piece of paper?"

If you can query the metadata of your source database then you should be able to deal with the long variable names at least.

If SAS will automatically shorten the names for you (I know it can do this with PROC IMPORT) then you can just use the original names as labels by matching by relative position in the data vector.

You also might want to get STAT/TRANSFER or some other utility to convert your data for you.

[nrose]

Thanks everyone for their comments,

We have received word from SAS that there is nothing much that can be done about this, and it is a restriction in SAS that is not going away. We are pursuing talks with the database owners in question to create SAS compliant views, but in the end, it is understandable if they want to use more descriptive names for their tables. Programming tools that require explict typing out of table and variable name are becoming dated now. Tools such as DI studio have a sophisticated GUI interface and use a lot of point and click, and tools from other vendors have autocomplete and enhanced editor functions, which enables programmers  to easy pick table names and variable names from drop down lists that are generated without writing it out. Programs actually become more readable in that you don't have to try to decipher the meaning of, short, cryptic names.

In the end, as data integration specialists, SAS should fix this so that you can at least see the tables in DI studio.

Nick

[JohnJPS]

This would be a potential deal-breaker in my book.  Brushing it off with Bill Cosby anecdotes is logically unsound: it's not like every single column name is longer than 32 characters, and it's not like you reference them in every line of your code, and it's not like modern IDEs require you to type in the whole thing anyway: they have auto-complete for that. Never mind that 1080P and 4K monitors easily allow for pretty long lines of code without horizontal scrolling.

Granted, if the consumer has only a handful of instances, it's easy enough to create a handful of views... but if this potentially affects hundreds of tables and several thousand columns, it becomes untenable. For those shops, it's a deal-breaker: the listing of companies that do not have this limitation includes pretty much everybody but SAS, and that's a problem: they simply are not with the times.

[SASKiwi]

I recently ran into this problem myself with SQL Server. The only way I could reliably see all table names was to use SQL Server Management Studio as the SAS Explorer won't show them, The only workaround I could find in SAS to read these tables was SQL passthru as you found. Luckily only a few tables were involved so the problem is small enough to be managable. We don't have DI studio so we don't have that problem.

One suggestion for SAS is that they enhance the LIBREF.'non-valid-table-name'n capability to deal with > 32 chars long so this works:

data libref.valid_table_name;

  set libref.'non-valid-table-name'n;

run;

I would have thought if SAS can deal with non-standard characters this way it would not be much of a stretch to deal with > 32 chars.

Another idea I have would be to create a SAS macro that automatically generates validly-named SAS views of the database tables with long names. This should work with DI Studio, but haven't tried it so don't know if there would be major disadvantages or not.

[TomKari]

I never found the restriction too difficult to circumvent, but after reading these posts I feel strongly that if SAS wants to market DIS as an advanced ETL tool, which it is in most respects, they should make it a very high priority to enable dealing with this problem in the tool.

After all, the whole point of DIS is to enable moving data to and from external sources like databases. This limitation is pretty much a deal-breaker that could easily prevent an organization adopting it.

Tom

[nrose]

Interesting you say that Tom,

Organisations often need to make a decision about how they spend their money on IT, and Microsoft have the leading OS, the leading Office suite, and are now reaching in further into territories long the domain of other vendors such as Business Intelligence and analytics. SAS has done a great job in integrating their products with Microsoft, especially with products such as the add-in for Microsoft Office. What they have neglected is a seamless integration with the Microsoft back-end SQL Server and the 32 character length issue is just one issue. Whilst I, personally, can see the value of powerful tools like DIS (and would not think, at this stage anyway of using SSIS, SSRS etc), and understand the benifits that investment in SAS products in general can provide, it is hard to justify this to others (with the money!) when it has these sorts of restrictions.

Nick

[agoldma]

There is an idea related to the 32 character limit: Metadata&colon; More space please

(this name is a bit misleading, but it's the best I could find; there are only 12 measly votes so far)

PLEASE VOTE

[SASKiwi]

Agreed - I voted on this when it first came out.

[Malarkey]

Me, too!  The problem extends to database names as well.  I can't connect to ones that have more than 32 letters in their names.  YUCK!

[SASKiwi]

Have you tracked this issue to SAS? If so what was the response? If not please do so - the more people do this the more likely something will be done about it.

BTW - what database is this? If it is SQL Server then there is a workaround with SQL Passthru and ODBC. The secret is to connect to the database at the server level.

proc sql;
  connect to odbc (noprompt = "server=MySQLServer;DRIVER=SQL Server;Trusted Connection=yes;");

  create table MyTable as select * 
  from connection to odbc
  (select * from [MyVeryLongDatabaseNameMoreThan32Characters].[MySchema].[MyTable]);
  quit;

It may be possible to use this approach with some other databases / ACCESS products.