'Invalid column name' error when using Proc SQL in SAS (WPS)

Question

I am testing a very small macro which uses a Proc SQL command to extract from a SQL database. I have used this many times and understand how it works but this time it is stumping me!

%macro Correlation(dsn,db,prevdb,prodcode,sqltable,var,brick);

proc sql;
    connect to ODBC (required="DSN=&dsn;" );
    create table comp_correlation as select * from connection to ODBC
    (select a.Table_Name,
            a.Variable,
            a.Correlation as Current_Corr
        from DBTest.dbo.MetaData as a
        where Product_Code=&prodcode. and Table_Name=&sqltable. and 
     variable=&var.);
     disconnect from ODBC;
quit;

%mend;

I am then calling this macro with the following parameters. My server name is censored but I can assure you it connects successfully.

%Correlation(********, A2019, A2018, HouseValues, Houses, Value);

However, it returns the following error:

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'HouseValues'.

Anyone know why this is happening? I am not even trying to select the Product Code as a column, it is only in my where statement?

Answer 1

I have now fixed this using the %bquote macro.

%macro Correlation(dsn,db,prevdb,prodcode,sqltable,var,brick);

proc sql;
    connect to ODBC (required="DSN=&dsn;" );
    create table comp_correlation as select * from connection to ODBC
    (select a.Table_Name,
        a.Variable,
        a.Correlation as Current_Corr
      from DBTest.dbo.MetaData as a
     where Product_Code=%bquote('&prodcode') and Table_Name=%bquote('&sqltable') and 
 variable=%bquote('&var'));
 disconnect from ODBC;
quit;

%mend;