topic Re: Truncation of DBMS column names in SAS Programming

Truncation of DBMS column names

LOLO — Thu, 22 Feb 2018 21:33:55 GMT

I know this is an issue that comes up over and over and I've been reading posts about it all day trying to figure this out. I inherited a program that used to use an SQL pass through method to load data. This is no longer available to me and so now we are connecting to the SQL server more directly but it is causing a problem with the truncation of the column names. There are many tables and many variables and sometimes there will be several variables that all exceed 32 characters but that have the same 32 characters to begin with.

example:

var1=abcdefghijklmnopqrstuvwxyzaaaaaabb

var2=abcdefghijklmnopqrstuvwxyzaaaaaacc

var3=abcdefghijklmnopqrstuvwxyzaaaaaadd

So what happens right now is that var1 gets truncated to abcdefghijklmnopqrstuvwxyzaaaaaa and var2 and var3 are dropped.

What I want:

var1=abcdefghijklmnopqrstuvwxyzaaaaa1

var2=abcdefghijklmnopqrstuvwxyzaaaaa2

var3=abcdefghijklmnopqrstuvwxyzaaaaa3

My simplified code looks something like this:

Libname test odbc dsn=dataset schema=xx preserve_col_names=yes;

Proc sql ;

Create table xxx select

x.*

from yyy;

quit;

Does anyone know of any option that tells SAS to go ahead and truncate but add numeric suffixes to variables with the same first 32 characters??

THANK YOU!!

Re: Truncation of DBMS column names

foobarbaz — Fri, 23 Feb 2018 01:59:34 GMT

Hi,

I don't have a db to test on at the moment but on your libname statement you can add the following two options;

preserve_col_names=yes
perserve_tab_names=yes

There is also a SAS option for exceeding the 32 character restriction.

options validmemname=EXTEND;

Passthrough SQL lets you reference the DB column names and I believe FedSQL and DS2 also accepts DB names however I don't think I've had an opportunity to test it.

Hope that helps.

Re: Truncation of DBMS column names

ChrisNZ — Fri, 23 Feb 2018 01:59:41 GMT

Sadly this is an old issue and SAS seem to be sitting on their hands regarding SQL server names. See here.

The full variable name is normally stored as the variable label, but in your case some variables are dropped.

I can't think of a good reason why some variables are dropped when the 32 first characters of the name are identical.

The renaming logic (with suffix numbers) you are suggesting is much more sensible.

This is plainly an oversight, like "no one will ever make the 32 first characters of variables identical" to paraphrase an infamous line.

Are you sure you can't submit pass-through code? What happens when you run:

libname TEST odbc dsn=dataset schema=xx preserve_col_names=yes;

proc sql ;

connect using TEST;

select * from connection to TEST ( select abcdefghijklmnopqrstuvwxyzaaaaaabb as VAR1 from YYY);

quit;

If no pass-through code is possible, the only solution I can think of, since DBAs will probably not change the column names, is that a SQL server view is created for you with new names.

Re: Truncation of DBMS column names

Tom — Fri, 23 Feb 2018 03:35:10 GMT

SAS does not make this easy.

Are you sure that the passthru method is no longer available to you?

If you have a libref defined to connect to the database you can use it with PROC SQL.

libname xdb .... ;
proc sql ;
connect using xdb ;
create table work.sasds as
select
  var1 label='INeverLearnedHowToEditMyPapersInEnglishClass'
 ,var2 label='INeverLearnedHowToEditMyPapersInJournalismClass'
 ,var3 label='INeverLearnedHowToEditMyPapersInComputerScienceClass'
from connection to xdb (
select
   INeverLearnedHowToEditMyPapersInEnglishClass var1
  ,INeverLearnedHowToEditMyPapersInJournalismClass var2
  ,INeverLearnedHowToEditMyPapersInComputerScienceClass var3
from sometable
);
quit;

Re: Truncation of DBMS column names

LOLO — Fri, 23 Feb 2018 16:30:52 GMT

Thank you so much to all of you! A combination of all of your responses got me what I needed. it is so crazy that this is such a problem. smh