topic Re: Macro isn't working in the second column in SAS Programming

Macro isn't working in the second column

Buzzy_Bee — Sat, 01 Aug 2020 11:29:18 GMT

I've created some code that concatenates the mailing address variables together and the postal address variables, and then compares the two to see if they are the same. Some need to be stripped of things like "the manager" or "C/O" in front of the addresses, so I created a macro that is supposed to go over both the concatenated fields and remove these, however, it is only working on the concatenated Mail. I tried %type(Post Mail); which I'd seen on another example, but that doesn't work either. Any thoughts please?

data addresses;
infile datalines dlm=',' dsd truncover;
input Mail_addr1 :$30. Mail_addr2 :$10. Post_addr1 :$30. Post_addr2 :$10.;
datalines;
10 Downing St,London,C/O 10 Downing St,London
C/O 1600 Pennsylvania Avenue,Washington,1600 Pennsylvania Avenue,Washington
The Manager 12 North St,Anytown,12 North St,Anytown
;

%macro type(type);
data concatenate;
set addresses;
Mail = cats(Mail_addr1,Mail_addr2);
Post = cats(post_addr1,post_addr2);

&type = strip(tranwrd(&type,'C/O ',''));
&type = strip(tranwrd(&type,'The Manager ',''));

if Mail = Post then same = 'yes'; else same='no';
drop Mail_addr: Post_addr:;
run;
%mend;
%type(Post);
%type(Mail);

Re: Macro isn't working in the second column

PaigeMiller — Sat, 01 Aug 2020 19:08:28 GMT

When you run

%type(Post)

this creates a data set CONCATENATE where Post is cleaned up properly. Then you run

%type(Mail)

and this creates a brand new data set CONCATENATE where Mail is cleaned up properly, overwriting the previous data set CONCATENATE where Post is cleaned up properly. The end result is a data set CONCATENATE where only Mail is cleaned up properly.

You need to re-design your logic here. A method that is superior to using macros in this case is to use an ARRAY inside data set CONCATENTATE. ARRAYs can loop over variables and execute the same portion of code on each variable in the array.

data concatenate;
set addresses;
Mail = cats(Mail_addr1,Mail_addr2);
Post = cats(post_addr1,post_addr2);
array column(*) mail post; /* You can have more than two columns here */
do i=1 to dim(column);
column(i) = strip(tranwrd(column(i),'C/O ',''));
column(i) = strip(tranwrd(column(i),'The Manager ',''));
end;
if Mail = Post then same = 'yes'; else same='no';
drop Mail_addr: Post_addr: i;
run;

Re: Macro isn't working in the second column

Buzzy_Bee — Sat, 01 Aug 2020 20:23:43 GMT

Thanks PaigeMiller. That works perfectly and it's easier to read than using macros with fewer lines of code.