DATA Step, Macro, Functions and more

How to check words (from one column) in text (from another column)

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

How to check words (from one column) in text (from another column)

Hi,

I have a dataset A with a column named Location and a dataset B with a column named Keyword.

Location has rows like:

  • Texas
  • Florida
  • Arizona

which stores any single word.

Keyword stores rows with text, for example: I live in Florida.

I would like to list all the rows in Keyword if any of the words in location exists in Keyword.

So keyword in the above example would be listed but not if the row of Keyword is "I am a vagabond"

How do I code this in SAS --- using a data step or Proc SQL or any other proc?

Please help!

Thanks,

K


Accepted Solutions
Solution
‎09-12-2013 09:35 PM
Super Contributor
Posts: 297

Re: How to check words (from one column) in text (from another column)

You could use SQL.

DATA T1;

INPUT  LOCATION $;

DATALINES;

TEXAS

FLORIDA

ARIZONA

;

RUN;

DATA T2;

LENGTH KEYWORD $40;

INFILE DATALINES DLM=',';

INPUT KEYWORD $;

DATALINES;

I AM A VAGABOND

I LOVE TEXAS

I HATE ARIZONA

;

RUN;

PROC SQL;

CREATE TABLE WANT AS

SELECT *

FROM T1,T2

WHERE FIND(T2.KEYWORD,T1.LOCATION) > 0

;

RUN;

View solution in original post


All Replies
Solution
‎09-12-2013 09:35 PM
Super Contributor
Posts: 297

Re: How to check words (from one column) in text (from another column)

You could use SQL.

DATA T1;

INPUT  LOCATION $;

DATALINES;

TEXAS

FLORIDA

ARIZONA

;

RUN;

DATA T2;

LENGTH KEYWORD $40;

INFILE DATALINES DLM=',';

INPUT KEYWORD $;

DATALINES;

I AM A VAGABOND

I LOVE TEXAS

I HATE ARIZONA

;

RUN;

PROC SQL;

CREATE TABLE WANT AS

SELECT *

FROM T1,T2

WHERE FIND(T2.KEYWORD,T1.LOCATION) > 0

;

RUN;

Contributor
Posts: 34

Re: How to check words (from one column) in text (from another column)

Thank you, Scott.

Do you know how I can have an indicator variable (binary) for each row where FIND(T2.KEYWORD,T1.LOCATION) > 0 so that I can differentiate between the rows with the location and the rows without

Regards,

K

Respected Advisor
Posts: 4,649

Re: How to check words (from one column) in text (from another column)

Change the last step to:

PROC SQL;

CREATE TABLE WANT AS

SELECT

     T2.keyword,

     T1.location,

     T1.location IS NOT MISSING as indicator

FROM T2 LEFT JOIN T1

ON FIND(T2.KEYWORD ,T1.LOCATION) > 0;

QUIT;

PG

PG
Super Contributor
Posts: 297

Re: How to check words (from one column) in text (from another column)

How is this?

PROC SQL;

CREATE TABLE WANT AS

SELECT A.KEYWORD, B.INDIC

FROM T2 A

LEFT JOIN

(SELECT KEYWORD, 1 AS INDIC

FROM T1,T2

WHERE FIND(T2.KEYWORD,T1.LOCATION) > 0) B

ON A.KEYWORD = B.KEYWORD

;

RUN;

Contributor
Posts: 34

Re: How to check words (from one column) in text (from another column)

Thank you, Scott and PGStats.

K

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 316 views
  • 0 likes
  • 3 in conversation