Merging on a Partial/Prefix Key

[CurtisSmithDCAA]

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. -##

[Ksharp]

@art297

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

E.X.

BB

XXBBXX

your sql will matched it, but PRX would not .

[CurtisSmithDCAA]

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. -##

[Ksharp]

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;

[Ksharp]

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;

[CurtisSmithDCAA]

I want to thank each of you who have posted a solution. Each has resulted in finding a match for each row, which is the good news. The bad news is that each soultion has also resulted in more rows in the output than in the master input dataset. I have been testing with a small test dataset and will continue to do so, hoping to fine tune one of the solutions to get a good output. Then, I'll run against the full master input dataset and let it run over a weekend if necessary.

[art297]

@CurtisSmithDCAA: Can you attach a small datafile that produces such multiple records? I presume you tried the SQL solution using the distinct statement. That "should" have worked.

However, @Ksharp mentioned something that ought to be considered anyway. The approach I mentioned look for any records that "contained" the search string. Did you want/need that, or did you only want to match records that started with the matching string?

Art, CEO, AnalystFinder.com

[CurtisSmithDCAA]

Just those that start with. Containing might be creating the problem.

Distinct did not solve the problem.

Thanks. I really appreciate the help.

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

[art297]

Then change my suggested code to:

data master;
  input Contract $ Dollars;
  cards;
ABC12345        1000
BCD98765        5000
xBCD4421        200
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