Help using Base SAS procedures

SQL Table with Variable Name Lenghts > 32 Char

Reply
Occasional Contributor
Posts: 15

SQL Table with Variable Name Lenghts > 32 Char

I'm pulling in a table from a SQL server over which I have no control. One of the field names is greater than 32 characters, so when I try and do anything with it, including changing the name, I get an error telling me "the variable is longer than 32 characters". Is there any way to do convert the variable name without getting this error? I've tried using the rename function in the proc sql command I'm using to connect to the server.
The table reads in fine otherwise and even keeps the variable name as 35 characters long in its full title, but when I try and use it is where I get the problem.

Here's my code:

proc sql exec;
connect to odbc (dsn='xxxxx' user='xxxxx' pwd=xxxxxxxxxx);
create table XYZ
(rename = longvariablenamexxxxxxxxxxxxxxxx = shortvariablename)) as
select * from connection to odbc
(select * from tablename);
quit;

The error pops up even here when I try and use the rename function in that line of code.
Frequent Contributor
Posts: 102

Re: SQL Table with Variable Name Lenghts > 32 Char

I suspect that what you are seeing as a 35 character variable name in a SAS dataset is actually the variable label. The name has actually been automatically renamed to something shorter. One way to see the actual name is In VIEWTABLE. Open the table and go to the menu options 'View' then 'Column Names'. The other way is to use "View Columns" by right clicking on the table in the explorer window.

You could also do the rename inside of the call to ODBC, but I suspect you want to avoid switching from SELECT * to listing all of the variable names.
Super User
Posts: 17,780

Re: SQL Table with Variable Name Lenghts > 32 Char

You CAN do the following, it will give you two columns with that data value though and that can lead to all sorts of trouble. Ideally you should drop the old one with the long name which will be truncated in SAS.


***SQL code***
select *, long_variable_name as short_variable_name
From ...
***SQL code***
Ask a Question
Discussion stats
  • 2 replies
  • 228 views
  • 0 likes
  • 3 in conversation