Create a macro that applies translate on multiple columns that you define in a dataset

Question

I'm new to programming in SAS and I would like to do 2 macros, the first one I have done and it consists of giving 3 parameters: name of the input table, name of the column, name of the output table. What this macro does is translate the rare or accented characters, passing it a table and specifying in which column you want the rare characters to be translated:

The code to do this macro is this:

%macro translate_column(table,column,name_output);

*%LET table = TEST_MACRO_TRNSLT;
*%let column = marca;
*%let name_output = COSAS;


PROC SQL;
CREATE TABLE TEST AS
SELECT *
FROM &table.;
QUIT;

data &NAME_OUTPUT;
set TEST;
&column.=tranwrd(&column., "Á", "A");
run;
%mend;
%translate_column(TEST_MACRO_TRNSLT,marca,COSAS);

The problem comes when I try to do the second macro, that I want to replicate what I do in the first one but instead of having the columns that I can introduce to 1, let it be infinite, that is, if in a data set I have 4 columns with characters rare, can you translate the rare characters of those 4 columns. I don't know if I have to put a previously made macro in a parameter and then make a kind of loop or something in the macro.

The same by creating a kind of array (I have no experience with this) and putting those values in a list (these would be the different columns you want to iterate over) or in a macrovariable, it may be that passing this list as a function parameter works.

Could someone give me a hand on this? I would be very grateful

Answer 1

Either use an ARRAY or a %DO loop.

In either case use a space delimited list of variable names as the value of the COLUMN input parameter to your macro.

%translate_column
(table=TEST_MACRO_TRNSLT
,column=var1 varA var2 varB
,name_output=COSAS
);

So here is ARRAY based version:

%macro translate_column(table,column,name_output);
data &NAME_OUTPUT;
  set &table.;
  array __column &column ;
  do over __column; 
    __column=ktranslate(__column, "A", "Á");
  end;
run;
%mend;

Here is %DO loop based version

%macro translate_column(table,column,name_output);
%local index name ;
data &NAME_OUTPUT;
  set &table.;
%do index=1 %to %sysfunc(countw(&column,%str( )));
  %let name=%scan(&column,&index,%str( ));
  &name = ktranslate(&name, "A", "Á");
%end;
run;
%mend;

Notice I switched to using KTRANSLATE() instead of TRANWRD. That means you could adjust the macro to handle multiple character replacements at once

&name = ktranslate(&name,'AO','ÁÓ');

The advantage of the ARRAY version is you could do it without having to create a macro. The advantage of the %DO loop version is that it does not require that you find a name to use for the array that does not conflict with any existing variable name in the dataset.