"prxmatch" between two DB

[Tecla1]

Buongiorno vorrei utilizzare la funzione "prxmatch" tra due DB. Vorrei utilizzare questa funzione e non altre per le molteplici caratteristiche della ricerca (indifferentemente maiuscolo / minuscolo ecc...). Allego un passo di data come esempio. Tnks

data have;
a = 'emil, Nordpole, richard, Theo'; b = 'something, somethingelse'; output;
a = 'sandman, emil, peter'; b = 'whatever'; output;
a = 'samuel'; b = 'anoterexample'; output;
a = 'stephany'; b = 'lookingforwordshere'; output;
run;

data list2;
c = 'nordpole';output;
c= 'pet';output;
c='lookingforwords'; output;
run;


%macro quickndirty(varlist);

%let varlist = %upcase(&varlist.);

proc sql;
create table want2 as
select * from have
where 1=0
      %do i=1 %to %sysfunc(countw(&varlist.));
        %let next_name = %scan(&varlist., &i.);
        or upcase(a) prxmatch "&next_name."
        or upcase(b) prxmatch "&next_name."
      %end;
;quit;
%mend;

%quickndirty(&list2.)

[LinusH]

Hi, I know there is no Italian hub here at communities (maybe something to root for?).

Here is your question through Google translate:

"Hello I would like to use the "prxmatch" function between two DBs. I would like to use this function and not others due to the multiple characteristics of the search (indifferently case sensitive, etc...). I am attaching a date passage as an example."

Could please describe concretely what you need help with?

What does your current lack?

[Tecla1]

Il codice che ho allegato restituisce errore, l'ho inserito come traccia per rendere più facile spiegare quello che voglio fare. Tutti gli esempi con "PRXmatch" sono fatti inserendo direttamente nel passo di data il testo o i digit da cercare, io vorrei inserire il testo da cercare in una tabella (o DB). Grazie per la risposta.

[s_lassen]

If your dataset with list of words to look for is relatively small, if may be better to put all the words in a macro list with "|" between:

data _null_;
  do until(done);
    set list2 end=done;
    length words $200;
    call catx('|',words,c);
    end; 
  call symputx('words',words);
run;

You can then use that in a PRXMATCH function:

proc sql;
  create table want2 as
  select * from have
  where prxmatch("/(&words)/i",catx(',',a,b));
quit;

It looks to me like the words you are looking for all come right after a word boundary. If you want to implement that in the PRX expression, just change the PRXMATCH call to

prxmatch("/\b(&words)/i",catx(',',a,b));

[Tecla1]

Grazie, provo. Quindi in realtà non c'è un modo per poterlo eseguire senza il passaggio "catx".

[Patrick · Accepted solution]

I really like the power of regular expressions but based on the sample data you've provided it doesn't look to me like regular expressions are required here (they are very resource intensive).

You haven't shown us an expected result so not sure if you really want to search for words or just for strings.

Have a look at below code. Does this return what you're after?

data have;
  length a b $30;
  a = 'emil, Nordpole, richard, Theo';
  b = 'something, somethingelse';
  output;
  a = 'sandman, emil, peter';
  b = 'whatever';
  output;
  a = 'samuel';
  b = 'anoterexample';
  output;
  a = 'stephany';
  b = 'lookingforwordshere';
  output;
run;

data list2;
  length word $30;
  word= 'nordpole';
  output;
  word= 'pet';
  output;
  word='lookingforwords';
  output;
run;

proc sql;
  select t1.*,t2.*
  from 
    have t1 left join list2 t2
      /* searching for terms */
      on findw(t1.a,t2.word,', ','itrsp')>0 or findw(t1.b,t2.word,', ','itrsp')>0
      /* searching for strings */
/*      on find(t1.a,t2.word,'it')>0 or find(t1.b,t2.word,'it')>0*/
  ;
quit;

[Tecla1]

Grazie!