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

[Abelp9]

hello everyone, 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.

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

[ballardw · Accepted solution]

Over complicated. You can apply the translate you are looking at to ALL character variables in a data set using an array.

data want;
   set have;
   array _c (*) _character_;
   do i = 1 to dim(_c);
     _c[i] = translate(_c[i],"Auea","Áüéâ");
   end;
   drop i;
run;

TRANSLATE is the function you want if you are doing single character replacements. You place the "to", the desired results first and matching "from" characters second. The first of the from, when encountered will be replaced by the first of the "to" variables for as many pairs as you provide. Tranwrd, while it will work requires many calls to replace single characters as you would need one for each character.

[Abelp9]

Thank you very much for your help, I have tried to execute this code but it returns strange characters:

data want;
   set TEST_MACRO_TRNSLT;
   array _c (*) _character_;
   do i = 1 to dim(_c);
     _c[i] = translate(_c[i],"Auea","Áüéâ");
   end;
   drop i;
run;

I like your idea of using the translate, although in the fields to which I want to apply the translate are words with several characters and I will want to change them because the ones that are rare, if a word is HÓLÁ I want it to return HOLA.

_c what does it refer to? I don't have much experience in arrays but I find them very interesting

[PaigeMiller]

---

@Abelp9 wrote:

Thank you very much for your help, I have tried to execute this code but it returns strange characters:

---

We don't know what this means. SHOW US the "strange characters". Show us the LOG. Help us help you by being specific (not just here in this case, but in every situation)

[Abelp9]

This is my input table:

And this is what I get back when I run the code:

[ballardw]

Example with translate that does exactly as requested for

From my log running similar example.

158  data example;
159     word = "HÓLÁ";
160     word = translate(word,'AO','ÁÓ');
161     put word=;
162  run;

word=HOLA

Since you have not provided any actual example data we cannot tell what " strange characters" might mean.

If you data contains other than simple ASCII (or EBCDIC) characters then neither Translate or Tranwrd may be appropriate. I also did not provide a complete map of characters. It was expected that you identify the characters you need to replace along with the desired characters and use them in your code with your data.

Arrays provide a quick tool. The _c is the name of the array, should not match any existing variable in your data. Then _c[i] refers to one of the character variables in your data set. The i will iterate over the number of your character variables and apply the same rule(s) to all of them.

[Abelp9]

Excuse me, I did not know that you could attach files, here I give you my test data set, all the columns are character, so I would have to change all the columns.

In the example with which I opened the post, I only put the casuistry of an Á that passes to A, but in the excel, each row is a different one, so it would be worth seeing it only with the Á and I apply it to the rest of the characters.

Thank you very much for the explanation, I am very grateful

[PaigeMiller]

---

@Abelp9 wrote:

Excuse me, I did not know that you could attach files, here I give you my test data set, all the columns are character, so I would have to change all the columns.

In the example with which I opened the post, I only put the casuistry of an Á that passes to A, but in the excel, each row is a different one, so it would be worth seeing it only with the Á and I apply it to the rest of the characters.

Thank you very much for the explanation, I am very grateful

---

Many of us will not download Excel files, as they are security risks. Do not attach files, use DATA step code to represent your data, you can type it in directly as working SAS data step, or (recommended) follow these instructions 

[Tom]

You cannot use TRANSLATE() with multi-byte characters like those.

Either use KTANSLATE() or use a different method.

 73         data example;
 74            length word word2-word5 $50;
 75            word  = "HÓLÁ";
 76            word2 = translate(word,'AO','ÁÓ');
 77            word3 = ktranslate(word,'AO','ÁÓ');
 78            word4 = basechar(word);
 79            word5 = htmlencode(word,'7bit');
 80            put (word:) (=/);
 81         run;
 
 word=HÓLÁ
 word2=HA LAO
 word3=HOLA
 word4=HOLA
 word5=HÓLÁ
 NOTE: The data set WORK.EXAMPLE has 1 observations and 5 variables.

[PaigeMiller]

You don't really need a macro. The ARRAY feature of DATA steps allows you to execute the same code on many different variables.

%LET table = TEST_MACRO_TRNSLT;
%let column = marca column2 column3 column5;
%let name_output = COSAS;

data &NAME_OUTPUT;
    set &table;
    array t &columns;
    do i=1 to dim(t);
        t(i)=tranwrd(t(i), "Á", "A");
    end;
    drop i;
run;

Good point by @ballardw to use TRANSLATE instead of TRANWRD.

[Abelp9]

Thank you very much !! but I need it in a macro to be able to call it easily in other programs without the need to be copying that code in the different programs

