Deal with DBMS column names over 32 characters on SAS

[claudiodejesusa]

Hi everybody,

In my company, we manage hadoop datalake and oracle datawarehouse with limit of column name over 128 characters. Also, we have production tables with more than 40 characters on column names..

I want suggestions about how to deal these size of column names on SAS 9.4 and SAS Viya without cutting the name.

Thanks,
Claudio

[Kurt_Bremser]

For use in SAS, column (and table) names MUST be reduced to 32 characters, this is a hard limit in SAS. Create views in the DB's to achieve this.

[SASKiwi]

A search of the SAS Communities will reveal a lot of posts on this topic. This one is probably the most useful: https://communities.sas.com/t5/SAS-Data-Management/Dealing-with-32-character-restrictions/m-p/100033...

Apart from @Kurt_Bremser 's workaround, you can use SQL Passthru to reference and query DBMS tables with names or columns greater than 32 characters. SAS has previously committed to increasing SAS table and column lengths to 128 characters but that was for a SAS release that is no longer happening - 9.5. I have not heard if this enhancement is on the SAS Viya 4 roadmap. If this is a major issue for you I suggest you pass that on to your SAS account manager. The more users who push for this enhancement the more likely it will happen.   

[sbxkoenk]

Hello,

You need to reduce to 32 chars or less.

This is how I do it using PROC FEDSQL :

/* Column name Draft_10_DieLifeForecast_Diameter         */
/* has 33 positions and is residing in an INTELDWD table */
/* INTELDWD is an AZURE SYNAPSE datalake                 */

proc FEDSQL sessref=manuSESSION;
create table CASUSER.test_table as (
  select Draft_10_DieLifeForecast_Diameter as test1
  from INTELDWD.C_Machine_Data_Temp) ;
QUIT;
/* end of program */

Cheers,

Koen

[SASKiwi]

@sbxkoenk - Good to know you can do this in FEQSQL as well as SQL Passthru.