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)
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.