How to check if column in one table is part of column in another table .I have used Like keyword but it did not work.
data TB1;
COMP_KEY="AAA BBBB CCC";output;
COMP_KEY="DDD FF";output;
run;
data tb2;
COMP_KEY="BBB";output;
COMP_KEY="DDD";output;
run;
PROC SQL ;
CREATE TABLE GRP1 AS
SELECT C.COMP_KEY, B.COMP_KEY AS GRP_COMP_KEY
FROM TB1 B , TB2 C
where B.COMP_KEY like '%'||C.COMP_KEY||'%' ;
QUIT;
PROC SQL ;
CREATE TABLE GRP2 AS
SELECT C.COMP_KEY, B.COMP_KEY AS GRP_COMP_KEY
FROM TB1 B , TB2 C
where find( B.COMP_KEY, c.comp_key, 'it')>0 ;
QUIT;
Both sets of code work fine for me given your example data and code.
Please generate a fully worked example, as above, that shows where this code isn't working.
Log:
NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized.
NOTE: Table created, with 0 rows and 2 columns.
data TB1;
COMP_KEY="AAA BBBB CCC";output;
COMP_KEY="DDD FF";output;
run;
data tb2;
COMP_KEY="BBB";output;
COMP_KEY="DDD";output;
run;
PROC SQL ;
CREATE TABLE GRP1 AS
SELECT C.COMP_KEY, B.COMP_KEY AS GRP_COMP_KEY
FROM TB1 B , TB2 C
where B.COMP_KEY like '%'||C.COMP_KEY||'%' ;
QUIT;
PROC SQL ;
CREATE TABLE GRP2 AS
SELECT C.COMP_KEY, B.COMP_KEY AS GRP_COMP_KEY
FROM TB1 B , TB2 C
where find( B.COMP_KEY, c.comp_key, 'it')>0 ;
QUIT;
Both sets of code work fine for me given your example data and code.
Please generate a fully worked example, as above, that shows where this code isn't working.
Thanks !!! Find function worked for me ..probably my issue is with the trailing or leading spaces
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.