Nested DO loop in Macro

[JenniferBernard]

I am brand new to SAS programming, so forgive my ignorance on how do loops iterate.  I'm hoping someone can give me a sample to work with, as I have been reading and experimenting to no avail.  In the example below, I have a dataset with variables that increment in months, for 36 columns total.  These columns have names that reflect the month from the start date (ie VAR_1 = variable recorded on Jan 2010, VAR_2 = variable recorded on Feb 2010, all the way to VAR_36 = variable recorded on Dec 2012).  For programming purposes, I need to cycle through the column names and rename each variable with a month/year format: rename VAR_1=var_jan_2010  and so forth.

I've tried the code listed, and I know it is overwriting each variable name.  I've tried rearranging the loop and am just stuck.  Any tips would be greatly appreciated!

data have;
  input dum VAR_1 VAR_2 VAR_3 VAR_4 VAR_5 VAR_6
  			VAR_7 VAR_8 VAR_9 VAR_10 VAR_11 VAR_12
  			VAR_13 VAR_14 VAR_15 VAR_16 VAR_17 VAR_18
  			VAR_19 VAR_20 VAR_21 VAR_22 VAR_23 VAR_24
  			VAR_25 VAR_26 VAR_27 VAR_28 VAR_29 VAR_30
  			VAR_31 VAR_32 VAR_33 VAR_34 VAR_35 VAR_36;
datalines;
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1      
;


%macro changenames;
data want;
  set have;
  %local i next_month;
  %let month=jan feb mar apr may jun jul aug sep oct nov dec;
  %do k=1 %to 36;
    %do j=2010 %to 2012;
      %do i=1 %to %sysfunc(countw(&month));
        %let next_month = %scan(&month, &i);
        rename VAR_&k. = var_&next_month._&j.;
      %end;
    %end;
  %end;
run;
%mend changenames;
%changenames *;

[ballardw]

The real question is why do your variables have the "wrong" name to begin with?

And almost every process that has variables running across with information in the name is more complicated than one that has a "long" structure with one record per date and variable to hold the date value.

The data step is a potentially extremely time consuming method to rename variables. Proc Datasets will do that in place with the MODIFY and Rename statements.

[JenniferBernard]

I will look into proc datasets. Nothing I can do about the format of the data

[Reeza]

Do not use macros here at all. Instead create a data driven data set that maps your data from one variable to the name. Then it's trivial to rename them all at once. If you had a data set called rename_table with old_name containing the current variable name and the new variable name in new_name this is what you would do (untested)

data _new_list;
set rename_table end=eof;



