BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

Hi,

I have a data set that has 200 columns, named VAR1, VAR2, ..., VAR200, say. I need to rename all these in a data step, something like this.

%let realname=A B C D E; /*200 different names*/

***Create a list of 200 fake vars VAR1, VAR2, ...., VAR200;

%macro create_200_vars;

     %global fakevars;

     %do i=1 %to 200 %by 1;

          %let fakevars=&fakevars. VAR&i.;

     %end;

     %put &fakevars;

%mend create_200_vars;

data renamed;

     set unnamed; /*Dataset with unnamed vars VAR1, VAR2, ..., VAR200*/

     do i=1 to 200 by 1;

          rename VAR&i.=scan(&fakevars., &i.); ***WRONG HERE, but don't know how to fix it in this datastep;

     end;

run;

Could you please help? Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

Arthur, Maybe you put it in a wrong direction.

OP want var1 -> a , not a -> var1 .

I often do it everyday. Call execute is good .

data have;
  input var1-var5;
  cards;
1 1 1 1 1
2 2 2 2 2
;
run;

data x;
input name : $40.;
cards;
a
b
c
d
e
;
run;
data _null_;
set x end=last;
if _n_ eq 1 then call execute('proc datasets library=work nolist; modify have; rename ');
call execute(cats('var',_n_,'=',name));
if last then call execute(';quit;');
run;


Ksharp

View solution in original post

6 REPLIES 6
Linlin
Lapis Lazuli | Level 10

example(using capital letters in RED 😞

data fake;

   array _fake fake1-fake200;

   do over _fake; _fake=1;end;

data real;

   array _real real1-real200;

   do over _real;_real=2;end;

run;

proc sql noprint;

  select name into :fake separated by ' '

     from sashelp.vcolumn

    where libname='WORK' and memname='FAKE';

   select name into :real separated by ' '

     from sashelp.vcolumn

    where libname='WORK' and memname='REAL';

quit;

%macro test;

data real;

   set real;

   rename

     %do i=1 %to 200;

    %scan(&real,&i)=%scan(&fake,&i)

     %end;;

   run;

%mend;

%test

proc contents data=real;run;

smilingmelbourne
Fluorite | Level 6

I cannot use an array because I don't know which column is sure to be character- /numeric-type. They all are supposed to have corresponding columns of identical types, but because many first observations of a column in a data set may be missing, it is set to character-type.

Should I make a macro for converting a column of all data set to its correct type?

shivas
Pyrite | Level 9

Hi,

Check this link...it will provide you some idea.

https://communities.sas.com/message/123403#123403

Thanks,

Shiva

art297
Opal | Level 21

An alternative approach:

data have;

  input a b c d e;

  cards;

1 1 1 1 1

2 2 2 2 2

;

proc sql noprint;

  select catt(name,"=var",varnum)

    into :renames separtated by " "

      from dictionary.columns

        where libname="WORK" and

              memname="HAVE"

  ;

quit;

data want;

  set have (rename=(&renames.));

run;

Ksharp
Super User

Arthur, Maybe you put it in a wrong direction.

OP want var1 -> a , not a -> var1 .

I often do it everyday. Call execute is good .

data have;
  input var1-var5;
  cards;
1 1 1 1 1
2 2 2 2 2
;
run;

data x;
input name : $40.;
cards;
a
b
c
d
e
;
run;
data _null_;
set x end=last;
if _n_ eq 1 then call execute('proc datasets library=work nolist; modify have; rename ');
call execute(cats('var',_n_,'=',name));
if last then call execute(';quit;');
run;


Ksharp

Tom
Super User Tom
Super User

To rename variables you really want to generate code. So you could use a MACRO.  The code you basically want to generate are the OLD=NEW name pairs.

If your old names will always be VAR1-VARnnn then the macro only needs to receive the list of new names.

%macro rename(newnames);

%local i ;

%do i=1 %to %sysfunc(countw(&newnames));

  var&i = %scan(&newnames,&i)

%end;

%mend rename;

Then you can use the generated pairs in either RENAME statement or a RENAME dataset option.  The RENAME statement could be part of a DATA step or use PROC DATASETS to modify the names in an existing dataset.

data renamed;

  set unnamed;

  rename %rename(A B C D E);

run;


If the old names vary then you might want a macro that takes two lists.

%macro rename(oldnames,newnames);

%local i ;

%do i=1 %to %sysfunc(countw(&oldnames));

  %scan(&oldnames,&i) = %scan(&newnames,&i)

%end;

%mend rename;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 9342 views
  • 8 likes
  • 6 in conversation