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
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
;
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.
Hi RW9,
Thanks, yes it indeed a solution.
However, I am still wondering why my %do %while iterate only once.
saskap
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
;
Thanks Tom, it works fine !
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 ;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.