topic rename all variables by removing last 5 characters in SAS Programming

rename all variables by removing last 5 characters

Satori — Wed, 12 Apr 2023 20:46:55 GMT

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?

Re: rename all variables by removing last 5 characters

Reeza — Wed, 12 Apr 2023 21:45:34 GMT

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?

Re: rename all variables by removing last 5 characters

ballardw — Wed, 12 Apr 2023 21:51:31 GMT

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.

Re: rename all variables by removing last 5 characters

Satori — Thu, 13 Apr 2023 13:39:39 GMT

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;

Re: rename all variables by removing last 5 characters

Satori — Thu, 13 Apr 2023 13:41:59 GMT

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;

Re: rename all variables by removing last 5 characters

PaigeMiller — Thu, 13 Apr 2023 13:53:57 GMT

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

Re: rename all variables by removing last 5 characters

ballardw — Thu, 13 Apr 2023 14:31:16 GMT

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

Re: rename all variables by removing last 5 characters

Reeza — Thu, 13 Apr 2023 16:20:55 GMT

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.