topic Re: PRXmatch and Macro problems in SAS Programming

PRXmatch and Macro problems

guillory21 — Thu, 26 Jul 2012 22:45:30 GMT

Thanks to everyone who has helped! I revised my code but see that the issue is with my PRXmatch not reading as I want it. I am a novice PRXmatch coder and my macro skills are ok.

Its definitely my logic with the PRXmatch.

My PRXmatch "AND statement" does not read in both macro variables.

Any help would be appreciated!!!

data _null_;

set jessica.TwoWords_&BeginMonth.;

call symput('word1'||left(_n_), Words1);

call symput('word2'||left(_n_), Words2);

CALL SYMPUT( 'TOTOBS' , _N_ );

run;

/*Runing prxmatch for word groupings*/

/*This looks at user defined particular words in the string*/

%MACRO LOOP;

data temp;

set jessica.PercentImp_&BeginMonth.&EndMonth. ;

%do i=1 %to &TOTOBS;

if prxmatch("/&&word1&i./",search_term) gt 0 and

prxmatch("/&&word2&i./",search_term) gt 0 then output ;

%end;

run;

%mend;

%loop;

Re: PRXmatch and Macro problems

ballardw — Thu, 26 Jul 2012 23:00:31 GMT

Have you tried &&word1&i without the trailing "."? I've seen some instances where the trailing period causes logic to fail.

Re: PRXmatch and Macro problems

art297 — Thu, 26 Jul 2012 23:01:38 GMT

Please post the two files. Your first data _null_ statement has me questioning what

jessica.TwoWords_&BeginMonth.; looks like.

Art

Re: PRXmatch and Macro problems

Haikuo — Fri, 27 Jul 2012 03:26:29 GMT

Hi,

Your code looks fine, so like Art said, sample data will be needed to do the trouble shooting. One small suggestion to improve your efficiency:

CALL SYMPUT( 'TOTOBS' , _N_ );

You probably already realize that you are refreshing macro variable 'totobs' n times, if you have multimillion records, that would result in significant amount of waste. Try to use nobs= or end=last + if last to avoid it.

Haikuo

Re: PRXmatch and Macro problems

Peter_C — Fri, 27 Jul 2012 20:02:30 GMT

another suggestion :

use call SYMPUTX() instead of call SYMPUT()

because it will remove leading/trailing blanks from the value in _N_

Not only leading blanks can be a problem, trailing blanks will affect you too in

"/&&word1&i./"

Re: PRXmatch and Macro problems

Patrick — Sat, 28 Jul 2012 04:11:36 GMT

I've added 2 changes to your code to make it work with the sample data I've created.

I've added a strip() function around your "words" variables to get rid of leading and trailing blanks and I've added a 'i' to the regular expression to make the search case insensitive.

Then I've got inspired and added a variant which doesn't need macro coding.

HTH

Patrick

data SearchTerms;
infile datalines dsd;
input words1 $ words2 $;
datalines;
word b,word x
word a,word b
word z,word a
;
run;

data have;
search_term='String with word A and word B in it'; output;
search_term='String with word B and word A in it'; output;
search_term='String with word A and word C in it'; output;
search_term='String with word A in it'; output;
search_term='String with word B in it'; output;
run;

/* variant 1 with macro code */
data _null_;
set SearchTerms;
call symput('word1'||left(_n_), strip(Words1));
call symput('word2'||left(_n_), strip(Words2));
CALL SYMPUT( 'TOTOBS' , _N_ );
run;

%MACRO LOOP;
data temp;
set have ;
%do i=1 %to &TOTOBS;
if prxmatch("/&&word1&i./i",search_term) gt 0 and
prxmatch("/&&word2&i./i",search_term) gt 0 then output ;
%end;
run;
%mend;
%loop;

/* variant 2 without macro code */
data test;

if _n_=1 then
do;
    retain _prxKey '1';
    dcl hash h1(multidata:'y');
    h1.defineKey('_prxKey');
    h1.defineData('_prxID');
    h1.defineDone();
    do _i=1 to nobs;
      set SearchTerms(keep=words1 words2) nobs=nobs point=_i;
      _prxID=prxparse(cats('/(?=.*',words1,')(?=.*',words2,')/i'));
      h1.add();
    end;

drop _: words:;
end;

set have;

_rc=h1.find();
do while(_rc=0);
if prxmatch(_prxID,search_term) then output;
_rc=h1.find_next();
end;

run;

Re: PRXmatch and Macro problems

Tom — Sat, 28 Jul 2012 19:08:34 GMT

Doesn't look like a problem that needs either macro logic or prxmatch function. Indexw function should work fine.

data want;

set jessica.PercentImp_&BeginMonth.&EndMonth ;

found=0;

do i=1 to nobs while (not found);

set jessica.TwoWords_&BeginMonth nobs=nobs point=i;

found=indexw(search_term,trim(words1))

and indexw(search_term,trim(words2))

;

end;

if found then output;

run;

Re: PRXmatch and Macro problems

Patrick — Sun, 29 Jul 2012 00:32:27 GMT

If you're using prxmatch then the "words" could also be RegEx expressions. Or one could just allow for wildcards in the "words" and then translate these wildcards into a corresponding RegEx expression.

Re: PRXmatch and Macro problems

Tom — Sun, 29 Jul 2012 04:19:43 GMT

I usually find it a lot easier to debug generated code when I use a data step to write the code to file. Then you can look at the file and make sure the syntax is correct.

filename code temp;

data _null_;

set jessica.TwoWords_&BeginMonth. end=eof;

if _n_=1 then put 'if ' @;

else put 'or ' @ ;

put "(prxmatch('" words1 +(-1) "',search_term) "

"and prxmatch('" words2 +(-1) "',search_term))"

;

if eof then put ' then output' / ';' ;

run;

data temp;

set jessica.PercentImp_&BeginMonth.&EndMonth. ;

%inc code / source2 ;

run;

Re: PRXmatch and Macro problems

Patrick — Sun, 29 Jul 2012 07:07:56 GMT

Agree.