topic Rename List of Columns in SAS Programming

Rename List of Columns

Lindsey — Thu, 18 Jun 2015 06:51:57 GMT

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,

Re: Rename List of Columns

Steelers_In_DC — Thu, 18 Jun 2015 12:33:15 GMT

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;

Re: Rename List of Columns

Ksharp — Thu, 18 Jun 2015 12:39:34 GMT

Make a map table Like:

VAR1 CARNUM

VAR2 MARK

VAR3 2000-01

Then call execute + proc datasets

Re: Rename List of Columns

ballardw — Thu, 18 Jun 2015 14:50:15 GMT

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.

Re: Rename List of Columns

Tommywhosc — Thu, 18 Jun 2015 16:31:49 GMT

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 "   ;

put @1 " ; quit; ";
stop;
run;

%include "C:\junk\rename.txt"

NOTE: I used standard SAS variable names - added some underscores. (See ballardW's comments).

Re: Rename List of Columns

dcruik — Thu, 18 Jun 2015 20:32:26 GMT

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)

Re: Rename List of Columns

Reeza — Thu, 18 Jun 2015 20:41:41 GMT

Step1 - create mapping table

Step 2 - create rename statement

Step 3 - rename

There's a worked example in this question: