topic Re: Changing columns names across multiple data sets in a library in SAS Programming

Changing columns names across multiple data sets in a library

SAS_ACE — Fri, 11 Sep 2020 05:33:10 GMT

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;

Re: Changing columns names across multiple data sets in a library

jimbarbour — Fri, 11 Sep 2020 06:27:55 GMT

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;

Re: Changing columns names across multiple data sets in a library

andreas_lds — Fri, 11 Sep 2020 07:04:37 GMT

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

Re: Changing columns names across multiple data sets in a library

Shmuel — Fri, 11 Sep 2020 08:57:03 GMT

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

Re: Changing columns names across multiple data sets in a library

Shmuel — Fri, 11 Sep 2020 14:43:03 GMT

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

Re: Changing columns names across multiple data sets in a library

SAS_ACE — Fri, 11 Sep 2020 14:30:55 GMT

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.

Re: Changing columns names across multiple data sets in a library

ballardw — Fri, 11 Sep 2020 20:36:24 GMT

@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.

Re: Changing columns names across multiple data sets in a library

SAS_ACE — Fri, 11 Sep 2020 16:51:39 GMT

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;