How to combine data from multiple cells into one cell

[ballardw]

---

@tazz_bh wrote:

 would like to combine data from multiple cells into one cell using SAS Data Integration Studio.

My data is divided into three different tables, as follows:

Table 1 (Number of columns is unknown)

Col 1 Col 2 Col 3

City A

City B

City C

Table 2 (Number of columns is unknown)

Col 1 Col 2 Col 3

State A

State B

State C

Table 3

Variable 1 Variable 2

x

y

Desired final table:

That is, I want to create a final table in which I can join the data from table 1 (Number of columns is unknown) in just one field and the data from table 2 (Number of columns is also unknown) in another field, separating the respective values ​​with a comma.

Variable 1 Variable 2 States Cities

x

y

State A, State B, State C

City A, City B, City C

Can someone help me with the code so I can count columns and then CATX () without having to do mappings, since I don't know how many columns I will have? In addition, the next time I run the job, some city may have left the list, which reduces my number of columns.

---

Please describe just what you intend to do with the resulting data. This kind of data structure is obnoxious for any form of modeling, analysis, summary or even legible reporting.