Rename variables using a loop - data step

[ayin]

I have a macro variable, named 'Num_period', which was used to create new columns.

%global Num_period;
%let Num_period=23;

Now, names for those new columns have a pattern like:

Period_1_Sum, Period_2_Sum, ... , Period_23_Sum.

Now we would like to use a loop, and a rename function in data step to rename all columns to:

0, 1, 2 ... 22;

The codes I expect would be something like:

data test;
      set train;

      do i = 1 to &Num_period;
          rename Period_{i}_Sum {i}-1;
      end;

run;

The codes provided above aren't correct obviously. Could you provide the correct way of doing it?

Thanks.

[art297 · Accepted solution]

If your trying to change 1 thru 23 to be 0 thru 22, then wrapping your code in a macro would do it:

%macro doit(Num_Period=23);
  data test;
      set train;
      %do i = 1 %to &Num_period;
          rename Period_&i._Sum=Period_%eval(&i.-1)_Sum;
      %end;
  run;
 %mend doit;

%doit

Art, CEO, AnalystFinder.com

[ayin]

Thanks! Can we just use the number (e.g. 0) as the column name? so instead of 'Period_0_Sum', it would be just '0'.

[art297]

No! SAS variables have to start with either a letter or underscore. I was asking if you wanted:

Period_1_Sum to Period_23_Sumto be reccoded to Period_0_Sum to Period_22

That is what the macro I suggested would do.

Art, CEO, AnalystFinder.com

[RW9]

Hi,

Well, its really not a good idea to name a range of variables with the incrementor not at the end of the name.  The reason is that you can't use inbuilt lists as easily with such a setup e.g:

array period_sum_{23}; 
or
sum(of period_sum_:);

Versus (in your method):
array abc{23} period_1_sum--period_23_sum;
or 
sum(of period_1_sum--period_23_sum);

A further tip here is that if you were to normalise your data, i.e. having the data go down rather than across, you would make your programming simpler - and you would be inline with industry standards such as CDISC.

Finally, if you really have to go this way, then you don't need macro att all, nor do you need to loop as:

data want;
  set train;
  array period_sum_{&num_period.};
run;

Will effectively create the 23 elements for you, no need to loop.