Metadata: More space please

[ChrisNZ]

32 characters is rather short these days, especially when dealing with external (DBMS, excel, etc) data.

Why not allow longer variable names? and labels? and likewise for data members?

The data structure would not be affected, just the metadata.

The change from 8 to 32 (40 to 256 for labels) characters took place for version 7 almost 20 years ago; time for a new enhancement.

Of course, longer text strings in SAS variables (200 to 32k en V7) and macro variables (no change in V7 iirc) would be nice too, but the metadata upgrade should be both easier and more immediately useful.

[FriedEgg]

There were some significant enhancements to variable metadata made recently, such as Extended Attributes (http://support.sas.com/documentation/cdl/en/lrcon/67885/HTML/default/viewer.htm#p0wvxeb8936663n1f0x4...).  And Macro variables have been extended as well since V7, I believe it was in 9.2 that 32,767 was extended, optionally, to 65,534.  It would be great to see it possible to store whole documents of arbitrary size in a variable!  Below are some column name lengths I can recall off hand:

Oracle - 30

DB2 - 128

SQL Server - 128

MySQL/MariaDB (w/ InnoDB) - 64

PostgreSQL - 31

Teradata - 30

Greenplum - 63

Hive - 128?

Edit - Some of the comments made above no longer appear relevant as the OP modified his posting.  In regards to to table name lengths, all identities typically share their max length, so the above information should be directional to not just column names.

[SASKiwi]

The biggest inconvenience for us at the moment is when you have a SAS library pointing at an SQL Server database, tables with names > 32 characters just don't show at all causing users all sorts of confusion. I know you can still read these with SQL Passthru, but you have to resort to using SQL Server Studio to confirm what their real names are. There should be a better way of dealing with DBMS tables name > 32 characters even if SAS table and column name lengths are not increased.

[jakarman]

For Teradata 15 limit has changed with enableon=yes to 128 instead of the 30.  The SAS/Access interface is probably not updated for that.

http://www.info.teradata.com/htmlpubs/DB_TTU_15_00/index.html # page/Database_Management/B035_1093_015K/1093AppB.046.09.html

[Tom]

You would think that SAS/Access could take care of much of this for you.

For example for databases that do not have the LABEL concept it could generate a unique 32 character variable name (similar to PROC IMPORT) and store the actual variable (or dataset name) in the label (or memlabel) field.  You might even have an option to tell SAS/Access to use the LABEL of the variable as then name to use in the remote database.

It would also be nice if there was a method for user to specify the mapping similar to the DBTYPE dataset option.

[alecwh22]

I have raised this elsewhere and just want to show my support for someone who has posted it earlier, this is a great idea that will go some way to solving SAS's issues with user friendliness

[Malarkey]

I'd also like to point out that this goes for database names, too.  I have to read tables in two databases that have names that are longer than 32 characters.  For now, I'll have to use SSMS to copy the tables in question to a temp area and then use SAS to get at them there.  Yuck!

[GTickner]

I could not agree more; my first vote for a SAS enhancement would be the ability to support database, table, and variable names longer than 32 characters. This seems a critical capability for a data integration engine.

[Nigel_Pain]

I'd like to add my support for this too.

The problem I have here is that I am supporting SAS users who wish to connect to databases over which neither they nor I have any control. SQL pass-through is OK but over a number of years we have developed a strategy of configuring libraries in the metadata to connect to specific databases. Users can then use the “Update Library Metadata” task in Enterprise Guide to ensure that table metadata is kept up-to-date. However, if there are variable names > 32 it causes an exception (see below) and the update fails. The only way to stop this happening appears to be to set the VALIDVARNAME option to from ANY to V7 temporarily whilst updating the metadata. However, although it doesn't throw an exception it does simply ignore any table with variable names > 32.

I have some sympathy for the view that 32 characters should be plenty, however we are in a situation where many databases do support longer names, and so SAS simply needs to retain some compatibility with that.

Here's an excerpt of a log with the exception:

18         
19         /* show report of changes that *would* be made */
20         proc metalib;
21           omr (library="DX_SPBT");
22           update_rule=(delete);
23           report;
24           noexec;
25         run;

ERROR:  An exception has been encountered.
Please contact technical support and provide them with the following traceback information:
 
The SAS task name is [METALIB (2)]
ERROR:  Read Access Violation METALIB (2)
Exception occurred at (00C0A288)
Task Traceback
Address   Frame     (DBGHELP API Version 4.0 rev 5)
0000000000C0A288  00000000090EA2A0  tkmk:tkBoot+0x9268
000000000226478A  00000000090EA2A8  sashost:Main+0x1B0CA
0000000008AEB8AF  00000000090EA338  sasmetas:tkvercn1+0x2A86F
0000000008AD2D5E  00000000090ECBE0  sasmetas:tkvercn1+0x11D1E
0000000008ACF119  00000000090EF3C0  sasmetas:tkvercn1+0xE0D9
0000000008AC2E6D  00000000090EFCB0  sasmetas:tkvercn1+0x1E2D
0000000002258AE7  00000000090EFF20  sashost:Main+0xF427
000000000225D10D  00000000090EFF50  sashost:Main+0x13A4D
00000000775259CD  00000000090EFF58  kernel32:BaseThreadInitThunk+0xD
000000007765A2E1  00000000090EFF88  ntdll:RtlUserThreadStart+0x21

[GTickner]

I work for a large public enterprise with a diverse data library and strict
naming standards, forcing a growing number of table and field names to
exceed 32 characters. The SAS limit, the fact that tables with longer names
disappear from Explorer, and the awkwardness of pass-through queries are
barriers against the wider adoption of SAS in our company.

##- Please type your reply above this line. Simple formatting, no
attachments. -##


______________________________________________
Glen Tickner
Phone: 780-498-7537
Cell: 780-668-1152
Fax: 780-498-7872
Workers' Compensation Board - Alberta

[BobW_HBF_Oz]

Encountered this for the first time today. 

Luckily, my first instinct was to go to SQLPassthru. 

But this is a frustration in 2016 to find this limitation.