Hi,
I have a dataset A with a column named Location and a dataset B with a column named Keyword.
Location has rows like:
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
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;
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;
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
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
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;
Thank you, Scott and PGStats.
K
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.