02-22-2018 04:33 PM
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.
So what happens right now is that var1 gets truncated to abcdefghijklmnopqrstuvwxyzaaaaaa and var2 and var3 are dropped.
What I want:
My simplified code looks something like this:
Libname test odbc dsn=dataset schema=xx preserve_col_names=yes;
Proc sql ;
Create table xxx select
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??
02-22-2018 08:59 PM
I don't have a db to test on at the moment but on your libname statement you can add the following two options;
There is also a SAS option for exceeding the 32 character restriction.
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.
02-22-2018 08:59 PM
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);
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.
02-22-2018 10:35 PM
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;