How do I remove a list of words from a string using a macro ?

[Rob_HILL_79]

Hi, hope someone can help ...

I have a dataset named 'RA_RULES' containing one column of RULE_ID's:

RULE_ID

RAE001
RAE002
RAE003
RAE007
RAE011
RAE012
RAE013
RAE014
RAE015
RAE016

.....

I have a second dataset named BASE_DATA which holds a column of concatenated rules named 'DQA_ISSUES':

DQA_ISSUES:

RAE012, RAE013, RAE016, PP001
DQA1917
DQA1896, DQA1896, DQA1912, RAE007
DQA1898, RAE003
PP003
DQA1898
PP001

If possible I need to remove any RAE rule from this column to give me:

DQA_ISSUES:

PP001
DQA1917
DQA1896, DQA1896, DQA1912,
DQA1898
PP003
DQA1898
PP001

Does anyone know how I could do this ? I can do it by hard coding the rules in a do loop but I was hoping I could pull in the rules from the dataset in step one and run it through a macro. Any tips please ?

[Rob_HILL_79]

See below code which works using hard coded values:

data DQA.BASE_DATA3;

set DQA.BASE_DATA2;

DQA_ISSUES = DQA_ISSUES;

length word $27 ;

IF DQA_RA_BACKLOG = 'N' THEN

do word = 'RAE001,',

'RAE002,',

'RAE003,',

'RAE007,',

'RAE011,',

'RAE012,',

'RAE013,',

'RAE014,',

'RAE015,',

'RAE016,',

'RAE017,',

'RAE021,',

'RAE022,',

'RAE024,',

'RAE030,',

'RAE031,',

'RAE033,',

'RAE035,',

'RAE037,',

'RAE038,',

'RAE040,',

'RAE044,',

'RAE045,',

'RAE046,',

'RAE047,',

'RAE048,',

'RAE049,';

　

DQA_ISSUES = tranwrd(' '||DQA_ISSUES, ' '||strip(word)||' ', ' ');

end;

DQA_ISSUES = compbl(DQA_ISSUES);

drop word ;

run;

[PaigeMiller · Accepted solution]

Don't jump to the conclusion that macros are needed here. Usually, in a data step, an ARRAY will do the work, without the complications of using macros.

data list;
input rule_id $;
cards;
RAE001
RAE002
RAE003
RAE007
RAE011
RAE012
RAE013
RAE014
RAE015
RAE016
;
proc transpose data=list out=listt prefix=rule;
var rule_id;
run;

data dqa_issues;
	input string :& $100.;
	cards;
RAE012, RAE013, RAE016, PP001
DQA1917
;
data dqa_issues1;
    if _n_=1 then set listt;
	array rule $ rule:;
	set dqa_issues;
	string=compress(string,',');
	do i=1 to dim(rule);
	    string=tranwrd(string,trim(rule(i)),' ');
	end;
	string=left(compbl(string));
	drop i rule:;
run;

[Rob_HILL_79]

Wow - Thanks so much for the speedy response, it worked a treat ! This code will come in really useful as I can adapt it for a few other  scenarios In other programs I have. Thanks again for this really appreciated.  

[novinosrin]

data list;
input rule_id $;
cards;
RAE001
RAE002
RAE003
RAE007
RAE011
RAE012
RAE013
RAE014
RAE015
RAE016
;

data dqa_issues;
	input string :& $100.;
	cards;
RAE012, RAE013, RAE016, PP001
DQA1917
DQA1896, DQA1896, DQA1912, RAE007
DQA1898, RAE003
PP003
DQA1898
PP001
;

data want;
 if _n_=1 then do;
  if 0 then set list;
   dcl hash H (dataset:'list') ;
   h.definekey  ("rule_id") ;
   h.definedone () ;
 end;
 set dqa_issues;
 length want $100;
 do _n_=1 to countw(string,', ');
  if h.check(key:scan(string,_n_,', '))=0 then continue;
  want=catx(', ',want,scan(string,_n_,', '));
 end;
 drop rule_id;
run;

string	want
RAE012, RAE013, RAE016, PP001	PP001
DQA1917	DQA1917
DQA1896, DQA1896, DQA1912, RAE007	DQA1896, DQA1896, DQA1912
DQA1898, RAE003	DQA1898
PP003	PP003
DQA1898	DQA1898
PP001	PP001

[Rob_HILL_79]

Wow - thanks so much for the speedy response it worked a treat ! This code will come in really useful as I can adapt it for a few other scenarios in other programs I have. Thanks once again for this, really appreciated.

[novinosrin]

Hi @Rob_HILL_79  It appears you are new to SAS forum. So, hearty welcome. Please close the thread by marking a solution as answered. We the community, generally expect to honor the 1st person who responded to be given the credit and so I would request you to mark Paigemiller's solution.

Of course many a time, the original poster would prefer an answer that best meets their needs and mark that very solution, however in this case it's apparent both did in your case. So easy to mark and complete. Cheers!

[Rob_HILL_79]

Thanks for the kind welcome and also the answer to my post in record time ! I've accepted the solution as requested. Once again many thanks.