Hi,
I have a dataset called TEMP and has column names as VAR1, VAR2, VAR3, ....., VAR300.
I would like to rename VAR1 to CARNUM, VAR2 to MARK, AND VAR3 ~ VAR300 in Y4-M2 format.
VAR3 = 2000-01, VAR4 = 2000-02, VAR5 = 2000-03, VAR6 = 2000-04, VAR7 = 2000-05, VAR8 = 2000-06, .... VAR15 = 2001-01, VAR16 = 2001-12, and ect.
Is there a way that I can use a do loop to rename VAR3 ~ VAR 300?
Many thanks,
You can have a macro write out all the PROC DATASETS....RENAME... text to an external file, then %include it....
data temp; *create test data set ;
array v {*} var1-var300;
do x = 1 to 3;
do i = 1 to dim(v);
v{i}=ranuni(0);
end; output; end;
run;
/* Macro to write the variable name-pairs to an external file. */
%macro r;
%let x = 2;
%do yr4 = 2000 %to 2025;
%do mth = 1 %to 9;
%let x = %eval(&x+1);
%let yrmo = _&yr4._0&mth;
%let var = VAR&x;
%if %eval(&x <= 300) %then %do;
put @1 " &var = &yrmo "; %end;
%end;
%do mth = 10 %to 12;
%let x = %eval(&x+1);
%let yrmo = _&yr4._&mth;
%let var = VAR&x;
%if %eval(&x <= 300) %then %do;
put @1 " &var = &yrmo" ; %end;
%end;
%end;
%mend r;
data _null_; *put PROC DATASETS..w/RENAME statement to external file ;
set temp;
file "C:\junk\rename.txt";
if _n_ = 1 then
put @1 "Proc DATASETS lib=work;" /
@1 "Modify TEMP; " /
@1 "RENAME " ;
%r
put @1 " ; quit; ";
stop;
run;
%include "C:\junk\rename.txt"
NOTE: I used standard SAS variable names - added some underscores. (See ballardW's comments).
Here's a template I've been using for a while, you'll have to play around with the variable names that you want changed.
proc sql noprint;
select cats(name,'=','OLD_',name)
into :prefixlist
separated by ' '
from dictionary.columns
where libname = 'MDJ' and lowcase(memname) = 'old_lgd_s1_base';
data mdj.old_lgd_s1_base(rename=(&prefixlist));
set mdj.old_lgd_s1_base;
run;
Make a map table Like:
VAR1 CARNUM
VAR2 MARK
VAR3 2000-01
Then call execute + proc datasets
If you actually want the variable name to start with a digit and have a - (dash) not at underscore you will have to use the '2002-01'n form to use non-standard variable names.
You can have a macro write out all the PROC DATASETS....RENAME... text to an external file, then %include it....
data temp; *create test data set ;
array v {*} var1-var300;
do x = 1 to 3;
do i = 1 to dim(v);
v{i}=ranuni(0);
end; output; end;
run;
/* Macro to write the variable name-pairs to an external file. */
%macro r;
%let x = 2;
%do yr4 = 2000 %to 2025;
%do mth = 1 %to 9;
%let x = %eval(&x+1);
%let yrmo = _&yr4._0&mth;
%let var = VAR&x;
%if %eval(&x <= 300) %then %do;
put @1 " &var = &yrmo "; %end;
%end;
%do mth = 10 %to 12;
%let x = %eval(&x+1);
%let yrmo = _&yr4._&mth;
%let var = VAR&x;
%if %eval(&x <= 300) %then %do;
put @1 " &var = &yrmo" ; %end;
%end;
%end;
%mend r;
data _null_; *put PROC DATASETS..w/RENAME statement to external file ;
set temp;
file "C:\junk\rename.txt";
if _n_ = 1 then
put @1 "Proc DATASETS lib=work;" /
@1 "Modify TEMP; " /
@1 "RENAME " ;
%r
put @1 " ; quit; ";
stop;
run;
%include "C:\junk\rename.txt"
NOTE: I used standard SAS variable names - added some underscores. (See ballardW's comments).
I haven't tested it yet, but after the data step to rename the first two variables, you could try something like the following rename macro. Hope this helps!
data old;
set temp (rename=(VAR1=CARNUM VAR2=MARK));
run;
%macro rename(n);
%let year=2000;
%let month=01;
%do i=3 %to &n;
data new;
set old (rename=(VAR&i="&year-&month"n));
run;
%if &month<09 %then %do;
%let month=0%eval(&month+1); %end;
%else %if &month<12 %then %do;
%let month=%eval(&month+1); %end;
%else %do;
%let month=01;
%let year=%eval(&year+1); %end;
%end;
%mend;
%rename(300)
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.