BookmarkSubscribeRSS Feed
JonathanWarrick
Calcite | Level 5
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.
2 REPLIES 2
CurtisMack
Fluorite | Level 6
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.
Reeza
Super User
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***

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1158 views
  • 0 likes
  • 3 in conversation