Changing columns names across multiple data sets in a library

[SAS_ACE]

Hello,

I have 3 datasets in a library (WORK) named DETAIL1, DETAIL2, DETAIL3.  These datasets all contain column names Var1, Var2, Var3.  

I know how to change the columns names of a given data set in a library:

data x;
set DETAIL_1;
rename 
var1=Name
var2=Address
var3=State
;run;

How can I do this dynamically to also rename the variables in DETAIL2, DETAIL3, DETAILx etc?  Is there a way to use memname to accomplish this ? 

PROC SQL;

SELECT MEMNAME INTO : MEMNAMES SEPARATED BY ' ' from dictionary.tables where libname='WORK' and memname like 'DET%';quit;

[jimbarbour]

You've got the right idea, but, since all the Memnames are essentially the same, instead of getting the Memnames themselves, just get a count, like so:

PROC SQL	NOPRINT;
	SELECT		COUNT(1)	INTO	:	Mem_Cnt
		FROM	Dictionary.Tables
		WHERE	LIBNAME	=		'WORK'
			AND Memname LIKE	UPCASE("&Base_Name%")
			;
QUIT;

Notice that the name here doesn't even have to be "DETAIL."  I made the base table name a macro variable so that you can name the tables anything you want so long as they have a common base name followed by a number (or really any character).

Once you've got the count of tables with matching base names, run a small macro, like so:

&Null	%MACRO	Rename_DS();
	%LOCAL	i;
	%DO	i	=	1	%TO	&Mem_Cnt;
		DATA	Renamed_&Base_Name&i;
			SET	&Base_Name&i	(RENAME=(Var1=Name Var2=Address Var3=State));
		RUN;
	%END;
%MEND	Rename_DS;

%Rename_DS;

"&Null" resolves to (literally) nothing and is just there to maintain colorization in the SAS editor.

That's it; your done.  Your results look like this:

Regards,

Jim

P.S.  In case it's useful, below is the full code that includes creating the original DETAIL datasets using a macro base name:

%LET	Null		=	;
%LET	Base_Name	=	Detail_;

**------------------------------------------------------------------------------**;

&Null	%MACRO	Create_DS(i);
	DATA	&Base_Name&i;
		Var1="Joe&i";
		Var2="&i&i&i Main Street";
		Var3="WA";
	RUN;
%MEND	Create_DS;

%Create_DS(1);
%Create_DS(2);
%Create_DS(3);

**------------------------------------------------------------------------------**;

PROC SQL	NOPRINT;
	SELECT		COUNT(1)	INTO	:	Mem_Cnt
		FROM	Dictionary.Tables
		WHERE	LIBNAME	=		'WORK'
			AND Memname LIKE	UPCASE("&Base_Name%")
			;
QUIT;

%PUT	&=Mem_Cnt;

**------------------------------------------------------------------------------**;

&Null	%MACRO	Rename_DS();
	%LOCAL	i;
	%DO	i	=	1	%TO	&Mem_Cnt;
		DATA	Renamed_&Base_Name&i;
			SET	&Base_Name&i	(RENAME=(Var1=Name Var2=Address Var3=State));
		RUN;
	%END;
%MEND	Rename_DS;

%Rename_DS;

[andreas_lds]

This problem should be fixed in the process reading the files, not afterwards.

[Shmuel]

You can use next tested code:

data test1 test2 test3;
    var1="A";
    var2="B";
    var3="C";
run;

proc format lib=work;
  value renvar
    1 = 'Name'
	2 = 'Address'
	3 = 'State'
; run;

