Restrict sas dataset column name to max 30 characters

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

Restrict sas dataset column name to max 30 characters

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;

 

 


Accepted Solutions
Solution
‎09-04-2016 08:39 AM
Respected Advisor
Posts: 4,649

Re: Restrict sas dataset column name to max 30 characters

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

View solution in original post


All Replies
Solution
‎09-04-2016 08:39 AM
Respected Advisor
Posts: 4,649

Re: Restrict sas dataset column name to max 30 characters

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;
PG
Trusted Advisor
Posts: 1,381

Re: Restrict sas dataset column name to max 30 characters

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 ?

Respected Advisor
Posts: 4,649

Re: Restrict sas dataset column name to max 30 characters

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

PG
Contributor
Posts: 33

Re: Restrict sas dataset column name to max 30 characters


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.

 

Trusted Advisor
Posts: 1,381

Re: Restrict sas dataset column name to max 30 characters

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

 

 

Attachment
Contributor
Posts: 55

Re: Restrict sas dataset column name to max 30 characters

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.

Trusted Advisor
Posts: 1,381

Re: Restrict sas dataset column name to max 30 characters

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 526 views
  • 1 like
  • 4 in conversation