PROC SQL subquerying

Question

I have this table:

library	table	attribute_exclusion
lib_A	Table A	('column_A','column_H')
lib_A	Table B	('column_C','column_X')

And I want to get all columns from SASHELP.VCOLUMN in my HAVE table except the ones in the column attribute_exclusion.

I am trying to do that with the code shown below, but it's returning all columns from both tables, not taking in account my field_exclusion values.

proc sql;
create table want as
    select upcase(compress(t1.libname)) as libname
        , upcase(compress(t1.memname)) as memname
        , upcase(compress(t1.name)) as name
    from SASHELP.VCOLUMN t1, have t2
    where upcase(compress(t2.library) = upcase(compress(t1.libname))
    and upcase(compress(t2.table)) = upcase(compress(t1.memname))
    and upcase(compress(t1.name)) not in 
        (SELECT t3.attribute_exclusion
        from have t3
        where upcase(compress(t3.library)) = upcase(compress(t1.libname))
        and upcase(compress(t3.table)) = upcase(compress(t1.memname))
        )
    ;
quit; 

If I just test the code with a implicit value in attribute_exclusion, it works, so not sure what I missing here :/

proc sql;
create table want as
    select upcase(compress(t1.libname)) as libname
        , upcase(compress(t1.memname)) as memname
        , upcase(compress(t1.name)) as name
    from SASHELP.VCOLUMN t1, have t2
    where upcase(compress(t2.library) = upcase(compress(t1.libname))
    and upcase(compress(t2.table)) = upcase(compress(t1.memname))
    and upcase(compress(t1.name)) not in ('column_C',column_X')
    ;
quit; 

Thanks in advance for all help.

Answer 1

When you perform a subquery using not in, you are using in in the sense of the results of the query.

SELECT t3.attribute_exclusion
from have t3
where upcase(compress(t3.library)) = upcase(compress(t1.libname))
and upcase(compress(t3.table)) = upcase(compress(t1.memname))

Returns (for tableA):

in ("('column_A','column_H')")

For this to match a row in sashelp.vcolumn, the column name in vcolumn would need to be exactly "('column_A','column_H')" - you cannot get this to match (and exclude) more than one column in sashelp.vcolumn by using a single row containing more than one value without some extra coding.

I can't remember how this could be achieved using a single row (if it's possible), so I'll propose a new table structure for HAVE which should work:

library	table	attribute_exclusion
lib_A	Table A	column_A
lib_A	Table A	column_H
lib_A	Table B	column_C
lib_A	Table B	column_X

Here the subquery returns:

column_A
column_H

Which is translated by the in to:

in ('column_A','column_H')

Whereas in the previous version:

in ("('column_A','column_H')")

Subtle, but different.