topic Re: Deal with DBMS column names over 32 characters on SAS in SAS Programming

Deal with DBMS column names over 32 characters on SAS

claudiodejesusa — Fri, 23 Sep 2022 15:36:04 GMT

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

Re: Deal with DBMS column names over 32 characters on SAS

Kurt_Bremser — Fri, 23 Sep 2022 16:23:19 GMT

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.

Re: Deal with DBMS column names over 32 characters on SAS

SASKiwi — Fri, 23 Sep 2022 23:12:13 GMT

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#M1111

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.

Re: Deal with DBMS column names over 32 characters on SAS

sbxkoenk — Fri, 23 Sep 2022 23:56:19 GMT

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

Re: Deal with DBMS column names over 32 characters on SAS

SASKiwi — Sat, 24 Sep 2022 02:10:17 GMT

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