Datastep importing less columns from source table due to variable names over 32 characters

[promo_at_work]

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
#	Variable	Type	Len	Format	Informat	Label
1	MY_VERY_LONG_VARIABLE_NAME_HERE_	Char	5	$5.00	$5.00	MY_VERY_LONG_VARIABLE_NAME_HERE_PART1
2	MY_VERY_LONG_VARIABLE_NAME_HERE_	Char	5	$5.00	$5.00	MY_VERY_LONG_VARIABLE_NAME_HERE_PART2
3	MY_VERY_LONG_VARIABLE_NAME_HERE_	Char	5	$5.00	$5.00	MY_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
#	Variable	Type	Len	Format	Informat	Label
1	MY_VERY_LONG_VARIABLE_NAME_HERE_	Char	5	$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

[HB]

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

[PGStats]

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

[promo_at_work]

Hi @PGStats

No warning whatsoever

[Reeza]

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]

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]

Try shorter names like TEST1 to see if it works.

[promo_at_work]

Hi @Reeza
Tried it out but still getting same error

[Reeza]

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

[Reeza]

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

[Reeza]

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

---

[PGStats]

This problem should be submitted to Technical Support

https://support.sas.com/en/technical-support/contact-sas.html

[promo_at_work · Accepted solution]

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.