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 |
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
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
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;
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.
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
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!
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().
Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.