column rename macro

[learn_SAS_23]

Hi all am trying to execute column rename for a large data set , am getting below error

all is i am trying to all column names in different variables, and trying to rename .

%macro rename;

proc sql noprint;
select count(*) into :n
from sashelp.vcolumn
where libname='WORK' and
memname='_VAR_OLD';
select distinct(name) into :var1-:var&n
from sashelp.vcolumn
where libname='WORK' and
memname='_VAR_OLD';
quit;

proc datasets libname='WORK' nolist;
modify _var_old;
rename

%do i=1 %to &n;
%if %index(%upcase(&except), %upcase(&&var&i)) eq 0 %then
%do;
&var&i=OLD_&var&i.
%end;
%end;
;
quit;

%mend rename;

NOTE: Line generated by the macro variable "N".
326 var 8
_
22
76

ERROR 22-322: Syntax error, expecting one of the following: ',', FROM, NOTRIM.

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: Line generated by the invoked macro "RENAME".
326 proc datasets libname='WORK' nolist; modify _var_old;
_______ ______
1 22
200
NOTE: Enter RUN; to continue or QUIT; to end the procedure.
NOTE: Statements not processed because of errors noted above.
WARNING 1-322: Assuming the symbol LIB was misspelled as libname.

Apparent symbolic reference VAR1 not resolved.

NOTE 138-205: Line generated by the macro variable "I".
326 OLD_&var1
_
22
ERROR 22-322: Expecting a name.

[Kurt_Bremser]

Indirect addressing of macro variables needs a double ampersand.

To get the contents of &var6 when &i=6, use &&var&i

[learn_SAS_23]

Thanks for quick help, am trying to execute the below code , but still can't able to resolve the variable

%macro rename;

proc sql noprint;
select count(*) into :n
from sashelp.vcolumn
where libname='WORK' and
memname='_VAR_OLD';
select distinct(name) into :var1-:var&n
from sashelp.vcolumn
where libname='WORK' and
memname='_VAR_OLD';
quit;

proc datasets libname='WORK' nolist;
modify _var_old;
rename

%do i=1 %to &n;
%if %index(%upcase(&except), %upcase(&&var&i)) eq 0 %then
%do;
&&var&i=OLD_&&var&i.
%end;
%end;
;
quit;

%mend rename;

[Kurt_Bremser]

Can that macro, it's much easier in a simple data step with call execute:

data class;
set sashelp.class;
run;

data _null_;
set sashelp.vcolumn (where=(libname = "WORK" and memname = "CLASS")) end=eof;
if _n_ = 1 then call execute("proc datasets lib=WORK nolist;modify CLASS; rename");
call execute(' ' !! trim(name) !! '=old_' !! trim(name));
if eof then call execute(';quit;');
run; 

[Quentin]

You've got a few problems:

1. select count(*) into :n  will have leading blanks in the macro var N which cause problems.  Change to : select count(*) into :n trimmed.  TRIMMED will get rid of leading and trailing blanks.  The easiest way to detect such blanks is with %put >>&n<<;
2. proc datasets libname='WORK' nolist;  The option is library= not libname=, and the value should not be in quotes.  Change to: proc datasets library=WORK nolist;
3. There are also general macro design issues.  Your macro has no parameters, I'm guessing you would want parameters for name of the the library, datasest, except, etc.  It's a good idea to declare macro variables to be %local, so they don't collide with macro variables from another scope.  This is a hassle when working with "macro arrays" but still worth while.  You can do stuff like: %do i=1 %to &n ; %local var&i; %end ;

[RW9 · Accepted solution]

Please post test data in the form of a datastep, and what the output should look like.

Also, we had more or less exactly the same question yesterday:

https://communities.sas.com/t5/SAS-Programming/how-to-select-rows-with-column-value-not-in-a-macro-l...

[learn_SAS_23]

Thanks for that , can able to customise solution

proc contents data=_Test_old
out=_var_old(keep = memname memlabel name label format length type varnum)
noprint order=collate;
run;

proc sql;
create table _var_old as
select name ,strip(name)||'_O' as OLD_name
from _var_old
;quit;


proc sql ;
select catt(strip(name),"=",strip(OLD_name))
into :renames separtated by " "
from _var_old
;
quit;
data _Test_old_1;
set _Test_old (rename=(&renames.));
run;