BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
krishmar1
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Scott_Mitchell
Quartz | Level 8

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

5 REPLIES 5
Scott_Mitchell
Quartz | Level 8

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;

krishmar1
Fluorite | Level 6

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

PGStats
Opal | Level 21

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
Scott_Mitchell
Quartz | Level 8

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;

krishmar1
Fluorite | Level 6

Thank you, Scott and PGStats.

K

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 3395 views
  • 0 likes
  • 3 in conversation