'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

it seems you have missed one parameter in the below call :

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

Is it a typo or i interpreted it wrong ?

thanks

Answer 2

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;

Answer 3

Sounds like your actual error was that you passed a string value to your remote database without quotes. You could solve that by passing the quotes in the macro call.

%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;

%Correlation(dsn=********,db=A2019,prevdb=A2018
   ,prodcode='HouseValues', sqltable='Houses', var='Value');

Note that you can use parameter names in the macro call, even for parameters that are defined to allow them to also be called by position.

You could also make your macro a little smarter and have it add the quotes. You could even make it smart enough to remove any optional existing double quotes around the value and replace them with single quotes.

%let prodcode=%sysfunc(quote(%qsysfunc(dequote(%superq(prodcode)))),%str(%'));

Then you could call it anyway you want.

prodcode=HouseValues
prodcode="HouseValues"
prodcode='HouseValues'