BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
6 REPLIES 6
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Peter_C
Rhodochrosite | Level 12
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
deleted_user
Not applicable
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
deleted_user
Not applicable
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
deleted_user
Not applicable
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 674 views
  • 0 likes
  • 3 in conversation