topic Re: Like Function between 2 tables in SAS Programming

Like Function between 2 tables

RyanWong0510 — Wed, 15 Sep 2021 05:37:13 GMT

Hi,

I am trying to match 2 tables and generate exceptions.

For example :

Table1.Name : DAVID,RYAN,JOHN,..... (20k unique names)

Table2.Article : THE VISA WAIVER PROGRAM VWP ALLOWS CITIZENS,... (10k of article crawled from google)

I want to generate exceptions where Article like '%NAME%' which is the 20k of unique names from table1.

Appreciate someone can assist on it.

Re: Like Function between 2 tables

PeterClemmensen — Wed, 15 Sep 2021 06:56:15 GMT

Please provide some sample data. Makes it much easier to provide a usable code answer 🙂

Re: Like Function between 2 tables

ChrisNZ — Wed, 15 Sep 2021 08:50:41 GMT

Like this?

select * from NAMES, ARTICLES where ARTICLE contains NAME;

Note this will evaluate 10k*20k = 200m rows. It might run slowly on a small server.

Re: Like Function between 2 tables

RyanWong0510 — Wed, 15 Sep 2021 10:51:05 GMT

Hi ,

Thanks for the support.

Please find the sample data as below :
DATA Name;
INPUT id $ Name $ ;
DATALINES;
a1 Ryan
a2 Jack
a3 Larry

;
RUN;

Data Article;
length id article $5000;
infile cards dsd dlm='|' truncover ;
Input id Article $5000. ;
cards;
1|"California, I'm not sure you know it, but if you didn't know it you should," the president said Monday evening in Long Beach."
2|Malaysia, I'm not sure Ryan know it, but if you didn't know it you should," Jack said Monday evening in Long Beach. "The eyes of the nation, this is not hyperbole'
3|The front-running Republican who would likely replace Newsom if he's recalled is Larry Elder, a conservative talk show host and vocal supporter of the former president, as well as a proponent of the so-called "big lie'
;
run;

My expected result will be

ID	Article	Name
2	\|Malaysia, I'm not sure Ryan know it, but if you didn't know it you should," Jack said Monday evening in Long Beach. "The eyes of the nation, this is not hyperbole'	Ryan
2	\|Malaysia, I'm not sure Ryan know it, but if you didn't know it you should," Jack said Monday evening in Long Beach. "The eyes of the nation, this is not hyperbole'	Jack
3	The front-running Republican who would likely replace Newsom if he's recalled is Larry Elder, a conservative talk show host and vocal supporter of the former	Larry

Re: Like Function between 2 tables

RyanWong0510 — Wed, 15 Sep 2021 10:54:22 GMT

Thanks Chris,
By referring to the sample data I provided, it does not turn out any output.

Re: Like Function between 2 tables

ballardw — Wed, 15 Sep 2021 16:54:14 GMT

@RyanWong0510 wrote:
Thanks Chris,
By referring to the sample data I provided, it does not turn out any output.

No output? Post any log in a code box. Copy from the log the entire procedure code and any messages, open a text box on the forum using the </> icon above the message window and paste the copied text.

Likely need to remove trailing blanks in the Name variable:

proc sql;
   select * from NAME, ARTICLE 
   where ARTICLE contains strip(name)
   ;
quit;