topic Re: Merging table with a like? in SAS Procedures

Merging table with a like?

deleted_user — Fri, 15 May 2009 11:41:19 GMT

Hi

Im trying to merge two tables but the variables aren't exactly the same.
in sql it is possible, using a like join.
select *
table1 a left join table2 b
on a.var LIKE b.var

what i have is a list of words in one table2 ie
var
it
is

and in table1 i have words like
fit
food
his
you
hit
item
....etc

so i want to flag all the words in table that contain any word from table 2.
to get
fit 1
food 0
his 1
you 0
hit 1
item 1

Is this possible?

I hope i have made myself clear enough.

Thanks
Simon

Re: Merging table with a like?

sbb — Fri, 15 May 2009 19:53:32 GMT

I don't believe SAS has equivalent support/behavior with LIKE processing, as you describe. You may want to open a SAS support track to get a specific and accurate response from SAS.

Scott Barry
SBBWorks, Inc.

Re: Merging table with a like?

Peter_C — Sat, 16 May 2009 14:20:08 GMT

put the short list of words into memory either hash table or array. Then pass through the larger table; test each entry in memory against the row from the large table with whatever "like" test you need.
depending on your data model, you may want to select for every word in the memory which is "like" or just for the first.

good luck

PeterC

Re: Merging table with a like?

deleted_user — Sat, 16 May 2009 20:10:06 GMT

Hello Spud,

Could you try this?

proc sql;
create table T02_contains as
select a.var, count(distinct b.var) as count_distinct, (calculated count_Distinct gt 0) as flag_exist
from T01_table1 a LEFT JOIN T01_table2 b
on index(a.var,trim(b.var)) gt 0
group by 1;
quit;

I tested it with the following data sets:

* Test data;
data T01_table2;
infile cards;
length var $30;
input var;
cards;
var
it
is
fi
;
run;

data T01_table1;
infile cards;
length var $30;
input var;
cards;
fit
food
his
you
hit
item
;
run;

Watch out: it will perform a cartesian product. As a consequence, it is time and resource consuming. But I think it will do what you want.

Could you let me know whether this helped,

Regards,

Yoba

Re: Merging table with a like?

deleted_user — Thu, 02 Jul 2009 12:53:51 GMT

Sorry for the extremely late reply....
Yes it worked but was incredibly slow....and i was really after a SAS equivalent not a proc sql.
But thanks anyway Message was edited by: Spud

Re: Merging table with a like?

deleted_user — Thu, 02 Jul 2009 18:21:09 GMT

Hello Spud,

It is slow because a cartesian product is needed to find the matches. I don't think there are very performant alternatives.

Yoba

Re: Merging table with a like?

sbb — Thu, 02 Jul 2009 18:53:34 GMT

Suggest you investigate DATA step functions (FIND, INDEX, INDEXW) and possibly a HASH table approach which may be suitable for your needs.

Scott Barry
SBBWorks, Inc.