DATA Step, Macro, Functions and more

SAS Macros

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

SAS Macros

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;


Accepted Solutions
Solution
‎04-11-2017 07:02 AM
PROC Star
Posts: 554

Re: SAS Macros

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


All Replies
Solution
‎04-11-2017 07:02 AM
PROC Star
Posts: 554

Re: SAS Macros

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;
Contributor
Posts: 32

Re: SAS Macros

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

PROC Star
Posts: 554

Re: SAS Macros

If you are trying do do this over ALL the character variables in your dataset do this Smiley Happy

 

data want;
	set have;
	Array Vars[*] _CHARACTER_;

	do i = 1 to dim(Vars);
		Vars[i] = tranwrd(Vars[i], "USA.", "-USA: ");
	end;

	drop i;
run;
Respected Advisor
Posts: 3,907

Re: SAS Macros

@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;

 

Super User
Posts: 10,550

Re: SAS Macros

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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