topic Re: Merging on a Partial/Prefix Key in SAS Programming

Merging on a Partial/Prefix Key

CurtisSmithDCAA — Fri, 19 May 2017 17:12:03 GMT

I don't know why I have not encountered this before, but...

I need to merge two SAS data sets, both with the same key variable, but in the lookup data set the key may only contain the first few characters of the key in the master data set. For some rows, the key values in both data sets will exactly match. In other rows, the lookup data set may only be partial, but still exact for as many characters as are in the key value. When the lookup data set has only part of the full key value (essentially, a prefix), it will still be unique: that is, no two rows in the lookup data set will have the same key value. So, the merge would need to be similar to performing a WHERE statement using the LIKE operator.

I was thinking of pursuing fuzzy matching, but that process seems too impercise for my needs. I don't risk incorrect matching as long as I can match based on the first few characters of the key in the lookup data set.

Any ideas on the right direction to take?

Re: Merging on a Partial/Prefix Key

art297 — Fri, 19 May 2017 17:36:45 GMT

Can you provide examples of the two data steps?

Art, CEO, AnalystFinder.com

Re: Merging on a Partial/Prefix Key

CurtisSmithDCAA — Fri, 19 May 2017 17:43:52 GMT

The two data sets might look something like this:

Master data set:

Contract Dollars

ABC12345 1000

BCD98765 5000

XYZ99999 100

Lookup data set:

Contract Customer

ABC12345 Army

BCD Navy

XYZ99999 Air Force

An exact match using contract as the BY variable will merge contracts ABC12345 and XYZ99999, but not contract BCD. but I want to match it with customer "Navy".

Re: Merging on a Partial/Prefix Key

art297 — Fri, 19 May 2017 17:51:46 GMT

Here is one way:

data master;
  input Contract $ Dollars;
  cards;
ABC12345        1000
BCD98765        5000
XYZ99999        100
;

data lookup;
  input Contract$ Customer $;
  cards;
ABC12345       Army
BCD                 Navy
XYZ99999       Air Force
;

proc sql noprint;
  create table want as
    SELECT a.*, b.Customer
      FROM master a
       left JOIN lookup b
         ON a.Contract LIKE catt('%', b.Contract, '%') 
  ;
quit;

Art, CEO, AnalystFinder.com

Re: Merging on a Partial/Prefix Key

CurtisSmithDCAA — Fri, 19 May 2017 17:56:00 GMT

Thanks, I had a hunch the answer would invlove sharpening my SQL skills. I will give this a whirl later today.

Re: Merging on a Partial/Prefix Key

ballardw — Fri, 19 May 2017 22:34:53 GMT

If you search the Base SAS Programming board for "fuzzy match" you'll find a number of related posts and solutions.

Re: Merging on a Partial/Prefix Key

Ksharp — Sat, 20 May 2017 03:55:17 GMT

Or Perl Regurlar Expression could you more power.

data master;
  input Contract $ Dollars;
  cards;
abc        1000
BCD98765        5000
XYZ99999        100
;

data lookup;
  input Contract$ Customer $;
  cards;
ABC12345       Army
BCD                 Navy
XYZ99999       Air Force
;

proc sql ;
    SELECT a.*, b.Customer,b.Contract as b
      FROM master a
       inner JOIN lookup b
         ON prxmatch(cats('/^',a.Contract,'/i'),b.Contract) or 
            prxmatch(cats('/^',b.Contract,'/i'),a.Contract)
  ;
quit;

Re: Merging on a Partial/Prefix Key

CurtisSmithDCAA — Tue, 23 May 2017 13:53:01 GMT

I used this method on my 12.4 million row master table. After 24 hours I stopped it. It processed about 1/4 of the master table, but does have a match for each row. I'm going to visually inspect the matching to see if it appears to be accurate. Then, I may subset the master table and run the process in pieces, unless anyone has an idea how to make this run more efficiently. I could try indexing both the master table and the lookup table, but I don't know if indexes matter to this matching process. Thanks.

Re: Merging on a Partial/Prefix Key

Ksharp — Tue, 23 May 2017 13:57:42 GMT

If you have a big table, then it is not suited for SQL.

Hash Table + Data Step might be more efficient.

What size of these two tables ?

Re: Merging on a Partial/Prefix Key

art297 — Tue, 23 May 2017 14:01:55 GMT

@CurtisSmithDCAA: Did you try the LIKE operator or the regular expression method? I would think that 12.4 Million records should not have posed any problem, in itself, for PROC SQL.

Art, CEO, AnalystFinder.com

