BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
promo_at_work
Obsidian | Level 7

Hi,

 

I'm encountering a problem due to the 32 character restriction. I'm trying to bring in part of a file from a SQL source system that has some long variable names.

 

In the example, there are 3 in particular which bring about this issue.

 

 

proc contents data=source.source_data;
run;

For which I get the following output:

 

Alphabetic List of Variables and Attributes
#VariableTypeLenFormatInformatLabel
1MY_VERY_LONG_VARIABLE_NAME_HERE_Char5$5.00$5.00MY_VERY_LONG_VARIABLE_NAME_HERE_PART1
2MY_VERY_LONG_VARIABLE_NAME_HERE_Char5$5.00$5.00MY_VERY_LONG_VARIABLE_NAME_HERE_PART2
3MY_VERY_LONG_VARIABLE_NAME_HERE_Char5$5.00$5.00MY_VERY_LONG_VARIABLE_NAME_HERE_PART3

 

However, if I ran the following:

 

data my_data;
set source.source_data (obs=1000);
run;

proc contents data=my_data;
run;

I'd get this output:

Alphabetic List of Variables and Attributes
#VariableTypeLenFormatInformatLabel
1MY_VERY_LONG_VARIABLE_NAME_HERE_Char5$5.00$5.00 

 

It seems like SAS only took in the first of these and didn't bring in the others because they had the same name after truncation to 32 character limit.

 

Is there anyway to alter the variable names or some other step so I can bring them all in? Something like:

MY_VERY_LONG_VARIABLE_NAME_HERE1

MY_VERY_LONG_VARIABLE_NAME_HERE2

MY_VERY_LONG_VARIABLE_NAME_HERE3

1 ACCEPTED SOLUTION

Accepted Solutions
promo_at_work
Obsidian | Level 7

Hi @Reeza

 

Managed to get it working with validvarname option in SQL passthrough. Thanks for suggesting that.

 

My code:

 

proc sql inobs=10;
connect to db2 (database=BIGSQL user=&user password=&password validvarname=v7);
create table my_data( as
select *
from connection to db2 (
     select *
from source.source_data); 
disconnect from db2;
quit; 

 

@PGStats@HB: Thanks to both of you for your input. Looks like it's a foregone conclusion that nothing can be done about the variable names unless the change comes from SAS or Data Source (which I don't have direct access to). At least now I have the variables I was trying to bring in and have labels to keep track of them.

View solution in original post

12 REPLIES 12
HB
Barite | Level 11 HB
Barite | Level 11

To be honest, I wouldn't fight it in SAS.  I would change the variable name in the source file. 

PGStats
Opal | Level 21

Did you check the log? I can't believe that SAS did this without some warning.

PG
Reeza
Super User

The usual workaround is to use SQL Pass Thru and rename in the pass thru query or to create a view in the database with shorter names.

promo_at_work
Obsidian | Level 7

Hi @Reeza

 

I tried following SQL passthru

 

proc sql inobs=10;
connect to db2 (database=BIGSQL user=&user password=&password);
create table my_data( as
select *
from connection to db2 (
     select
     MY_VERY_LONG_VARIABLE_NAME_HERE_PART1 as MY_VERY_LONG_VARIABLE_NAME_HERE1
     MY_VERY_LONG_VARIABLE_NAME_HERE_PART2 as MY_VERY_LONG_VARIABLE_NAME_HERE2
     MY_VERY_LONG_VARIABLE_NAME_HERE_PART3 as MY_VERY_LONG_VARIABLE_NAME_HERE3
     from source.source_data); 
disconnect from db2;
quit;  

But would get error like

ERROR 65-58: Name MY_VERY_LONG_VARIABLE_NAME_HERE_PART1 is too long for a SAS name in this context.
Reeza
Super User

Try shorter names like TEST1 to see if it works.

 

promo_at_work
Obsidian | Level 7
Hi @Reeza
Tried it out but still getting same error
Reeza
Super User

Can you post your full code and the log as well? That's worked any other time I've encountered this issue.....

 

Reeza
Super User

Can you post your full code and the log as well? That's worked any other time I've encountered this issue.....

 

Reeza
Super User

Can you post your full code and the log as well? That's worked any other time I've encountered this issue.....


@promo_at_work wrote:
Hi @Reeza
Tried it out but still getting same error

 

promo_at_work
Obsidian | Level 7

Hi @Reeza

 

Managed to get it working with validvarname option in SQL passthrough. Thanks for suggesting that.

 

My code:

 

proc sql inobs=10;
connect to db2 (database=BIGSQL user=&user password=&password validvarname=v7);
create table my_data( as
select *
from connection to db2 (
     select *
from source.source_data); 
disconnect from db2;
quit; 

 

@PGStats@HB: Thanks to both of you for your input. Looks like it's a foregone conclusion that nothing can be done about the variable names unless the change comes from SAS or Data Source (which I don't have direct access to). At least now I have the variables I was trying to bring in and have labels to keep track of them.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 12 replies
  • 2446 views
  • 2 likes
  • 4 in conversation