How do I recursively read one table then search another table

[FormerSQLuser]

Hi Everyone

I'm quite new to SAS - I've been a SQL user most of my life and only trying SAS out now.

Excuse my ignorance on some of the questions.

I have 1 table (Table1)in SAS that has 1 distinct variable with around 1000 observations (each observation is a single word) and I have another table (Table2) that has a 1 string variable which is not distinct (with around 10 million observations).

I would like to take EACH of the 1000 obs in Table1 and recursively search for them in strings in Table2 then save them in Table3 when its a match.

example: 

Table1

These

Are

the

Words

and

strings

that

might

appear

in

table1

Table2

this variable may contain distinct strings

but may also have words

that

are 

duplicated a number of times in 

Table1 so regardless

of that I need

to use all the help 

I might get from you

to help me find the

words 

Please help in 

any way that you 

can help me with these

I thank you for any help you might 

give to find var from table1

So in the above Table1 - i want to pick up each word then search for it in table2.

Where it is found, I'd like to have output like this:

Table3

Table1Var             Table2String

These                   can help me with these

Are                        are

The                       to use all the help 

The                       to help me find the

words                    but may also have words

words                   words

and                       .

strings                   this variable may contain distinct strings

That                      that

That                      of that I need

Might                     I might get from you

Might                     I thank you for any help you might 

Appear                  .

in                           duplicated a number of times in 

in                           Please help in

Table1                   Table1 so regardless

Table1                   give to find var from table1

I hope the above makes sense.

Thank you.

[ballardw]

Would it help you to know that SAS supports a Proc SQL that implements ANSI standard sql?

[FormerSQLuser]

Yep, that helps quite a bit.

So all I need is a quick SQL cursor then I'm good.

Thanks.

[Reeza]

SAS doesn't support cursors.

[Reeza]

Temporary arrays are a great option here. Table1 doesn't seem to work but I'll leave that one for you to debug. 

data t1;
input word_to_find $;
cards;
These
Are
the
Words
and
strings
that
might
appear
in
table1
;;;;

 

 

data t2;
length long_string $50.;
infile cards dlm='09'x;
input long_string $;
cards;
this variable may contain distinct strings
but may also have words
that
are 
duplicated a number of times in 
Table1 so regardless
of that I need
to use all the help 
I might get from you
to help me find the
words 
Please help in 
any way that you 
can help me with these
I thank you for any help you might 
give to find var from table1
;;;;
run;



data want;
array words2find(10) $50. _temporary_ ;
if _n_ = 1 then do j=1 to 10;
set t1;
words2find(j) = word_to_find;
end;

set t2;
do i=1 to dim(words2find);
if indexw(trim(lowcase(long_string)), trim(lowcase(words2find(i))), '') then found_word = words2find(i);
end;

run;

---

@FormerSQLuser wrote:

Hi Everyone

I'm quite new to SAS - I've been a SQL user most of my life and only trying SAS out now.

Excuse my ignorance on some of the questions.

I have 1 table (Table1)in SAS that has 1 distinct variable with around 1000 observations (each observation is a single word) and I have another table (Table2) that has a 1 string variable which is not distinct (with around 10 million observations).

I would like to take EACH of the 1000 obs in Table1 and recursively search for them in strings in Table2 then save them in Table3 when its a match.

example: 

Table1

These

Are

the

Words

and

strings

that

might

appear

in

table1

Table2

this variable may contain distinct strings

but may also have words

that

are 

duplicated a number of times in 

Table1 so regardless

of that I need

to use all the help 

I might get from you

to help me find the

words 

Please help in 

any way that you 

can help me with these

I thank you for any help you might 

give to find var from table1

So in the above Table1 - i want to pick up each word then search for it in table2.

Where it is found, I'd like to have output like this:

Table3

Table1Var             Table2String

These                   can help me with these

Are                        are

The                       to use all the help 

The                       to help me find the

words                    but may also have words

words                   words

and                       .

strings                   this variable may contain distinct strings

That                      that

That                      of that I need

Might                     I might get from you

Might                     I thank you for any help you might 

Appear                  .

in                           duplicated a number of times in 

in                           Please help in

Table1                   Table1 so regardless

Table1                   give to find var from table1

I hope the above makes sense.

Thank you.

---

[FormerSQLuser]

Thank you so much @Reeza 

This works nicely

I need to go through it and figure out how it works but it gets the job done.

Thank you so much.

[Tom]

Not sure how a cursor helps with this.

It is just a join between two tables.

select a.word,b.string
from table1 a
   , table2 b
where b.string contains a.word
;

You can play around with what test you want to use in the WHERE clause.

[FormerSQLuser]

Hahaha

Thank you Tom

So simple...

Ta!

[Reeza]

Make sure to factor in case and spaces.