Search code examples
sas

Resolve SAS Macro Variable in Select Statement

I have a field that needs to be selected from one table in certain cases and another table in other cases. I'm trying to use a Macro Variable to resolve which table to select the field from but keep getting an error.

In the scenario below, the variable TableValue has a value of 'abc' based on conditions determined earlier in my program. The variable is used within the SELECT statement to reference a table with that alias:

SELECT 
    &TableValue..type as ITEM_TYPE
FROM 
    rca.items abc
    left join rca2.items abcd on rca2.ItemNum = rca.ItemNum

I am having trouble resolving the above variable, I am receiving the following error:

ORACLE prepare error: ORA-22806: not an object or REF. SQL statement: SELECT 
       'abc'.type

Any help would be appreciated, I suspect it has to do with the single quotes but haven't been able to replace etc. Thank you!

Solution

  • If the macro variable TableValue has a value with quotes in it: 'abc' the answer would be to remove the quotes.

    To debug this, get the program working with no macro variables:

    SELECT 
    abc.type as ITEM_TYPE
FROM 
    rca.items abc
    left join rca2.items abcd on rca2.ItemNum = rca.ItemNum

    Then after that is working, replace abc with a macro variable reference that will resolve to abc.