topic Re: Datastep importing less columns from source table due to variable names over 32 characters in SAS Procedures

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

promo_at_work — Wed, 15 Aug 2018 19:38:44 GMT

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

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

HB — Wed, 15 Aug 2018 20:15:29 GMT

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

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

PGStats — Wed, 15 Aug 2018 20:20:29 GMT

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

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

Reeza — Wed, 15 Aug 2018 20:24:43 GMT

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.

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

promo_at_work — Wed, 15 Aug 2018 20:26:42 GMT

Hi @PGStats

No warning whatsoever

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

promo_at_work — Wed, 15 Aug 2018 20:31:46 GMT

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.

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

Reeza — Wed, 15 Aug 2018 20:44:48 GMT

Try shorter names like TEST1 to see if it works.

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

promo_at_work — Wed, 15 Aug 2018 20:50:16 GMT

Hi @Reeza
Tried it out but still getting same error

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

Reeza — Wed, 15 Aug 2018 20:51:20 GMT

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

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

Reeza — Wed, 15 Aug 2018 20:51:20 GMT

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

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

Reeza — Wed, 15 Aug 2018 20:51:22 GMT

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

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

PGStats — Wed, 15 Aug 2018 20:51:51 GMT

This problem should be submitted to Technical Support

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

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

promo_at_work — Thu, 16 Aug 2018 14:36:15 GMT

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.