topic Re: Renaming variable names with macro and loop in SAS Programming

Renaming variable names with macro and loop

SamuelKnapp — Wed, 15 Apr 2020 22:41:06 GMT

Hello,

I would like to replace a fixed string of charaters in the variable names of a dataset. I created the follwing example. I don't want give the number of columns, but automatically loop over all columns. However, I always get this error message:

ERROR: Required operator not found in expression: dim(cols)

Many thanks

/* create test data */
data test (drop= i);
	array a{*} A1-A3;
	do i=1 to dim(a);
		a{i}=i;
	end;
run;

/* with specifying the number of coluns */
%macro doit(ncols=3);
data testB;
	set test;
	%do i = 1 %to &ncols;
		rename A&i.=B&i.;
	%end;
run;
%mend doit;
%doit;

/* with loop automatically over all columns */
%macro doit();
data testC;
	set test;
	array cols(*) _all_;
	%do i = 1 %to dim(cols); /* here is the problem*/
		rename A&i.=B&i.;
	%end;
run;
%mend doit;
%doit;

Re: Renaming variable names with macro and loop

Quentin — Wed, 15 Apr 2020 18:32:26 GMT

Hi,

Your code doesn't work because the macro language is a preprocessor. Macro code executes before any DATA step code executes (or even compiles). The macro language does not know about data sets, data set variables, and arrays. So in this line:

%do i = 1 %to dim(cols);

dim(cols) is just a series of 9 characters to the macro language, thus the error.

Can you describe a bit more of your real problem? Do you really have only one list of variable to rename, with numeric suffixes (A1-A3) or do you have other variables as well?

One approach to this is to build a macro variable that has the list of variables to be renamed. And you can use the macro language to loop over that list.

For you current example, you don't actually need a macro, as you can do:

data testC;
  set test (rename=(A1-A3=B1-B3));
run;

Re: Renaming variable names with macro and loop

SamuelKnapp — Wed, 15 Apr 2020 19:36:33 GMT

Thanks for your quick answer.
The actual problem is to rename Cov1-Cov90 (output lsmeans) into Col1-Col90 (for use as coefficent matrix in proc mixed). As I need to run it several times, I don't want to specify the number of columns by hand, and it differs between the datasets.
I found this other post, where it was possible in a macro, but also by specifying the number of columns by hand (like the first approach):
https://communities.sas.com/t5/SAS-Enterprise-Guide/Rename-variables-using-a-loop-data-step/td-p/337185
If there is any replace method, all I need is to replace "Cov" by "Col".

And yes, there are also other variables in the dataset, but my idea was to split these variables off, change their names and merge it again...
I'm a bit surprised that this seems to be so difficult, as in R it's one line and done...
Thanks again

Re: Renaming variable names with macro and loop

RichardDeVen — Wed, 15 Apr 2020 20:01:36 GMT

Macro generates the source code that is submitted.

A running DATA step can't interact with macro and modify it's own running source code.

You can rely on knowing your variables and use `numbered range lists` to rename the variables

data have;
  array x x1-x10;
run;

data want;
  set have;
  rename x1-x10=y1-y10;  * numbered range list;
run;

or use a step to access to variable names (part of a data set's metadata) and write the rename statement.

Re: Renaming variable names with macro and loop

Quentin — Wed, 15 Apr 2020 20:47:15 GMT

It's trivial to add a step that reads the variable names from metadata, and generates a list of rename pairs, e.g.:

data have ;
  array a{*} x y Cov1-Cov90;
run;

proc sql noprint;
  select cats(name,"=",transtrn(name,'Cov','Col'))
    into :renamelist separated by " "
    from dictionary.columns
    where libname='WORK' and memname='HAVE' and name like 'Cov%'
  ;
quit ;

%Put &renamelist ;

data want ;
  set have ;
  rename &renamelist ;
run ;