topic Re: How to create a character string that list character values for one column in SAS Programming

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

LisaYIN9309 — Thu, 19 Oct 2017 02:06:52 GMT

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

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

Reeza — Thu, 19 Oct 2017 02:18:11 GMT

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

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

LisaYIN9309 — Thu, 19 Oct 2017 02:41:04 GMT

Thank you Reeza, this is great!