I need to use the tranwrd function to cleanse some data in column. Please see the code below. I need to copy and repeat the code multiple times to change the country. Is there are a quicker way to change the country via a macro rather than writing repeat coding lines.
data test (compress=yes);
set test;
Aboriginal_Law=tranwrd(Aboriginal_Law, "USA.", "-USA: ");
antitrust=tranwrd(antitrust, "USA.", "-USA: ");
Arbitration__International_=tranwrd(Arbitration__International_, "USA.", "-USA: ");
Asset_Finance=tranwrd(Asset_Finance, "USA.", "-USA: ");
Aviation=tranwrd(Aviation, "USA.", "-USA: ");
Banking___Finance=tranwrd(Banking___Finance, "USA.", "-USA: ");
run;
Something like this?
data have;
input Aboriginal_Law :$18. antitrust :$18. Arbitration__International_ :$18. Asset_Finance :$18. Aviation :$18. Banking___Finance :$18.;
datalines;
blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla
blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla
blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla
;
data want;
set have;
Array Vars[*] Aboriginal_Law antitrust Arbitration__International_ Asset_Finance Aviation Banking___Finance;
do i = 1 to dim(Vars);
Vars[i] = tranwrd(Vars[i], "USA.", "-USA: ");
end;
drop i;
run;
Something like this?
data have;
input Aboriginal_Law :$18. antitrust :$18. Arbitration__International_ :$18. Asset_Finance :$18. Aviation :$18. Banking___Finance :$18.;
datalines;
blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla
blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla
blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla
;
data want;
set have;
Array Vars[*] Aboriginal_Law antitrust Arbitration__International_ Asset_Finance Aviation Banking___Finance;
do i = 1 to dim(Vars);
Vars[i] = tranwrd(Vars[i], "USA.", "-USA: ");
end;
drop i;
run;
Yes I think this will work perfectly. I will just add each additional country to the Vars[i] line. Thanks for your help.
If you are trying do do this over ALL the character variables in your dataset do this 🙂
data want;
set have;
Array Vars[*] _CHARACTER_;
do i = 1 to dim(Vars);
Vars[i] = tranwrd(Vars[i], "USA.", "-USA: ");
end;
drop i;
run;
You can also add the countries via a nested loop.
data have;
input Aboriginal_Law :$18. antitrust :$18. Arbitration__International_ :$18. Asset_Finance :$18. Aviation :$18. Banking___Finance :$18.;
datalines;
blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla
blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla
blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla blablaUSA.bla
blablaAUS.bla blablaAUS.bla blablaAUS.bla blablaAUS.bla blablaAUS.bla blablaAUS.bla
;
run;
data want;
set have;
Array Vars[*] Aboriginal_Law antitrust Arbitration__International_ Asset_Finance Aviation Banking___Finance;
do _country='USA','AUS';
do i = 1 to dim(Vars);
Vars[i] = tranwrd(Vars[i], strip(_country)||"." , "-"||strip(_country)||": " );
end;
end;
drop i _country;
run;
You may need to be aware that you are replacing 4 character with 6. If your original variable is not long enough that it has two unused character spaces at the end you may find some of your data truncated.
data example; length var $ 8; var= 'ThisUSA.'; var= tranwrd(var,'USA.','-USA: '); put var=; run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.