%macro renall(lib=,dsprefix=,varpref=);
   /* lib = library containing the datasets */
   /* dsprefix = dataset prefix - yot DETAIL */
   /* varpref = variables' prefix to rename from, by using above format */
   proc sort data=sashelp.vcolumn(where=(
                strip(libname)=%sysfunc(upcase("&lib")) and 
			    upcase(substr(memname,1,%length(&dsprefix))) = upcase("&dsprefix") and
				upcase(upcase(substr(name,1,%length(&varpref)))) = upcase("&varpref") ))
              out=mylist;
		by memname name;
	run;

	data _null_;
	 set mylist;
	  by memname;
	     length prog $10000;
		 retain prog nvar ;
	     if first.memname then do;
		    prog = "proc datasets lib=&lib nolist;" ||
			       "modify " || memname || "; Rename ";
			nvar=1;
		 end;
		 prog = compbl(prog || strip(name) || " = " 
		              || put(nvar,renvar32.) || " " );
		 nvar = nvar +1;
		 if last.memname then do;
		    prog = compbl(prog || "; run;");
			putlog prog;  /*for debug */
			call execute(prog);
		 end;
	run;
%mend renall;
%renall(lib=work,dsprefix=test,varpref=var);
		

[Shmuel · Accepted solution]

Alternative code using full variable name in proc format step:

data test1 test2 test3;
    var1="A";
    var2="B";
    var3="C";
run;

proc format lib=work; /* left side should be in UPCASE */
  value $renvar
    "VAR1" = 'Name'
	"VAR2" = 'Address'
	"VAR3" = 'State'
; run;

%macro renall(lib=,dsprefix=,varpref=);
   /* lib = library containing the datasets */
   /* dsprefix = dataset prefix - your DETAIL */
   /* varpref = variables' prefix to rename from, by using above format */
   proc sort data=sashelp.vcolumn(where=(
                strip(libname)=%sysfunc(upcase("&lib")) and 
			    upcase(substr(memname,1,%length(&dsprefix))) = upcase("&dsprefix") and
				upcase(upcase(substr(name,1,%length(&varpref)))) = upcase("&varpref") ))
              out=mylist;
		by memname name;
	run;

	data _null_;
	 set mylist;
	  by memname;
	     length prog $10000;
		 retain prog nvar ;
	     if first.memname then do;
		    prog = "proc datasets lib=&lib nolist;" ||
			       "modify " || memname || "; Rename ";
			nvar=1;
		 end;
		 prog = compbl(prog || strip(name) || " = " 
		            || put(upcase(compress("&varpref"||put(nvar,3.))),$renvar32.) || " " );
		 nvar = nvar +1;
		 if last.memname then do;
		    prog = compbl(prog || "; run;");
			putlog prog;  /*for debug */
			call execute(prog);
		 end;
	run;
%mend renall;
options mprint symbolgen;
%renall(lib=work,dsprefix=test,varpref=var);

[SAS_ACE]

Thank you for your help. This is exactly what I was trying to do. There is definitely lots here for me to learn so I appreciate the work and effort you put into this.

[ballardw]

---

@SAS_ACE wrote:

Hello,

I have 3 datasets in a library (WORK) named DETAIL1, DETAIL2, DETAIL3.  These datasets all contain column names Var1, Var2, Var3.  

I know how to change the columns names of a given data set in a library:

data x;
set DETAIL_1;
rename 
var1=Name
var2=Address
var3=State
;run;

---

Technically that code does not "change" variable names. It creates an entirely new data set with differently named variables eve if you reuse the data set name for source and output.

Proc datasets will change the names, format or labels of variables in place.

Proc datasets library=work;
   modify detail_1;
   rename 
       var1=Name
       var2=Address
       var3=State
   ;
run;
quit;

changes the names in place. This can make a big difference in execution time if the data set is large since it does not rebuild the entire data set.

[SAS_ACE]

Hi Everyone,

I also wanted to share another solution that looks like it is working (found on another site).  Not sure what the "risks" or blind spots are with this one, but wanted to add this to the thread.

data _null_;
  set sashelp.vtable end = eof;
  /*Replace xx with your two-letter dataset prefix*/
  where libname = upcase('work') and memname eq: upcase('TE') and memtype = 'DATA';
  if _n_ = 1 then call execute('proc datasets lib = work;');
  call execute(catx(' ','modify',memname,'; rename var1 = newvar1 var2 = newvar2; run;'));
  if eof then call execute('quit;');
run;