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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.