You demonstrated your data as
My data looks like the following:
Table1(Have1):
Keyword Category
Hello | Hey Greeting
Canada | America North America
Football | Basketball Sports
Date Comment
01JAN2021 Hello, my name is larsc
01JAN2021 I live in Europe
01JAN2021 I like to watch football
My desired output would be like:
Category Comment
Greeting Hello, my name is larsc
I live in Europe
Sports I like to watch football
Few clarifications needed:
Are the bold words the variable names in your data (Keyword, Category, Date, Comment)
Can Keyword contain more then one keyword and if positive are they separated by '|' ? and in case of match, shall they be written to different output observations?
It seems that by "matching" you mean that the keyword is present in the comment, though maybe you need ignore case.
Suggestion:
1) create a dataset with each keyword and its category.
2) match by SQL on upcase(comment) contains upcase(keyword) as in attached code:
data have1;
length keyword $15 category $20;
infile cards dlm=',' truncover;
input keyword $ category $;
cards;
Hello, Greeting
Hey, Greeting
Canada, North America
America, North America
Football, Sports
Basketball, Sports
; run;
data have2;
length comment $40;
infile cards dlm='|' truncover;
input Date date9. comment $;
cards;
01Jan2021| Hello, my name is larsc
01Jan2021| I live in Europe
01Jan2021| I like to watch football
; run;
proc sql;
create table want as
select a.category, b.comment
from have1 a right join have2 b
/* on index(upcase(comment),upcase(strip(keyword))) > 0 */
on upcase(b.comment) contains upcase(strip(a.keyword))
;quit;
... View more
