SPSS Syntax Concatenate Case Values From Single Column

I am trying to build a string of values to be inserted into an SQL IN list. For example -

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

The list that I want needs to be constructed from values within a single column of my dataset but I'm struggling to find a way to concatenate those values.

My first thought was to use CASESTOVARS to put each of the values into columns prior to concat. This is simple but the number of cases is variable.

Is there a way to concat all fields without specifying?
Or is there a better way to go about this?

Unfortunately Python is not an option for me in this instance.

A simple sample dataset would be -

CasestoConcat
AAA
BBB
CCC
DDD

Solution

You can use the lag function for this.

First creating a bit of sample data to demonstrate on:

data list free/grp (F1) txt (a5).
begin data 
    1 "aaa" 1 "bb" 1 "cccc" 2 "d" 2 "ee" 2 "fff" 2 "ggggg" 3 "hh" 3 "iii"
end data.

Now the following code makes sure that rows that belong together are consecutive. You can also sort by any other relevant variable to keep the combined text in a specific order.

sort cases by grp.
string merged (A1000).
compute merged=txt.
if $casenum>1 and grp=lag(grp) merged=concat(rtrim(merged), " ", rtrim(lag(merged))).
exe.

At this point if you want to just keep the line that has all the concatenated texts, you can use this:

add files /file=* /by grp /last=lst.
select if lst=1.
exe.