topic Re: Match table A string with Table B keyword in SAS Enterprise Guide

Match table A string with Table B keyword

cheema11 — Mon, 26 Sep 2022 06:50:47 GMT

Hi All,

New to SAS, need some help in string matching. I have Table A which have millions of records. Table B has less than 5K records.

I want to match Address field of table A with Keywords field of table B. Currently doing Proc SQL with Like operation which is very slow and query takes ages. There is a cross join between both the tables. Both tables are in SAS.

Wanted to know the efficient way of doing this. Using SAS EG V 8.2.

It will be a great help. Thanks in advance.

Re: Match table A string with Table B keyword

PeterClemmensen — Mon, 26 Sep 2022 06:48:40 GMT

Are these both SAS Data Sets?

Re: Match table A string with Table B keyword

cheema11 — Mon, 26 Sep 2022 06:49:53 GMT

Yes both are sas data sets.

Re: Match table A string with Table B keyword

PeterClemmensen — Mon, 26 Sep 2022 07:01:19 GMT

Ok. What if a string in table a matches multiple substrings in table b?

Re: Match table A string with Table B keyword

cheema11 — Mon, 26 Sep 2022 07:08:30 GMT

Return the one with longest string length. If string length is same, then return any one of them.

Re: Match table A string with Table B keyword

PeterClemmensen — Mon, 26 Sep 2022 07:09:57 GMT

The longest string from table b, right?

Re: Match table A string with Table B keyword

cheema11 — Mon, 26 Sep 2022 07:11:18 GMT

Yes longest string from Table B

Re: Match table A string with Table B keyword

Patrick — Mon, 26 Sep 2022 09:26:30 GMT

Thanks @PeterClemmensen for asking all the questions.

@cheema11 Please provide next time sample data via a working SAS data step so we don't have to spend time to create such data in order to provide tested code.

I believe below should do what you're asking for. It's still a resource intensive process but certainly better than a cartesian join.

data master;
  id=_n_;
  infile datalines truncover;
  input address $40.;
  datalines;
alias_address: abc@swy.com.au
alias_address: pa@baxa.com
alias_address: baxa@test.com
alias_address: 123456789@baxa.com
;

data lookup;
  infile datalines truncover;
  input keyword $ category $;
  datalines;
A swy
A baxa
B 12345678
C test
;

data lookup;
  set lookup;
  _str_len=length(category);
run;

proc sort data=lookup;
  by descending _str_len;
run;

data want(drop=_:);
  set master;
  _r_len=-1;
  do _i=1 to _nobs;
    set lookup point=_i nobs=_nobs;
    if _r_len>_str_len then leave;
    if find(address,category,'it') then
      do;
        output;
        _r_len=_str_len;
      end;
  end;
run;

proc print data=want;
run;

Re: Match table A string with Table B keyword

cheema11 — Tue, 27 Sep 2022 02:00:53 GMT

Thanks for the response on this. Really appreciate for the help. @PeterClemmensen @Patrick

Re: Match table A string with Table B keyword

cheema11 — Mon, 03 Oct 2022 00:43:43 GMT

Hi @Patrick.
In response to your query...."What if a string in table A matches multiple substrings in table B?" My answer was "Return the one with longest string length in Table B. If string length is same, then return any one of them."

What do i need to change in the script if I want to return the string with shortest length in Table B?

Re: Match table A string with Table B keyword

Patrick — Mon, 03 Oct 2022 07:26:28 GMT

@cheema11 wrote:
Hi @Patrick.
In response to your query...."What if a string in table A matches multiple substrings in table B?" My answer was "Return the one with longest string length in Table B. If string length is same, then return any one of them."

What do i need to change in the script if I want to return the string with shortest length in Table B?

@PeterClemmensen did all the work getting the full requirements from you.