BookmarkSubscribeRSS Feed
ballardw
Super User

@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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 15 replies
  • 6884 views
  • 3 likes
  • 5 in conversation