sas macro loop to rename variable

1
votes

Hi I am trying to rename variables using SAS Macro loop.

%Let t1=12Mth;
%Let t2=20;
%Let t3=30;
%Let t4=40;
%Let t5=50;
%Let t6=60;

%macro Re(time);
%Do I = 1 %to &time.; 
data MilkNew;
set Milk;
rename MT&&t&I..Sp=MTSp&&t&I.;
run;
%end;
%mend Re;
%Re(6)

This loop is mean to rename MT...Sp to MTSp.... Eg:MT20SP to MTSp20. When I run my loop, there was no error but the variable names were not changed in MilkNew at all. Where does the problem come? Thanks!

3
sassas-macro

3 Answers

2
votes

If the only purpose of the macro is to rename the variables in the data set, then why read the data with a set statement. Your data set is probably really small so you don't even realize the inefficiency of doing that. Instead use the modify statement in proc datasets to accomplish the same thing, but more efficiently. Here's an alternative macro for you.

%macro renamevar(dsname, time);
%local lib ds i; 
%let lib = %sysfunc(coalescec(%scan(&dsname, -2, %str(.)), work));
%let ds = %scan(&dsname, -1, %str(.));

proc datasets lib=&lib nolist;
modify &ds;
    rename
        %do i = 1 %to &time; 
            mt&&t&i..Sp=MTSp&&t&i.
        %end;
        ;
quit;
%mend;

%renamevar(milk, 6);

Here's the log after the macro call:

NOTE: Renaming variable mt12MthSp to MTSp12Mth.
NOTE: Renaming variable mt20Sp to MTSp20.
NOTE: Renaming variable mt30Sp to MTSp30.
NOTE: Renaming variable mt40Sp to MTSp40.
NOTE: Renaming variable mt50Sp to MTSp50.
NOTE: Renaming variable mt60Sp to MTSp60.

NOTE: MODIFY was successful for WORK.MILK.DATA.
NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
1
votes

You should move the loop so that it only generates just the RENAME statement (or even just the old=new name pairs). What is happening now is that you keep overwriting MilkNew so only the last RENAME has any effect.

%macro Re(time);
 data MilkNew;
   set Milk;
 %do I = 1 %to &time.; 
   rename MT&&t&I..Sp=MTSp&&t&I.;
 %end;
 run;
%mend Re;
%Re(6)
0
votes

You should have seen the last variable name in the loop (so the 6th) changed. That's because you repeated the same data step with the same source dataset but a different destination - so each time you 'forgot' the changes made in the earlier step.

So, this would've worked, though I'll get in a minute to why this isn't a good way to do this.

%Let t1=12Mth;
%Let t2=20;
%Let t3=30;
%Let t4=40;
%Let t5=50;
%Let t6=60;

%macro Re(time);
%Do I = 1 %to &time.; 
data Milk;
set Milk;
rename MT&&t&I..Sp=MTSp&&t&I.;
run;
%end;
%mend Re;

data milk;
  input 
    MT12mthSP
    MT20SP
    MT30SP
    MT40SP
    MT50SP
    MT60SP
  ;
  datalines;
12 20 30 40 50 60
;;;;
run;


%Re(6)

Here I had it make all changes to Milk and save them back in that dataset. If you want to preserve Milk then first make Milk_New then have that in both set and data statements.

Second, you should not do a new data step for each change. Macros don't have to have a data step in them; they can be run inside the datastep.

So for example:

%macro Re(time);
  %Do I = 1 %to &time.; 
  rename MT&&t&I..Sp=MTSp&&t&I.;
  %end;
%mend Re;
data milk_new;
  set milk;
  %Re(6);
run;

Even better would be generating this list outside of a macro entirely - look up "generating code SAS" for suggestions on that.

If you didn't see any renames at all, you also may have an issue where a label is present on the column(s). That won't affect your usage of the variable name, but it will make it confusing. Use

label _all_;

Or include a label-clearing statement (label <varname>; where you pop in the same variable name as the original variable name before rename) inside your macro loop to fix that.