Need homework help!

[ASchmit13]

So I have a data set with Employees, some other information, and then a column for Recipients. The Recipients column contains 2 charities separated by a comma, the task is to create a new dataset that has each charaity in its own row, so there would be two lines for each Employee ID, one for their first charity, one for their second. I can't figure out how to do this. Any insight?

[Astounding]

Just a few questions to get things rolling ...

You say you have a data set.  Do you have a SAS data set, or is the data in some other form?

Are you sure that there are at most 2 charities per Recipient?

Could the name of a charity include a comma, so that one Recipient value might actually contain more than 1 comma?

[ASchmit13]

It is a SAS data set. yes I am sure, but the charity names do contain commas as well as being separated by commas.

For example one row is:

Disaster Assist, Inc. 50%, Cancer Cures, Inc 50%

[ballardw]

You can post example data by creating a data step. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

The post what you have tried.

Is that "ROW" in your SAS data set or a text file you are attempting to read?

[ballardw]

There are number of ways to accomplish this. Most will involve finding the two values of charity, which can be found using the function SCAN in a datastep.

Use of OUTPUT statement coupled with either a KEEP or DROP list would likely accomplish what you need.

Since this is admitted homework it would be best to try something and tell use what you have tried and if it doesn't work to ask specifically what didn't. That way you'll get a bit more learning from the exercise than if we show you a complete solution.

What you are describing is also known as a transpose and SAS provides a procedure called Transpose that could work when the data is structured correctly.

[ASchmit13]

I would like insight on how to separate based on the fact that they do contain mulitple commas, and as well as that taking each charity and creating a new row. I have tried the substr, and index functions to separate the charities, with a comma deliminator, but with commas included in the names it is diifcult.

[Astounding]

It sounds like you are already familiar with the right tools required.  It's a question of finding the patterns.  For example, can you use the INDEX function to search for the string "%,"?  Will that string always be there when there is more than one charity?  (If not, what are the other possibilities?)

[Reeza]

---

@ASchmit13 wrote:

So I have a data set with Employees, some other information, and then a column for Recipients. The Recipients column contains 2 charities separated by a comma, the task is to create a new dataset that has each charaity in its own row, so there would be two lines for each Employee ID, one for their first charity, one for their second. I can't figure out how to do this. Any insight?

---