topic Re: How do I recursively read one table then search another table in New SAS User

How do I recursively read one table then search another table

FormerSQLuser — Thu, 13 Feb 2020 17:41:21 GMT

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

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.

Re: How do I recursively read one table then search another table

ballardw — Thu, 13 Feb 2020 18:30:13 GMT

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

Re: How do I recursively read one table then search another table

Reeza — Thu, 13 Feb 2020 18:40:28 GMT

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.

Re: How do I recursively read one table then search another table

FormerSQLuser — Thu, 13 Feb 2020 18:59:35 GMT

Yep, that helps quite a bit.

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

Thanks.

Re: How do I recursively read one table then search another table

Reeza — Thu, 13 Feb 2020 19:00:47 GMT

SAS doesn't support cursors.

Re: How do I recursively read one table then search another table

FormerSQLuser — Thu, 13 Feb 2020 19:15:13 GMT

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.

Re: How do I recursively read one table then search another table

Tom — Thu, 13 Feb 2020 19:23:12 GMT

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.

Re: How do I recursively read one table then search another table

FormerSQLuser — Thu, 13 Feb 2020 20:29:08 GMT

Hahaha

Thank you Tom

So simple...

Ta!

Re: How do I recursively read one table then search another table

Reeza — Thu, 13 Feb 2020 20:43:51 GMT

Make sure to factor in case and spaces.