Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Separating data in a comma delimited column into individual rows in SAS

Posted on 2016-07-17
1
Medium Priority
?
83 Views
Last Modified: 2016-07-21
Experts,

I have a macro that takes multiple rows and puts them into one comma delimited column:
Example:
Code              Color      
  001               Red
  001               Blue
  001               Green
  001               Yellow
Becomes:

001                   Red, Blue, Green, Yellow

Can anyone tell me how I can take a column that is comma delimited and split the values out to a separate rows:
Code  Lit
 001    Red, Blue, Green, Yellow
Becomes:
Code        Color
001           Red
001           Blue
001          Green
001          Yellow

I would like to do this in SAS
0
Comment
Question by:morinia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 8

Accepted Solution

by:
ShannonEE earned 2000 total points
ID: 41716314
Hi there  morinia,

Is this homework?

====

I suggest you code this up with a data step.

For each row you just need to read in the column (which you call lit) and break into pieces.

To to that you probably will need a  do - while  loop or a do until loop.

For each piece (inside the loop), assign the piece to a variable called color and then code the statement
output;
to produce an output record for that color.

Useful SAS functions for splitting into pieces could include some of

find()
findw()
index()
indexw()
lengthN()
substr()
translate()


look them up in

base sas function reference   (url is  support.sas.com/documentation/cdl/en/lefunctionsref/67960/HTML/default)/viewer.htm#titlepage.htm)

and decide which of them you will use.  You will probably will need to use at least 2 different functions to look for the comma between items (you may need to be careful about blanks), determine the start, end and length of the piece and finally assign that piece across to the color variable.

Ian
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whoever said that “a picture is worth one thousand words” observed a fact that can dramatically affect your marketing success. Most people tend to learn visually, so many publishers commonly acknowledge the effectiveness of visual learning by using…
On September 18, Experts Exchange launched the first installment of the Help Bell, a new feature for Premium Members, Team Accounts, and Qualified Experts. The Help Bell will serve as an additional tool to help teams increase question visibility.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question