BookmarkSubscribeRSS Feed
guillory21
Calcite | Level 5

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;

9 REPLIES 9
ballardw
Super User

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

art297
Opal | Level 21

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

jessica.TwoWords_&BeginMonth.; looks like.

Art

Haikuo
Onyx | Level 15

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

Peter_C
Rhodochrosite | Level 12

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

Patrick
Opal | Level 21

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;

Tom
Super User Tom
Super User

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;

Patrick
Opal | Level 21

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.

Tom
Super User Tom
Super User

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;

Patrick
Opal | Level 21

Agree.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 3231 views
  • 1 like
  • 7 in conversation