How do I rename all variables by variable creation order quickly in one step?

[Emma22]

Hello @Sas programing SAS coding experts: 

Can someone help me to write a simple do loop to rename my variables by the variable creation order as shown in the table below? 

I want to rename my old variables to what shown under the New_variable column below. I have 3 numeric variable and the rest of them are all character variables. 

I can rename the variables by manually typing them as shown below. However, when I have 300 variables, it became a very cumbersome task.  Any suggestions? 

data new (rename =(ID =VAR1 FIRST_NAME=VAR2 MIDDLE_NAME=VAR3 .....)) ;

set old;

run;

Table1.

Variables in Creation Order	old_Variable	Type	New_variable
1	ID	Num	VAR1
2	FIRST_NAME	Char	VAR2
3	MIDDLE_NAME	Char	VAR3
4	LAST_NAME	Char	VAR4
5	CATEGORY	Char	VAR5
6	DATE	Num	VAR6
7	INQUIRY_ID	Num	VAR7
8	MEMBERSHIP_TYPE	Char	VAR8

[PeterClemmensen]

Try this. 

I just made up some data. However, the size of the data does not matter when we use Proc Datasets because it modifies the descriptor portion of the data only. Not the actual data.

data rn;
input VariablesOrder old_Variable :$20. Type $ New_variable $;
datalines;
1 ID              Num  VAR1 
2 FIRST_NAME      Char VAR2 
3 MIDDLE_NAME     Char VAR3 
4 LAST_NAME       Char VAR4 
5 CATEGORY        Char VAR5 
6 DATE            Num  VAR6 
7 INQUIRY_ID      Num  VAR7 
8 MEMBERSHIP_TYPE Char VAR8 
;

data have;
input ID FIRST_NAME $ MIDDLE_NAME $ LAST_NAME $ CATEGORY $ DATE INQUIRY_ID MEMBERSHIP_TYPE $;
datalines;
1 a b c d 1 1 a
;

data _null_;
   set rn end = z;
 
   if _n_ = 1 then 
      call execute('proc datasets lib=work nolist; modify have;');
      call execute(compbl(cat('rename ', old_Variable, '=', New_variable, ';')));

   if z then 
      call execute('quit;');
run;

Result:

VAR1	VAR2	VAR3	VAR4	VAR5	VAR6	VAR7	VAR8
1	a	b	c	d	1	1	a

[Emma22]

Hello Peter:

Thanks a lot for your help. Can you help me to understand what I should do if my data does not have a separate variable list rn as what you shared? My sample data is shown below.  Is there an additonal step that I need to replace the "set=rn end=z;"?

What does end=z do or mean here? Thank you very much again!  

data sample22;

INPUT

ID FIRST_NAME $ MIDDLE_NAME $ LAST_NAME $ CATEGORY $

DATE $ INQUIRY_ID $ MEMBERSHIP_TYPE $;

datalines;

1 Abby v lee a 7/3/2021 13141978 Classic

2 bella g smith b 7/6/2021 14609861 Basic

3 charmy h taylor e 7/7/2021 14609876 Premium

4 steve j white q 7/8/2021 14609882 Premium

5 Peter t black c 7/9/2021 14609890 Classic

6 John r green f 7/10/2021 14609891 Basic

7 Grace e johnson e 7/12/2021 14609892 Premium

8 Sam t goodwill f 7/13/2021 14609893 Basic

;

run;

[Reeza]

data class;
set sashelp.class;
run;


proc sql noprint;
select catx("=", name, catt("Var", put(varnum, 8. -l))) 
into :rename_list
separated by " "
from sashelp.vcolumn
where libname='WORK'
and memname='CLASS'
order by varnum;
quit;


%put &rename_list;


proc datasets library=work nodetails nolist;
modify class;
rename &rename_list;
run; quit;

proc print data=class noobs;
run;

Slightly different approach.

[PeterClemmensen · Accepted solution]

Same principle, but using the SASHELP.VCOLUMN metadata view and your new posted sample data.

data sample22;
INPUT ID FIRST_NAME $ MIDDLE_NAME $ LAST_NAME $ CATEGORY $
      DATE $ INQUIRY_ID $ MEMBERSHIP_TYPE $;
