this is my code
data want; set have;
do i=9 to nvars;
varname = vname(i);
newname = substr(varname,1,length(varname)-5);
rename varname = newname;
end;
drop i varname newname;
the error:
NOTE: Variable nvars is uninitialized.
WARNING: The variable varname in the DROP, KEEP, or RENAME list has never been referenced.
ERROR: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY expression is missing, zero, or invalid.
Note: the variables names from the 9th until the last one end with '_2017'. I want to remove that. There are 139 variables in total. 131 variables ending with '_2017'
What am I doing wrong?
I think you can use a different approach here. The methodology used here is incorrect - rename names must be hardcoded it doesn't take variable values.
proc sql noprint;
select catx("=", name, substr(name, 1, length(name)-5))
into :rename_list
separated by " "
from sashelp.vcolumn
where libname='WORK'
and memname='HAVE'
and upper(trim(name)) like '%_2017';
quit;
%put &rename_list;
proc datasets library=work nodetails nolist;
modify have;
rename &rename_list;
run; quit;
proc contents data=have;
run;
@Satori wrote:
this is my code
data want; set have; do i=9 to nvars; varname = vname(i); newname = substr(varname,1,length(varname)-5); rename varname = newname; end; drop i varname newname;
the error:
NOTE: Variable nvars is uninitialized. WARNING: The variable varname in the DROP, KEEP, or RENAME list has never been referenced. ERROR: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY expression is missing, zero, or invalid.
Note: the variables names from the 9th until the last one end with '_2017'. I want to remove that. There are 139 variables in total. 131 variables ending with '_2017'
What am I doing wrong?
I think you can use a different approach here. The methodology used here is incorrect - rename names must be hardcoded it doesn't take variable values.
proc sql noprint;
select catx("=", name, substr(name, 1, length(name)-5))
into :rename_list
separated by " "
from sashelp.vcolumn
where libname='WORK'
and memname='HAVE'
and upper(trim(name)) like '%_2017';
quit;
%put &rename_list;
proc datasets library=work nodetails nolist;
modify have;
rename &rename_list;
run; quit;
proc contents data=have;
run;
@Satori wrote:
this is my code
data want; set have; do i=9 to nvars; varname = vname(i); newname = substr(varname,1,length(varname)-5); rename varname = newname; end; drop i varname newname;
the error:
NOTE: Variable nvars is uninitialized. WARNING: The variable varname in the DROP, KEEP, or RENAME list has never been referenced. ERROR: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY expression is missing, zero, or invalid.
Note: the variables names from the 9th until the last one end with '_2017'. I want to remove that. There are 139 variables in total. 131 variables ending with '_2017'
What am I doing wrong?
I got a few errors:
484 proc sql noprint;
485 select catx("=", name, substr(name, 1, length(name)-5))
486 into :rename_list
487 separated by " "
488 from sashelp.vcolumn
489 where libname='bvd'
490 and memname='brics2017'
491 and upper(trim(name)) like '%_2017';
NOTE: No rows were selected.
492 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
WARNING: Apparent symbolic reference RENAME_LIST not resolved.
493
494
495 %put &rename_list;
&rename_list
496
497
498 proc datasets library=bvd nodetails nolist;
499 modify brics2017;
WARNING: Apparent symbolic reference RENAME_LIST not resolved.
500 rename &rename_list;
_
22
76
NOTE: Enter RUN; to continue or QUIT; to end the procedure.
ERROR 22-322: Expecting a name.
ERROR 76-322: Syntax error, statement will be ignored.
501 run;
NOTE: Statements not processed because of errors noted above.
501 ! quit;
Names are stored in all upper case
489 where libname='bvd'
490 and memname='brics2017'
Make the libname and memname upper cased and it should work.
Are you sure it isn't easier to re-read the data?
If the file started as any form of text I might ask why you have characters you don't want.
You have several issues.
First, nowhere do you define NVARS and apparently it is not a variable in your data set.
Vname returns the name of the variable if you have a an array reference like Vname(array(i)) . If you use Vname(somevariable) it will return the literal text "somevariable" if that is the name of a variable in the set.
So your Vname does not have any reference to anything related to variables to return those names.
The Rename statement in a data step will not allow use of expressions. So Rename varname=newname would actually be renaming the variable Varname that you just created to newname.
You can use information to build a control data set to create rename statements, preferably for Proc Datasets that is intended to do things like change properties of datasets including variable names, formats, labels and informats.
A small working example:
/* create something to work with*/ data work.have; input a b_2017 c_2017 $; datalines; 1 2 A 4 5 B ; run; proc sql; create table rename as select name, tranwrd(name,'_2017','') as newname from dictionary.columns where libname='WORK' and memname='HAVE' and index(name,'_2017')>0 ; quit; data _null_; set rename end=last; if _n_=1 then call execute('proc datasets library=work nodetails nolist; modify have; rename'); call execute (name||'='||newname); if last then call execute('; quit;'); run;
What this does: The Proc sql uses the metadata maintained by SAS to search in the data set indicated in the Libname and Memname (data set) that have variable names that have the text _2017 in the name. The Library and Memname values are stored in the metadata in upper case so you want to use that in the where clause. Caution: I am not searching for the "last 5 characters" just the presence of the string '_2017'. If you have that as valid text as part of the variable names in other positions I would recommend going back and rereading the data from start. The SQL also creates a new variable with the modified name removing _2017. Extreme caution: There is a chance, depending on YOUR data that removing that next from the name might result in the name of another variable that already exists in your data set. Have you checked for that? Example your data set as variable Measure and Measure_2017. If you attempt to rename Measure_2017 as Measure you will not get it as that variable already exists.
The data _null_ step uses the Call execute function to place statements in to the execution buffer. It starts with boilerplate to use proc datasets with your library , set some options to reduce output, and then select the data set to modify and then the instruction rename. the next call execute creates the rename instructions. The data set option END creates a temporary variable Last that is true when the observation the data step is processing is the last one. So we can provide the code to end the Proc data sets call.
For my example the log shows:
NOTE: CALL EXECUTE generated line. 1 + proc datasets library=work nodetails nolist; modify have; NOTE: Writing HTML Body file: sashtml1.htm 1 + rename 2 + b_2017 =b 3 + c_2017 =c 4 + ; NOTE: Renaming variable b_2017 to b. NOTE: Renaming variable c_2017 to c. 4 + quit;
You should be able to compare those lines after the + with the Call execute statements.
Note that Proc Datasets is one of a few procedures that does "run group processing" and could have multiple Run statements in one procedure call. This procedure uses QUIT; to end the procedure.
I got no errors, but something is not working:
484 proc sql;
485 create table rename as
486 select name, tranwrd(name,'_2017','') as newname
487 from dictionary.columns
488 where libname='bvd' and memname='brics2017'
489 and index(name,'_2017')>0
490 ;
NOTE: Table WORK.RENAME created, with 0 rows and 2 columns.
491 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
492
493 data _null_;
494 set rename end=last;
495 if _n_=1 then call execute('proc datasets library=bvd nodetails nolist; modify brics2017; rename');
496 call execute (name||'='||newname);
497 if last then call execute('; quit;');
498 run;
488 where libname='bvd' and memname='brics2017' 489 and index(name,'_2017')>0 490 ; NOTE: Table WORK.RENAME created, with 0 rows and 2 columns.
LIBNAME is always upper case, it can't be lower case 'bvd'. Similarly, MEMNAME is always upper case, it can't be lower case 'brics2017'. NAME can be mixed case, you would need to check how it exists in the dictionary table (please run PROC CONTENTS to see the actual case of your variable names).
@Satori wrote:
I got no errors, but something is not working:
484 proc sql; 485 create table rename as 486 select name, tranwrd(name,'_2017','') as newname 487 from dictionary.columns 488 where libname='bvd' and memname='brics2017' 489 and index(name,'_2017')>0 490 ; NOTE: Table WORK.RENAME created, with 0 rows and 2 columns.
And from my original post:
The Library and Memname values are stored in the metadata in upper case so you want to use that in the where clause.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.