BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cmoore
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

5 REPLIES 5
PeterClemmensen
Tourmaline | Level 20

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;
cmoore
Obsidian | Level 7

Yes I think this will work perfectly. I will just add each additional country to the Vars[i] line. Thanks for your help.

PeterClemmensen
Tourmaline | Level 20

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;
Patrick
Opal | Level 21

@cmoore

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;

 

ballardw
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1012 views
  • 6 likes
  • 4 in conversation