Re: use of tranwrd and translate on variables or in a loop

On Tue, 15 Jul 2008 05:27:56 -0700, n <nikhil.abhyankar@GMAIL.COM> wrote:

>Hi all;
>
>can the arguments of tranwrd and translate be variables?

Why not?

>
>
>I have a dataset with 2 columns. The first has the names of
>companies.
>some of the names contain terms like ltd, co, limited etc. The second
>variable has the list of such terms to be removed.
>I need to remove each of the terms from each of the company name
>wherever they occur.
>I tried using tranwrd and translate but the replacement was only
>rowwise.

TRANSLATE is not appropriate here (unless you want "coca cola" to become "ca
la".

>
>
>ie
>
>Name Terms_to_remove Result_obtained Result_desired
>abc co ltd co abc ltd abc
>abc co ltd abc co abc
>abc limited limited abc abc
>abc limited abc limited abc
>
>
>
>Please tell me how could I do this using translate or tranwrd.
>Is there any other better way to do this?
>
>
>Thanks and regards

There are two problems with your data structure. First, two vectors have
been placed side-by-side in one data set, implying a term-by-term
correspondence which is false. Second, no unique IDs differentiate
repetitions of NAME.

data have;
infile cards missover;
input Name : & $11. Terms_to_remove $;
cards;
abc co ltd co
abc co ltd ltd
abc limited limited
abc limited
;

Create unique line numbers:

data numbered;
line + 1;
set have;
run;

Cross the two vectors to associate each name with each removal term:

proc sql;
create view allpairs as
select *
from (select line, name
from numbered)
cross join
(select Terms_to_remove
from numbered
where Terms_to_remove is not null)
order by line, name;
quit;

Now apply TRANWRD:

data desired(keep = line name Result_obtained);
do _i = 1 by 1 until (last.line);
set allpairs;
by line;
if _i =1 then Result_obtained = name;
Result_obtained = tranwrd(Result_obtained,trim(Terms_to_remove),'');
end;
run;

Result:

Result_
line Name obtained

1 abc co ltd abc
2 abc co ltd abc
3 abc limited abc
4 abc limited abc

On Jul 16, 2:38*am, hs AT dc-sug DOT org ("Howard Schreier)" wrote:
> On Tue, 15 Jul 2008 05:27:56 -0700, n <nikhil.abhyan...@GMAIL.COM> wrote:
> >Hi all;
>
> >can the arguments of tranwrd and translate be variables?
>
> Why not?
>
>
>
> >I have a dataset with 2 columns. The first has the names of
> >companies.
> >some of the names contain terms like ltd, co, limited etc. The second
> >variable has the list of such terms to be removed.
> >I need to remove each of the terms from each of the company name
> >wherever they occur.
> >I tried using tranwrd and translate but the replacement was only
> >rowwise.
>
> TRANSLATE is not appropriate here (unless you want "coca cola" to become "ca
> la".
>
>
>
>
>
>
>
> >ie
>
> >Name * *Terms_to_remove Result_obtained Result_desired
> >abc co ltd * * *co * * *abc ltd abc
> >abc co *ltd * * abc co *abc
> >abc limited * * limited abc * * abc
> >abc limited * * * * * * abc limited * * abc
>
> >Please tell me how could I do this using translate or tranwrd.
> >Is there any other better way to do this?
>
> >Thanks and regards
>
> There are two problems with your data structure. First, two vectors have
> been placed side-by-side in one data set, implying a term-by-term
> correspondence which is false. Second, no unique IDs differentiate
> repetitions of NAME.
>
> * *data have;
> * *infile cards missover;
> * *input Name : & $11. Terms_to_remove $;
> * *cards;
> abc co ltd * * *co
> abc co ltd * * *ltd
> abc limited * * limited
> abc limited
> * *;
>
> Create unique line numbers:
>
> * *data numbered;
> * *line + 1;
> * *set have;
> * *run;
>
> Cross the two vectors to associate each name with each removal term:
>
> * *proc sql;
> * *create view allpairs as
> * * select *
> * * *from (select line, name
> * * * * * * from numbered)
> * * * * * cross join
> * * * * * (select Terms_to_remove
> * * * * * * from numbered
> * * * * * * where Terms_to_remove is not null)
> * * order by line, name;
> * *quit;
>
> Now apply TRANWRD:
>
> * *data desired(keep = line name Result_obtained);
> * *do _i = 1 by 1 until (last.line);
> * * * set allpairs;
> * * * by line;
> * * * if _i =1 then Result_obtained = name;
> * * * Result_obtained = tranwrd(Result_obtained,trim(Terms_to_remove),'');
> * * * end;
> * *run;
>
> Result:
>
> * * * * * * * * * * * * * Result_
> * *line * * * Name * * * *obtained
>
> * * *1 * * abc co ltd * * * abc
> * * *2 * * abc co ltd * * * abc
> * * *3 * * abc limited * * *abc
> * * *4 * * abc limited * * *abc- Hide quoted text -
>
> - Show quoted text -

but is this feasible when I have a million names in the column of
names?