topic Re: How to check if column in one table is part of column in another table . in SAS Programming

How to check if column in one table is part of column in another table .

Sandhya55 — Thu, 28 Oct 2021 16:45:13 GMT

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.

Re: How to check if column in one table is part of column in another table .

Reeza — Thu, 28 Oct 2021 16:47:08 GMT

More details please.
Like/FIND is usually the way.

Re: How to check if column in one table is part of column in another table .

Sandhya55 — Thu, 28 Oct 2021 16:49:22 GMT

I have used like statement as below
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;
But this is not working

Re: How to check if column in one table is part of column in another table .

Sandhya55 — Thu, 28 Oct 2021 16:50:56 GMT

Can some one pls suggest

Re: How to check if column in one table is part of column in another table .

Reeza — Thu, 28 Oct 2021 16:53:03 GMT

What does not working mean? What do you have - very specifically what does your input data look like?
Can't share it due to privacy, please make fake data that replicates your problem.
What do you want to do? If that was your input data what do you expect as results?

I see your code, but what does the log show?

What about the following instead? FIND with the i/t makes it so that there case differences (upper/lower case) are ignored as are trailing blanks.

PROC SQL ;
CREATE TABLE GRP1 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;

Re: How to check if column in one table is part of column in another table .

Sandhya55 — Thu, 28 Oct 2021 16:55:36 GMT

Below are the contents of
File A:
AAA BBBB CCC
DDD FF
File B :
BBB
DDD

Re: How to check if column in one table is part of column in another table .

Sandhya55 — Thu, 28 Oct 2021 16:58:54 GMT

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.

Re: How to check if column in one table is part of column in another table .

Reeza — Thu, 28 Oct 2021 17:01:00 GMT

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.

Re: How to check if column in one table is part of column in another table .

Sandhya55 — Thu, 28 Oct 2021 17:11:31 GMT

Thanks !!! Find function worked for me ..probably my issue is with the trailing or leading spaces