[PaigeMiller]

---

@Abelp9 wrote:
Thank you very much !! but I need it in a macro to be able to call it easily in other programs without the need to be copying that code in the different programs

---

If you want to use this code easily in other programs, you can get rid of the %LET statements and then save what is left to a file, and then use %INCLUDE.

So, let's assume the file you save the code to is named g:\myfolder\mysubfolder\translate_columns.sas

Then, here is how you would use it

%LET table = TEST_MACRO_TRNSLT;
%let column = marca column2 column3 column5;
%let name_output = COSAS;
%include "g:\myfolder\mysubfolder\translate_columns.sas";

Although certainly a macro would work as well (macro using the ARRAY statement logic), but why create a macro when it is not needed?

[Abelp9]

and being in another program, after putting the %include how could I call that block of code? to give it the data set that I want and the columns that I want it to translate for me?

Thank you very much

[PaigeMiller]

---

@Abelp9 wrote:

and being in another program, after putting the %include how could I call that block of code? to give it the data set that I want and the columns that I want it to translate for me?

Thank you very much

---

There is nothing to do after %include, because %include EXECUTES the code in the included file. You just run the four lines of code in my previous reply.

[Abelp9]

I have tried to do it, in a program I have put this code:

data &NAME_OUTPUT;
    set &table;
    array t &columns;
    do i=1 to dim(t);
        t(i)=tranwrd(t(i), "Á", "A");
    end;
    drop i;
run;

In another program I run this:

%LET table = TEST_MACRO_TRNSLT;
%let columns = marca cc_marca var3;
%let name_output = COSAS2;

%include "/opt/sas/data/xx/xx/xxx/macros/test_trnslt.sas";

and from another I have executed this statement but it returns the same table as the input, however when I go to the individual program where I have the code with the array and the loaded macrovariables it does return the table I expect, what could I be doing wrong ?

I share the log:

15             STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
16             NOGTITLE
17             NOGFOOTNOTE
18             GPATH=&sasworklocation
19             ENCODING=UTF8
20             options(rolap="on")
21         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22         
23         GOPTIONS ACCESSIBLE;
24         %LET table = TEST_MACRO_TRNSLT;
25         %let columns = marca cc_marca var3;
26         %let name_output = COSAS2;
27         
28         %include "/opt/sas/data/xx/xx/xx/macros/test_trnslt.sas";

NOTE: La compresión del conjunto de datos WORK.COSAS2  está deshabilitada porque aumentaría el tamaño del  conjunto de datos.
NOTE: There were 30 observations read from the data set WORK.TEST_MACRO_TRNSLT.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2022-03-15T16:22:41,348+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| 30| _DISARM| 21397504| _DISARM| 12| _DISARM| 16| _DISARM| 8| _DISARM| 6800| _DISARM| 0.000000| _DISARM| 
      0.001808| _DISARM| 1962976961.346445| _DISARM| 1962976961.348253| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: The data set WORK.COSAS2 has 30 observations and 4 variables.
NOTE: MVA_DSIO.OPEN_CLOSE| _DISARM|         STOP| _DISARM| 2022-03-15T16:22:41,349+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| 
      _DISARM| | _DISARM| 30| _DISARM| 21397504| _DISARM| 12| _DISARM| 16| _DISARM| 136| _DISARM| 6928| _DISARM| 0.000000| _DISARM| 
      0.001564| _DISARM| 1962976961.347533| _DISARM| 1962976961.349097| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: PROCEDURE| _DISARM|         STOP| _DISARM| 2022-03-15T16:22:41,349+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | 
      _DISARM| 22974464| _DISARM| 21397504| _DISARM| 12| _DISARM| 16| _DISARM| 136| _DISARM| 6928| _DISARM| 0.000000| _DISARM| 
      0.003680| _DISARM| 1962976961.345588| _DISARM| 1962976961.349268| _DISARM| 0.000000| _DISARM| | _ENDDISARM 
NOTE: Sentencia DATA used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

53         
54         GOPTIONS NOACCESSIBLE;
55         %LET _CLIENTTASKLABEL=;
56         %LET _CLIENTPROCESSFLOWNAME=;
57         %LET _CLIENTPROJECTPATH=;
58         %LET _CLIENTPROJECTNAME=;
59         %LET _SASPROGRAMFILE=;
60         
61         ;*';*";*/;quit;run;
62         ODS _ALL_ CLOSE;
63         
2                                                           Sistema SAS                                16:01 Tuesday, March 15, 2022

64         
65         QUIT; RUN;