DATA Step, Macro, Functions and more

Rename List of Columns

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Rename List of Columns

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,


Accepted Solutions
Solution
‎06-18-2015 12:31 PM
Contributor
Posts: 20

Re: Rename List of Columns

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).

View solution in original post


All Replies
Valued Guide
Posts: 858

Re: Rename List of Columns

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 Smiley Tonguerefixlist

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;

Super User
Posts: 9,691

Re: Rename List of Columns

Make a map table Like:

VAR1   CARNUM

VAR2   MARK

VAR3   2000-01

Then call execute + proc datasets

Super User
Posts: 10,543

Re: Rename List of Columns

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.

Solution
‎06-18-2015 12:31 PM
Contributor
Posts: 20

Re: Rename List of Columns

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).

Frequent Contributor
Posts: 130

Re: Rename List of Columns

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)

Super User
Posts: 17,928

Re: Rename List of Columns

Step1 - create mapping table

Step 2 - create rename statement

Step 3 - rename

There's a worked example in this question:

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 422 views
  • 8 likes
  • 7 in conversation