I have an SQL query written into a GET DATA command. It works perfectly as it is, but now I would like to modify it to take in a string value from my current dataset.

Sample query -

SELECT * FROM TABLE WHERE field IN ('AAA', 'BBB', 'CCC', 'DDD')

I would like the bracketed list to be a string value that already exists in my dataset (ref - previous post)

I have tried several things to date including using a concatenated string in the /SQL portion of the GET DATA command. For the concatenation I tried referencing the field itself and a scratch variable.

I've also attempted to call the command via a macro using the variable as an input. Unfortunately, the SQL string only goes through with the variable name rather than its contents.

I've seen a few posts that recommend using the WRITE OUT command for similar use cases but haven't found anything regarding concatenating into an SQL query. Would this be the only option for me?

CodePudding user response：

Looks to me like write out is a good option for you.
Assuming you have one row of data in which myconcat has the value "'AAA', 'BBB', 'CCC', 'DDD'" (if you have more then one row in the data, first select only the one):

select if (the row with the full concatenated list) .
string cmd (a2000).
compute cmd=concat("define !mysqlcommand() 'SELECT * FROM TABLE WHERE field IN (", rtrim(myconcat), ")' !enddefine.").
write out = "path\create val list macro.sps" /cmd.
execute.
insert file = "path\create val list macro.sps".

The code writes a new syntax file where it creates a macro call. After the insert command runs this, you theoretically have a macro defined so when you use the macro call !mysqlcommand in syntax it will automatically write in the full SQL command you've created, so:

.
.
/sql = !mysqlcommand
.
.