BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LisaYIN9309
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

2 REPLIES 2
Reeza
Super User

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

 

LisaYIN9309
Obsidian | Level 7

Thank you Reeza, this is great!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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