topic Re: Renaming multiple variabes using a macro in SAS Programming

Renaming multiple variabes using a macro

Neff — Mon, 03 May 2021 17:01:31 GMT

I want to rename 276 variables. The old names are nL1-nL276 and the new name to be recoded to is contained in a macro variable (&L)

This is the code I came up with but it only renames nL1 and not the rest of the 275 variables.

%macro rename(oldvarlist, newvarlist);
%let k=1;
%let old = %scan(&oldvarlist, &k);
%let new = %scan(&newvarlist, &k);
%do %while(("&old" NE "") & ("&new" NE ""));

data xx; set xx;
rename &old = &new;
run;
%let k = %eval(&k + 1);
%let old = %scan(&oldvarlist, &k);
%let new = %scan(&newvarlist, &k);
%end;
%mend rename;

%rename(nL1 - nL276, &L);

Re: Renaming multiple variabes using a macro

sbxkoenk — Mon, 03 May 2021 17:10:34 GMT

Hello,

It does not work indeed!

But what you try to do is also very, very inefficient and greedy.

You recreate the data set XX for every variable to be renamed (276 times!).

You should use a PROC DATASETS with a MODIFY statement and then renaming the 276 variables at once.

This way you alter the metadata (variable names) all at once without going through the observations (not even 1 time).

I will produce an example!

Cheers,

Koen

Re: Renaming multiple variabes using a macro

Reeza — Mon, 03 May 2021 17:11:32 GMT

What are the new names? Show examples of your macro variable lists and how you call it. And the log.
Add options mprint; before your code, re-run it and include the log.

Also, use PROC DATASETS to rename instead of data step.

Re: Renaming multiple variabes using a macro

Neff — Mon, 03 May 2021 17:21:17 GMT

An example will be greatly appreciated

Re: Renaming multiple variabes using a macro

sbxkoenk — Mon, 03 May 2021 17:25:10 GMT

Here's an example.

It uses (what I call) 'data-driven code generation' instead of a macro.

I can turn it into a macro but I hope this solution is OK for you.

data oldnew;
input oldvarname $ newvarname $;
cards;
height HHHHH
weight WWWWW
age    AAAAA
;
run;

data test; set sashelp.class; run;

data _NULL_;
if cexist('work.t') then
call execute('PROC CATALOG cat=work.t; delete t.source; run; QUIT;');
run;

filename tt CATALOG 'work.t.t.source';

data _NULL_;
 set oldnew end=last;
 file tt;
if _N_=1 then do;
 PUT 'PROC DATASETS library=WORK NoList;';
 PUT 'modify test;';
 PUT 'rename';
end;
PUT  oldvarname '=' newvarname;
if last then do;
 PUT '; run;';
 PUT 'QUIT;'; 
end;
run;

options source2;
%INCLUDE tt;
/* end of program */

Cheers,

Koen

Re: Renaming multiple variabes using a macro

Reeza — Mon, 03 May 2021 17:25:26 GMT

data _new_list;
 set rename_names end=eof;
 
 
 *start proc datasets;
 if _n_=1 then
    call execute ('proc datasets lib=WORK nodetails nolist; change');
    
 
 *pass new and old name to proc datasets;
 call execute (oldname);
 call execute ('=');
 call execute (new_name);
 
 *if last record then quit;
 If eof then
    call execute (';run;quit;');
run;

Modified from a reverse example. This assumes you have a data set with old_name and new_name.

https://gist.github.com/statgeek/ba360dd1cf748bf6d0da7e2e16aafe66

FYI - if it's just a prefix a RENAME statement works fine.

rename NL1-NL256 = START1-START256;

Re: Renaming multiple variabes using a macro

ballardw — Mon, 03 May 2021 17:34:00 GMT

First thing is that the macro language does not use variable lists, so the parameter value of nL1 - nL276 is one piece of text and you have nothing in you code that actually parses out the "names" for all of the variable names that I think you intend.

As an absolute minimum you need to show what the &L parameter looks like.

This may show part of what is happening:

75   %macro dummy(oldvarlist, newvarlist);
76   %let k=1;
77   %let old = %scan(&oldvarlist, &k);
78   %let new = %scan(&newvarlist, &k);
79   %do %while(("&old" NE "") & ("&new" NE ""));
80   %put Old: &old.   New: &new.;
81   %let k = %eval(&k + 1);
82   %let old = %scan(&oldvarlist, &k);
83   %let new = %scan(&newvarlist, &k);
84   %end;
85   %mend dummy;
86
87   %dummy(A1-A10, B1-B10);
Old: A1   New: B1
Old: A10   New: B10