Re: Merging on a Partial/Prefix Key

CurtisSmithDCAA — Tue, 23 May 2017 14:05:56 GMT

The master table is 12.4 million rows. The lookup table is about 1,000 rows.

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Re: Merging on a Partial/Prefix Key

Ksharp — Tue, 23 May 2017 14:13:29 GMT

Yeah. Try @art297 's SQL + LIKE firstly .

Re: Merging on a Partial/Prefix Key

Ksharp — Tue, 23 May 2017 14:16:39 GMT

Also need consider this scenario .

data master;
  input Contract $ Dollars;
  cards;
BCD        5000
;

data lookup;
  input Contract$ Customer $;
  cards;
XXBCDXX                Navy
;

Re: Merging on a Partial/Prefix Key

CurtisSmithDCAA — Tue, 23 May 2017 14:29:21 GMT

The SQL with the LIKE operator did run after several hours but the resulting table had more rows than the master table. I have not had a chance to diagnose why. The method with the Perl Regurlar Expression seems to work without creating addiditonal rows, but at the rate it was running would take about 4 days to complete.

Re: Merging on a Partial/Prefix Key

art297 — Tue, 23 May 2017 14:45:21 GMT

@CurtisSmithDCAA: I think you just have to add DISTINCT to your select statement. Otherwise, you'll get multiple rows if there are both partial and full matches. e.g.:

data master;
  input Contract $ Dollars;
  cards;
ABC12345        1000
BCD98765        5000
XYZ99999        100
;

data lookup;
  input Contract$ Customer $;
  cards;
ABC12345       Army
BCD            Navy
BCD98765       Navy
XYZ99999       Air Force
;

proc sql noprint;
  create table want as
    SELECT distinct a.*, b.Customer
      FROM master a
       left JOIN lookup b
         ON a.Contract LIKE catt('%', b.Contract, '%') 
  ;
quit;

Art, CEO, AnalystFinder.com

Re: Merging on a Partial/Prefix Key

CurtisSmithDCAA — Tue, 23 May 2017 15:22:56 GMT

Thanks. I'll give distinct a try this evening and let it run overnight. I'm
a Data Step guy, learning SQL as I go.

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Re: Merging on a Partial/Prefix Key

CurtisSmithDCAA — Tue, 23 May 2017 15:47:56 GMT

Yes, I am starting to find some items in the lookup table that are more
complicated than I initially thought. When I find a solution that works, I
will have a great tool for the future.

Thanks.

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Re: Merging on a Partial/Prefix Key

Ksharp — Wed, 24 May 2017 12:12:35 GMT

@art297

I think I only consider about START WITH ( =: ), you also consider about CONTAINS

E.X.

XXBBXX

your sql will matched it, but PRX would not .

Re: Merging on a Partial/Prefix Key

Ksharp — Wed, 24 May 2017 13:32:53 GMT

Here is Hash Table solution ,maybe could get you a little faster.

NOTE: I matched it with START WITH , not all like Arthur's CONTAINS .

data master;
  input Contract $ Dollars;
  cards;
ABC        1000
BCD98765        5000
XYZ99999        100
;

data lookup;
  input Contract$ Customer $;
  cards;
ABC12345       Army
BCD                 Navy
XYZ99999       Air Force
;

data want;
 if _n_=1 then do;
  if 0 then set lookup(rename=(Contract=_Contract));
  declare hash h(dataset:'lookup(rename=(Contract=_Contract))',multidata:'y');
  declare hiter hi('h');
  h.definekey('_Contract');
  h.definedata('_Contract','Customer ');
  h.definedone();
 end;
set master;
do while(hi.next()=0);
 if Contract =: strip(_Contract) or _Contract =: strip(Contract) then output;
end;
run;

Re: Merging on a Partial/Prefix Key

Ksharp — Wed, 24 May 2017 13:49:54 GMT

data master;
  input Contract $ Dollars;
  cards;
ABC        1000
BCD98765        5000
XYZ99999        100
;

data lookup;
  input Contract$ Customer $;
  cards;
ABC12345       Army
BCD                 Navy
XYZ99999       Air Force
;

data want;
 if _n_=1 then do;
  if 0 then set lookup(rename=(Contract=_Contract));
  declare hash h(dataset:'lookup(rename=(Contract=_Contract))',multidata:'y');
  declare hiter hi('h');
  h.definekey('_Contract');
  h.definedata('_Contract','Customer ');
  h.definedone();
 end;
set master;
do while(hi.next()=0);
 if strip(Contract) =: _Contract or strip(_Contract) =: Contract then output;
end;
run;