a rename macro : %do %while (%scan()..) condition is FALSE not iterate again

[saskapa]

Hi,

Can someone please explain me why my %do %while  stops after first iteration while it shouldn't...

I created a macro to perform a rename for a selected list of variable.

This the table that I would like to change the variable names :

data list;

infile cards  dsd missover;

input var1 $4. var2 $4. var3 $4. ;

cards;

aaa bbb ccc;

run;

In order to do so I get the new variable's name ( values of variable col )  from 'test' dataset (it is important to notice the value of col have underscore and I do not want to remove those underscore) :

data test;

infile cards  dsd missover;

input orig $4. col $20.;

cards;

var1 over_tap

var2 under_top_more

var3 any_over_before

;

run;

So I would like in list dataset to rename  var1  to 'over_tap', var2  to 'under_top_more' and var3 to 'any_over_before'.

So my code is :

%macro rename (data=) ;

proc sql;

select distinct orig,compress(col) into :old,:new  separated by ' '

from test;

quit;

data new_&data. ; set &data.;

%let count=1;

%do %while(%scan(&old,&count,%str( )) ne %str());

     %let old_&count=%scan(&old,&count);

     %let new_&count=%scan(&new,&count);

     rename &&old_&count.=&&new_&count.;

   %let count=%eval(&count+1);

%end;

run;

%mend rename;

%rename(data=list)

Unfortunately, my macro does only perform  one iteration...There are no ERROR or WARNING  and the log says :

SYMBOLGEN:  Macro variable COUNT resolves to 2

MLOGIC(RENAME):  %DO %WHILE() condition is FALSE; loop will not iterate again.

MPRINT(RENAME):   run;

Any clue ?

Regards,

sasp

[RW9]

Sorry, not going to read that as really can't look at %&%&'s.  As an alternative to doing it that way, why not just generate the code directly from the data you have:

data _null_;

     set test end=last;

     if _n_=1 then call execute(cats('data want; set list (rename=(',orig,'=',col));

     else call execute(cats(' ',orig,'=',col));

     if last then call execute(')); run;');

run;

This will generate a datastep with the renames from your dataset.

[saskapa]

Hi RW9,

Thanks, yes it indeed a solution.

However, I am still wondering why my %do %while iterate only once.

saskap

[Tom · Accepted solution]

You are only selecting one value into the macro variable OLD.  If you want multiple values you need to include the SEPARATED BY clause for each macro variable.

select distinct

  orig

,compress(col)

into

  : old separated by ' '

,: new separated by ' '

from test

;

[saskapa]

Thanks Tom, it works fine  !

[Tom]

Unless you have a lot of variables you should be able to do it with one macro variable and no need for macro logic.

select distinct

  catx('=',orig,compress(col))

into

  : rename separated by ' '

from test

;

... rename &rename ;

[Loko]

Hello,

from your code the value of &old is var1. The sql should be corrected like this

proc sql;

select distinct orig,compress(col) into :old separated by ' ',:new  separated by ' '

from test;

quit;

in order for the &old to be var1 var2 var3