How to remove a string from the beginning of the name of all variables

[malena]

I am importing an excel file and all the variable names start with either B_ or S_

I will like to remove that from all. Is there a quick way to do that? we are talking about over 200 variables. I am attaching a test of 10 observations and a few variables. 

[Reeza]

%let dsn_in=TEST;

data _null_;
set sashelp.vcolumn end=eof;
where libname = 'WORK' and memname = "&dsn_in";

 if _n_=1 then
    call execute ("proc datasets lib=WORK nodetails nolist; modify &dsn_in; rename ");
 
 *create new variable name here;
 *in this example just renaming it to VAR001 - VAR###;
 *variable name is 'name';
 new_name=substr(name, 3);
 
 *pass new and old name to proc datasets;
 call execute (name);
 call execute ('=');
 call execute (new_name);
 
 *if last record then quit;
 If eof then
    call execute (';run;quit;');
    
    
run;

New_name may need to be 2 or 3 in the substr...not sure. Try it and see if that works for you.

EDIT: Small typo in the code (dsn versus dsn_in)

---

@malena wrote:

I am importing an excel file and all the variable names start with either B_ or S_

I will like to remove that from all. Is there a quick way to do that? we are talking about over 200 variables. I am attaching a test of 10 observations and a few variables. 

---

[ballardw]

Here is an example that uses a SAS data set you should have available to create a new data set with some similarly named variables to yours that examines the data set properties, selects the variables to rename into a data set and uses that data set to rename the variables in the source data set.

data namejunk;
   set sashelp.class;
   rename name=B_name sex=S_sex age=b_age weight=b_weight;
run;

proc sql;
   create table renames as
   select name as oldname, scan(name,2,'_') as newname
   from dictionary.columns
   where libname='WORK' and memname='NAMEJUNK'
       and upcase(substr(name,1,2) in ('B_' 'S_')
   ;
quit;

data _null_;
  set renames end=lastone;
  if _n_=1 then do;
    call execute("proc datasets library=work nodetails nolist; modify namejunk; rename ");
  end;
  call execute (oldname||'='||newname);
  if lastone then call execute("; run;quit;");
run;

The first data step creates the new data set with similar named variables and demonstrates basic RENAME functionality.

The Proc SQL step uses SAS metadata to examine the data set in the library (Libname) and data set (Memname) of interest. Note that the Libname and Memname are stored in upper case and should be used as such in the code. The second part of the Where clause only keeps the names of the variables that start with B_ or S_.Variables may have lower case letters so the UPCASE function is used to standardize the values to all the same for comparison with the list of values to use.

The data _null_ step uses the data set created in Proc SQL to write statements to the Proc Datasets which can modify variable and data set properties in place. The Log will show the statements generated with + preceding the statement. Proc Data sets uses run group and interactive processing and is one of the procedures that wants a Quit to end the procedure.

Note: Providing SAS data set, data step code that generates a data set or Proc Contents output would be better in this case than an Excel file.