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;
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;
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;
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 ?
Proc SQL will give you a warning if you try to create a duplicate name.
@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.
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)) ;
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.
I need your code or full log to understand what caused the error
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.
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.