topic Re: Dealing with 32 character restrictions in SAS Data Management

Dealing with 32 character restrictions

nrose — Thu, 05 Jul 2012 00:14:15 GMT

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.

Re: Dealing with 32 character restrictions

Keith — Fri, 06 Jul 2012 08:59:37 GMT

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.

Re: Dealing with 32 character restrictions

Ksharp — Fri, 06 Jul 2012 09:11:08 GMT

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.

Re: Dealing with 32 character restrictions

Tom — Fri, 06 Jul 2012 22:49:19 GMT

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.

Re: Dealing with 32 character restrictions

nrose — Fri, 06 Jul 2012 23:30:06 GMT

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

Re: Dealing with 32 character restrictions

SASKiwi — Wed, 25 Jul 2012 02:02:39 GMT

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.

Re: Dealing with 32 character restrictions

TomKari — Wed, 25 Jul 2012 13:01:12 GMT

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

Re: Dealing with 32 character restrictions

nrose — Thu, 26 Jul 2012 00:23:35 GMT

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

Re: Dealing with 32 character restrictions

agoldma — Mon, 15 Feb 2016 14:47:14 GMT

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

Re: Dealing with 32 character restrictions

SASKiwi — Mon, 15 Feb 2016 18:43:59 GMT

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

Re: Dealing with 32 character restrictions

Malarkey — Tue, 28 Jun 2016 14:51:37 GMT

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!

Re: Dealing with 32 character restrictions

SASKiwi — Tue, 28 Jun 2016 20:21:29 GMT

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.

Re: Dealing with 32 character restrictions

Malarkey — Thu, 21 Jul 2016 15:05:27 GMT

Thanks, @SASKiwi! The work-around is how I was able to deal with the immediate problem. I haven't yet opened a ticket, but I will do so soon.

Re: Dealing with 32 character restrictions

ScottBass — Thu, 08 Sep 2016 05:57:25 GMT

I ran into this issue today. In fact my Googling took me to this post.

For the old timers out there...remember when?

1) SAS V5 was written in PL/1

2) SAS made the very strategic decision to do a major rewrite using C and Multi Vendor Architecture (MVA). They even bought Lattice C to assist in this process (https://en.wikipedia.org/wiki/Lattice_C)

3) While this rearchitecture was a lot of work for SAS, it proved very successful, and IMO is a major reason SAS is as successful as it is today.

SAS is 40 years old, and it's age is showing. While I love SAS software and enjoy working with it every day, IMO I feel SAS is in a similar position as it was back then.

I certainly hope SAS V10 will incorporate many of the best features of more modern software packages. Perhaps even (gasp!) let go of backwards compatibility for the sake of moving forward. Perhaps "old SAS" and "new SAS", as companies go through a transition period of old code vs. new and improved code.

In the meantime, I'll go through my application and shorten those dynamically generated table names that are > 32 characters (which contain the userid in the path - long story). The SQL Passthrough works fine - SQL Server manages these table names just fine. But when I try to access them in SAS...sigh.

</soapbox>

Re: Dealing with 32 character restrictions

SASKiwi — Thu, 08 Sep 2016 19:58:31 GMT

Aaah a trip down memory lane....SAS V10 AKA SAS Viya.

Re: Dealing with 32 character restrictions

LinusH — Thu, 08 Sep 2016 20:43:46 GMT

Will there ever be a SAS 10?

Re: Dealing with 32 character restrictions

TomKari — Thu, 08 Sep 2016 21:07:59 GMT

Unless you've had to wade through a System/370 memory dump, I'm not sure any of you belong in this conversation, if we're limiting it to old-timers!

Tom

Re: Dealing with 32 character restrictions

Malarkey — Thu, 08 Sep 2016 21:13:13 GMT

Oh, I've been through 370 dumps - and without AbendAid! ICK!

Re: Dealing with 32 character restrictions

ScottBass — Thu, 08 Sep 2016 21:14:58 GMT

Didn't have to wade through a memory dump but do (or did) know JCL, clist, and ISPF. And my first SAS version was 5.16 🙂

Re: Dealing with 32 character restrictions

TomKari — Thu, 08 Sep 2016 21:25:42 GMT

I'm not sure what release I started with. What was the last release where the documentation was only one book? I remember the next release was divided into a book for the PROCs, and one for everything else. Also, it introduced the MACRO facility, and we've been getting into trouble ever since!!

Tom