rename variables trouble :S

[SachinRuk]

I am sorry if this is the wrong forum.

I've modified the code from one of the SAS-papers: Paper 118-28, "Renaming All Variables in a SAS® Data Set Using the Information from PROC SQL's Dictionary Tables", Prasad Ravi ,Household Credit Services, Beaverton, OR

to look like the following. I am having trouble with the renaming part (bolded). Its something to do with the %if statements i think.

Any thoughts?
thanks,
Sachin


*test data;
Data one;
U=1;
V=2;
_X=3;
Y=4;
Z=5;
Run;

/* Running the renaming macro */
options macrogen mprint mlogic;

/* Running the renaming macro */
%macro rename(lib,dsn);
/*create a temporary fily by appending name with '2'*/
data &lib..&dsn.2;
set &lib..&dsn;
run;

/*get the number of variables and the variable names*/
proc sql noprint;
select nvar into :num_vars
from dictionary.tables
where libname="&LIB" and
memname="&DSN.2";

select distinct(name) into :var1-
:var%TRIM(%LEFT(&num_vars))
from dictionary.columns
where libname="&LIB" and
memname="&DSN.2";
quit;
run;

/*reaname the variable*/
proc datasets library=&LIB;
modify &DSN.2;
rename
%do i=1 %to &num_vars;
%if &i=1 %then %do;
&&var&i=x;
%end;
%else %do;
&&var&i=y&i;
%end;

%end;
quit;
run;
%mend rename;
%rename(WORK,ONE);

[Robert_Bardos]

As I see it (can't test unfortunately) your macro generates the rename statements with semicolons between the elements, i.e. it syntactically terminates the rename statement after the first rename pair.

Remember to be careful with semicolons in macro generated statements, especially in macro generated statement parts.

[chang_y_chung_hotmail_com]

The abuse of the so-called "macro variable arrays" like this should be avoided. There is no need for them in this context and they are not used correctly (they should be %local^ed to the macro at the least.) Here is an alternative using the vcolumn sashelp view that is associated with the similarly named dictionary table. This will error if there exist variables like x, y1, y2,... already in the new dataset. Hope this helps a bit.

[pre]
/* test data with five vars and one obs */
data one;
retain U V _X Y Z .;
run;

/* making a copy */
data two;
set one;
run;

%*-- first var to x, the rest to y2, y3, ... --*;
%macro renameXY(data);
%local d1 d2 libname memname where ds rc name varnum;
%if not %sysfunc(exist(&data)) %then %return;
%let d1 = %scan(&data,1,.);
%let d2 = %scan(&data,2,.);
%let libname = %upcase(%sysfunc(ifc(&d2=,work,&d1)));
%let memname = %upcase(%sysfunc(ifc(&d2=,&d1,&d2)));
%let where = (libname="&libname" and memname="&memname");
%let ds = %sysfunc(open(sashelp.vcolumn(where=&where),is));
%syscall set(ds);
%do %while (%sysfunc(fetch(&ds))=0);
%if &varnum=1 %then %sysfunc(catx(=,&name,x));
%else %sysfunc(catx(=,&name,y&varnum));
%end;
%let rc=%sysfunc(close(&ds));
%mend renameXY;

proc datasets lib=work;
modify two;
rename %renameXY(one);
run;
quit;

/* check */
proc contents data=two;
run;
/* on lst -- in part
# Variable Type Len
1 x Num 8
2 y2 Num 8
3 y3 Num 8
4 y4 Num 8
5 y5 Num 8
*/
[/pre]