An Idea Exchange for SAS software and services

Comments
by Trusted Advisor
on ‎03-13-2015 10:29 AM

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.

by Super User
on ‎03-15-2015 04:27 AM

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.

by Valued Guide
on ‎04-17-2015 01:28 PM

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

by Super User
‎03-29-2016 10:06 PM - edited ‎03-29-2016 10:07 PM

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.

 

by Occasional Contributor alecwh22
on ‎06-22-2016 04:04 AM
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
by Contributor Malarkey
on ‎06-28-2016 10:49 AM

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!

by Contributor GTickner
on ‎08-16-2016 04:20 PM

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.

by Occasional Contributor Nigel_Pain
on ‎09-20-2016 09:55 AM

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

 

 

by Contributor GTickner
on ‎09-20-2016 01:28 PM
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
by Occasional Contributor BobW_HBF_Oz
on ‎11-02-2016 01:57 AM

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.

 

Idea Statuses
Top Liked Authors