I "assumed" that you newvarlist was provided in a manner similar to the old var. Note that only the first and last are processed when that happens.

Or if the newvarlist is provided in a different manner:

88   %dummy(A1-A10, B1 B3 B5);
Old: A1   New: B1
Old: A10   New: B3

In any case the the OLD list only ever has 2 values so only 2 are used from the "new" list and quite likely not the correct one.

If your "old" list is always a sequential list then you could provide a "start" and "end" along with at prefix for parameters such as :

97   %macro dumdum(stem=, start=,end=);
98
99   %do i=&start. %to &end.;
100  %put varname is: &stem.&i;
101  %end;
102  %mend;
103
104  %dumdum(stem=AL,start=3, end=6)
varname is: AL3
varname is: AL4
varname is: AL5
varname is: AL6

But without knowing what your "new" list actually looks like I can't at this time make a suggestion on matching the old and new.

And create a rename list in proc datasets . The syntax would look like

Proc datasets libname=<library the dataset resides>;

modify datasetname;

rename

oldname1 = newname1

oldname2 = newname2

;

run;

quit; /*Data sets is one of the procedures that has run group processing and requires QUIT to end*/

Re: Renaming multiple variabes using a macro

PaigeMiller — Mon, 03 May 2021 18:13:02 GMT

Example using data set SASHELP.PRICEDATA and variables price1-price10. Requires the %EXPANDVARLIST macro from https://support.sas.com/resources/papers/proceedings13/032-2013.pdf

%macro rename(oldvarlist, newvarlist, datasetname);
%local i inputlist;
%let inputlist=%expandvarlist(data=&datasetname,var=&oldvarlist);

proc datasets library=work nolist;
    modify &datasetname;
    rename
    %do i=1 %to %sysfunc(countw(&inputlist));
            %scan(&inputlist,&i,%str( )) = %scan(&newvarlist,&i,%str( ))
    %end;
    ;
quit;
%mend rename;

data pricedata;
    set sashelp.pricedata;
run;
%rename(price1-price10, name1 name2 name3 george hector gorilla dog cat ocelot guinea_pig, pricedata)

Re: Renaming multiple variabes using a macro

Tom — Mon, 03 May 2021 18:37:09 GMT

Why are you passing in two parameters if the list of original names is known?

You might re-do the macro to just generate the OLD=NEW pairs.

%macro rename(newvarlist);
  %local i ;
  %do i=1 %to %sysfunc(countw(&newvarlist,%str( ));
      NL&i=%scan(&newvarlist,&i,%str( ))
  %end;
%mend rename;

proc datasets nolist lib=work;
  modify xx ;
    rename %rename(&L);
  run;
quit;

You could modify it to allow a prefix for the old variables.

%macro rename2(prefix,newvarlist);
  %local i ;
  %do i=1 %to %sysfunc(countw(&newvarlist,%str( ));
     &prefix&i=%scan(&newvarlist,&i,%str( ))
  %end;
%mend rename2;

... rename %rename(NL,&L);

If you really want a macro that allows you to use variable lists then you will need to do more work to translate the variable lists into an actual list of variables. Here is a sketch of how you might do that using PROC TRANSPOSE.

%let oldlist=A B C1-C5 D ;
%let newlist=AGE RACE INCOME2001-INCOME2005 OUTCOME ;

data ;
  retain &oldlist;
  call missing(of &oldlist);
  stop;
run;
proc transpose out=oldnames name=old; run;
data ;
  retain &newlist;
  call missing(of &newlist);
  stop;
run;
proc transpose out=newnames name=new; run;
data pairs ;
  set oldnames end=eof1;
  set newnames end=eof2;
  length rename $32767;
  if upcase(old) ne upcase(new) then 
    rename=catx(' ',rename,catx('=',nliteral(old),nliteral(new)))
  ;
  retain rename;
  if eof1 or eof2 then call symputx('rename',rename);
run;

proc datasets nolist lib=work;
  modify xx ;
  rename &rename ;
  run;
quit;

Resulting list of rename pairs:

891   %put &rename;
A=AGE B=RACE C1=INCOME2001 C2=INCOME2002 C3=INCOME2003 C4=INCOME2004 C5=INCOME2005 D=OUTCOME

Re: Renaming multiple variabes using a macro

Neff — Mon, 03 May 2021 19:24:21 GMT

Thank you all for the answers provided