Restrict sas dataset column name to max 30 characters

[jayakumarmm]

I am creating a sas dataset using excel table I am following this below approach since I am able to import .xlsm extension excel files using this approach only. This works fine but I want to reduce some manual for renaming column names before uploading into teradata. i.e. SAS dataset max column name length is 32 but teradata is 30 characters.

Please let me know if there are any ways to restrict all sas dataset columns max length to 30 characters. There are more than 50 columns so I dont want go one by one column formatting.

libname xlsFile XLSX /path/filename;
options validvarname=v7;

PROC SQL;
    create table work.sample as 
	(select * from xlsFile.sheet1);
 quit;

[PGStats · Accepted solution]

Here is an example:

proc sql;
/* Limit name length to 5 chars */
select catx(" as ",name,substr(name,1,5)) 
into :cols separated by ","
from dictionary.columns
where libname="SASHELP" and memname="CLASS"; /* use uppercase */
create table myClass as
select &cols.
from sashelp.class;
describe table myClass;
quit;

[Shmuel]

Truncating variable names may lead to duplicate names ?!

    having variables names, for example:  variable1 , variable2

    then truncatin them by substring(1,5) will result in: varia , varia 

    that means duplicate names.

At least need check and give warning.

Am I wrong ?

[PGStats]

Proc SQL will give you a warning if you try to create a duplicate name.

[error_prone]

---

@PGStats wrote:

Proc SQL will give you a warning if you try to create a duplicate name.

---

Consequence: Don't rename columns automatically, always use a mapping table.

[Shmuel]

I have built a macro to rename any dataset variable names

that can be addapted to any desired transformation - (line 15 in attached progran, define a_line contents).

For debuging I have renamed variables to v1 ... vn, but can be changed to :

      a_line = compbl( var || '=substr(' || var || ',1,30);') ;  

or in order to eliminate posibility of duplicate names

      a_line = compbl(var || '=substr(' || var || ',1,27)' || left(i)) ; 

[jayakumarmm]

Thank you for your great solution but I am getting below error

+_COLUMN1 =substr(_COLUMN1,1,27)1
                                    _
                                   22
                                   76
ERROR 22-322: Syntax error, expecting one of the following: a name, ;.

ERROR 76-322: Syntax error, statement will be ignored.

[Shmuel]

I need your code or full log to understand what caused the error