topic Re: SAS Macros in SAS Programming

SAS Macros

cmoore — Tue, 11 Apr 2017 10:29:24 GMT

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;

Re: SAS Macros

PeterClemmensen — Tue, 11 Apr 2017 10:48:42 GMT

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;

Re: SAS Macros

cmoore — Tue, 11 Apr 2017 11:01:55 GMT

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

Re: SAS Macros

PeterClemmensen — Tue, 11 Apr 2017 11:04:42 GMT

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;

Re: SAS Macros

Patrick — Tue, 11 Apr 2017 11:10:50 GMT

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

Re: SAS Macros

ballardw — Tue, 11 Apr 2017 14:23:02 GMT

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;