DATA Step, Macro, Functions and more

How to create a character string that list character values for one column

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

How to create a character string that list character values for one column

So I've came across this task with a big dataset, I've make two brief tables to sum the core of this problem. I know if it's a very small table, I can just manually add more columns for each row to list all states for each organization ID and then use TRIM or CATX, but with big data, I couldn't figure out how to do it systematically, since the # of states an organization operates in varies, and the values are stored in different rows of the same column, rather than in different columns of the same row.

 

What I want to do is to bring States values to a higher level, as an aggregate of all states that this organization operate in. 

 

My input data looks like:

OrganizationIDProductStates
1footballDC
1footballVA
1footballMD
2footballCA
3footballNV
3footballCA

 

My desired output data should look like 

OrganizationIDProductStates
1footballDC, VA, MD
2footballCA
3footballNV, CA

Accepted Solutions
Solution
‎10-18-2017 10:41 PM
Super User
Posts: 24,014

Re: How to create a character string that list character values for one column

[ Edited ]
Posted in reply to LisaYIN9309

There are several options, you can use a data step with BY group processing or you can TRANSPOSE and then use CATX(). 

 

https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a

 


LisaYIN9309 wrote:

So I've came across this task with a big dataset, I've make two brief tables to sum the core of this problem. I know if it's a very small table, I can just manually add more columns for each row to list all states for each organization ID and then use TRIM or CATX, but with big data, I couldn't figure out how to do it systematically, since the # of states an organization operates in varies, and the values are stored in different rows of the same column, rather than in different columns of the same row.

 

What I want to do is to bring States values to a higher level, as an aggregate of all states that this organization operate in. 

 

My input data looks like:

OrganizationID Product States
1 football DC
1 football VA
1 football MD
2 football CA
3 football NV
3 football CA

 

My desired output data should look like 

OrganizationID Product States
1 football DC, VA, MD
2 football CA
3 football NV, CA

 

View solution in original post


All Replies
Solution
‎10-18-2017 10:41 PM
Super User
Posts: 24,014

Re: How to create a character string that list character values for one column

[ Edited ]
Posted in reply to LisaYIN9309

There are several options, you can use a data step with BY group processing or you can TRANSPOSE and then use CATX(). 

 

https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a

 


LisaYIN9309 wrote:

So I've came across this task with a big dataset, I've make two brief tables to sum the core of this problem. I know if it's a very small table, I can just manually add more columns for each row to list all states for each organization ID and then use TRIM or CATX, but with big data, I couldn't figure out how to do it systematically, since the # of states an organization operates in varies, and the values are stored in different rows of the same column, rather than in different columns of the same row.

 

What I want to do is to bring States values to a higher level, as an aggregate of all states that this organization operate in. 

 

My input data looks like:

OrganizationID Product States
1 football DC
1 football VA
1 football MD
2 football CA
3 football NV
3 football CA

 

My desired output data should look like 

OrganizationID Product States
1 football DC, VA, MD
2 football CA
3 football NV, CA

 

Contributor
Posts: 38

Re: How to create a character string that list character values for one column

Thank you Reeza, this is great!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 108 views
  • 0 likes
  • 2 in conversation