topic Re: column rename macro in SAS Enterprise Guide

column rename macro

learn_SAS_23 — Fri, 07 Sep 2018 09:18:49 GMT

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.

Re: column rename macro

Kurt_Bremser — Fri, 07 Sep 2018 09:22:14 GMT

Indirect addressing of macro variables needs a double ampersand.

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

Re: column rename macro

learn_SAS_23 — Fri, 07 Sep 2018 09:24:40 GMT

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

%macro rename;

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;

Re: column rename macro

RW9 — Fri, 07 Sep 2018 09:38:15 GMT

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-list/m-p/492959#M129654

Re: column rename macro

Kurt_Bremser — Fri, 07 Sep 2018 10:02:36 GMT

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;

Re: column rename macro

Quentin — Fri, 07 Sep 2018 10:59:08 GMT

You've got a few problems:

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<<;
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;
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 ;

Re: column rename macro

learn_SAS_23 — Fri, 07 Sep 2018 13:03:31 GMT

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;