*start proc datasets;
if _n_=1 then
call execute ('proc datasets lib=WORK nodetails nolist;
modify have; rename');

*add on new name calculation;
new_name=catt('Name', put(_n_, z3.));

*pass new and old name to proc datasets;
call execute (old_name);
call execute ('=');
call execute (new_name);

*if last record then quit;
If eof then
call execute (';run;quit;');
run;

[PaigeMiller]

---

@Reeza wrote:
Do not use macros here at all. Instead create a data driven data set that maps your data from one variable to the name. Then it's trivial to rename them all at once. If you had a data set called rename_table with old_name containing the current variable name and the new variable name in new_name this is what you would do (untested)

data _new_list;
set rename_table end=eof;



*start proc datasets;
if _n_=1 then
call execute ('proc datasets lib=WORK nodetails nolist;
modify have; rename');

*add on new name calculation;
new_name=catt('Name', put(_n_, z3.));

*pass new and old name to proc datasets;
call execute (old_name);
call execute ('=');
call execute (new_name);

*if last record then quit;
If eof then
call execute (';run;quit;');
run;

---

And it would still be simpler — by an order of magnitude — to convert the data to long instead of wide, because just about any subsequent analysis or graphics are much easier to code with long data sets.

[Reeza]

Oh I agree. And less likely to run into issues with potentially missing data and a host of other problematic issues. It's very easy for Month1 to be one value for row 1 but another value for row 99 and that alone would drive me away from this method.

It's also likely in a long format at some point, so the key is when transposing to use the right variable names or labels automatically.

OP, is that an option for you. Instead of variable names can you have them as labels? Then when you export/display it does look like it's correct but your variable names are maintained.

[PaigeMiller]

Advice: don't do this at all. Variable names with month names in them make programming a lot harder than if you leave these as var1 var2 etc. You can add labels to the variables to indicate the month.

Even better... EVEN BETTER

Make the data long rather than wide, and your programming becomes even easier.

[JenniferBernard]

Not my data, I have to use this format.

[PaigeMiller]

---

@JenniferBernard wrote:

Not my data, I have to use this format.

---

You can, and should, reshape the data from wide to long. Look at PROC TRANSPOSE. The rest of your coding will be much easier. Then, you probably won't need macros, much less nested macro %DO loops.

[Astounding]

Many smart people have encouraged you to use a long format instead of a wide one.  Despite the wisdom of their suggestions, I understand that you have requirements to keep the wide format.  In that case, I will make a different suggestion where you can keep the wide format.  However, use different variable names.  Instead of:

var_jan_2010

use:

var_2010_01

This should be easily readable, yet it contains several advantages.  Given what you are posted so far, you can probably complete the programming yourself with no additional help.  And that set of names will make future programming much easier.  For example, you will be able to use a VAR statement such as this:

var var_2010_: ;

That will get you all the variables from 2010.  So see if the requirements have any flexibility in terms of which names to use and consider a slightly different set of names.

[sustagens]

If you REALLY need to rename them......you can try:

data col_names (keep=name:);
set sashelp.vcolumn (where=(libname='WORK' and memname='HAVE' and substr(name,1,4)='VAR_'));

start=mdy(1,1,2010);
num=tranwrd(name,'VAR_','');
date=intnx('month',start,num-1);

name_yyyymm=cats('VAR_',put(date,yymmn6.)); /*VAR_201001*/
name_monyyyy=cats('VAR_',put(date,monyy7.)); /*VAR_JAN2010*/
name_mon_yyyy=cats('VAR_',put(date,monname3.),'_',put(date,year4.)); /*VAR_Jan_2010*/
run;

proc sql noprint;
select catx('=',name,name_mon_yyyy) into :renames separated by ' ' from col_names;
quit;

proc datasets nolist library=work;
modify have;
rename &renames;
run;
quit;

[s_lassen · Accepted solution]

If you want to do it with a macro, even though everybody seems to know better, here is a way:

%macro changenames;
data want;
  set have;
  %local i month months year;
  %let months=jan feb mar apr may jun jul aug sep oct nov dec;
  %do i=1 %to 36;
    %let year=%eval(2010+(&i-1)/12);
    %let month = %scan(&months, &i-12*((&i-1)/12));
    rename VAR_&i. = var_&month._&year.;
    %end;
run;
%mend changenames;

The macro really does not need a nested loop, you can calculate the year and month from the variable number. The catch is just understanding macro arithmetic, which is strictly integer, e.g. %eval(11/12) returns 0. 

Of course you may want to rewrite the macro to use proc datasets to rename the variables in place instead of creating a new table.

Another possibility is to use SQL to create the content of the RENAME statement, which can be used in a datastep as well as proc datasets:

proc sql noprint;
  create table vardates as select 
  name,intnx('month','31dec2009'd,input(scan(name,2,'_'),2.)) as date format=date9.
  from dictionary.columns
  where libname='WORK' and memname='HAVE' and name like 'VAR%';
  select catx('_',name,'=VAR',put(date,year4.),put(date,monname3.)) into :rename separated by ' '
  from vardates;
quit;

data want;
  set have;
  rename &rename;
run;

This is probably what I would do for such a task, it automatically adapts if you get more variables next month.