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
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):
If I check the values stored in the macro sql_in I do get exactly what I want
but when I check whether the macro is recognized in -odbc- with
then I get this error
Could you please help me? Thank you!
table() or exec() is required
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'
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')"
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);
r(198);
Comment