BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jayakumarmm
Quartz | Level 8

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

7 REPLIES 7
PGStats
Opal | Level 21

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
Shmuel
Garnet | Level 18

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
Opal | Level 21

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

PG
error_prone
Barite | Level 11

@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
Garnet | Level 18

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
Quartz | Level 8

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
Garnet | Level 18

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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