DATA Step, Macro, Functions and more

PRXmatch and Macro problems

Reply
Occasional Contributor
Posts: 6

PRXmatch and Macro problems

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. Smiley Sad 

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;

Super User
Posts: 11,343

Re: PRXmatch and Macro problems

Posted in reply to guillory21

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

PROC Star
Posts: 7,468

Re: PRXmatch and Macro problems

Posted in reply to guillory21

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

jessica.TwoWords_&BeginMonth.; looks like.

Art

Respected Advisor
Posts: 3,156

Re: PRXmatch and Macro problems

Posted in reply to guillory21

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

Valued Guide
Posts: 2,177

Re: PRXmatch and Macro problems

Posted in reply to guillory21

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./"

Respected Advisor
Posts: 4,173

Re: PRXmatch and Macro problems

Posted in reply to guillory21

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;

Super User
Super User
Posts: 7,039

Re: PRXmatch and Macro problems

Posted in reply to guillory21

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;

Respected Advisor
Posts: 4,173

Re: PRXmatch and Macro problems

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.

Super User
Super User
Posts: 7,039

Re: PRXmatch and Macro problems

Posted in reply to guillory21

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;

Respected Advisor
Posts: 4,173

Re: PRXmatch and Macro problems

Agree.

Ask a Question
Discussion stats
  • 9 replies
  • 1226 views
  • 1 like
  • 7 in conversation