topic Re: Restrict sas dataset column name to max 30 characters in SAS Programming

Restrict sas dataset column name to max 30 characters

jayakumarmm — Fri, 02 Sep 2016 19:22:42 GMT

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;

Re: Restrict sas dataset column name to max 30 characters

PGStats — Fri, 02 Sep 2016 22:29:24 GMT

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;

Re: Restrict sas dataset column name to max 30 characters

Shmuel — Sat, 03 Sep 2016 00:24:20 GMT

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 ?

Re: Restrict sas dataset column name to max 30 characters

PGStats — Sat, 03 Sep 2016 01:27:58 GMT

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

Re: Restrict sas dataset column name to max 30 characters

error_prone — Sat, 03 Sep 2016 20:36:19 GMT

@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.

Re: Restrict sas dataset column name to max 30 characters

Shmuel — Sat, 03 Sep 2016 21:15:12 GMT

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)) ;

Re: Restrict sas dataset column name to max 30 characters

jayakumarmm — Sun, 04 Sep 2016 00:16:51 GMT

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.

Re: Restrict sas dataset column name to max 30 characters

Shmuel — Sun, 04 Sep 2016 04:53:49 GMT

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