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

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

saskapa — Tue, 04 Aug 2015 12:54:10 GMT

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

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

RW9 — Tue, 04 Aug 2015 13:06:11 GMT

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.

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

saskapa — Tue, 04 Aug 2015 13:16:19 GMT

Hi RW9,

Thanks, yes it indeed a solution.

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

saskap

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

Tom — Tue, 04 Aug 2015 13:31:00 GMT

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

;

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

Tom — Tue, 04 Aug 2015 13:33:24 GMT

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 ;

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

Loko — Tue, 04 Aug 2015 13:35:33 GMT

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

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

saskapa — Tue, 04 Aug 2015 13:36:13 GMT

Thanks Tom, it works fine !