datalines;
1 Abby   v lee      a 7/3/2021  13141978 Classic 
2 bella  g smith    b 7/6/2021  14609861 Basic   
3 charmy h taylor   e 7/7/2021  14609876 Premium 
4 steve  j white    q 7/8/2021  14609882 Premium 
5 Peter  t black    c 7/9/2021  14609890 Classic 
6 John   r green    f 7/10/2021 14609891 Basic   
7 Grace  e johnson  e 7/12/2021 14609892 Premium 
8 Sam    t goodwill f 7/13/2021 14609893 Basic   
;

data _null_;
   set sashelp.vcolumn end = z;
   where libname='WORK' and memname='SAMPLE22';
 
   if _n_ = 1 then 
      call execute('proc datasets lib=work nolist; modify sample22;');

      call execute(compbl(cat('rename ', name, '= Var_', varnum, ';')));

   if z then call execute('quit;');
run;

Result:

Var_1  Var_2   Var_3  Var_4     Var_5  Var_6     Var_7     Var_8
1      Abby    v      lee       a      7/3/2021  13141978  Classic
2      bella   g      smith     b      7/6/2021  14609861  Basic
3      charmy  h      taylor    e      7/7/2021  14609876  Premium
4      steve   j      white     q      7/8/2021  14609882  Premium
5      Peter   t      black     c      7/9/2021  14609890  Classic
6      John    r      green     f      7/10/202  14609891  Basic
7      Grace   e      johnson   e      7/12/202  14609892  Premium
8      Sam     t      goodwill  f      7/13/202  14609893  Basic

[Emma22]

Thanks a lot, Peter! 

This works, but the SAS log returned a warning "NOTE: Variable lr is uninitialized.". 

Can you explain what does lr mean? Thank you again! 

[Reeza]

That's actually a typo, lr should be z. The Z variable tells SAS it's the last record (lr) and that the procedure should be quit at that line.
PROC DATASETS is an interactive proc that requires a quit rather than a RUN.

I suspect Peter usually uses LR for this but used z for some reason and forgot to update it.

[Tom]

You don't really need to generate the QUIT; statement in this case.  You can just hardcode it.

data _null_;
   set sashelp.vcolumn ;
   where libname='WORK' and memname='SAMPLE22';
   if _n_ = 1 then call execute('proc datasets lib=work nolist; modify sample22;rename ');
   call execute(catx('=',nliteral(name),cats('VAR',varnum)));
run;

;run;
quit;

Log:

208  data _null_;
209     set sashelp.vcolumn ;
210     where libname='WORK' and memname='SAMPLE22';
211     if _n_ = 1 then call execute('proc datasets lib=work nolist; modify sample22;rename ');
212     call execute(catx('=',nliteral(name),cats('VAR',varnum)));
213  run;

NOTE: There were 8 observations read from the data set SASHELP.VCOLUMN.
      WHERE (libname='WORK') and (memname='SAMPLE22');
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


NOTE: CALL EXECUTE generated line.
1   + proc datasets lib=work nolist; modify sample22;
1   +                                                rename
2   + ID=VAR1
3   + FIRST_NAME=VAR2
4   + MIDDLE_NAME=VAR3
5   + LAST_NAME=VAR4
6   + CATEGORY=VAR5
7   + DATE=VAR6
8   + INQUIRY_ID=VAR7
9   + MEMBERSHIP_TYPE=VAR8
214
215  ;
NOTE: Renaming variable ID to VAR1.
NOTE: Renaming variable FIRST_NAME to VAR2.
NOTE: Renaming variable MIDDLE_NAME to VAR3.
NOTE: Renaming variable LAST_NAME to VAR4.
NOTE: Renaming variable CATEGORY to VAR5.
NOTE: Renaming variable DATE to VAR6.
NOTE: Renaming variable INQUIRY_ID to VAR7.
NOTE: Renaming variable MEMBERSHIP_TYPE to VAR8.
215!  run;

NOTE: MODIFY was successful for WORK.SAMPLE22.DATA.
216  quit;

NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

But it does make for some strange looking source code.

So probably better to generate the full step with CALL EXECUTE().

[PeterClemmensen]

Yes, typo on my end 🙂

Corrected in code