Hi everyone, I am trying to extract some data from an external database using the command -odbc load, exec ()- with a local macro inside the argument. However I am getting the following error
table() or exec() is required
Quick background. I need first to merge (left join) two tables from the database using a unique identifier that shows up in both tables (BvD_ID_number), and second to return only observations whose value of ID (this is a different variable from BvD_ID_number) is in a specific list.

In order to do this I want to create a local macro with all the values of ID that have to be searched for, and then use this macro in the SQL "IN" clause. Furthermore, the values in this list have to be enclosed in single quotes ' ' and be separated by a comma in order to be correctly processed. For that I use the subinstr command.

My code is this one below (I just changed the name of the database and of the dta name/path):

Code:
clear

use "identifiers.dta"

odbc query "database", dialog(complete)

levelsof ID, local(id)
local sql_in

foreach i of local id {
                local sql_in `sql_in' '`i''
}

local sql_in: subinstr local sql_in "' '" "','", all

clear

odbc load, exec("SELECT t1.*, t2.Country from dbo.Identifiers as t1 left join dbo.Contact_info as t2 on t1.BvD_ID_number = t2.BvD_ID_number WHERE t1.VAT_Tax_number IN (`sql_in')") noquote clear

If I check the values stored in the macro sql_in I do get exactly what I want
Code:
macro list _sql_in
'PL6151146213','PL6171279408',(…),'PL6222675121'
but when I check whether the macro is recognized in -odbc- with
Code:
display in smcl as text "SELECT t1.*, t2.Country from dbo.Identifiers as t1 left join dbo.Contact_info as t2 on t1.BvD_ID_number = t2.BvD_ID_number WHERE t1.VAT_Tax_number IN (`sql_in')"
then I get this error
SELECT t1.*, t2.Country from dbo.Identifiers as t1 left join dbo.Contact_info as t2 on t1.BvD_ID_number = t2.BvD_ID_number WHERE t1.VAT_Tax_number IN ('' invalid name
r(198);
Could you please help me? Thank you!