Using contents of macro for inlist or SQL "IN" statement

[Abram Huyser-Honig]

Hi there,

I'm trying to do something like the following to use the IDs from an existing dataset to specify which records to pull in from an external database using an odbc load, exec command. I think I'm close, but can't quite get it to work. A similar scenario others might want to use this for would be with a Stata inlist command. Would appreciate any tips!


*Put all distinct values of ID variable into a local macro

levelsof id, local(ids)

*Add single quotes around each id number and a comma afterwards, for use in SQL "IN" statement

local idsf '`ids'',

*Now pull in data from external database

#delimit ;
odbc load, exec("
SELECT id, characteristic
FROM table
WHERE id IN ( substr(`idsf',1,length(`idsf')-1)) )
") clear low u($usr) p($pwd) dsn("TABLE")
;

#delimit cr

*Above, the purpose of the substr command is to trim the comma off of the final item in the list of IDs

[William Lisowski]

Welcome to Statalist.

At the bottom of this post I've put your sample code into Statalist CODE delimiters to make it readable. Looking at it, It's clear that

*Add single quotes around each id number and a comma afterwards, for use in SQL "IN" statement

local idsf '`ids'',

does not do what you hope it to do. Consider the example below. (The macro list command is a good way of seeing unambiguously what the value of a macro is, where an underscore before the name signals a local macro name.)

. sysuse auto, clear
(1978 Automobile Data)

. levelsof rep78, local(reps)
1 2 3 4 5

. local q1 '`reps'',

. local q2

. local comma

. foreach r of local reps {
  2.     local q2 `q2'`comma' '`r''
  3.         local comma ,
  4.         }

. macro list _q1 _q2
_q1:            '1 2 3 4 5',
_q2:            '1', '2', '3', '4', '5'

We see that in the local macro q1, the single quotes surround the entire list of numbers, and the comma follows the list. The local macro q2 seems to contain what you intended, although you don't explain precisely what it is that the SQL syntax requires,

With that said, to improve your future posts, please review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question.

The more you help others understand your problem, the more likely others are to be able to help you solve your problem.

*Put all distinct values of ID variable into a local macro

levelsof id, local(ids)

*Add single quotes around each id number and a comma afterwards, for use in SQL "IN" statement

local idsf '`ids'',

*Now pull in data from external database

#delimit ;
odbc load, exec("
SELECT id, characteristic
FROM table
WHERE id IN ( substr(`idsf',1,length(`idsf')-1)) )
") clear low u($usr) p($pwd) dsn("TABLE")
;

#delimit cr

*Above, the purpose of the substr command is to trim the comma off of the final item in the list of IDs

[Rebecca Raciborski]

Alternately, to avoid what might be a costly loop with a large number of IDs

. levelsof rep78, local(ids)
1 2 3 4 5

. local idsf : subinstr local ids " " "','", all

. local idsf "'`idsf''"

. di "`idsf'"
'1','2','3','4','5'

[Dimitriy V. Masterov]

-levelsof- has an option that allows you to specify a separator:

. sysuse auto, clear
(1978 Automobile Data)

. levelsof rep78 , sep(,)
1,2,3,4,5

This makes things even simpler.

[Rebecca Raciborski]

Dimitriy V. Masterov is right, levelsof does have a sep() option. In this case, Abram Huyser-Honig needs the values also enclosed in single-quotes. Stata will let you specify multiple characters to the sep() option, but the the first and last single quotes still have to be added separately.

. levelsof rep78, sep(',') local(ids)
1','2','3','4','5

. local idsf "'`ids''"

. di "`idsf'"
'1','2','3','4','5'

[Dimitriy V. Masterov]

You can also add those leading and trailing apostrophes in the IN clause:

. sysuse auto, clear
(1978 Automobile Data)

. levelsof rep78 , sep(',') local(my_list)
1','2','3','4','5

. display "SELECT * FROM my_table where id IN('`my_list'')"
SELECT * FROM my_table where id IN('1','2','3','4','5')

[Rebecca Raciborski]

Dimitriy V. Masterov

You can also add those leading and trailing apostrophes in the IN clause

Doh! I missed the forest for the trees on that.

[Abram Huyser-Honig]

Dimitriy V. Masterov , Rebecca Raciborski , and William Lisowski , thank you so much! This is very helpful. And per William Lisowski's suggestion, I'll make sure to review Statalist FAQ again before my next post. Thanks!

[Abram Huyser-Honig]

I should have mentioned the ID variable from which I am creating the list of values is a string variable, not numerical. As I describe in detail below, just swapping in a string variable in place of rep78 into the code proposed above does not produce the desired results. I would appreciate any advice on how to make these solutions work with string variables. Thank you!

Details
I attempted substituting in make for rep78 in all of the solutions proposed above in this thread, but clearly something about the way Stata interprets strings, quotes, and commas produces errors.

@William Lisowski's solution produces the following: q1 includes an unwanted set of double quotes around each string value, and q2 includes only the punctuation I want to add, but not the string values that should be enclosed by that punctuation.

. sysuse auto, clear
(1978 Automobile Data)

. 
. levelsof make, local(make)
`"AMC Concord"' `"AMC Pacer"' `"AMC Spirit"' `"Audi 5000"' `"Audi Fox"' `"BMW 320i"' `"Buick Century"' `"Buick Electra"' `"Buick LeSabre"' `"Buick Opel"' `"Bui
> ck Regal"' `"Buick Riviera"' `"Buick Skylark"' `"Cad. Deville"' `"Cad. Eldorado"' `"Cad. Seville"' `"Chev. Chevette"' `"Chev. Impala"' `"Chev. Malibu"' `"Che
> v. Monte Carlo"' `"Chev. Monza"' `"Chev. Nova"' `"Datsun 200"' `"Datsun 210"' `"Datsun 510"' `"Datsun 810"' `"Dodge Colt"' `"Dodge Diplomat"' `"Dodge Magnum"
> ' `"Dodge St. Regis"' `"Fiat Strada"' `"Ford Fiesta"' `"Ford Mustang"' `"Honda Accord"' `"Honda Civic"' `"Linc. Continental"' `"Linc. Mark V"' `"Linc. Versai
> lles"' `"Mazda GLC"' `"Merc. Bobcat"' `"Merc. Cougar"' `"Merc. Marquis"' `"Merc. Monarch"' `"Merc. XR-7"' `"Merc. Zephyr"' `"Olds 98"' `"Olds Cutl Supr"' `"O
> lds Cutlass"' `"Olds Delta 88"' `"Olds Omega"' `"Olds Starfire"' `"Olds Toronado"' `"Peugeot 604"' `"Plym. Arrow"' `"Plym. Champ"' `"Plym. Horizon"' `"Plym. 
> Sapporo"' `"Plym. Volare"' `"Pont. Catalina"' `"Pont. Firebird"' `"Pont. Grand Prix"' `"Pont. Le Mans"' `"Pont. Phoenix"' `"Pont. Sunbird"' `"Renault Le Car"
> ' `"Subaru"' `"Toyota Celica"' `"Toyota Corolla"' `"Toyota Corona"' `"VW Dasher"' `"VW Diesel"' `"VW Rabbit"' `"VW Scirocco"' `"Volvo 260"'

. 
. local q1 '`make'',

. 
. local q2

. 
. local comma

. 
. foreach m of local make {
  2.        local q2 `q2'`comma' '`r''
  3.        local comma ,
  4.            }

.   
.   macro list _q1 _q2
_q1:            '`"AMC Concord"' `"AMC Pacer"' `"AMC Spirit"' `"Audi 5000"' `"Audi Fox"' `"BMW 320i"' `"Buick Century"' `"Buick Electra"' `"Buick LeSabre"'
                `"Buick Opel"' `"Buick Regal"' `"Buick Riviera"' `"Buick Skylark"' `"Cad. Deville"' `"Cad. Eldorado"' `"Cad. Seville"' `"Chev. Chevette"'
                `"Chev. Impala"' `"Chev. Malibu"' `"Chev. Monte Carlo"' `"Chev. Monza"' `"Chev. Nova"' `"Datsun 200"' `"Datsun 210"' `"Datsun 510"' `"Datsun
                810"' `"Dodge Colt"' `"Dodge Diplomat"' `"Dodge Magnum"' `"Dodge St. Regis"' `"Fiat Strada"' `"Ford Fiesta"' `"Ford Mustang"' `"Honda Accord"'
                `"Honda Civic"' `"Linc. Continental"' `"Linc. Mark V"' `"Linc. Versailles"' `"Mazda GLC"' `"Merc. Bobcat"' `"Merc. Cougar"' `"Merc. Marquis"'
                `"Merc. Monarch"' `"Merc. XR-7"' `"Merc. Zephyr"' `"Olds 98"' `"Olds Cutl Supr"' `"Olds Cutlass"' `"Olds Delta 88"' `"Olds Omega"' `"Olds
                Starfire"' `"Olds Toronado"' `"Peugeot 604"' `"Plym. Arrow"' `"Plym. Champ"' `"Plym. Horizon"' `"Plym. Sapporo"' `"Plym. Volare"' `"Pont.
                Catalina"' `"Pont. Firebird"' `"Pont. Grand Prix"' `"Pont. Le Mans"' `"Pont. Phoenix"' `"Pont. Sunbird"' `"Renault Le Car"' `"Subaru"' `"Toyota
                Celica"' `"Toyota Corolla"' `"Toyota Corona"' `"VW Dasher"' `"VW Diesel"' `"VW Rabbit"' `"VW Scirocco"' `"Volvo 260"'',
_q2:            '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
                '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
                '', ''

When I ask Stata to display the contents of the macro produced using @Dimitriy V. Masterov's solution, Stata produces an error:

. sysuse auto, clear
(1978 Automobile Data)

. 
. levelsof make, sep(',') local(make)
`"AMC Concord"'','`"AMC Pacer"'','`"AMC Spirit"'','`"Audi 5000"'','`"Audi Fox"'','`"BMW 320i"'','`"Buick Century"'','`"Buick Electra"'','`"Buick LeSabre"'','`"
> Buick Opel"'','`"Buick Regal"'','`"Buick Riviera"'','`"Buick Skylark"'','`"Cad. Deville"'','`"Cad. Eldorado"'','`"Cad. Seville"'','`"Chev. Chevette"'','`"Che
> v. Impala"'','`"Chev. Malibu"'','`"Chev. Monte Carlo"'','`"Chev. Monza"'','`"Chev. Nova"'','`"Datsun 200"'','`"Datsun 210"'','`"Datsun 510"'','`"Datsun 810"'
> ','`"Dodge Colt"'','`"Dodge Diplomat"'','`"Dodge Magnum"'','`"Dodge St. Regis"'','`"Fiat Strada"'','`"Ford Fiesta"'','`"Ford Mustang"'','`"Honda Accord"'','`
> "Honda Civic"'','`"Linc. Continental"'','`"Linc. Mark V"'','`"Linc. Versailles"'','`"Mazda GLC"'','`"Merc. Bobcat"'','`"Merc. Cougar"'','`"Merc. Marquis"'','
> `"Merc. Monarch"'','`"Merc. XR-7"'','`"Merc. Zephyr"'','`"Olds 98"'','`"Olds Cutl Supr"'','`"Olds Cutlass"'','`"Olds Delta 88"'','`"Olds Omega"'','`"Olds Sta
> rfire"'','`"Olds Toronado"'','`"Peugeot 604"'','`"Plym. Arrow"'','`"Plym. Champ"'','`"Plym. Horizon"'','`"Plym. Sapporo"'','`"Plym. Volare"'','`"Pont. Catali
> na"'','`"Pont. Firebird"'','`"Pont. Grand Prix"'','`"Pont. Le Mans"'','`"Pont. Phoenix"'','`"Pont. Sunbird"'','`"Renault Le Car"'','`"Subaru"'','`"Toyota Cel
> ica"'','`"Toyota Corolla"'','`"Toyota Corona"'','`"VW Dasher"'','`"VW Diesel"'','`"VW Rabbit"'','`"VW Scirocco"'','`"Volvo 260"'

. 
. local make2 "'`make''"

. 
. di "`make2'"
'`AMC not found
r(111);

Stata also produces an error when I ask it to display the contents of the macro produced using @Rebecca Raciborski's solution:

. sysuse auto, clear
(1978 Automobile Data)

. 
. levelsof make, local(make)
`"AMC Concord"' `"AMC Pacer"' `"AMC Spirit"' `"Audi 5000"' `"Audi Fox"' `"BMW 320i"' `"Buick Century"' `"Buick Electra"' `"Buick LeSabre"' `"Buick Opel"' `"Bui
> ck Regal"' `"Buick Riviera"' `"Buick Skylark"' `"Cad. Deville"' `"Cad. Eldorado"' `"Cad. Seville"' `"Chev. Chevette"' `"Chev. Impala"' `"Chev. Malibu"' `"Che
> v. Monte Carlo"' `"Chev. Monza"' `"Chev. Nova"' `"Datsun 200"' `"Datsun 210"' `"Datsun 510"' `"Datsun 810"' `"Dodge Colt"' `"Dodge Diplomat"' `"Dodge Magnum"
> ' `"Dodge St. Regis"' `"Fiat Strada"' `"Ford Fiesta"' `"Ford Mustang"' `"Honda Accord"' `"Honda Civic"' `"Linc. Continental"' `"Linc. Mark V"' `"Linc. Versai
> lles"' `"Mazda GLC"' `"Merc. Bobcat"' `"Merc. Cougar"' `"Merc. Marquis"' `"Merc. Monarch"' `"Merc. XR-7"' `"Merc. Zephyr"' `"Olds 98"' `"Olds Cutl Supr"' `"O
> lds Cutlass"' `"Olds Delta 88"' `"Olds Omega"' `"Olds Starfire"' `"Olds Toronado"' `"Peugeot 604"' `"Plym. Arrow"' `"Plym. Champ"' `"Plym. Horizon"' `"Plym. 
> Sapporo"' `"Plym. Volare"' `"Pont. Catalina"' `"Pont. Firebird"' `"Pont. Grand Prix"' `"Pont. Le Mans"' `"Pont. Phoenix"' `"Pont. Sunbird"' `"Renault Le Car"
> ' `"Subaru"' `"Toyota Celica"' `"Toyota Corolla"' `"Toyota Corona"' `"VW Dasher"' `"VW Diesel"' `"VW Rabbit"' `"VW Scirocco"' `"Volvo 260"'

. 
. local make2 : subinstr local make " " "','", all

. 
. di "`make2'"
`AMC' invalid name
r(198);

[William Lisowski]

Well, yes, it would have been helpful to have fully described your problem.

clearly something about the way Stata interprets strings, quotes, and commas produces errors.

No, what causes the problem generally is that, as help levelsof tells us and as is visible in its output, the levelsof command encloses string values in compound double quotes, which it does not do for numeric values. What would you expect to happen when a code is run on data that is different than it was designed for?

Well, as it happens, my code works perfectly.

You did not run my code in post #9. You modified it incorrectly, changing the forloop local macro without making the corresponding change within the loop. This is copied from your code:

. foreach m of local make {
  2.        local q2 `q2'`comma' '`r''
  3.        local comma ,
  4.            }

Here is my original code, unchanged except to use make from the first 5 observations, rather than the 5 levels of the variable rep78, and to omit q1, since we've previously demonstrated the manner in which your code from post #1 does not work.

. sysuse auto, clear
(1978 Automobile Data)

. levelsof make in 1/5, local(reps)
`"AMC Concord"' `"AMC Pacer"' `"AMC Spirit"' `"Buick Century"' `"Buick Electra"'

. local q2

. local comma

. foreach r of local reps {
  2.     local q2 `q2'`comma' '`r''
  3.     local comma ,
  4.     }

. macro list _q2
_q2:            'AMC Concord', 'AMC Pacer', 'AMC Spirit', 'Buick Century', 'Buick Electra'

I will add that I have not looked into the code provided in other posts. But as I wrote in post #2

The macro list command is a good way of seeing unambiguously what the value of a macro is, where an underscore before the name signals a local macro name.

and you should have tried replacing the display command with the macro list command to see the values of the macros in question.

[Abram Huyser-Honig]

Thanks William Lisowski , this is helpful. And I certainly did not mean to imply that your code "didn't work"! Thanks for noting my typo in my modification of your code, and for pointing out that the macro list command is better to use than display for seeing the values of the macro.

[Abram Huyser-Honig]

@Dimitriy V. Masterov and @Rebecca Raciborski, I also got the solution using the sep option of the levelsof command to work with a string variable by also using the clean option.

. sysuse auto, clear
(1978 Automobile Data)

. 
. levelsof make in 1/5, local(make) sep(', ') clean
AMC Concord', 'AMC Pacer', 'AMC Spirit', 'Buick Century', 'Buick Electra

. 
. macro list _make
_make:          AMC Concord', 'AMC Pacer', 'AMC Spirit', 'Buick Century', 'Buick Electra

. 
. display "SELECT * FROM Table WHERE make IN ('`make'')"
SELECT * FROM Table WHERE make IN ('AMC Concord', 'AMC Pacer', 'AMC Spirit', 'Buick Century', 'Buick Electra')